[zzzeek]

So on that topic, a request typically has a single transaction going out to the database so within the scope of the request, has to perform its steps in serial in any case. If it needs to make several requests to web services that aren't dependent on each other, that's an area where you can get into stacking them with some kind of concurrency construct (I'd pass it into a greenlet oriented worker pool). but this is already going to be a heavy web request with multiple web service calls.

[kazagistar]

> a request typically has a single transaction going out to the database

Its typical because people are still in a "single thread single transaction ORM crud" model of thinking. "Its linear because thats how it is"?

[zzzeek]

if you're using an ACID kind of database then yes, that's how it is :)

[makmanalp]

> this is already going to be a heavy web request with multiple web service calls

Sure, but it can now be a less heavy web request! ¯\_(ツ)_/¯

> a request typically has a single transaction going out to the database

The fact of the matter is, as applications develop, become richer, and grow larger, it becomes less and less uncommon to have more than one query per page. Especially in the context of larger organizations, it's very common to have everything wrapped behind a service call with an entire armada of infrastructure hidden behind it, and having to make many service calls to put together one web API result or page.

---

sigh Slight tangent. Look at where we are now and how we came here.

Back in the non-ajax days we used to do them all on the server side, then render the whole page all in one go. This would have come in handy back then! Imagine doing 5x 50ms queries asynchronously, dropping a 250ms response delay down to 50ms! But this stuff was hard back then, and we mostly left it alone.

This is also along the times when we figured out that since we can have pages that take a long time to load and block the interpreter, perhaps it's not such a great idea to serve many requests with a single interpreter, so people started using stuff like nginx to run multiple python interpreters in parallel (not even getting into threads here), which was easier to reason about since each python process is a separate universe that can block entirely, but overall we can still serve a new request with a new interpreter, so for the most part things are good.

Then the twisted people thought that this was silly, and why should we block in the first place, and they decided that the way to fix this was to change the way we program entirely, and re-create or wrap an entire ecosystem of software. It sort of worked, except there wasn't a good twisted package for your thing. But all in all it worked.

Then the greenlets (or one of its other 20 names) people came and wanted to instead use fine-grained implicit concurrency, and said "no no, we can get something with nicer abstraction packaging while mostly not changing the code we have", and that was even nicer, except when something didn't get monkey patched correctly for some reason. We got stuff like gunicorn, which was impressive.

Then as we moved more stuff to the client to create more responsive (in the original meaning of the word) applications, so we pushed the burden of requesting and fetching data to the browser side, which means that as a page loads, it might call REST APIs one by one (hopefully asynchronously!), each of which might make a single (finer-grained) database or service call behind the scenes.

So how different is this now from the gunicorn model? In the latter, you get fine threads of control, each working asynchronously to fetch their own thing, which gets put together in the server side, and then sent back to the client. In the former, you get similarly fine threads of control, but the fine threads perhaps live in their own universes, and it doesn't get all put back together until it travels over the internet to the browser.

So it's a little bit different, but overall what's happening is similar. It feels like we just keep moving concerns and procedures up and down the stack.

Surely there's reasons for all this. Times and technologies change, and we find ways to adapt. I like the "async" stuff because it makes things explicit. It's the middle-ground result of the culmination of our learnings that hiding async behavior makes libraries hard to design and can result in frustrating and unpredictable behavior, whilst changing the entire programming model isn't great either. So we get asyncio. I'm mostly happy with this result. Admittedly this article isn't doing any of this justice.

[zzzeek]

> it becomes less and less uncommon to have more than one query per page.

I said transaction, not query. A database transaction is on a single connection at a time and queries are performed via the transaction serially.

[coldtea]

>I said transaction, not query. A database transaction is on a single connection at a time and queries are performed via the transaction serially.

Unless you're doing e-commerce or banking sites, that's far less common that non-transaction requests.

[zzzeek]

unless you're using MyISAM or something like that, all your queries are in transactions.

edit: also, I'd challenge you to prove that for a web request that needs to make ten read queries to a relational database, from Python, that you can get better performance by opening up ten separate database connections (or from a pool) and running one query in each, bundled into the async construct of your choice and then merging them all back into your response, vs. just running ten queries on a single connection in serial. Assume these are not slow reporting-style transactions, just the usual "load the users full name, load the current status, load the user's current items", etc., small queries common in a web request that is looking for a very fast response with ten SQL queries.

Note that at the very least, it means your web application needs to use ten times as many database connections for a given set of load. In database-land that's more or less crazy.

[makmanalp]

Sorry - I wasn't clear enough. Who says it's one single relational database? And besides, like I mention, it's often not relational database queries but a service calls (think microservice architectures, for example). Or both!

Anyway, I respect your position that yes, for the average user, throwing a bunch of "async" in there isn't going to make their code faster, and it's just cargo cult programming. And yes, there is some tradeoff curve where sometimes, for a small benefit, it's not worth the effort to worry about it, as with all things. But it's just a tough sell to argue that no one should need this :-)

More and more often today, the backend serves as glue between frontend clients and a horde of services / data systems. This is often an I/O heavy workload (wait while I make a request, wait for a response, wait while I download x10). This kind of workload is ripe for speeding up with async. That's all I'm saying!

[merb]

It's not uncommon to have ~20 pooled connections lying around. Maybe it's not that frequently used in Python or PHP, but in various other platforms, that's just the normal case.

At least in Java, C#, Golang. And even psycopg2 offers a Pooling Abstraction (I guess it's not used in Django, but SQLAlchemy offers that aswell) But of course running a blocking driver atop a non-blocking framework does not give the best performance.

However just challenging it without proof is not really that useful.

Also some workloads are better for Threaded Servers while others are better in Async Fashion, it's also highly unlikely that just wrapping your Database connection in a Async function that it will be faster or better suited for a async workload. If you are not non-blocking from the ground up you will still carry a lot of overhead around.

[zzzeek]

> It's not uncommon to have ~20 pooled connections lying around. Maybe it's not that frequently used in Python or PHP, but in various other platforms, that's just the normal case.

OK but you're doing....500 req/s let's say, so, if base latency is 50ms, you're going to have at least 25 requests in play at once, so that's 500 database connections. That's one worker process. If your site is using....two app servers, or your web service has multiple worker processes, or etc., now you have 1000, 1500, etc. database connections in play at capacity. This is a lot. Not to mention you'd better be using a middleware connection pool if you have that many connections per process to at least reduce the DB connection use for processes that aren't at capacity.

On MySQL, each DB connection is a thread (MariaDB has a new thread pooling option also), so after all the trouble we've gone to not use threads, we are stuck with them anyway. On Postgresql, each DB connection is a fork(), and they also use a lot of memory. In both of these cases, we have to be mindful of having too many connections in play for the DB servers to perform well. We're purposely using many, many more DB connections than we need on the client side to try to grab at some fleeting performance gain by stacking small queries among several transactions/connections per request which is not how these databases were designed to be used (a DB like Redis, sure, but an RDBMS, not so much), and on the client side, I still argue that the overhead of all the async primitives is going to be in a very tight race to not be ultimately slower than running the queries in serial (plus the code is much more complicated), and throughput across many requests is reduced using this approach. Marginal / fleeting gains on the client vs. huge price to pay on the server + code complexity + ACID is gone makes this a pretty tough value proposition.

Postgresql wiki at https://wiki.postgresql.org/wiki/Number_Of_Database_Connecti...: "You can generally improve both latency and throughput by limiting the number of database connections with active transactions to match the available number of resources, and queuing any requests to start a new database transaction which come in while at the limit. ". Which means stuffing a load of connections per request means you're limiting the throughput of your applications....and throughput is the reason we'd want to use non-blocking IO in the first place.

> However just challenging it without proof is not really that useful.

this is all about a commonly made assertion (async == speed) that is never shown to be true and I only ask for proof of that assertion. Or maybe if blog posts like this one could be a little more specific in their language, which would go a long way towards bringing people back into reality.

[merb]

not everything is transaction centric. and also before I make a transaction I mostly fetch various stuff before and sometimes after.

and also my count example, it just makes no sense to have the count and the list data called inside a transaction (ok there are cases, but these are way more rare, because mostly It's not to bad to give users a wrong count, you don't need strict Serializability)

[zzzeek]

see my edit at https://news.ycombinator.com/item?id=14218862 where I propose a challenge to show that it's more efficient to use ten relational database connections for a request that needs to run ten small queries, vs running ten queries on a single DB connection.

[dagw]

In many non trivial cases I tend to find that I have to query several different databases to render a single page.

[zzzeek]

depending on the latency of those database connections I've argued in the past that the overhead of adding asyncio context switching and boilerplate is more expensive than just hitting the two or three databases in serial (and if your web request is having to hit dozens of DB sources to serve one request, I think you've already lost the performance game :) ). When your one web request is contending with many other concurrent web requests in any case, doing the DB calls in serial just lets the CPU attend to other requests.

[vittore]

Do you think it make more sense to do async backend when we are moving to real-time ( meaning websocket-based connections ) web apps?

[zzzeek]

I've maintained that async is better suited towards web services and lightweight databases like redis, and is not useful for relational databases. However, it's very hard to get async to make your code actually "faster", as opposed to just handling very high throughout with less resources. If people stop saying "faster!", I'll go away.