3 ms·

"We had complete lock ups where max_connections was exceeded and we could never find a source, internal or external, to our system. Literally hundreds of connec

"We had complete lock ups where max_connections was exceeded and we could never find a source, internal or external, to our system. Literally hundreds of connections doing SELECT statements but nothing else. Eventually manually killing them “solved” it" Wouldn't having "max_connections" postgresql processes create more problems?

davidgould 9y ago
Usually not. When you reach max connections it does not cause problems for existing connections ie, no "complete lock ups)", it just stops allowing new connections. Also, there are normally reserved connections for postgresql superusers so you can query to find out where all the excess connections are coming from and kill them as needed.
jollife 9y ago
after migrating, we did not encounter any problems with max connections again. after 2 weeks of production use, we, nevertheless, switched to pgbouncer to have a more stable connection management, though.
petre 9y ago
I second that. We had hit the same problem with our mysql database a week ago and a manual restart was needed to fix the problem.
some_developer 9y ago
Hard to say. Now that we switched to PostgreSQL, operations run much more smoother. You literally can't compare it. But this is also attributed to the major refactoring we did under the hood too; as I pointed out in the article, I simply "driver switch" didn't cut it :-) We hit the max connections limit in postgres too at peak times (but no lock-up or similar thing happened) and per advice of our hosting provider we added pgbouncer to the stack last week and will hopefully don't have much problems here anymore. Disclaimer: I wrote that article.
pgaddict 9y ago
I don't think PostgreSQL has issues with max_connections, it'll simply start rejecting new connection requests (as pointed out by davidgould). What we see in practice, though, is that people often increase max_connections to rather insane values. There's only a certain number of active connections each box can support (say, ~2*cores, sometimes more), and it's one of the things we check whenever a customer contacts us with performance issues. Oh, you increased max_connections to 7243, it was running fine for a while and then you got a short burst of activity on Monday morning and it's crawling out of the rack? pgbouncer FTW in most cases (in transaction pooling mode).

davidgould 9y ago

Usually not. When you reach max connections it does not cause problems for existing connections ie, no "complete lock ups)", it just stops allowing new connections. Also, there are normally reserved connections for postgresql superusers so you can query to find out where all the excess connections are coming from and kill them as needed.

jollife 9y ago

after migrating, we did not encounter any problems with max connections again. after 2 weeks of production use, we, nevertheless, switched to pgbouncer to have a more stable connection management, though.

petre 9y ago

I second that. We had hit the same problem with our mysql database a week ago and a manual restart was needed to fix the problem.

some_developer 9y ago

Hard to say. Now that we switched to PostgreSQL, operations run much more smoother. You literally can't compare it. But this is also attributed to the major refactoring we did under the hood too; as I pointed out in the article, I simply "driver switch" didn't cut it :-) We hit the max connections limit in postgres too at peak times (but no lock-up or similar thing happened) and per advice of our hosting provider we added pgbouncer to the stack last week and will hopefully don't have much problems here anymore. Disclaimer: I wrote that article.

pgaddict 9y ago

I don't think PostgreSQL has issues with max_connections, it'll simply start rejecting new connection requests (as pointed out by davidgould). What we see in practice, though, is that people often increase max_connections to rather insane values. There's only a certain number of active connections each box can support (say, ~2*cores, sometimes more), and it's one of the things we check whenever a customer contacts us with performance issues. Oh, you increased max_connections to 7243, it was running fine for a while and then you got a short burst of activity on Monday morning and it's crawling out of the rack? pgbouncer FTW in most cases (in transaction pooling mode).