9 ms·
Costs of a PostgreSQL connection
- tbarbugli 13y agohard to believe there are still people non using pgbouncer in production :)
- nousernamesleft 13y agoNot everyone runs some PHP damaged setup where connection pooling has to be pushed out to a proxy. Some people use sane applications in the first place. There is no reason for those people to add an extra layer of latency and potential failure to achieve what they already have.
- tbarbugli 13y agothere are a number of applications that are sane and dont have a postgresql connection pooling built-in (eg. django introduced it on 1.6)
- nousernamesleft 13y agoDjango requires multiple processes, which makes sharing things like connections quite difficult. That is not sane.
- tbarbugli 13y agowhats your alternative to that?
- nousernamesleft 13y agoA language without a global lock to make threads useless.
- pekk 13y agoYou have taken a FUD sound bite and repeated it without understanding. The language is not at issue here. CPython's GIL does not make threads useless at all, and there are already Python interpreters without a GIL anyway. An addiction to globally shared state across threads multiplies race conditions and scaling problems, even if the GIL magically goes away. If you are incapable of using even processes effectively then I really fear for you when you have to scale out horizontally. I'd say this more nicely if I could think of any way, but you don't know what you're talking about and you should read up before you begin talking about it again. It looks bad and you might mislead someone.
- nousernamesleft 13y agoNo, you have taken a "stick my head in the sand and pretend the last 30 years of progress didn't happen" and repeating it without understanding. I do web development in haskell. I have thousands of concurrent connections to a single process, which is using 32 cores without problems. There has not been a single race condition, deadlock, mutex bottleneck, etc, etc. Just because you are happy with an absurdly primitive language, doesn't mean those of us in the 21st century are ignorant.
- marcosdumay 13y agoDjango connections to the database are bounded by the number of threads and processess you set. It didn't have a pool, but it's a mistake to think it just oppened connections at will.
- yummyfajitas 13y agoTrue - it's only application insanity preventing a distributed collection of database clients from properly coordinating the proper allocation of postgres connections amongst themselves. Now true, in the event of a network partition, you lose the ability to open a database connection. But that's a small price to pay, right?
- teraflop 13y agoI guess this is sarcasm, but it's not clear what you're trying to actually say. How is a connection-pooling proxy supposed to make an application more resilient to network partitions? If anything, it's worse, since you have twice as many points of failure -- if either the application can't talk to the proxy, or the proxy can't talk to the database, you're hosed.
- yummyfajitas 13y agoPractically speaking, you typically run pgbouncer on the same box as postgres. Even if you ran pgbouncer on a different box, the situation is still better. Suppose you have a probability P of having a partition between any two boxes. Then the probability of a partition taking down the system with pgbouncer is P. If you instead had N clients coordinating among themselves how many connections to use, your odds go way up. The probability of a partition existing between two clients is 1-(1-P)^(N(N-1)/2) ~= N(N-1)P/2 (for very small P). Once two clients can't communicate, neither one can connect to the DB (because they don't know if the other two have pushed the db over the limit). All clients will need to stop connecting to the DB if any client is ever disconnected from the other N-1 of them. In a distributed system, ensuring that SUM(x[i]) remains bounded for all time is a tricky problem to solve.
- wmf 13y agoOr you could give each client C/N connections.
- yummyfajitas 13y ago
- AznHisoka 13y agoFor some reason, Rails doesn't behave well with pgbouncer or any other pg connection pool. So I really have no choice but to set my max connections to 1900, and pray that it's enough. Surprisingly, my 48 GB RAM, quad core CPU, non SSD server can handle that load, although the hardware will probably degrade after a certain time (oh well shrugs)
- _hans_ 13y agoWe enabled PGBouncer transaction mode and had to set 'prepared_statements: false' in our database.yml b/c the transaction mode doesn't support that. Works fine for us.
- gkop 13y agoWhat are the implications of 'prepared_statements: false'? It sounds really bad.
- tbarbugli 13y agodont prepare statements?
- justincormack 13y agoPrepared statements are not a massive performance gain in postgres so I wouldn't worry.
- nousernamesleft 13y agoIf done right they can be huge. The fact that they do nothing when you just do "prepare" followed immediately by "execute" in place of just running the query directly is not a problem with postgresql or prepared statements, it is a problem of doing something dumb. For simple selects the parsing and planning stage can be 90% of the time spent. The problem is that postgresql doesn't have stored procedures, so you have to make sure every connection is initialized by running all of your "prepare"s before it is made available to the pool.
- jmnicolas 13y agoNow you fixed your Postgres could you fix the reminders in Wunderlist pretty please ? It doesn't work (for me) on Android so I started to use the app less and less.
- lazyjones 13y ago* work_mem is not used per connection. It may be used for some queries. Same for temp_buffers. What I miss here is information about the Postgres version used. Also, if 3.5GB of memory hurt you on a database server with 370 connections, you need to think about buying a DB server with more RAM (our main DB server has 512G and typically ~220 connections, we don't sweat over it and don't use PGBouncer to add latency).
- _hans_ 13y agoThanks for your comment! Re work_mem: I think this is per query and thus might affect every connection?! At least thats what I gather from 'PostgreSQL 9.0 High Performance' Re temp_buffers: 'Sets the maximum number of temporary buffers used by each database session.' For us it practically means per connection. Not for you? Shit, I'm missing the version! 9.3. I edited the blog post. Re memory: I think that really depends on the application I think. We have many idle connections.
- lazyjones 13y ago> Re temp_buffers: 'Sets the maximum number of temporary buffers used by each database session.' For us it practically means per connection. Not for you? It means that potentially every connection may at some point use this amount of memory, but idle connections will not. If you have 20 busy connections and 350 idle ones, at most 20 x temp_buffers will be used and there is no benefit from PGbouncer in this regard. If you have 370 busy connections, all of them might use temp_buffers and your tradeoff can be 370 concurrently executing queries at higher memory cost vs. e.g. 20 concurrently executing queries via PGbouncer at lower memory cost and 350 stalled queries with potentially high latency. > We have many idle connections. In this case, you will not have any issues with memory use from temp_buffers, work_mem ...
- _hans_ 13y agoYes you're right, idle connection do not use temp_buffers or work_mem. I was trying to gather everything relevant to resources per connection. I'm not saying work_mem caused our problem.
- arielweisberg 13y agoI have always found it odd how some databases conflate network connections with threads (or processes) of execution. If Postgres has an ideal number of concurrent workers it should instantiate that many and multiplex requests from various connections across them which has several benefits including allowing you to preemptively shed requests you know you won't get to in time and do other scheduling and QoS stuff. I get it from a historical perspective where it would have been needless complexity to implement something else. On an unrelated note it's pretty amazing what threads get conflated with in general.
- wulczer 13y agoOne of the important reasons Postgres connections are serviced by a separate process is that if that process segfaults, you only disconnect other connections (because if might have tainted shared memory when it died), but the main database process keeps running and you can reconnect immediately.
- adamtj 13y agoNot quite. If a backend segfaults, postgres will detect it and immediately terminate all connections and exit. When it restarts, it will enter recovery mode. It does this because a segfaulted backend may have corrupted shared memory. The only way to fix that to restart in recovery mode and replay the WAL.
- gaius 13y agoIt is no odder than Unix conflating your shell with a process, really. You connect, run some commands within a context that's "yours", then it's all cleaned up when you go.
- smilliken 13y ago> We were running out of file handles which caused the database to crash Most distributions ship with conservative defaults for the maximum number of concurrent file descriptors and socket connections. Especially for a database server, it's wise to tune these values before optimizing anything else. > 1000 files per connection * 370 = 370,000 files This is a very pessimistic estimate, in reality you'll need much less than 1000 file descriptors per connection. As long as you tune your file descriptor limit, you should be able to handle one or two orders of magnitude more connections than this. A few resources that might help: http://serverfault.com/questions/92723/file-descriptors-setting-on-etc-sysctl-conf http://serverfault.com/questions/92723/file-descriptors-sett... http://www.cyberciti.biz/faq/linux-increase-the-maximum-number-of-open-files/ http://www.cyberciti.biz/faq/linux-increase-the-maximum-numb... http://stackoverflow.com/questions/2569620/socket-accept-error-24-to-many-open-files http://stackoverflow.com/questions/2569620/socket-accept-err...
- _hans_ 13y agoThank you. We configured the system to accept more file descriptors. I think our issue was caused by something else.
- robbles 13y agoIf pgbouncer makes this big a difference to performance, is it included in managed PostgreSQL services, such as Amazon RDS and Heroku Postgres? Or would you have to roll your own pgbouncer setup for these systems?
- _hans_ 13y agoNeither AWS RDS PostgreSQL nor Heroku has a PGBouncer.
- btown 13y agoHeroku actually does seem to support/encourage it: https://postgres.heroku.com/blog/past/2013/11/22/connection_limit_guidance/ https://postgres.heroku.com/blog/past/2013/11/22/connection_... https://github.com/gregburek/heroku-buildpack-pgbouncer https://github.com/gregburek/heroku-buildpack-pgbouncer
- robbles 13y agoSaw that reference in the linked post from Heroku in the original article. Has anyone used this? Is it configurable, or do they just choose some reasonable defaults?
- thruflo 13y agoWorks great for me. There's some minimal configuration explained in the pgbouncer docs. What the pgbouncer buildpack does is just route your DATABASE_URL through the local pgbouncer. In my python app, I switched from an sqlalchemy connection pool per process (eg per gunicorn worker) to using a Null pool everwhere. No increase in latency; far fewer connections needed.
- zzzeek 13y agotl;dr;tl;dr; - use a connection pool.
- Spearchucker 13y agoWould be nice to see a little more detail on the clients that need hundreds of connections. I have no experience with PostgreSQL, but do get around this in other RDBs through a mix of async calls, caching and optimising data structures and application logic (meta-data driven schema; indexing content in the app before a db write to eliminate having to retrieve that content to search it, or indexing it using the db).
- ocharles 13y agoIf you only have `2 * n cores` connections, doesn't that mean that you can at most have `2 * n cores` transactions running at once? I suppose on larger servers that is fairly reasonable (as you want these transactions to be very short lived anyway), but it does seem like you don't give yourself much wiggle room.