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

Hi

čt 30. 7. 2020 v 12:33 odesílatel Zwettler Markus (OIZ) <
Markus(dot)Zwettler(at)zuerich(dot)ch> napsal:

> 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
>
>
>
A similar symptom has an application that uses a very long connection -
Linux process returns memory to OS on finish. Is good to close connections
after +/-one hour.

Regards

Pavel