大网数据广告活动
服务器运维问答

Too much I/O generated by postgres stats collector process

· 52CE 我爱测

I am using XenServer with several virtual machines having local postgres databases. Even when all applications are unused and the databases are idle, each vm causes constant storage network traffic which degrades the performance …

问题原文

原帖链接 · nn4l · CC BY-SA 3.0

I am using XenServer with several virtual machines having local postgres databases. Even when all applications are unused and the databases are idle, each vm causes constant storage network traffic which degrades the performance of the iscsi storage device.

After running iotop I have noted that the postgres stats collector process process is constantly writing to the disk at a rate of about 2 MByte/s.

I then disabled collecting of stats by editing /etc/postgresql/8.4/main/postgresql.conf:

#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------

# - Query/Index Statistics Collector -

track_activities = off
track_counts = off
...

as suggested in http://www.postgresql.org/docs/8.4/static/runtime-config-statistics.htm.

This eliminated the continuous writing, but are there any disadvantages turning off the statistics tracking?

Or should I rather place the pg_stat_tmp directory on a ramdisk to avoid the disk/network traffic?

The system is an up-to-date Debian 6.0.7 (squeeze) with postgres 8.4 and about 20 databases with about 50 tables, total dump file size is less than 100 MByte.

已采纳解答

原帖链接 · nn4l · CC BY-SA 3.0

Since upgrading PostgreSQL is not an option, I have tried placing the pg_stat_tmp directory on a tmpfs file system, which delivered a significant performance improvement. I am now running this on a few dozen systems for a couple of months without any noticeable drawbacks.

To do this, simply mount pg_stat_tmp with tmpfs in your /etc/fstab file:

# <file system> <mount point>                                <type>  <options>  <dump>  <pass>
tmpfs           /var/lib/postgresql/8.4/main/pg_stat_tmp     tmpfs   defaults,noatime,mode=1777,uid=postgres,gid=postgres,nosuid,nodev 0 0

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2013-04-01)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。