7

I'm using postgresql 9.5.4 on amazon RDS with ~1300 persistent connections from rails 4.2 with "prepared_statements: false". Over the course of hours and days, the "Freeable Memory" RDS stat continues to go down indefinitely but jumps back up to a relatively small working set every time we reconnect (restart our servers). If we let it go too long, it goes all the way to zero and the database instance really does start to go into swap and eventually fail. Subtracting the freeable memory over days from the peaks when we restart we see that there are 10's of MB per connection on average.

enter image description here

Digging into the per-pid RSS from enhanced monitoring, we see the same slow growth on example connection pids but the total RSS seems to just be a proxy for actual memory usage per connection (https://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/).

enter image description here

How can I either:

  1. Change the default.postgres9.5 parameters below to avoid unbounded memory growth per-connection
  2. Determine what queries cause this unbounded growth and change them to prevent it
  3. Determine what type of buffering/caching is causing this unbounded growth so that I can use that to do either of the above

enter image description here

2
+100

If you were using community PostgreSQL, the definitive answer to figuring out where the memory is going on Linux is to connect to a bloated backend with gdb, and do p MemoryContextStats(TopMemoryContext) and then inspect the output from the server log file. But since you are not using community PostgreSQL, I guess the definitive answer would be to contact your paid support and ask them to figure it out for you.

The most likely cause for this is that PostgreSQL keeps a per-connection cache of metadata about all the database objects (tables, indexes, etc.) it has touched during the connection lifetime. There is no upper limit on how big this cache can get, and no mechanism for expiring it. So if you have hundreds of thousands or millions of these things, and a long-lived connection will eventually touch each one of them, then their memory usage will continuously grow. The usual solution to this (assuming you can't reduce the number of objects in your database) is to set up your connection pooler so that each connection has a maximum time to live before being closed rather than recycled.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.