So just a bit ago I ran into a bit of excitement when the kernel decided to kill one of my postmaster processes due to an out-of-memory issue, which would have been fine, except that the problem was then compounded by Pacemaker attempting to restart postgresql, but only managing to get as far as stopping the primary and failing to promote the secondary, leaving me with nothing. Not fun for a mission-critical database, but luckily I was notified of the issue nearly immediately, and was able to get everything back up and running quickly (after a few moments of panic).
In any case the root problem here was the out-of-memory issue. The logs show this:
Feb 1 11:58:34 fai-dbs1 kernel: Out of memory: Kill process 26316 (postmaster) score 837 or sacrifice child
Feb 1 11:58:34 fai-dbs1 kernel: Killed process 26316, UID 26, (postmaster) total-vm:72328760kB, anon-rss:55470760kB, file-rss:4753180kB
So a single postmaster process was using over 72GB of ram. Obviously I have something in my config tuned too high. The question is "what"? The machine has 64 GB of RAM, and I want postgresql to be able to use as much of that as it wants (since the machine is dedicated to postgresql), but obviously it needs to be more limited than what I have.
From my config:
max_connections = 300
work_mem = 75MB
Not sure what else in there would play a role. I was seeing some of this in the postgresql logs shortly before the issue arose:
2017-02-01 11:58:02.074 AKST > LOG: checkpoints are occurring too frequently (12 seconds apart)
2017-02-01 11:58:02.074 AKST > HINT: Consider increasing the configuration parameter "max_wal_size".
I was thinking perhaps the work_mem was the issue, but if my understanding and calculations are correct, that would mean I had more than 800 simultaneous sorts/queries going on at the time, which seems quite improbable, given the relatively light load on this database. At the moment, for example, a query on pg_stat_activity reveals only 11 entries.
What am I missing here?
-----------------------------------------------
5245 Airport Industrial Rd
-----------------------------------------------