Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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
share|improve this answer

Upgrade PostgreSQL. At absolute minimum make sure you're on the latest 8.4 release; if that doesn't address it and it's practical to do so you should probably upgrade to 9.2. At least some issues around the stats collector have been addressed since 8.4, and will be reaching end-of-life in about a year. You may be able to find more information by searching the pgsql-general mailing list archives.

You shouldn't have too many problems upgrading from 8.4 to 9.2, though as usual you must read the upgrade section of the release notes for each .0 release inbetween (9.0, 9.1 and 9.2). Pay particular attention to standard_conforming_strings and bytea_output.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.