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.

I have a postgresql server running on a small linode partition. It is running a web app for a site which gets on average 0 users / minute (it has not launched yet). At one point, I loaded up the database with a lot of data, to do a stress test. I have since deleted the data and now have a few hundred megabyte DB just sitting there without anyone asking anything of it.

The linode server is reporting an average IO rate of 2.6k blocks a second. I ran pidstat -d 10 90 and figured out that the culprit was postgres:

Average:          PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
Average:        28836   1251.31      0.00      0.00  postgres

Further digging shows that approximately once every three minutes postgresql decides to do some heavy lifting (a short burst of reading at 21mb/s).

I know the bare minimum when it comes to DB administration and have an issue. My research unearthed things like VACUUMing and such. However, I am not sure what is happening, why, and how to stop it. There is absolutely no load on this machine. Can anyone point me in the right direction?

share|improve this question
    
Enabling more detailed logging will help. Turn query logging, checkpoint logging, autovaccum logging, etc on, and watch the logs. I wonder if you DELETEd the rows from the tables instead of TRUNCATEing tables. In that case Pg might still be going through and doing post-deletion housekeeping. –  Craig Ringer Feb 19 '13 at 23:35
    
Also: for performance issues, you should add your Postgres version + (relevant) postgres.conf -lines. (part of) your schema (cascading deletes? triggers?) plus the exact command you used to invoke this behavior will also help. But, @CraigRinger 's post-delete vacuuming appears to be the usual suspect here. –  wildplasser Feb 20 '13 at 11:33
add comment

migrated from stackoverflow.com Feb 22 '13 at 14:07

This question came from our site for professional and enthusiast programmers.

1 Answer

If you have added a lot of data and later deleted it, most likely Postgres starts auto-vacuuming process on your tables.

Auto-vacuum is designed to be rather gentle and not put heavy load on running server - otherwise it will significantly reduce system performance if it was serving live data.

Since your database is mostly empty and does not have any clients, you can fix this by performing full vacuum:

VACUUM FULL VERBOSE ANALYZE;

On a few hundred megabytes database it should not take more than a few minutes, and should stop any further activity on your database.

However, it is not recommended to perform full vacuum on production database because it locks tables as it goes - do simple VACUUM instead, or rely on auto-vacuum.

share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.