I'll make a full report on our recent work soon, probably next week, but in the meantime I wanted to share the performance improvement we're seeing after switching hivemind from the python based server to postgREST.
We've had several devs working on this task for a few months now; it was a big job as there was a lot of python code in the "server" portion of hivemind that needed to be replaced by SQL code in order for us to switch to using postgREST as hivemind's web server.
But the performance improvements seem well worth it: of the API calls we've benchmarked, we're generally seeing speedups between 3-4x faster on average. We had a few regressions where we still need to tweak the SQL code to get optimal performance (e.g. get_discussions_by_created, get_following), but for our very first benchmark of the old and new code, I'm quite pleased.
Here's the old/new benchmark data below. The median column shows the median time for the API call to complete and is probably one of the most useful figures of merit for comparing the two servers.
Generally speaking, I think the most noticeable speedup will be for "quick" API calls, due to the reduced fixed-overhead latency of postgREST. But we can see cases where just the replacement of python code with equivalently-functioning SQL has resulted in good speedups as well.
Old Python-based Server:
| Endpoint | Max [ms] | Min [ms] | Average [ms] | Median [ms] |
|---|---|---|---|---|
| condenser_api.get_discussions_by_blog | 7311 | 115 | 2399 | 2399 |
| bridge.get_account_posts | 6237 | 55 | 1011 | 897 |
| bridge.get_discussion | 15793 | 3 | 1610 | 206 |
| bridge.get_ranked_posts | 2511 | 226 | 893 | 862 |
| condenser_api.get_discussions_by_comments | 883 | 5 | 384 | 430 |
| condenser_api.get_followers | 387 | 21 | 85 | 63 |
| condenser_api.get_discussions_by_created | 2590 | 49 | 562 | 255 |
| bridge.get_profile | 1102 | 349 | 574 | 531 |
| condenser_api.get_discussions_by_feed | 2206 | 1411 | 1883 | 1915 |
| condenser_api.get_blog | 4482 | 1070 | 3299 | 3564 |
| condenser_api.get_following | 362 | 167 | 221 | 201 |
New PostgREST Server:
| Endpoint | Max [ms] | Min [ms] | Average [ms] | Median [ms] |
|---|---|---|---|---|
| condenser_api.get_discussions_by_blog | 2858 | 66 | 638 | 669 |
| bridge.get_account_posts | 5402 | 9 | 344 | 252 |
| bridge.get_discussion | 36736 | 1 | 2705 | 50 |
| bridge.get_ranked_posts | 5041 | 106 | 431 | 233 |
| condenser_api.get_discussions_by_comments | 277 | 1 | 102 | 111 |
| condenser_api.get_followers | 121 | 11 | 48 | 19 |
| condenser_api.get_discussions_by_created | 28137 | 30 | 2352 | 1266 |
| bridge.get_profile | 1079 | 308 | 559 | 455 |
| condenser_api.get_discussions_by_feed | 722 | 479 | 599 | 588 |
| condenser_api.get_blog | 1376 | 323 | 885 | 914 |
| condenser_api.get_following | 1352 | 246 | 1053 | 1211 |
Next up, production testing
Note these are results are from a synthetic benchmark we've created, not from real world traffic testing.
After we fix the API calls with performance regressions, we'll setup a server with the optimized code and direct a copy of production traffic to it as a final test.
Thank you very much for the outstanding work of your team. I believe that the deployment of hivemind based on postgREST will bring great improvements to the front-end site and various service programs.
In addition, I guess switching from Python code to SQL code should greatly improve maintainability, right?
Yes, it'll be easier to optimize the queries. Previously, performance issues could be in either the python or the SQL portion of the code. Now that it is all in the SQL code, we can just do
explainon the queries and fully analyze the reasons for the performance.It was worth the effort! That's a significant speedup on most operations, and, you said you'll continue to tweak the SQL code in the case of the few operations that are slower now than with Python code.
Yes, I expect we will have those fixed in the new few days.
Nice, this speedup is more than I would have expected. I really enjoy working with postgREST and the many benefits it brings - performance, security, simplification, flexibility.
Do you plan to use the postgREST native filtering via URI params? It can save a tremendous amount of work since each endpoint can have arbitrary filtering without needing to manually write-up the validation and logic for each parameter. I'm sure you already know this. But if you decide to use it, you have to follow particular steps in order for your function to be inlined and to have good performance: check this Q&A with the postgREST devs.
Regardless of whether you go for the filtering or not, since you are using plpgsql functions for the endpoints (just like I was), you can get substantial performance improvements just from switching to pure SQL and making the functions STABLE.
I wasn't aware that such filters could be used on anything except exposed tables and views, so it was interesting to learn it could be applied to functions, thanks for the link.
We'll experiment more with such features moving forward as we move away from the json RPC API to the new REST-based API. We haven't even started creating a REST API for hivemind yet, because first we wanted to replace the python server to postgREST.
Impressive. I am particularly interested in the bridge.get_account_posts speeds. Hopefully, this will allow for faster sorting of posts in different orders. It would be great to have the option to sort from First to latest.
Having all the code in pure SQL versus a mix of Python and SQL will definitely make it easier for us to optimize the API calls further in the future.
Didn't know about that technology. We use PostgreSQL, si knowing about this is pretty interesting. Curious about the connection pooling limits though...
PostgREST is a web server that sits in front of your Postgres database. It has built-in support for a connection pool with configurable number of maximum connections: https://docs.postgrest.org/en/v12/references/connection_pool.html
Those are some nice improvements. I notice a couple got slower, but what tends to matter is how often each is called. We need Hive to scale well as it grows. I am impressed with how small the machines some witnesses use are. We need to publicise more that Hive does not require energy hungry server farms.
I'm not really involved in the publicizing side of Hive, but it does sound like a good idea.