Understanding Connections and Pools

https://sudhir.io/understanding-connections-pools/

by sudhirj • 6 years ago

216 73 6 years ago

sudhir.io

sudhirj22• 6 years ago

Author here, self-posted. Can AMA.

victor106• 6 years ago

In depth article, would be good if you can cover Java based connection pool like HickariCP. They are very widely used

sudhirj22• 6 years ago

I've added a link to the project in the context of another comment here about deadlocks... is there anything in particular you'd like to mention? Will look into the pool in detail to see if there's any special tips and tricks they use.

pqdbr2• 6 years ago

Great article!

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

sudhirj22• 6 years ago

Well, even the default 5 would probably be overkill, unless you’re spawning threads on a request that each use their own DB connections. But yes, I think you can go with the the default. Once you’re confident about being threadsafe, the Heroku deployment guide for puma is pretty good for all the little knobs to turn.

sudhirj22• 6 years ago

Re MySQL counts, based on your notes you’re probably using 50 conns at max whatever your settings are. 25 for sidekiq (assuming you do run a hot queue) and 25 at max for the servers. Irrespective of DB pool size, I think Rails does lazy connections, so you’re never going to go over 1.

artimaeis• 6 years ago

Just 6 days ago you published what was a pretty great writeup on message queues, now this thorough dive on connections & pools; what's your writing schedule like? At some 12k words of fairly accurate and precise technical content I'm kind of blown away by your pace.

sudhirj22• 6 years ago

I had a few paragraphs written earlier (maybe half) over the course of the pandemic.

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 :-/

est3• 6 years ago

the popular servers for Python, PHP and other languages that use a global lock or assume single-threaded single-process execution

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)

sudhirj22• 6 years ago

Will edit that, yeah. I was am struggling to explain the GIL without having to talk about the GIL, because if open that can of worms it becomes a much longer article and goes off track.

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.

mekoka• 6 years ago

I'm confused by this statement about pgBouncer et al.

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?

nickelbob• 6 years ago

I think he meant that "you" can manage (or not manage them at all) them inefficiently because the proxy pool well take care of that for you. It reads awkwardly.

sankyo• 6 years ago

Thank you for this. Very few people can explain complicated ideas with easy to follow language. You have a gift for explaining.

sudhirj22• 6 years ago

Thank you for saying that. I enjoy helping people understand things in minutes, especially if those things took me years.

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.

fotta3• 6 years ago

ot: do plan on adding an RSS feed? Would love to subscribe.

hansvm• 6 years ago

In case the author is worried about sufficient readership, I second this motion :)

sudhirj22• 6 years ago

I thought this blog engine already had one, I certainly didn’t disable anything. Let me check.

sudhirj22• 6 years ago

Yeah, it’s active on https://sudhir.io/rss/ Pasting the base URL into a feed reader should usually check the /rss path as a convention.

toredash2• 6 years ago

Am I reading this correctly, that with NodeJS and PostgreSQL, the usage of multiple connection poolers _combined_ is a not-so-good idea?

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.

sudhirj22• 6 years ago

It depends on how knex is used. If it gives you a method to run a DB query, and internally checks out a connection, runs the query and releases the connection, then it’s already doing exactly what would happen with a proxy.

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.

tjvc2• 6 years ago

Great article, many thanks.

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?

sudhirj22• 6 years ago

Yeah, Rails doesn't currently strongly analyze what it needs to do. Based on my experience with it, I remember that it either 1) checks out a connection at the beginning of the request into a thread local / request context and releases it at the end of the request, or 2) checks out when the first active record work is attempted, and releases at the end of the request.

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.

wiradikusuma• 6 years ago

Related, about sizing DB connection (counterintuitively): https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-...

sudhirj22• 6 years ago

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?

twic2• 6 years ago

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.

Traubenfuchs• 6 years ago

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?

cogman10• 6 years ago

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.

ramchip• 6 years ago

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.

batter• 6 years ago

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.

commandlinefan2• 6 years ago

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.

sudhirj22• 6 years ago

For a read-only load, right? If there were writes and transactions I don’t see how that would work.

calpaterson5• 6 years ago

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

nine_k• 6 years 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)

spacemanmatt3• 6 years 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.

karmakaze• 6 years 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.

kohlerm• 6 years ago

IMHO a very good summary of the trade offs, with concrete examples

est3• 6 years ago

One thing I never understand about connection pool is how do they handle server-sent close connection (FIN) events? Like I can guarantee there are tons of CLOSE_WAIT connections on my near-idle Python process for various of db pools, mysql or redis or pg.

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.

Example: https://github.com/andymccurdy/redis-py/pull/886

sudhirj22• 6 years ago

I'm not familiar with specific implementations, but from what I've seen with Redis and Postgres clients there's always a reconnect hook of some sort, which I assume pooling proxies give you as well. We've had plenty of broken pipe errors and we just reconnect.

hilbertseries3• 6 years ago

Generally a connection pool also checks the health of its connections. In SQL connection pools this can be done by executing a select 1, if this fails then a new connection can be attempted.

outworlder• 6 years ago

Like I can guarantee there are tons of CLOSE_WAIT connections on my near-idle Python process for various of db pools, mysql or redis or pg.

Generally your client should close the connection when it's done. Server-side close should mostly be related to idle timeouts.

est3• 6 years ago

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.

x4m• 6 years ago

Thanks for the post, great read! Just one minor note s/Odessey/Odyssey/g // From Odyssey maintainer

sudhirj22• 6 years ago

Thanks, will do.

orware• 6 years ago

Not that many people may be using Oracle and PHP together, but last summer I decided to take a closer look at some performance aspects since I'm wanting to move certain PHP things to the cloud, while our Oracle database remains local, which would introduce some extra latency.

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

cxcorp• 6 years ago

Good read, I've been doing some work related to database bottlenecks recently and there's definitely food for thought in the article.

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?

sudhirj22• 6 years ago

Have edited to "These systems allow you to make as many database connections as you want without worrying about management, because the connections..."

sathley• 6 years ago

Brilliant article! Shared it with the rest of my team.

boberoni• 6 years ago

Great article, Suhir. Very well written and informative :DD

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

[1] https://github.com/chenbobby/editing/pull/1/files

Understanding Connections and Pools

Related Stories