Postgresql – How to debug when OS kills postgres for high memory usage

performancepostgresqlpostgresql-9.4postgresql-performance

I am running Postgres 9.4 server in a VPS running Ubuntu. It gets killed often (multiple times in a day).

This is the message from dmesg

Out of memory: Kill process 1020 (postgres) score 64 or sacrifice child

Killed process 1020 (postgres) total-vm:445764kB, anon-rss:140640kB, file-rss:136092kB

How to debug what is causing this crash? Is it long running queries or some misconfiguration of the server or lots of idle connections open?

Best Answer

It's the OOM killer in the Linux kernel. Disable VM overcommit, per the advice in the PostgreSQL manual.

Related Solutions

Postgresql – Measure CPU and Memory usage while running pgbench

I show some tools I know.

pg_activity is a simple monitoring tool like top.

pg_statsinfo and pg_stats_reporter are high quality utilities that monitor statistics and the activity of PostgreSQL as well as Operating system.

There are some general purpose monitoring tools such as Cacti and Munin. Those support postgres by providing modules and they can easily be used.

BTW, I know a great book: The Art of Computer Systems Performance Analysis. This gives you some hints for benchmarking.

Mysql – thesqld is terminated because the system is low on memory; what to do

In 1GB of RAM, you may be able to set innodb_buffer_pool_size to 50M, maybe 100M. But definitely not 800M. The 80% advice is predicated on having at least 4GB of RAM, and 80% is too high even for that.

Having other things on the same machine (Apache?) adds to the cramped quarters. If you have Apache, lower MaxClients to 10.

Other things to keep out of trouble on that tiny machine:

max_connections = 25
table_open_cache = 300
key_buffer_size = 4M
tmp_table_size = 4M
max_heap_table_size = 4M

And look at the queries -- they are taking far more resources than they should ("Temporary tables created on disk: 82%")

Best Answer

Related Solutions

Postgresql – Measure CPU and Memory usage while running pgbench

Mysql – thesqld is terminated because the system is low on memory; what to do

Related Question