Too much I/O generated by postgres stats collector process

Question

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.

Solution

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

Comments

[Halfgaar]

I did this for Postgresql 9.1. One of my servers had a continuous write of 1 MB/s throughout the day. This made it drop to almost nothing. It's approved by the docs, BTW: "... Pointing this at a RAM-based file system will decrease physical I/O requirements and can lead to improved performance. "