5 ms·

A few months ago I wrote an article testing out the relative speed of Python's async options for web apis/sites (https://calpaterson.com/async-python-is-not-fas

A few months ago I wrote an article testing out the relative speed of Python's async options for web apis/sites (https://calpaterson.com/async-python-is-not-faster.html https://calpaterson.com/async-python-is-not-faster.html). My findings were a bit controversial at the time as I found that uWSGI + sync Python is about the best option, largely because replacing interpreted Python code with native code is a very significant factor in the performance of a Python program. In the following discussion (and private emails) I was stunned by the number of async enthusiasts who proposed raising the number of database connections in the connection pool by two or three orders of magnitude (I had 20 conns in a pool for a 4 cpu machine) for reasons along the lines of "the async app is starved of connections". In my opinion that suggestion betrays a misunderstanding of what is likely going on in the database when you have 100s or 1000s of connections all contending for the same data: in SQL even reads create locks. Async applications yield a lot, by design, and under hundreds or thousands of requests per second there is a considerable buildup of "work in progress" in the form of async tasks that were yielded from and which have not yet been returned to and completed. Many hundreds of database connections is going to create an enormous volume of bookkeeping on the database side and is very likely to slow things down to a absolute crawl. Even idle connections are known to have detrimental effect in postgres. Someone at Microsoft is apparently working on this and released this great blog post quantifying the problem: https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266 https://techcommunity.microsoft.com/t5/azure-database-for-po...

nine_k 6y ago
> in SQL even reads create locks. IIRC, of all popular SQL databases, only MySQL uses locks at all. Postgres, MS SQL, Oracle all use MVCC. (Edited: typo)
- hilbertseries 6y ago
  Innodb does not create locks for reads, unless in a transaction. > SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row. https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.htm...
  - calpaterson 6y ago
    > Innodb does not create locks for reads, unless in a transaction. Right...but I have a feeling that most libraries/frameworks put you in a transaction by default and just rollback at the end of the request lifecycle.
    - hilbertseries 6y ago
      I’ve worked with many libraries and frameworks and I haven’t seen transactions by default. Given how easy it is to deadlock yourself this way, for instance by naively making batch reads in random order. I doubt many libraries would make transactions the default. For instance in rails and django you need to explicitly specify a transaction. Can you give an example of a framework that turns off auto commit by default and instead runs it at the end of http requests?
- calpaterson 6y ago
  MySQM? MySQL presumably? Anyway MVCC is implemented via locks (among other things, like copying, transaction ids, visibility maps, etc). Regardless of that, my main point is that even reading requires transactional bookkeeping in the database - which some people don't realise. If you read a row and, do nothing and then rollback a few ms later (common in webapps) there is still bookkeeping to be done.
  - spacemanmatt 6y ago
    But it shouldn't result in a write to disk unless a page is dirtied.
    - calpaterson 6y ago
      I wasn't talking about writes but as an aside: I wouldn't bet on that. SQL databases are complicated and writes can happen for many reasons - if you manage to bloat the working set by reading more stuff concurrently (old rows, etc) it's not hard to imagine something having to be paged out as a consequence.
      - spacemanmatt 6y ago
        Quite agreed. It's still way harder and way less frequent for read activity to force much write activity, although not hard to imagine.
- csharptwdec19 6y ago
  MSSQL will lock on Reads by default, and can(will) escalate locks to entire pages for reads. Making MVCC Mode 'Opt In' requires enabling Snapshot Isolation, and making it default requires another change. It's also worth noting the way MSSQL does MVCC is far more TempDB/Memory hungry than Oracle/Postgres (due to the nature of using snapshots). I've been in more than one shop that did not want to enable it due to the memory requirements.
spacemanmatt 6y ago
> Even idle connections are known to have detrimental effect in postgres. I have a personal backlog task to evaluate whether connection multiplexing benefits the idle connection cost issue. I wish it could get more priority, as I'm super curious, but my load/volume are so low for my work that it just doesn't matter yet.
- calpaterson 6y ago
  > connection multiplexing What do you mean by that term?
karmakaze 6y ago
Idle connections did show up as taking substantial db CPU time on one MySQL (Rails/Ruby) cluster I administered. We had thousands of connections each making a 'ping' request to check the validity of the connection every second. That was enough context switching load to have an noticeable load.

nine_k 6y ago

> in SQL even reads create locks. IIRC, of all popular SQL databases, only MySQL uses locks at all. Postgres, MS SQL, Oracle all use MVCC. (Edited: typo)

hilbertseries 6y ago
Innodb does not create locks for reads, unless in a transaction. > SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row. https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.htm...
- calpaterson 6y ago
  > Innodb does not create locks for reads, unless in a transaction. Right...but I have a feeling that most libraries/frameworks put you in a transaction by default and just rollback at the end of the request lifecycle.
  - hilbertseries 6y ago
    I’ve worked with many libraries and frameworks and I haven’t seen transactions by default. Given how easy it is to deadlock yourself this way, for instance by naively making batch reads in random order. I doubt many libraries would make transactions the default. For instance in rails and django you need to explicitly specify a transaction. Can you give an example of a framework that turns off auto commit by default and instead runs it at the end of http requests?
calpaterson 6y ago
MySQM? MySQL presumably? Anyway MVCC is implemented via locks (among other things, like copying, transaction ids, visibility maps, etc). Regardless of that, my main point is that even reading requires transactional bookkeeping in the database - which some people don't realise. If you read a row and, do nothing and then rollback a few ms later (common in webapps) there is still bookkeeping to be done.
- spacemanmatt 6y ago
  But it shouldn't result in a write to disk unless a page is dirtied.
  - calpaterson 6y ago
    I wasn't talking about writes but as an aside: I wouldn't bet on that. SQL databases are complicated and writes can happen for many reasons - if you manage to bloat the working set by reading more stuff concurrently (old rows, etc) it's not hard to imagine something having to be paged out as a consequence.
    - spacemanmatt 6y ago
      Quite agreed. It's still way harder and way less frequent for read activity to force much write activity, although not hard to imagine.
csharptwdec19 6y ago
MSSQL will lock on Reads by default, and can(will) escalate locks to entire pages for reads. Making MVCC Mode 'Opt In' requires enabling Snapshot Isolation, and making it default requires another change. It's also worth noting the way MSSQL does MVCC is far more TempDB/Memory hungry than Oracle/Postgres (due to the nature of using snapshots). I've been in more than one shop that did not want to enable it due to the memory requirements.

spacemanmatt 6y ago

> Even idle connections are known to have detrimental effect in postgres. I have a personal backlog task to evaluate whether connection multiplexing benefits the idle connection cost issue. I wish it could get more priority, as I'm super curious, but my load/volume are so low for my work that it just doesn't matter yet.

calpaterson 6y ago
> connection multiplexing What do you mean by that term?

karmakaze 6y ago

Idle connections did show up as taking substantial db CPU time on one MySQL (Rails/Ruby) cluster I administered. We had thousands of connections each making a 'ping' request to check the validity of the connection every second. That was enough context switching load to have an noticeable load.