Postgresql – pg_stat_statements causing DB to run out of memory

awspg-stat-activitypostgresql

I enabled pg_stat_statements on an AWS RDS postgres 9.6.3 instance with 30GB RAM.

We got the error message below in postgres log, and an event saying OS killed DB server process due to excessive memory consumption.

UTC::@:[32358]:LOG: server process (PID 9689) was terminated by signal 9: Killed"

We opened a case with AWS and they said server is too small for the load and to increase server size, we increase to a larger server with 64GB but after few hours (took slightly longer than last time) here also the DB went into recovery as OS killed DB process.

Disabling pg_stat_statements fixes the issue, we saw the same issue in other RDS instances also.

It seems there is some memory leak with pg_stat_statements as based on our setting below this extension should not use more than 100MB of RAM.

1.  pg_stat_statements.max=100000
2.  pg_stat_statements.save=1
3.  pg_stat_statements.track_utility=1
4.  Create extension from psql command line utility.
5.  shared_preload_libraries=pg_stat_statements

Any ideas how to fix this, as we want to use pg_stat_statements to look at DB performance.

Also is there an Oracle AWR equivalent on postgres.

Best Answer

It seems very surprising that you see an out of memory issue due to pg_stat_statements with the default kept statement count (5000). It makes somewhat more sense with a very high count, especially if you tried to query the view frequently.

One scenario, without going through the pgss code in detail, that I could imagine causing a memory issue, is the query text handling itself (not the statistics tracking), especially in the light of a lot of unique entries.

Given this is RDS we unfortunately can't check the size of the pgss file, but if you are open to trying this again (maybe you can reproduce a similar workload on a staging environment?), I think it would be useful to run a simple "SELECT * FROM pg_stat_statements" and visually look at the entries.

In particular I'd look for very large entries in the "query" field, as well as a lot of seemingly identical queries in pg_stat_statements (some ORMs tend to cause problems like this).

Also I'd recommend posting about this to the Postgres mailing lists - possibly to pgsql-bugs and/or pgsql-performance.

(For context: I built and operate pganalyze where we process a lot of pg_stat_statements data - memory issues like this are very rare)

Related Question