Understanding Connections and Pools
sudhir.io
sudhir.io
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.
Will add a section talking about that and this formula.
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.
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.
Inserts/updates require transactions. How did you escape having transactions?
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...
IIRC, of all popular SQL databases, only MySQL uses locks at all. Postgres, MS SQL, Oracle all use MVCC.
(Edited: typo)
> 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...
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.
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?
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.
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.
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.
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.
What do you mean by that term?
For sqlalchemy it just complaints "MySQL server has gone away (error(32, 'Broken pipe'))" and never know how to recover. For redis it just 100% the CPU and keep epoll the dead TCP.
Generally your client should close the connection when it's done. Server-side close should mostly be related to idle timeouts.
To achieve that, the client must have an extra thread listening for the close event, or a periodic check to purge the dead TCP connections.
But there's a gotcha, some TCP connections without KEEPALIVE just timeout naturally, and FIN packets may never reach the client, you only know the connections is dead when you initiate a real request. This is when most connection pool fail apart.
We have a Rails app that uses Puma with MySQL, but so far single threaded (the Puma config has `threads 1,1`), because we migrated from Unicorn and are still worrying about thread-safety.
Currently our database.yml pool size is set to 25, because we have 25 puma workers on the server.
Reading your article, this seems to be overkill, since that setting is _per worker_, and since each worker is running only with 1 thread, the default 5 should be more than enough?
Also, I just checked and our MySQL is configured with 151 max connections; if every worker of ours can use up to 25 connections, that can lead up to 625 connections to the database, and that's without considering Sidekiq (concurrency 25).
Strangely, tonight I've seen the first exceptions like `ActiveRecord::ConnectionTimeoutError: could not obtain a connection from the pool within 5.000 seconds (waited 5.000 seconds); all pooled connections were in use` in many, many years.
All of these exceptions came from within Sidekiq Workers, which is strange because, like I pointed, I didn't change the Sidekiq concurrency, only the database.yml pool, which shouldn't affect workers.
But basically if your sidekiq workers and app servers are using the same database.yml values, you’ll need to set the greater of the two on both. I assumed you have different settings on app servers and workers.
I spent a lot of the pandemic procrastinating by trying to build my own blogging software, a more efficient version of Ghost, but it’s too much duplicated effort given what I learned at AWS reinvent. So I decided to just set up and start publishing.
I write for about two hours a day, maybe four days or writing and two of editing for each article. I’d like to publish once a week, but don’t want to set that kind of goal because then I’ll write even if I have nothing interesting to say. But maybe that’s the best process anyway?
Will tell you what’s working if you ask again in a couple of months :-/
I’m trying to make a Ghost hosting service that’s free / $5 a month for personal use. I’ve figured out a way to make the economics work with the existing 500MB disk + 1GB RAM requirements, but it would have been a lot easier with Go and Dynamo.
Thanks a ton to you and the team for the work you’re doing. I think you’re making a fantastic system, and want to make it available to more people as a personal blogging platform. I just wish it could run multi tenant or with lower memory needs.
I don’t suppose making it easier for others to compete with the official Ghost revenue model is a priority, so I doubt this is anything you’ll want to take up. But I’m happy to share revenues on this - I just think Ghost should be a lot more accessible to individuals around the world.
A lot of blogging platforms (including Ghost) start out at $5/mo targeting personal use -- but the ones that survive never stay there. Probably some worthwhile takeaways from that :)
And yeah, don’t think the $5 / month is sustainable for a company, but for a solo founder it might work. Of course, that might be a classic case of this time it’s different.
This is not true for python. The article you linked mentions Gunicorn, uWSGI and CherryPy, they are threaded and can handle multiple requests per process.
Even if Python does have this GIL thing, it does not lock network operations for a tcp/udp based database protocol (some C based modules do tend to lock the GIL)
All of the GL and event loop based stacks go multi threaded a the lower library levels, really. Will think about how to explain that. And will move Python off that general category.
These systems allow you to make as many database connections as you want and manage them inefficiently, because the connections they give you are fake & cheap simulated connections that they handle with low overhead.
What makes them inefficient then, if they're cheap and have low management overhead?
Being stuck at home the only way to keep doing that is writing and videos. And I have a pretty bad stammer so videos aren’t an option.
Example, using Knex as the connection pooler for your node app, which is connected to your PgBouncer deployment?
I have not considered this a fault, in one way I find it compelling to have a layer of "protection" by using Knex in addition to PgBouncer, but not I'm not that sure anymore.
If it or you are setting up middleware to start a transaction before every request, or put a checked out connection in the request context, that’s a lot of possible inefficiency. If you really want the convenience this offers, and have request flows that do a lot of other non-DB work, you’ll need a pooling proxy.
If your request handling is very database heavy and does nothing else, then automatic request level connection check out is still efficient, even without a proxy. Even if you have a proxy one real connection is going to be working the entire duration of the request anyway.
> Rails auto-management needs to be conservative and cautious, therefore inefficient
Can you help me understand this better? Are you saying that Rails tends to hold onto connections for longer than it needs to?
Either way, if you have a request flow of 1) DB read - 50ms 2) Network request - 200ms 3) DB write - 50ms, you can see that the connection is idle for 2/3 of your request handling time. Rails does this so that if you start a transaction in step 1 you can commit it in step 3. All your writes are also automatically wrapped in a transaction, I think.
In Go on the other hand, step 1 will checkout and release the connection immediately, and step 3 will checkout and release. So the connection isn't idle locked for the 200ms in step 2. But if you don't remember this explicitly and try in step 3 to finish something (transaction/advisory lock/prepared statement) that you started in step 1, you're screwed.
So Rails saved you from doom by holding onto a connection for an inefficient extra 200ms, and Go shoots you in the foot in the name of efficiency.
On a sidenote, I wonder if this part has a typo?
> These systems allow you to make as many database connections as you want and manage them inefficiently
Perhaps inefficiently should be inexpensively or efficiently?
In some basic testing on my local machine at work just connecting to our local Oracle database introduced a good 60 ms - 100 ms for a request (before running any sort of queries) and due to PHP's nature this recurs on every new request.
I had searched for options for a long time in past months/years that would allow PHP to have some sort of connection pool the same way languages like Java/Go might allow, but had come up short each time.
I don't even recall how I came across it, but last July I finally learned about Oracle's Database Resident Connection Pooling feature (and then reminded my coworker about wanting to try it out in November) and was able to run some tests and it does a good job of eliminating most of that unnecessary connection waiting time for PHP applications so I was pretty happy with that testing and it didn't seem to increase the load much at all on the database server side.
Even though it might not be as ideal as a truly separate Connection Pool being managed at the application server level, or outside of the database, a Connection Pool available for use at the database level still has benefits so it would be kind of neat to maybe see more of this sort of approach be available with other vendors (if it isn't already).
EDIT: Forgot to add that as a result of the DRCP testing, even though moving the PHP side to the cloud introduces extra latency, with the time savings of the DRCP change, it helps reduce latency enough that overall it results in a time savings over the normal/conventional connection approach when the PHP code is local so for me that was a win (hopefully I'll get to try using it in production in the near future, but waiting on my colleague to sort out a few things with enabling it in production at the moment).
I would like to help point out some typos and missing words, and I made this PR[1] so you can easily see the diff. I hope this is useful, but also feel free to ignore these suggestions