Hi čt 30. 7. 2020 v 12:33 odesílatel Zwettler Markus (OIZ) < markus.zwett...@zuerich.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
