Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are facing a strange problem since yesterday.

In a database of ours there is a partitioned table containing historical data. That is, every week's data goes to a different partition and stays there till the end of time. There are no further modifications to these - no UPDATE, no DELETE, no TRUNCATE, even no INSERT once the week has passed.

At least this is the theory. We realized that some bug caused a heavy multiplication of a part of the data. The order of magnitude is about 60k useful and some 15-20M unnecessary rows. There are other rows as well, unaffected by the bug - the overall partition size varies between 6 and 15 GBs.

Once the bug was fixed, we decided to remove the unnecessary rows, keeping the latest one of the sometimes 9000 siblings. Of course, this means that some serious vacuuming will be needed as around a quarter of the tables is gone now. The nice autovacuum daemon jumps on the back of the partitions a few minutes after finishing the removal. And, at least for some of the partitions, when it finishes, has a few minute's rest, and starts again. And again. And...

It seems that this has an adverse effect on other IO-heavy processes, so we tried to VACUUM ANALYZE manually the table(s) in question. As expected, this terminates the autovacuum process, removes what should be removed and exists successfully. And then the autovacuum process jumps again, and again, and... Each run lasts more than two hours.

Have you ever experienced this? If yes, what was the solution? I have a feeling that a VACUUM FULL would solve it, but as the applications must be able to read the tables nearly continuously, it is not an option. Actually, we are migrating away from that structure, and once it is done, the problem disappears, but this issue seems to slow the migration process down significantly. So any idea would be more than welcome.

share|improve this question
    
Have you tried a VACUUM FREEZE issued on the partitions? That should help keep autovacuum from running over and over. Do you use two-phase commit in your system at all? I believe I read something recently about that causing autovac issues, but since our apps don't use 2PC I can't verify that assertion. –  bma Sep 18 '13 at 15:12
    
Unfortunately, VACUUM FREEZE generates a really high IO load which block normal operation. The ever-ticking autovacuum is still a better solution... Probably I'll try it scheduled to some low-activity period. –  dezso Sep 19 '13 at 11:34
1  
Would it be feasible to recreate partitions with create table newpart as select * from oldpart and find a small window of time when you may lock out the readers and switch oldpart with newpart, then drop oldpart? –  Daniel Vérité Sep 19 '13 at 15:54
    
Can you show output of SELECT version()? –  filiprem Nov 12 '13 at 14:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.