0

We have an application where we are collecting timestamp-based data. Since a month of data goes over 30M rows (and growing each month), we decided to partition the data on an year-and-month basis.

We basically have one master table and partition tables like master_y2013m01 and so on. Because of our application stack we use a before insert trigger to insert rows into the right partition and in the master table and then a post insert trigger to delete them from master table (we need the original insert to return the inserted row info).

This means the master table becomes quickly populated with dead tuples and triggers the autovacuum frequently (we are using default settings for autovacuum).

After upgrading our DB from PostgreSQL 8.4 to 9.1 we noticed a large increase in IO and traced it back to auto vacuum: iotop reports bursts of 4-6M/s taking 90 seconds followed by a 30 seconds pause. That's a lot of data to chew on especially since the master table is only a couple of megabytes (indexes included).

Can anyone explain why this is happening and how we can get around it?

Vlad
  • 187
  • 1
  • 10
  • I'm sure the Postgres engineers can explain it - unfortunately they don't hang out on Server Fault much. [They do have mailing lists and an IRC channel though](http://www.postgresql.org/support/). They will probably ask you a bunch of questions about your configuration in the process of troubleshooting. When you get an answer please let us know - I'd be interested in finding out if this is a known issue since I'm going to be doing the same upgrade soon :-) – voretaq7 Jan 31 '13 at 16:29
  • @voretaq7 Thanks for the suggestion, they were very helpful! – Vlad Feb 01 '13 at 13:50

1 Answers1

1

With help from Postgres developers we have found an answer, it seems that since 9.0 ANALYZE will also run on child tables when running on the parent. Since our use-case triggered the autovacuum very often, that triggered an ANALYZE before running as well and it was also analyzing the child tables.

We settled on using a bigger threshold for autovacuum:

ALTER TABLE ... SET (autovacuum_analyze_threshold  = ...);

That will decrease the ANALYZE frequency and keep our IO, CPU WAIT and LOAD values down.

Vlad
  • 187
  • 1
  • 10