1

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
Pascal Friedrich
  • 11
  • 1
  • 1
  • 3
  • Perhaps the guys & gals from Vapor 3? Their package for PostgreSQL is only a release candidate. The PSQL logs don't give you anything to go by? – Gerard H. Pille Jul 10 '18 at 12:20
  • "400.0 0.2 262:06.80" looks as if it's been running for at least 2 days? – Gerard H. Pille Jul 10 '18 at 12:28
  • 1
    postgres doesn't have any executable named `pgsrv`. It looks very much like your server has been **subverted into doing crypto-mining** like in [this case](https://dba.stackexchange.com/questions/191282) and others. Clean up, reinstall, cut down db access from the internet and use strong passwords. – Daniel Vérité Jul 11 '18 at 00:07

1 Answers1

1

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:

  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.
  2. Postgresql 9.2 is very old. There is a version 11. The newer releases usually have a better performance and stability.
  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
  • 1,094
  • 1
  • 9
  • 14