Tuesday, January 4, 2011

Scheduled VACUUM instead of AutoVacuum

Once I found in Postgresql 9.0.1 that one of the report queries was hanged in the DB server while it was perfectly running on the development server which is a virtual machine with only 2 GB of RAM. Investigating properly, I found that some tables and indexes which were used in joining for the report query were bloated. I executed VACUUM ANALYZE manually and the system started to perform better.

Then I came to solution that the autovacuum is not vacuuming all the tables properly and it is fact.
Present status of some tables:

select last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables  where relname ='crm';
 -[ RECORD 1 ]----+------------------------------
last_vacuum      | 2011-01-04 12:39:57.586728+06
last_autovacuum  | 2011-01-04 16:13:30.254226+06
last_analyze     | 2011-01-04 12:40:17.680571+06
last_autoanalyze | 2011-01-04 16:14:17.402896+06

Same as for other tables.

Data shows that tables were not vacuumed using autovacuum process and the reason behind the autovacuum parameters.

At present, our autovacuum configuration are as follows:
autovacuum_naptime = 5min
autovacuum_vacuum_threshold = 150
autovacuum_analyze_threshold = 150
autovacuum_vacuum_scale_factor = 0.2  
autovacuum_analyze_scale_factor = 0.1 

Thresholds for -
crm = 150 + 0.2 * 1446016 =  289353.2

So, according to postgresql docs, if 289353.2 number of rows have been modified for crm table since the last autovacuum  then it will be a candidate for autovacuum and that's why the tables has not been vacuumed yet by the autovacuumn process.

So I prefer schedule vacuum for the following reasons -
As, we have no online users during night time, so we may easily schedule routine vacuum. It'll remove the overhead of autovacuum process from the DB server during peak hour of operations.
Besides, it'll eliminate the parameter settings overhead though by table-level storage parameter settings we can ensure autovacuum for the tables.
Autovacuum does not work for temporary tables.

3 Responses so far

  1. Get the best deals for oxycodone at oxymediuser. We have a great online selection at the lowest prices with Fast & Free shipping on many items!
    buy adderall online
    buy hydrocodone online
    buy oxycodone online
    buy norco online
    buy oxycontin online
    buy percocet online
    buy vicodin online

  2. At buyxanaxbar, you can safely and secure buy xanax online without prescription. Products are of the best quality from FDA Approved facilities. The packaging is safe and shipping is 100% discreet and delivery is very fast.
    Buy Percocet Online
    Buy Ambien Online
    Buy Norco Online
    Buy Adderall Online
    Buy Xanax Online

  3. Best Deals at buyonlinekart, an international prescription service provider, which contracts with International dispensaries and USA pharmacies, is a leader in referring orders for prescription and non-prescription medications on behalf of customers throughout the world
    buy hydrocodone online
    buy oxycodone online
    buy oxycontin online
    buy percocet online
    buy vicodin online
    buy norco online
    buy adderall online
    buy xanax online

Leave a Reply