How to find out why user processes are using so much memory?

From: "Zwettler Markus (OIZ)" <Markus(dot)Zwettler(at)zuerich(dot)ch>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: How to find out why user processes are using so much memory?
Date: 2020-07-30 10:33:45
Message-ID: e204e89fc0d74c8b9b948238293c3ce5@zuerich.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Having an application on Postgres.
Most parameters are on default.
Memory related parameters are slightly increased:
effective_cache_size = 512MB
max_connections = 300
maintenance_work_mem = 64MB
shared_buffers = 512MB
wal_buffers = 16MB
work_mem = 10MB

92 user processes are using around 30G RAM + 20G Swap at the moment.
pg_top shows an even distribution of RAM per process (see below).

It seems the application is eating up more and more memory.
Any idea how to find out why the user processes are using that much memory?

Is there a statistic memory usage per statement or something like that?
pg_stat_statements is only showing shared_buffers per statement.

-Markus

last pid: 92572; load avg: 0.22, 0.32, 0.22; up 258+21:20:34 11:22:16
96 processes: 96 sleeping
CPU states: 0.2% user, 0.0% nice, 0.2% system, 99.6% idle, 0.0% iowait
Memory: 45G used, 2091M free, 624K buffers, 13G cached
DB activity: 33 tps, 0 rollbs/s, 81 buffer r/s, 98 hit%, 479 row r/s, 7 row w/s s
DB I/O: 5 reads/s, 171 KB/s, 2 writes/s, 19 KB/s
DB disk: 190.0 GB total, 28.2 GB free (85% used)
Swap: 19G used, 3407M free, 90M cached

PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
48064 postgres 20 0 788M 146M sleep 0:03 0.02% 0.20% postgres: pcl_p011: pdb_ppp_oiz pdb_p01_oiz 10.9.132.61(54140) idle
77908 postgres 20 0 2266M 1960M sleep 32:13 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(44496) idle
60355 postgres 20 0 2266M 1864M sleep 29:00 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36638) idle
92184 postgres 20 0 2187M 1893M sleep 28:20 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(33216) idle
60356 postgres 20 0 2236M 1852M sleep 27:56 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36642) idle
60359 postgres 20 0 2283M 1906M sleep 24:03 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36644) idle
77909 postgres 20 0 2219M 1947M sleep 23:59 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(44498) idle
77910 postgres 20 0 2191M 1918M sleep 22:20 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(44504) idle
60357 postgres 20 0 2203M 1935M sleep 20:48 0.52% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36635) idle
96423 postgres 20 0 2242M 1945M sleep 18:29 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(43288) idle
60328 postgres 20 0 2222M 1947M sleep 18:23 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36558) idle
99094 postgres 20 0 2349M 1989M sleep 16:58 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(58596) idle
60339 postgres 20 0 1774M 1361M sleep 16:10 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36578) idle
60354 postgres 20 0 2282M 1940M sleep 14:56 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36634) idle
77703 postgres 20 0 2035M 1714M sleep 12:29 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(45088) idle
60358 postgres 20 0 1788M 608M sleep 11:43 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36640) idle
110412 postgres 20 0 2100M 813M sleep 11:26 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(48528) idle
60317 postgres 20 0 1986M 1634M sleep 11:24 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(40160) idle
58878 postgres 20 0 2003M 1694M sleep 9:50 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54224) idle
60315 postgres 20 0 2060M 1805M sleep 9:50 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(40156) idle
51969 postgres 20 0 2018M 829M sleep 8:54 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54512) idle
58875 postgres 20 0 1957M 1553M sleep 8:53 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54218) idle
77704 postgres 20 0 2108M 563M sleep 8:31 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(45090) idle
58876 postgres 20 0 1925M 831M sleep 8:29 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54220) idle
58880 postgres 20 0 2236M 807M sleep 8:24 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54228) idle
58862 postgres 20 0 1684M 559M sleep 7:58 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54158) idle
77702 postgres 20 0 1027M 712M sleep 7:06 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(45086) idle
110414 postgres 20 0 1294M 710M sleep 6:54 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(48532) idle
110413 postgres 20 0 1968M 1641M sleep 6:53 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(48530) idle
60316 postgres 20 0 2341M 810M sleep 6:52 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(40158) idle
58877 postgres 20 0 1946M 900M sleep 6:51 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54222) idle
58851 postgres 20 0 1954M 764M sleep 6:27 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54138) idle
10366 postgres 20 0 2028M 730M sleep 6:17 0.00% 0.00% postgres: pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(49554) idle

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Olivier Leprêtre 2020-07-30 13:11:09 RE: Track pgsql steps
Previous Message Julien Rouhaud 2020-07-30 10:18:45 Re: Track pgsql steps