PostgreSQL is out of control: high cpu load, high memory consumption
11,860
Daniel's comment is probably right: it looks like someone abuses your system.
Some general Postgres performance/stability hints, for people that landed here through Google:
- 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.
- Postgresql 9.2 is very old. There is a version 11. The newer releases usually have a better performance and stability.
- Log slow queries. Look in pg_log what the slow queries are and check whether there's a table that needs an extra index.
Related videos on Youtube
Author by
Admin
Updated on September 18, 2022Comments
-
Admin over 1 year
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