11

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.

3 Answers 3

8

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
1
  • 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. "
    – Halfgaar
    Jan 13, 2015 at 13:02
0

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.

0

Same problem here. I also disabled track_* and so on.

The side effect is that autovacuum is using this collected data to fire up.

So, I take care to schedule nightly a vacuumdb.

Other solution is to set autovacuum_naptime higher enough to have system resting.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .