3 ms·
Why would anyone run hundreds of connections? A server can only process number_of_processor_cores connections at once. Sure, few connections might wait for I/O,
by vbezhenar 10y ago
Why would anyone run hundreds of connections? A server can only process number_of_processor_cores connections at once. Sure, few connections might wait for I/O, but not hundreds, unless database is very untypical.
- brianwawok 10y agoFor example: You are using Python and you have 10 web servers and 20 background servers connected to a common DB. Each server has 10 threads, and each thread holds 1 connection open. That is 300 open connections. Opening and closing connections is very slow and expensive, so almost always better to keep these 300 connections open than to try to be fancy. You COULD try to say give each server only 3 connections and make them share, which cuts you to 90 connections.. but then you have to try to share state between different python processes (not easy), and will often end up with deadlocks and sync overhead.
- tracker1 10y agoAgreed, which is why many clients create a pool of connections that gets reused. Connection cost is expensive, and the rdbms already handles concurrency and even a couple thousand connections shouldn't be a significant overhead.
- brianwawok 10y agoI'm trying to find evidence on what memory usage is for MySQL for 1k connections vs postgres with 1k connections. I am finding a lot of people saying postgres has heavier connections but for 1k connections what's the difference. 1MB of memory? 1GB?
- kasey_junk 10y agohttps://wiki.postgresql.org/wiki/Number_Of_Database_Connections https://wiki.postgresql.org/wiki/Number_Of_Database_Connecti... Its a little outdated but I've found it largely holds. That is, you'd want a mighty box for 1000 concurrent connections. That said, connection queuing works really well with postgres such that throughput is frequently better at lower connection counts than at higher ones. I have no experience with mysql that is less than 15 years out of date.
- Roboprog 10y agoLike you said, the connection is still in use while waiting for I/O, at which point, another process or thread runs a bit and can also kick off some I/O on yet another connection. Numbers vary, but that's the principal that uses more connections than cores.
- rusanu 10y agoIn my experience DB connections are overwhelmingly idle. Even if you omit the obvious dead times between requests and consider only actively executing connections, a request will spend most of the time waiting for a resource or another (read IO, commit flush, latches, locks). It takes an extraordinarily well tuned app to be able to drive all connections to be anywhere near 100% CPU bound and never conflict or wait.
- zzzeek 10y agothe connections are idle, are routed to dozens or hundreds of separate processes themselves, and are held ready for fast response when that process needs to serve a request. Unfortuantely, MySQL has no good replacement for Postgresql's PGBouncer which greatly mitigates the issue of cross-process connection pooling. (I'm actually working on one, but for Uber to use it they'd have to switch back to SQLAlchemy >:) )