今天接到监控组的告警,某数据库服务器的SWAP分区消耗超过阀值,消耗约8G。
free
total used free shared buffers cached
Mem: 24682828 24607764 75064 0 10128 6525184
-/+ buffers/cache: 18072452 6610376
Swap: 16779884 8794580 7985304
查看过往的SAR报告如下
kbmemfree kbmemused %memused kbbuffers kbcached kbswpfree kbswpused %swpused kbswpcad
02-06
Average: 79193 24603635 99.68 27148 8510109 13953197 2826687 16.85 967
02-07
Average: 81699 24601129 99.67 23619 8483106 13953178 2826706 16.85 985
02-08
Average: 73905 24608923 99.70 11064 8504025 13953172 2826712 16.85 970
02-09
Average: 78953 24603875 99.68 18140 8475291 13953172 2826712 16.85 968
02-10
09:20:01 AM 74356 24608472 99.70 2580 8361932 13953172 2826712 16.85 968
09:30:01 AM 74224 24608604 99.70 3412 8466448 13822936 2956948 17.62 2956
Average: 77257 24605571 99.69 7574 8344604 13196771 3583113 21.35 1432
02-11
Average: 87933 24594895 99.64 10818 7304608 10841381 5938503 35.39 2315
02-12
Average: 82804 24600024 99.66 15113 7149277 10581682 6198202 36.94 1978
02-13
Average: 85698 24597130 99.65 7834 7246716 10130720 6649164 39.63 3248
TODAY
Average: 73259 24609569 99.70 4444 6649324 8273769 8506115 50.69 21545
数据库日志中包含大量autovacuum 信息,其中有的执行时间比较长。(正常情况下一个autovacuum进程可以占用做多maintenance_work_mem设置的内存大小)
# 查看服务器上占用内存较多的进程pmem (百分比)
ps -eo user,rssize,vsize,sz,size,pmem,cmd --sort size|grep postgres
以下进程占用70%物理内存
postgres 17280128 27504196 6876049 26001504 70.0 postgres: autovacuum launcher process
怀疑是服务器内存处理出现问题造成.
到服务器上杀掉该进程
ps -ewf|grep auto
postgres 5821 5816 0 2010 ? 05:13:02 postgres: autovacuum launcher process
postgres=# select * from pg_terminate_backend(5821);
pg_terminate_backend
----------------------
t
(1 row)
日志输出如下:
2011-02-14 17:47:07.905 CST,,,5821,,4ccfbe6a.16bd,2,,2010-11-02 15:31:54 CST,1/0,0,LOG,00000,"autovacuum launcher shutting down",,,,,,,,,""
2011-02-14 17:47:10.005 CST,,,15773,,4d58fa1e.3d9d,1,,2011-02-14 17:47:10 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
autovacuum launcher的主进程为postgres进程,在被终结后自动产生.
ps -ewf|grep auto
postgres 15773 5816 0 17:47 ? 00:00:00 postgres: autovacuum launcher process
交换分区释放:
free
total used free shared buffers cached
Mem: 24682828 7307256 17375572 0 10676 6525460
-/+ buffers/cache: 771120 23911708
Swap: 16779884 54836 16725048
PS输出注释:
%mem %MEM ratio of the process’s resident set size to the physical memory on the machine, expressed as a
percentage. (alias pmem).
rss RSS resident set size, the non-swapped physical memory that a task has used (in kiloBytes).
(alias rssize, rsz).
rssize RSS see rss. (alias rss, rsz).
rsz RSZ see rss. (alias rss, rssize).
从上面的解释来看autovacuum launcher 进程占据了约70%的不可交换物理内存.
free
total used free shared buffers cached
Mem: 24682828 24607764 75064 0 10128 6525184
-/+ buffers/cache: 18072452 6610376
Swap: 16779884 8794580 7985304
查看过往的SAR报告如下
kbmemfree kbmemused %memused kbbuffers kbcached kbswpfree kbswpused %swpused kbswpcad
02-06
Average: 79193 24603635 99.68 27148 8510109 13953197 2826687 16.85 967
02-07
Average: 81699 24601129 99.67 23619 8483106 13953178 2826706 16.85 985
02-08
Average: 73905 24608923 99.70 11064 8504025 13953172 2826712 16.85 970
02-09
Average: 78953 24603875 99.68 18140 8475291 13953172 2826712 16.85 968
02-10
09:20:01 AM 74356 24608472 99.70 2580 8361932 13953172 2826712 16.85 968
09:30:01 AM 74224 24608604 99.70 3412 8466448 13822936 2956948 17.62 2956
Average: 77257 24605571 99.69 7574 8344604 13196771 3583113 21.35 1432
02-11
Average: 87933 24594895 99.64 10818 7304608 10841381 5938503 35.39 2315
02-12
Average: 82804 24600024 99.66 15113 7149277 10581682 6198202 36.94 1978
02-13
Average: 85698 24597130 99.65 7834 7246716 10130720 6649164 39.63 3248
TODAY
Average: 73259 24609569 99.70 4444 6649324 8273769 8506115 50.69 21545
数据库日志中包含大量autovacuum 信息,其中有的执行时间比较长。(正常情况下一个autovacuum进程可以占用做多maintenance_work_mem设置的内存大小)
# 查看服务器上占用内存较多的进程pmem (百分比)
ps -eo user,rssize,vsize,sz,size,pmem,cmd --sort size|grep postgres
以下进程占用70%物理内存
postgres 17280128 27504196 6876049 26001504 70.0 postgres: autovacuum launcher process
怀疑是服务器内存处理出现问题造成.
到服务器上杀掉该进程
ps -ewf|grep auto
postgres 5821 5816 0 2010 ? 05:13:02 postgres: autovacuum launcher process
postgres=# select * from pg_terminate_backend(5821);
pg_terminate_backend
----------------------
t
(1 row)
日志输出如下:
2011-02-14 17:47:07.905 CST,,,5821,,4ccfbe6a.16bd,2,,2010-11-02 15:31:54 CST,1/0,0,LOG,00000,"autovacuum launcher shutting down",,,,,,,,,""
2011-02-14 17:47:10.005 CST,,,15773,,4d58fa1e.3d9d,1,,2011-02-14 17:47:10 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
autovacuum launcher的主进程为postgres进程,在被终结后自动产生.
ps -ewf|grep auto
postgres 15773 5816 0 17:47 ? 00:00:00 postgres: autovacuum launcher process
交换分区释放:
free
total used free shared buffers cached
Mem: 24682828 7307256 17375572 0 10676 6525460
-/+ buffers/cache: 771120 23911708
Swap: 16779884 54836 16725048
PS输出注释:
%mem %MEM ratio of the process’s resident set size to the physical memory on the machine, expressed as a
percentage. (alias pmem).
rss RSS resident set size, the non-swapped physical memory that a task has used (in kiloBytes).
(alias rssize, rsz).
rssize RSS see rss. (alias rss, rsz).
rsz RSZ see rss. (alias rss, rssize).
从上面的解释来看autovacuum launcher 进程占据了约70%的不可交换物理内存.