Hacker News Reader

Hi - as mentioned in the article all connections went through pgbouncer (limited to 20) and I was careful to ensure that all configurations saturated the CPU so I'm pretty confident they were not waiting on connections to open. Opening a connection from pgbouncer over a unix socket is very fast indeed - my guess is perhaps a couple of orders of magnitude faster than without it. 20 connections divided by 4 CPUs is a lot, and pretty much all CPU time was still spent in Python.

Sidenote here: one thing I found but didn't mention (the reason I put in the pooling, both in Python and pgbouncer) is that otherwise, under load, the async implementions would flood postgres with open connections and everything would just break down.

I think making a database query and responding with JSON is a very realistic workload. I've coded that up many times. Changing it to make requests to other things (mimicking a microservice architecture) is also interesting and if you did that I'd be interested to read your write up.

Aren't you still capping the throughput by the query rate of your connection pool though? By limiting that, you are limiting the application as a whole - i.e. your benchmark is bound by the speed of your database, and has (almost) nothing to do with the performance of a specific python implementation.

Only if there are spare resources left to saturate the connection pools, which didn't seem to be the case.

If the system as a whole is well saturated, and the python processes dominate the system load with a DB load proportional to the requests served, then I don't think we would hit any external bottlenecks.

The benchmarks performed are not that great (e.g., virtualized, same machine for all components, etc.), but I don't think the errors are enough to throw off the result. Note, of course, that such results are not universal, and some loads might perform better async.

If the benchmark is bound by the database speed, wouldn't the expected result be that all implementations returned roughly the same number of requests per second?

>Sidenote here: one thing I found but didn't mention (the reason I put in the pooling, both in Python and pgbouncer) is that otherwise, under load, the async implementions would flood postgres with open connections and everything would just break down.

Doesn't this prove that async is waiting for connections when you put a limit on it? The only way async wins is if it is free to hit the db whenever it needs to.

But why async is spending so much CPU if it just waits?

Who knows. The point is, if when not restricted you get a ton of db connections, then any restriction on that almost definitely means you are imposing a bottle neck. The only way this would not be the case is if it was trying to create db connections when it didn't need them, unlikely.

So the CPU and database are the bottlenecks not async Python.

The benchmark is certainly flawed, but I don't see how you can jump to that conclusion.

> His async code creates a pool with only 10 max connections[1] (the default). Whereas his sync pool[2], with a flask app that has 16 workers, has significantly more database connections.

And the reasoning is explained in the article:

"The rule I used for deciding on what the optimal number of worker processes was is simple: for each framework I started at a single worker and increased the worker count successively until performance got worse."

Seems many commenters missed this statement. It's also troubling how common it is to hear assertions that async is king especially on projects where your future scale is unknown. Based on https://web.archive.org/web/20160203172420/https://www.maili... presentation, it looks like there is a stronger case for a sync model as the default.

That is talking about WSGI worker processes. OP is talking about database pool connections. They are not the same thing.

> Change your app to make 3 parallel requests to httpbin, collect the responses and insert them into the database. That's an actually realistic asyncio workload

I don't see how that is a more "realistic" asyncio workload.

It might be a workload that async is better suited for, but the point of the article is to compare async web frameworks, which will often be used just to fetch and return some data from the db.

If you had an endpoint which needed to fetch 3 items from httpbin and insert them in the db it may make sense to use asyncio tools for that, even within the context of a web app running under a sync framework+server like Falcon+Gunicorn.

In my experience Python web apps (Django!) often spend surprisingly little time waiting on the db to return results, and relatively a large amount of time cpu-bound instantiating ORM model instances from the db data, then transforming those instances back into primitive types that can be serialized to JSON in an HTTP response. In that context I am not surprised if sync server with more processes is performing better. In this test it's not even that bad... the 'ORM' seems to be returning just a tuple which is transformed to a dict and then serialized.

I should add that when I said above "I am not surprised if sync server with more processes is performing better"... that's only after reading this article and thinking about it

until then I'd had pretty much bought the hype that the new async frameworks running on Uvicorn were the way to go

I'm very glad to see this kind of comparative test being made, it's very useful, even if it later gets refined and added to and the results more nuanced

This raises an interesting point - async is less well suited to languages that are, well, slow as molasses. If your language is so slow that basic operations dominate even network IO, you're not going to gain much.

He only has 4 CPUs. I doubt rising the worker count is going to help the async situation. From my experience it’s really hard to make async outperform sync when databases are involved because the async layer adds so much overhead. Only when you are completely io bound with lots of connections does async outperform sync in python.

> From my experience it’s really hard to make async outperform sync when databases are involved because the async layer adds so much overhead

Highly disagree as the database is just another IO connection to a server, which is asyncio bread and butter. Being able to stream data from longer running queries without buffering and whilst serving other requests (and making other queries) is really quite powerful.

But yeah, if you're maxing out your database with sync code then async isn't going to make it magically go faster.

The database is mostly just idle IO. You send a query and then you wait for results. That’s something sync python is decent at because when you wait for that IO the GIL is released. The situation is different if there is a lot of activity on the epoll/kqueue etc. (connects, data ready etc.).

Apologies - I completely misread your initial comment. Yeah that's correct.

Despite this I think it's quite rare to hit this limit, at least in the orchestration-style use cases I use asyncio for. With those I value making independent progress on a number of async tasks rather than potentially being blocked waiting for a worker thread to become available.

Hi, take a look at my benchmarks from five years ago at https://techspot.zzzeek.org/2015/02/15/asynchronous-python-a.... The extra variable with Python is that it's a very CPU-heavy interpreted language and it's really unlikely for an application to be significantly IO bound with a database server on the same network within the realm of CRUD-style queries. asyncio was significantly slower than threads (noting they've made performance improvements since then) and gevent was about the same (which I'm pretty sure is close to as fast as you can get for async in Python).

Before you criticize the article, you should read it. He wrote a whole section about the specific worker numbers and why and how he choose them.

On top of that, the author uses aiopg rather than asyncpg[1] for the async database operations, even though asyncpg is (allegedly) a whole lot faster.

1. https://github.com/MagicStack/asyncpg

asyncpg is not scalable. It can only do "session pooling" because it needs advisory_locks, listen/notify, which will end up needing a lot of Postgresql connections.

Can you share more information on this (articles, etc)?

There is no 1 article to explain but you can research each part.

1. One Postgresql connection is a forked process and has memory overhead (4MB iirc) + context switching.

2. A connection can only execute 1 concurrent query (no multiplexing).

3. Asyncpg to be fast, uses the features that I mentioned in my parent post. Those can only be used in Session Pooling https://www.pgbouncer.org/features.html.

The whole point of async is to some other work while waiting for a query (ex a different query).

If you have 10 servers with 16 cores, each vcore has 1 python process, each python process doing 10 simultaneous queries. 10 * 16 * 10 = 1600 opened connections.

The best way IMHO: Is to use autocommit connections. This way your transactions execute in 1 RPC. You can keep multiple connections opened with very light CPU and pooling is best.

I've done 20K short lived queries/second from 1 process with only ~20 connections opened in Postgresql (using Pgbouncer statement pooling).

Absolutely agree, sum to this the quality of the driver aiopg vs asyncpg...