How to find out why user processes are using so much memory? - Mailing list 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