5 ms·

> The TL;DR; for my blog post you mention is that async itself usually has little to do with speed when talking to a database, because the database is typically

by 1st1 10y ago

> The TL;DR; for my blog post you mention is that async itself usually has little to do with speed when talking to a database, because the database is typically on the same network as your application, you're using typically no more than a few dozen connections per process (postgresql uses a process per connection, so using thousands of connections isn't feasible anyway), so the overhead of async and especially Python 3's async IO slows things down considerably without offering any opportunity to regain that speed. This simply isn't true for all use cases. If you have to manage 100-1000s of connections (some of each can be long-living sessions such as websockets) you have no other option but to use the async approach. To minimize the number of connections to the DB you should use a decent caching layer (redis/memcached will do). Once you have that, and a use a connection pool, you'll be able to do a lot more with asyncio than with threads. > The GIL doesn't block on IO so threads work just fine, and most applications are CRUD / transactional applications which means you can't spawn off several dozen queries within a transaction at the same time anyway, you need to wait for each one to complete serially regardless. If you have 10 threads and relatively low server load - the threads will work just fine. However GIL will make things really slow under load.

zzzeek 10y ago
> This simply isn't true for all use cases. Everytime I talk about my blog post, I am super careful to qualify: in this case, "usually has little to do w/ speed" and other qualifications. The blog post as well. But everytime, I get a response, "but that's not always TRUE!" We agree. It is not always true. But it is usually true :). > If you have to manage 100-1000s of connections Which I said, usually you are not. Postgresql connections are expensive. You do not want 100s-1000s. > some of each can be long-living sessions such as websockets you use websockets to talk to your Postgresql database? Where did I say I was talking about HTTP web services ? > However GIL will make things really slow under load. not IO load with a hundred threads or so. Thousands, sure. Otherwise no.
- 1st1 10y ago
  >> If you have to manage 100-1000s of connections > Which I said, usually you are not. Postgresql connections are expensive. You do not want 100s-1000s. I was talking about a micro-service that has to support a lot of connections and work with a DB. >> some of each can be long-living sessions such as websockets > you use websockets to talk to your Postgresql database? Where did I say I was talking about HTTP web services ? So you were talking about programs that don't communicate with the outside world except to a DB?
  - zzzeek 10y ago
    If you write a program that has thousands of persistent websockets open, you do not want all those thousands of connections to each have their own, persistent, long lived database transaction. You will not like your process listing nor will you be happy about the amount of table and row locking going on. Transactions and connection scope should be short lived, and if you're using a connection pool, you definitely are going for that. As my blog post stated, you'd use a thread pool for the database access part, given that the http-facing side is async.
  - 1st1 10y ago
    > If you write a program that has thousands of persistent websockets open, you do not want all those thousands of connections to each have their own, persistent, long lived database transaction. [..] Mike, I've never suggested to have a DB connection per HTTP request. Please re-read my other comment in this thread.
  - zzzeek 10y ago
    > Mike, I've never suggested to have a DB connection per HTTP request. right, so my suggestion is, keep the DB code in a thread pool. So you can use the async for your 1000-client frontend and not force it into the DB layer where it makes things much more complicated (or what I do, just use gevent :) ).
    - allendoerfer 10y ago
      Sorry to interrupt: You two can click on the timestamp to answer your posts. Please continue, we are listening carefully. And since I am already here @1st1: How hard would it be to create a synchronous version of your adapter?
- RedCrowbar 10y ago
  > Everytime I talk about my blog post, I am super careful to qualify: in this case, "usually has little to do w/ speed" and other qualifications. The blog post as well. I created an asyncpg suite for your bigdata benchmark (https://bitbucket.org/zzzeek/bigdata https://bitbucket.org/zzzeek/bigdata). Here are the results obtained from running it on my laptop against local PostgreSQL 9.5. 300 connections: Python3.5.2 threads -- avg 9798.16 recs/sec Python3.5.2 gevent -- avg 9677.84 recs/sec Python3.5.2 uvloop/asyncpg -- avg 11558.24 r/sec The modified suite is here: https://github.com/elprans/bigdata https://github.com/elprans/bigdata
  - zzzeek 10y ago
    that's great, you've written a very fast driver. My benchmarks are comparing psycopg2 to itself, under threads / gevent vs. asyncio, to show that asyncio is inherently slower. It follows that if you wrote your driver and all of its protocol improvements into a traditional blocking model, it would also be much faster. You've written something so fast that it overcomes the latency of asyncio (this whole thread is a huge plug for your driver, btw). It's not that the latency of asyncio is really that big of a deal. The point I try to make is, asyncio != speed. For databases, 95% of the time, it means, a little less speed. Not a big deal, but if you aren't into writing explicit context switching directives throughout 100% of your code when the OS does it for you just fine, it isn't worth it, unless you are in the zone of thousands of arbitrarily performing connections, which is really more of an incoming client connection thing, not usually a database thing.
    - RedCrowbar 10y ago
      > that's great, you've written a very fast driver. My benchmarks are comparing psycopg2 to itself, OK, I ran the asyncio suite (with uvloop event loop): Python3.5.2 asyncio+psycopg+uvloop -- avg 8356.84 recs/sec Really not that slower. There is also an important detail that you forget to mention: the threaded benchmark uses notably more CPU time (about 30% more).

by 1st1 10y ago

zzzeek 10y ago

> This simply isn't true for all use cases. Everytime I talk about my blog post, I am super careful to qualify: in this case, "usually has little to do w/ speed" and other qualifications. The blog post as well. But everytime, I get a response, "but that's not always TRUE!" We agree. It is not always true. But it is usually true :). > If you have to manage 100-1000s of connections Which I said, usually you are not. Postgresql connections are expensive. You do not want 100s-1000s. > some of each can be long-living sessions such as websockets you use websockets to talk to your Postgresql database? Where did I say I was talking about HTTP web services ? > However GIL will make things really slow under load. not IO load with a hundred threads or so. Thousands, sure. Otherwise no.

1st1 10y ago
>> If you have to manage 100-1000s of connections > Which I said, usually you are not. Postgresql connections are expensive. You do not want 100s-1000s. I was talking about a micro-service that has to support a lot of connections and work with a DB. >> some of each can be long-living sessions such as websockets > you use websockets to talk to your Postgresql database? Where did I say I was talking about HTTP web services ? So you were talking about programs that don't communicate with the outside world except to a DB?
- zzzeek 10y ago
  If you write a program that has thousands of persistent websockets open, you do not want all those thousands of connections to each have their own, persistent, long lived database transaction. You will not like your process listing nor will you be happy about the amount of table and row locking going on. Transactions and connection scope should be short lived, and if you're using a connection pool, you definitely are going for that. As my blog post stated, you'd use a thread pool for the database access part, given that the http-facing side is async.
- 1st1 10y ago
  > If you write a program that has thousands of persistent websockets open, you do not want all those thousands of connections to each have their own, persistent, long lived database transaction. [..] Mike, I've never suggested to have a DB connection per HTTP request. Please re-read my other comment in this thread.
- zzzeek 10y ago
  > Mike, I've never suggested to have a DB connection per HTTP request. right, so my suggestion is, keep the DB code in a thread pool. So you can use the async for your 1000-client frontend and not force it into the DB layer where it makes things much more complicated (or what I do, just use gevent :) ).
  - allendoerfer 10y ago
    Sorry to interrupt: You two can click on the timestamp to answer your posts. Please continue, we are listening carefully. And since I am already here @1st1: How hard would it be to create a synchronous version of your adapter?
RedCrowbar 10y ago
> Everytime I talk about my blog post, I am super careful to qualify: in this case, "usually has little to do w/ speed" and other qualifications. The blog post as well. I created an asyncpg suite for your bigdata benchmark (https://bitbucket.org/zzzeek/bigdata https://bitbucket.org/zzzeek/bigdata). Here are the results obtained from running it on my laptop against local PostgreSQL 9.5. 300 connections: Python3.5.2 threads -- avg 9798.16 recs/sec Python3.5.2 gevent -- avg 9677.84 recs/sec Python3.5.2 uvloop/asyncpg -- avg 11558.24 r/sec The modified suite is here: https://github.com/elprans/bigdata https://github.com/elprans/bigdata
- zzzeek 10y ago
  that's great, you've written a very fast driver. My benchmarks are comparing psycopg2 to itself, under threads / gevent vs. asyncio, to show that asyncio is inherently slower. It follows that if you wrote your driver and all of its protocol improvements into a traditional blocking model, it would also be much faster. You've written something so fast that it overcomes the latency of asyncio (this whole thread is a huge plug for your driver, btw). It's not that the latency of asyncio is really that big of a deal. The point I try to make is, asyncio != speed. For databases, 95% of the time, it means, a little less speed. Not a big deal, but if you aren't into writing explicit context switching directives throughout 100% of your code when the OS does it for you just fine, it isn't worth it, unless you are in the zone of thousands of arbitrarily performing connections, which is really more of an incoming client connection thing, not usually a database thing.
  - RedCrowbar 10y ago
    > that's great, you've written a very fast driver. My benchmarks are comparing psycopg2 to itself, OK, I ran the asyncio suite (with uvloop event loop): Python3.5.2 asyncio+psycopg+uvloop -- avg 8356.84 recs/sec Really not that slower. There is also an important detail that you forget to mention: the threaded benchmark uses notably more CPU time (about 30% more).