4 ms·

> 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

by zzzeek 10y ago

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

dagw 10y ago
In many non trivial cases I tend to find that I have to query several different databases to render a single page.
- zzzeek 10y ago
  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 10y ago
    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 10y ago
      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.
merb 10y ago
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 10y ago
  see my edit at https://news.ycombinator.com/item?id=14218862 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.
coldtea 10y ago
>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 10y ago
  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.
  - merb 10y ago
    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 10y ago

by zzzeek 10y ago

dagw 10y ago

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

zzzeek 10y ago
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 10y ago
  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 10y ago
    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.

merb 10y ago

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 10y ago
see my edit at https://news.ycombinator.com/item?id=14218862 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.

coldtea 10y ago

>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 10y ago
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.
- merb 10y ago
  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 10y ago