2013/2/1 Vlad Bailescu <vlad@xxxxxxxxxxxxxxxxxx>: > Hi everyone, > > We are experiencing a strange situation after upgrading our Postgres from > 8.4 to 9.1 and our OS from Debian 6 to Ubuntu 12.04 LTS. > > 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 (Hibernate ORM) we use a before_insert > trigger to insert rows into the right partition and in the master table and > then an after_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 8.4 to 9.1 (and moving from Debian 6 to Ubuntu > 12.04) we noticed a large increase in IO and traced it back to auto vacuum: > iotop and collectd report 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). we had similar problem - you can try to move stat file to ramdisk with setting stats_temp_directory with target on somewhere on ramdisk http://www.postgresql.org/message-id/CAFj8pRCRPv3i-9WwsSCa=my9M+J5B7K1i1c7TWi0pFLRyjkpWQ@xxxxxxxxxxxxxx Regards Pavel Stehule > > Can anyone please explain why this is happening and how we can get around > it? > > Thank you, > Vlad -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
