6 ms·

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 m

by orf 6y ago

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. I expect upping this number would have a positive effect on asyncio numbers because the only thing[3] this[4] is[5] measuring[6] is how many database connections you have, and is about as far from a realistic workload as you can get. 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 rather than a single DB query on a very contested pool. I'd be very interested to see how sync frameworks fare with that. 1. https://github.com/calpaterson/python-web-perf/blob/master/async_db.py#L9 https://github.com/calpaterson/python-web-perf/blob/master/a... 2. https://github.com/calpaterson/python-web-perf/blob/master/sync_db.py#L11 https://github.com/calpaterson/python-web-perf/blob/master/s... 3. https://github.com/calpaterson/python-web-perf/blob/master/app_aio.py#L8 https://github.com/calpaterson/python-web-perf/blob/master/a... 4. https://github.com/calpaterson/python-web-perf/blob/master/app_flask.py#L11 https://github.com/calpaterson/python-web-perf/blob/master/a... 5. https://github.com/calpaterson/python-web-perf/blob/master/app_sanic.py#L11 https://github.com/calpaterson/python-web-perf/blob/master/a... 6. https://github.com/calpaterson/python-web-perf/blob/master/app_starlette.py#L9 https://github.com/calpaterson/python-web-perf/blob/master/a...

the_mitsuhiko 6y ago
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.
- orf 6y ago
  > 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_mitsuhiko 6y ago
    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.).
    - orf 6y ago
      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.
  - zzzeek 6y ago
    Hi, take a look at my benchmarks from five years ago at https://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/ 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).
nik_s 6y ago
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 https://github.com/MagicStack/asyncpg
- ddorian43 6y ago
  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.
  - jaddison 6y ago
    Can you share more information on this (articles, etc)?
    - ddorian43 6y ago
      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 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).
jordic 6y ago
Absolutely agree, sum to this the quality of the driver aiopg vs asyncpg...
calpaterson 6y ago
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.
- supermatt 6y ago
  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.
  - arghwhat 6y ago
    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.
  - a1a1a1a1a1a1 6y ago
    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?
bildung 6y ago
> 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."
- tmstieff 6y ago
  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.mailinator.com/tymaPaulMultithreaded.pdf 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.
- thefreeman 6y ago
  That is talking about WSGI worker processes. OP is talking about database pool connections. They are not the same thing.
kissgyorgy 6y ago
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.
anentropic 6y ago
> 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.
- anentropic 6y ago
  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
- strbean 6y ago
  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.

by orf 6y ago

the_mitsuhiko 6y ago

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.

orf 6y ago
> 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_mitsuhiko 6y ago
  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.).
  - orf 6y ago
    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.
- zzzeek 6y ago
  Hi, take a look at my benchmarks from five years ago at https://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/ 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).

nik_s 6y ago

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 https://github.com/MagicStack/asyncpg

ddorian43 6y ago
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.
- jaddison 6y ago
  Can you share more information on this (articles, etc)?
  - ddorian43 6y ago
    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 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).

jordic 6y ago

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

calpaterson 6y ago

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.

supermatt 6y ago
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.
- arghwhat 6y ago
  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.
- a1a1a1a1a1a1 6y ago
  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?

bildung 6y ago

> 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."

tmstieff 6y ago
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.mailinator.com/tymaPaulMultithreaded.pdf 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.
thefreeman 6y ago
That is talking about WSGI worker processes. OP is talking about database pool connections. They are not the same thing.

kissgyorgy 6y ago

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.

anentropic 6y ago

> 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.

anentropic 6y ago
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
strbean 6y ago
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.