Asynchronous Python and Databases

Async has never been about speed. Cooperative multitasking doesn't magically gzip your instruction pipeline or something (actually CM is suboptimal). It's just the only tool for those of us who want to avoid giving the keys to arguably the most important subsystem in the kernel to agents outside our control: The scheduler.

Ever tried to ssh into a one-thread-per-connection setup under heavy load? Assuming you managed to log in, it'll be very very difficult to get htop to execute when it's competing with 2k other processes for cpu time.

So, spawning a thread for every incoming connection request is a bad idea. But what about spawning a thread for every outgoing database connection?

The memory overhead of a thread compared to a tcp socket may be significant, but it's nothing compared to a database connection and all the other resources tied up serve that one database query. And best of all, you can refuse to setup a database connection without upsetting your user and e.g. serve from cache instead, but you can't refuse an incoming connection request.

According to the Postgres guys [1]: "A formula which has held up pretty well across a lot of benchmarks for years is that for optimal throughput the number of active connections should be somewhere near ((core_count * 2) + effective_spindle_count)"

Assuming you got two tablespaces on two raid 1 arrays on a 16 core machine, that's 16 * 2 + 2 = 34 threads plus the event loop thread, maximum.

According to those numbers, there doesn't seem to be much stopping you from running your data access logic within a thread.

[1]: https://wiki.postgresql.org/wiki/Number_Of_Database_Connecti...

"Ever tried to ssh into a one-thread-per-connection setup under heavy load? Assuming you managed to log in, it'll be very very difficult to get htop to execute when it's competing with 2k other processes for cpu time."

Nothing about your comment is compatible with my experience. It is certainly not true that a Linux box with 2000 threads blocked on i/o will be having any sort of bad time. If you're really got 2000 threads competing for CPU time then your problem transcends execution architecture: you've simply admitted more work than you can reasonably discharge. Neither threads nor callbacks can solve this problem for you.

Also I'm not sure why you think that the kernel thread scheduler is all that good, or why it shouldn't be considered "outside our control." For my users the kernel thread scheduler is just another black object inside a dark box. The standard scheduler is pretty good for general purposes but I doubt its optimal for any particular case. In some loads it might be useful to yield to a specific thread that you think is holding a mutex your thread needs to acquire. This is cooperative multitasking, basically.

Ultimately, those queries you want to batch are either:

1. Not critical to the logic of your app and thus should be fairly obvious candidate for caching.

2. Critical to the logic of your app and quite possibly done within a specific DB connection / transaction for locking reason.

Trying to be too clever and parallel often flounders on mundane things like running out of DB connections.

> It is certainly not true that a Linux box with 2000 threads blocked on i/o will be having any sort of bad time.

You and I must have very different perceptions about the way a server is "having a bad time" :)

I was assuming they were at various stages of processing an incoming request, which means they were blocked on either legitimate disk i/o or swapping. It's very difficult to log in even locally in that case, because the login process can't read /etc/passwd in time.

> Also I'm not sure why you think that the kernel thread scheduler is all that good, or why it shouldn't be considered "outside our control.

I was just trying to say that it's dangerous to give non-trusted peers big influence on the way the scheduler behaves.

if you want to send out a series of long-reply SQL calls and wait for them all in batch, that is doable with Postgresql's async support, but they'd all be on distinct database connections, so you wouldn't get transactional consistency between these calls, but maybe that's not important. You can do the same thing with threads but it would mean you'd need to spin up that many threads, but at least would be something you could test in the short term to see if it is in fact feasible.

The rudimental SQLAlchemy-emulation system within aiopg right now can probably accommodate this use case but it is Postgresql specific. "Legacy ERP system" sounds like there's some different database in play there, if you are relying upon closed-source drivers you'd have to get access to a non-blocking API within them. Else you're stuck with threads.

acveilleux's point about caching here is very relevant and I thought of that also. if these are indeed independently read sets of fairly non-changing data, pulling it from a cache is the more traditional approach to reducing latency (as well as the need for 800+ database connections).