We are running a postgres cluster with 32 CPU and 84 GB RAM in production with db size close to ~800GB.
It has replica setup as well with streaming wall replication (another host) kept as hot-standby.
Linux 2.6.32-642.6.2.el6.x86_64 (CentOS 6.4, upgrade pending)
Postgres Version - 11.11
We see some weird behaviour since last month with our instance having memory usage going every 80 hours even without having any cronjobs running at the specified moment (usually cron setup works specified-time/hourly/daily/weekly/monthly).
At the same time we see huge(~70gb) of temp files , e.g pgsql_tmp90128.0.sharedfileset/i231326of1048576.p2.0) being created and not getting deleted even after the postgres started working fine after 10 minutes.
My related settings for postgres memory utilization are following
work_mem 8 MB
temp_buffers 8 MB
shared_buffers 22 GB
max_connections 2500
During the window we see system cpu shoots up and came down again once memory refreshes.
$ sar -f /var/log/sa/sa17 -s 08:36:00 -e 08:47:00
>
08:36:21 AM CPU %user %nice %system %iowait %steal %idle
08:37:21 AM all 18.66 0.01 26.05 9.54 0.00 45.74
08:38:21 AM all 5.21 0.01 81.71 2.11 0.00 10.96
08:39:21 AM all 2.71 0.01 96.90 0.04 0.00 0.34
08:40:08 AM all 1.55 0.00 98.44 0.00 0.00 0.00
08:41:08 AM all 9.96 0.02 38.49 2.20 0.00 49.34
08:42:08 AM all 0.12 0.00 1.50 1.63 0.00 96.74
08:43:08 AM all 0.10 0.01 1.32 1.79 0.00 96.79
08:44:08 AM all 0.20 0.01 2.25 0.76 0.00 96.79
08:45:08 AM all 1.74 0.01 1.83 5.89 0.00 90.53
08:46:08 AM all 10.09 0.01 7.37 18.90 0.00 63.63
Average: all 5.11 0.01 34.18 4.36 0.00 56.34
RAM usage pattern
$ sar -r -f /var/log/sa/sa17 -s 08:36:00 -e 08:47:00
>
08:36:21 AM kbmemfree kbmemused %memused kbbuffers kbcached kbcommit %commit
08:37:21 AM 430888 88277480 99.51 12176 23869496 79753764 87.83
08:38:21 AM 420612 88287756 99.53 7212 23425316 80174100 88.29
08:39:21 AM 424276 88284092 99.52 3552 23246368 81653952 89.92
08:40:08 AM 417172 88291196 99.53 4316 22819904 82023344 90.33
08:41:08 AM 84692800 4015568 4.53 978760 1124852 25077588 27.62
08:42:08 AM 78749436 9958932 11.23 2098912 1139416 25067128 27.61
08:43:08 AM 73598564 15109804 17.03 3065228 1140756 25067516 27.61
08:44:08 AM 70213124 18495244 20.85 3175984 1141168 25067016 27.61
08:45:08 AM 56661972 32046396 36.13 3179640 13233168 27063188 29.80
08:46:08 AM 30585252 58123116 65.52 3207036 30364932 32130264 35.38
Average: 39619410 49088958 55.34 1573282 14150538 48307786 53.20
I have the following questions in this respect:
- What can be the probable cause of this behaviour of memory usage going dropped at regular interval ?
- What these temp_files are and is it safer to delete manually ?
- Is there a bug in Postgres, why it's not getting automatically cleaned, related-thread
I see several run of CHECKPOINT and even then the temp files are not getting cleared.