edoceo

postgres: stats collector process - High I/O

In some situations the PostgreSQL stats collector process will have high I/O. More accurately; it has I/O that the administrator thinks is excessive - it really may not be.

In one circumstance, the complaint was caused by a steady 1.8KB/s write over a 24h period. That really is not very much for a large; active database.

Regardless, to reduce the I/O required by PostgreSQL there are a few tricks.

Checking I/O

The tool iotop is very good a measuring and displaying the I/O.

~ # iotop -o
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN      IO    COMMAND
27709 be/4 postgres    0.00 B/s 1381.06 K/s  0.00 %  1.97 % postgres: stats collector process
Total DISK READ: 0.00 B/s | Total DISK WRITE: 1381.52 K/s

Disable Autovacuum

Update postgresql.conf with the following setting.

autovacuum = off

Make this change and restart the PostgreSQL server; the I/O for stats collector should now be near zero. This may be a bit extreme; the autovacuum tool can be useful (but your DBA should decide).

Shrink Autovacuum

autovacuum = on
log_autovacuum_min_duration = 200
autovacuum_max_workers = 1
autovacuum_naptime = 960      # 16 minutes
autovacuum_vacuum_threshold = 128
autovacuum_analyze_threshold = 256

See Also

Loading Comments from Disqus...