PostgreSQL is out of control: high cpu load, high memory consumption

by Pascal Friedrich   Last Updated July 10, 2018 13:00 PM

I'm using PostgreSQL 9.2.32, installed on CentOS 7 with Plesk, with a very small amount of tables (the tables are small too) and Vapor 3 as backend.

After a few hours, my server is out of memory, because PostgreSQL is eating it like pacman and i have no idea why and how to analyze this behavior.

Additionally, PostgreSQL has a very high cpu load.

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                                      
 4192 postgres  20   0  434784   9956   1176 S 400.0  0.2 262:06.80 pgsrv                                                                                                                        
 2641 postgres  20   0  226292   9080   7976 S   0.0  0.1   0:00.12 postgres                                                                                                                     
 2642 postgres  20   0  192604   1524    440 S   0.0  0.0   0:00.00 postgres                                                                                                                     
 2644 postgres  20   0  226392   2952   1824 S   0.0  0.0   0:00.00 postgres                                                                                                                     
 2645 postgres  20   0  226292   1968    856 S   0.0  0.0   0:00.03 postgres                                                                                                                     
 2646 postgres  20   0  226292   1896    784 S   0.0  0.0   0:00.03 postgres                                                                                                                     
 2647 postgres  20   0  227120   2996   1216 S   0.0  0.0   0:00.06 postgres                                                                                                                     
 2648 postgres  20   0  194856   1796    588 S   0.0  0.0   0:00.10 postgres                                                                                                                     
 3881 postgres  20   0  227612   5896   3624 S   0.0  0.1   0:00.00 postgres                                                                                                                     
 3897 postgres  20   0  228260   7980   5428 S   0.0  0.1   0:00.05 postgres  

Heres the line a few minutes before the server stopped working:

postgres  20   0 8488060   2,9g   2588 S   0,3 47,9   1:42.40 ps3351955702

Would be great if someone has an idea to solve this issue.

Edit: output of pg_stat_activity

1   16458   test_lmmaps 3881    16460   lmmaps      139.xx.xx.xx    null    49526   2018-07-10T10:37:10.999Z    null    2018-07-10T10:37:11.246Z    2018-07-10T10:37:11.246Z    false   idle    SELECT * FROM "fluent" WHERE ("fluent"."name" = $1) LIMIT 1 OFFSET 0
2   16458   test_lmmaps 3897    16460   lmmaps      139.xx.xx.xx    null    49542   2018-07-10T10:38:51.000Z    null    2018-07-10T12:08:51.078Z    2018-07-10T12:08:51.080Z    false   idle    INSERT INTO "locations" ("longitude", "id", "createdAt", "latitude", "tourID", "loadno", …
3   12924   postgres    6900    16460   lmmaps      46.xx.xx.xx     null    56518   2018-07-10T12:14:21.512Z    null    2018-07-10T12:14:23.465Z    2018-07-10T12:14:23.466Z    false   idle    EXPLAIN SELECT * FROM pg_stat_activity ;
4   12924   postgres    6901    16460   lmmaps      46.xx.xx.xx     null    56519   2018-07-10T12:14:24.745Z    2018-07-10T12:14:24.796Z    2018-07-10T12:14:24.796Z    2018-07-10T12:14:24.796Z    false   active  SELECT * FROM pg_stat_activity


Answers 1


Some general hints:

  1. In postgresql.conf, you can set memory reservation amounts for various functions. Study the file, and see whether you need to modify some values. I'm afraid I can't tell you exactly what to change; that depends on your system and application. But since Postgresql seems to be eating a lot of memory, there might be a value that needs to be decreased.
  2. Postgresql 9.2 is a bit old. There is a version 10. I'd recommend using at least 9.5 or newer. The newer releases usually have a better performance.
  3. Log slow queries. Look in pg_log what the slow queries are and check whether there's a table that needs an extra index.
Onnonymous
Onnonymous
July 10, 2018 12:40 PM

Related Questions


Can't install PostgreSQL 9 server on Centos 6

Updated November 02, 2017 10:00 AM

CentOS 7 sudo not running commands as user

Updated February 10, 2018 04:00 AM

CPU/Node HT Link sync error SuperMicro H8QG6

Updated November 30, 2018 13:00 PM

Completely reset PostgreSQL to default?

Updated April 14, 2017 21:00 PM