• 9

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191


File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

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:


# - 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.

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
  • 7
Reply Report
      • 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. "

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
Reply Report

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.

  • 0
Reply Report

Trending Tags