11

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

1 Answer 1

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

1
  • This makes a lot of sense and corroborates what I've been reading in other articles about PostgreSQL. The memory footprint for each connection-specific forked child process grows over time. In my case this exceeded the memory capacity of my server and caused a failover which caused cascading failures from the resulting stale connection pool. Recycling connections is our current solution to the continually growing memory usage of the child PostgreSQL process.
    – Kent Bull
    Nov 11, 2021 at 2:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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