back
6 comments
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).

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

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

> connection multiplexing

What do you mean by that term?

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

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.

But it shouldn't result in a write to disk unless a page is dirtied.
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.

Quite agreed. It's still way harder and way less frequent for read activity to force much write activity, although not hard to imagine.
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.htm...

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

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?

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.

When I was working at a large online travel website a few decades ago, we had a few hundred front-end boxes each with a connection pool set to (I think) around 10 each - so around 2000 connections on the DB backend. We did some profiling and discovered that, under the heaviest load, we never had more than one connection active per web server (and usually 0), so a single shared DB connection would have been just as effective as the pool was.
For a read-only load, right? If there were writes and transactions I don’t see how that would work.
Hm - it's been almost 20 years, so my memory is a bit hazy, but I seem to recall that there were (relatively infrequent) writes. But definitely no transactions: just grab a connection, insert/update some data, return it to the pool.
>But definitely no transactions: just grab a connection, insert/update some data

Inserts/updates require transactions. How did you escape having transactions?

I think latency also matters a lot for client-side DB connection pooling. If the DB is a 10ms round-trip away, and queries take 10ms, the process on the DB side is only really busy half the time.
Doesn't all of this become mindbogglingly complex once you factor in a) changing replica counts (connection count changes in steps of up to the max pool size) and b) multiple applications using the same DB hardware?
Ish but not really. Once you realize your DB is all about resource constraints and connections aren't free then the optimization becomes pretty simple. Use as few connections as possible to get whatever work you need done done.

Once you view connections as a net cost to the system the math becomes simple. The network isn't infinite, DB hardware isn't infinite. At the end of the day, more connections == more load on the system as a whole due to management.

This will also lead you towards good DB management. If you want to be able to scale and respond to things, then the right thing to do is keep you Datasets as small as possible and stop multiple apps from using the same DB hardware. Use a fleet of dbs rather than one big db and you'll have a lot better time of scaling.

I say this as someone that currently works at a company where we have everything on a small set of big DB hardware. We are currently running into all the problems of hardware and network constraints that are causing us major issues.

This article helped me to reduce connections pool from 80 to 10 per box. Which helped to serve traffic spikes from couple requests per second to thousands per second.
This formula `pool size = Tn x (Cm - 1) + 1` is really interesting. Any idea how it's derived or what branch of math this is from?
I think that's just arithmetic. The fundamental idea is that you have enough connections that you can guarantee that at least one thread has all the connections it needs - because if it does, it can proceed, finish, release its connections, and then another thread can pick them up and gets to proceed, etc.

If a thread needs Cm connections, then Cm - 1 connections is enough to be one thread short of what you need. If you have Tn threads, then Tn * (Cm - 1) connections lets every thread, in the worst case, be one short of what it needs (if the connections are not evenly distributed, then at least one thread already has as many as it needs). So Tn * (Cm - 1) + 1 connections means that at least one thread definitely has enough connections.

Ah, ok. Seems clear that this is for a thread requiring C connections simultaneously to get its work done. That would make it a necessary calculation to avoid deadlocks. I don't see this being necessary in a Rails type app where there's usually one connection per request, but if one were using Go, setting a max connections parameter, and then simultaneously checking out more than one connections to do work, there's an easy chance of deadlocks.

Will add a section talking about that and this formula.

> Seems clear that this is for a thread requiring C connections simultaneously to get its work done.

Exactly. As that document says (my emphasis):

> The prospect of "pool-locking" has been raised with respect to single actors that acquire many connections. This is largely an application-level issue. Yes, increasing the pool size can alleviate lockups in these scenarios, but we would urge you to examine first what can be done at the application level before enlarging the pool.

> Where Tn is the maximum number of threads, and Cm is the maximum number of simultaneous connections held by a single thread.

To be honest, i think applications do this are broken ("largely an application-level issue"), so i'm not sure how important it is.