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.
I created an asyncpg suite for your bigdata benchmark (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
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.
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).
> 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?
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 :) ).
And since I am already here @1st1: How hard would it be to create a synchronous version of your adapter?
Protocol is implemented in two layers -- the base layer is IO and framework independent. The higher level is designed for asyncio.
Long story short, it's possible to add a synchronous version, but it still will require a lot of work. The API will also have to be split in two.
Well, I like what bountysource.com and others are doing, maybe we will get there.
Do not want to sound ungrateful, though. Thank you for your work also on you other contributions. Would definitely use this when applicable. Are more performance related projects like this and uvloop planned?
We'll consider doing that. We have to keep in mind that supporting two IO modes will add a lot of maintenance overhead. Anyways, we can certainly continue the discussion on GH, and maybe we can find a champion to do the heavy-lifting.
> Do not want to sound ungrateful, though. Thank you for your work also on you other contributions. Would definitely use this when applicable. Are more performance related projects like this and uvloop planned?
Thank you.
Right now most of our resources are consumed by getting EdgeDB alpha out as soon as possible. We also have plans to create a micro web framework based on httptools and few other exciting ideas. Stay tuned :)
Also check out PEP 525 -- another cool thing we're trying to make happen in Python 3.6.
Looks like a totally valid suggestion for folks who write async code and want to continue to use SQL Alchemy.
However, if you don't use SQL Alchemy (or another ORM) and just want to work with the DB directly in an async/await code base, having a threadpool will only needlessly complicate things.
Mike, I've never suggested to have a DB connection per HTTP request. Please re-read my other comment in this thread.
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.