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.

8 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

  4. Medical Pharmacy USA
    * No Prescription Required (NO RX)
    * Buy Direct and Save Time with Cryptocurrency
    * Reshipment is offered if the package does not get to location or refunds
    * High-Quality Pharmacy Grade Medicines.
    * Best and Affordable prices

    Buy Adderall Online | Buy Alprazolam 2mg Online | Buy Ambien 10mg Online | Buy Ativan Online Overnight Delivery | Buy Cheap Carisoprodol Online | Diazepam for Sale USA | Buy generic Fioricet Online | Best Place to Buy hydrocodone Online | Buy Lortab Online Legally | Buy Soma pills Online | Buy Cheap Xanax Online
    Buy Medicine Online

  5. Get 10% off on use coupon- "SALE10"
    All type of medicines available online 24*7 in Xanax stores and fast home delivery so get it now.

    Order here-
    Buy Ambien Online
    Buy Dilaudid Online
    Buy Ativan Online
    Buy Adderall Online
    Buy Tramadol Online
    Buy Methadone Online
    Buy norco online
    Buy Percocet Online
    Buy Xanax Online

  6. Welcome to Research Chem Lab We are USA and Euro based company specializing in retails wholesale distribution of the best pain medications. Note: We don’t manufacture this products we buy them from a network of highly qualify and trusted manufacturers.

    buy xanax 2mg online, buy Xanax online,Xanax For Sale On The Internet, Xanax For Sale   
    buy adderall 30mg online   
    buy rohypnol 2mg online   
    buy Valium 10mg online   
    buy percocet online   
    buy hydrocodone online    
    buy oxycodone online  
    buy klonopin online

  7. Adderall is a combination of medicine that is used to cure ADHD and sleeping disorders. It belongs to a class of drugs known as stimulants. It helps to increase the ability to pay attention, keep a check on behavioral problems, and focusing on an activity.

    Buy Adderall Online
    Adderall XR 5mg
    Buy Xanax Online

  8. Our Escort service in delhi ncr will make you feel delighted. If you are thinking of hiring a girl for your sexual pleasure then you should do it right now. You can pick any of our escort girls via our escort agency or website, or you can directly book your dream girl through outcall services. Your dream is not further from you, Dreaming for the best Escort service in Mahipalpur we will make it happen in every way and we know you're looking for someone for your night out's.
    Visit: https://www.escortserviceindelhincr.in/escort-services-in-mahipalpur/
    Sona : 9810685088

Leave a Reply