Tuesday, January 4, 2011

Scheduled VACUUM instead of AutoVacuum

125 comments
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.





125 Responses so far

  1. Vivek says:

    Thanks for letting me know about CRM. I was looking for some stuff to know more about autovacuum vs scheduled vacuum in PG. I got it here. Thanks again!

  2. @ Rumman

    Thanks to suggest these nice links. Literally I search a lots of days in hours and here you stops my search.

    Regards
    Amit

  3. Really simple and even more effective and this worked great, very useful tips
    Mobile repairing course in Hyderabad

  4. Vasu N says:

    it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly
    sharing with us that awesome article you have amazing blog…..
    Mobile repairing course in Hyderabad

  5. I read it entire and going to impart to my social companions. I making the most of your article and wanting to revise it all alone blog

    free animated desktop wallpaper

  6. Good and an informatory post. Thanks for the information. Good luck!
    Digital marketing institute laxmi nagar
    Digital marketing course in laxmi nagar

  7. Thank you for this post. Good luck!
    Digital marketing institute laxmi nagar
    Digital marketing course in laxmi nagar

  8. This comment has been removed by the author.
  9. thank you for sharing your view. Great work its very affective

    Led Lcd Repairing Course

    Mobile Repairing Course

    Laptop Repairing Course

    http://www.ledlcdcourse.com/

  10. Call on our toll-free number 1-844-797-8692 for apple id reset support or click on forgot apple id




  11. App Cloner Free is an application that will allow you to make exact copies of any app on your smartphone or tablet.

  12. samchakar says:

    Norton Internet security is the best option for protection against online threats of all kinds. Users might have some technical issues in norton login when they need to activate or install it on their devices.
    If facing such kind of problems then make call at our norton.com login support number and find best and effective troubleshooting steps from our experts. Our services are available for other antivirus products also.

  13. holiopeta says:

    Hotmail is one of the most popular emailing platforms being used for multiple types of information exchange. When you have issues with hotmail login because of lost password or wrong user name and due to many other faults then take our help.
    Our technical executives can assist you at any point of time though our toll free hotmail sign in support number and through chat also.

  14. samchakar says:

    For making your webmail account free from errors and other technical issues you should follow some steps or you can take online assistance of our professionals through roadrunner login support number.

    Through our toll free helpline numbers any user can get rid of unwanted technical glitches occurring during roadrunner sign in or sending and receiving emails with attachments.

  15. holiopeta says:

    When your email account is affected with suspicious activities and technical issues are causing problem in secure aol login then you must take assistance from genuine experts.

    As there are lots of fake assistance providers so being a responsible agency we are available to help in their technical hurdles through aol sign in support number.

  16. holiopeta says:

    If you are one of those users who is facing various technical glitches with iCloud account then by contacting our technical professionals through icloud login support number your all issues can be resolved instantly.

    Call our support number and get help regarding icloud sign in and for other problems also.

  17. holiopeta says:

    When user becomes failed in aol mail login then generally this issues is related to wrong user name and password but if your correct account credentials are not working then you must take online assistance from experts.

  18. Unknown says:

    If you want to help on make google my homepage and related services you can call us our toll-free number our expert technicians provide you best support.
    Our expert technicians will help you at any time when will you call them to make yahoo my homepage and its relevant services. You may also chat with our expert team for any technical help.

  19. Unknown says:

    Some time customer is facing a problem during rr login or want to recover the forgotten password, our team is specially trained and highly qualified for resolving that type of issue, so please call our team without any hesitation our toll free customer service number 1-844- 797-8692, our team also giving 24 * 7 hour service to the customer through the online process like this calling or chat process and also help in recovering the rr sign in and relevant issues.

  20. samchakar says:

    For security of your AOL email account you can take best suggestions from our technical experts through aol login support number. Our teams will give you effective tips for complete security of account and information as well.

    Being an independent third party we are always available to users who are dealing with various types of faults and issues in their account. Just get connected with us and make aol sign in smoother and error free.

  21. samchakar says:

    If you are facing attachment relate issues with you outgoing or incoming emails such as unable to attach images or text files the call our toll free hotmail login support number.

    Our technical support teams will assist your by finding exact causes behind such problems and will offer you most appropriate solutions. Our hotmail sign in support numbers are accessible and available to all users.

  22. This blog is really very well written and is very much informative helped me a lot in understanding complex HP Customer Support easily.
    To know more, you can visit here: HP Support Number

  23. samchakar says:

    You are unable and seeking for online help to change printer offline to online then call us because we are the most trustworthy and genuine support providing agency. Here experts give complete time to understand your issues and resolve them permanently.
    Our experts are having good experience in support for printers and we cover all the types and brands of printing devices. So when your printer status offline just give us a call and get excellent support. User can chat also if they want other contact medium rather than calling.

  24. Your Blog Post is nice. Worldwide Escorts Classifieds Directory website escortsfame for high profile escorts, massage girl, sensual & sizzling sexy full satisfaction call girls ads listing search here by your country location or nearest area in your city.

  25. Great Post!!

    Ashoka Packers and Movers in Hyderabad is the most reliably positioned organization and has conveyed more than 56,000 homes all over India. The administrations given by us incorporate Safest Packing of the family, Loading of the Goods, Unloading of the home things with most extreme security, Transportation of family as fast as conceivable with the time-bound conveyance framework. We at Ashoka Packers and Movers likewise ensure the protection of the merchandise of the client and guarantee that the way toward pressing and moving is done in the most secure of the habits.


    Packers And Movers Hyderabad
    Packers And Movers Jubilee Hills
    Packers And Movers Lingampally
    Packers And Movers Kothaguda
    Packers And Movers Secunderabad
    Packers And Movers Mehdipatnam

  26. "Great Post!!

    Ashoka Packers and Movers in Hyderabad is the most reliably positioned organization and has conveyed more than 56,000 homes all over India. The administrations given by us incorporate Safest Packing of the family, Loading of the Goods, Unloading of the home things with most extreme security, Transportation of family as fast as conceivable with the time-bound conveyance framework. We at Ashoka Packers and Movers likewise ensure the protection of the merchandise of the client and guarantee that the way toward pressing and moving is done in the most secure of the habits.


    Packers And Movers Hyderabad
    Packers And Movers Jubilee Hills
    Packers And Movers Lingampally
    Packers And Movers Kothaguda
    Packers And Movers Secunderabad
    Packers And Movers Mehdipatnam

  27. when your system is locked then safety of hardware and crucial information present in computer is most important. It is recommended to users to take experts advice only for better recovery of aol login resources. We help to recover your aol mail login password which you might lost or got steal by someone. We strongly suggest you to change aol.com login for future. AOL Technical Support & Help with Live Chat AOL Technical Support & Help with Live Chat and call.

  28. Get instant help through our best and professional executives which are always ready to give sure and easy tips to remove aol login issues. Call us on toll free number right now for better experience. We provide excellent aol sign in support helpline specially in the form of computer Customer Service Support.

  29. HP is one of the most popular brands for the printer device. If you face any problem with your HP printer, call HP Printer Support Number. This HP Printer Phone Number provides reliable tech supports to all the HP customers. When you have trouble to setup your printer then printer support number provider for customers we maintain 100 per cent transparency and authenticity. We provide them with value-added solutions and maximum support possible.

  30. Epson printer support service provide to installation Our assistant can help you to fix your all printer related issues such as Epson printer installation, printer setup, Epson printer not printing, connect Epson printer, add Epson printer. Contact to the Epson printer support number for accurate and instant help.

  31. If you are facing issues with sbcglobal email then Contact on 1-800-436-6070 sbcglobal support number to fix the technical issues via expert technicians.Sbcglobal customer service excutive are well expert and help their customers 24x7.

  32. Take the help of email specialists to resolve your roadrunner services related problems. with the help of professional assistance all your mail troubleshoots get resolve immediately. Dial +1-800-436-6070 roadrunner support number it is available 24/7 for your help. 

  33. If you are not able to reset your bellsouth password or face any other issues, feel free to dial our bellsouth support number anytime 24 X 7 in order to get the best help. The support team are always available for your help and provide you with the basic troubleshooting steps along with 100 % customer satisfaction just by dialing +1-800-436-6070 bellsouth phone number. 

  34. Are you facing any issues during the different macafee products? Is need you any help about macafee antivirus then this is the right place. Here you can solve your problems by just dialing +1-800-436-6070 macafee support number. It is available 24/7 for your help. 

  35. Yahoo support number to fix login, lost password of yahoo account. Contact for yahoo support in united states and canada to get solution all the necessary errors.Dial +1-800-436-6070 yahoo help number to get help about yahoo mail service. 

  36. icloud account and password is needed when you want to log into icloud login on your Mac, iPhone, iPad or iPod. Your iCloud password protects your personal files on iCloud, such as personal photos, contacts. If you forgot iCloud password, you are unable to access these files. call now on our icloud help number 1-800-436-6070.

  37. If you're having problems sending mail, there are a few troubleshooting steps you can take to fix the problem. Our aol support customer support team is 24*7 available for your help, so you can dial our toll-free aol help number 1-800-436-6070 anytime.

  38. Unknown says:

    Now you get several options including the account live password reset information', account.live.com/password/reset information ˜as well as other options. Click on the Account information and under that go to the option called customer support helpline number or vist our website.

  39. Unknown says:

    How to Reset Microsoft account live password reset What is Microsoft Account? Microsoft Account is the combination of an email address and a password that are used to account.live.com.sign in to a number of different services by Microsoft Tech customer Support.

  40. Unknown says:

    Are you facing any problem with outlook login account or hotmail login call us on toll free number 1-800-436-6070 to get more visit our website.

  41. Check the blog to get arrangement additionally you can call us at our with toll no 1-877-301-0214. Our sans toll no is accessible on our blog HP Printer Technical Support. Benevolently visit our site to get the best arrangement.
    Hp Printer Customer Support Contact Number

  42. roadrunner help number help the clients who are looking for roadrunner support, roadrunner password recovery, roadrunner help number. Call now 1-800-436-6070 for more information.

  43. To get one stop solution to all your issues instantly with Bellsouth Customer Care Toll free Number, Tech Support. Contact 1-800-436-6070 bellsouth support number is a fast and easy way to resolve all bellsouth email related issues, call our bellsouth help number.

  44. Need help resetting your apple id reset ? It's easy, but before you do, be sure to keep in mind that you will need to sign back into devices that you are currently logged into including any Apple computer, iPhone, iPod touch, iPad.

  45. yahoo help number for those that have some issue came arise in yahoo email account. If you have faced sign in issue, account setup problem, password recovery, receiving and sending mail and spam blocking issue. if you need help you can call us on our yahoo support number 1-800-436-6070 and get information.

  46. icloud account and password is needed when you want to log into icloud login on your Mac, iPhone, iPad or iPod. Your iCloud password protects your personal files on iCloud  such as personal photos, contacts. If you forgot iCloud password, you are unable to access these files. call now on our icloud help number 1-800-436-6070.

  47. If you are Facing issues with sbcglobal email then Contact on 1-800-436-6070 sbcglobal support number to fix the technical issues via expert team. sbcglobal customer service excutive are well expert and help their customers 24x7.

  48. Having aol Mail Login Issues? Check out our detailed video on aol.com Sign in and Login Guide. Related Keywords: aol.com, aol Mail, aol mail Login, www.aol.com, aol Mail Sign In, aol Account. Call on our toll free number 1-800-436-6070 for any aol mail login related problem.

  49. Moving family unit products starting with one area then onto the next is dependably a major test. For Agarwal Packers and Movers give the best chance to me. I moved to another area in Mumbai and I needed to move my family unit products, a vehicle with Agarwal Company. For the moving exercises, Agarwal Packers and Movers staff affirmed all exercises with full trust and care our home move securely.

    Agarwal Packers Customer feedback
    Agarwal Packers Customer Review
    Agarwal Packers Customer complaints

  50. Computers are having such important role in our tasks then security of such devices also becomes a serious concern. There are lots of threats available in aol mail sign in, which cause bad impacts on our crucial tasks. you need to contact our www aol com technical team for instant help.
    you might face various issues like aol mail login, aol sign in, aol login. Then you need to contact our best and certified technical experts are appointed in our agency who is available 24x7 in order to offer most appropriate and long term solutions.

  51. Find out how to sign up for aol mail login and what to do if you have account problems. Don't worry our best technical team members are help you. Get instant help through our best and professional executives which are always ready to give sure and easy tips to remove computer issues.

    When your email blocked setup your free aol login account to access mails, data and store your files, folders, images and videos for free. Call us on tollfree number right now for better experience.

  52. We help to recover your aol sign in which you might lost or got steal by someone. We strongly suggest you to change aol password for future. So in this aspect it is always recommended for users to consider technical support team for instant help and perfect solution.
    If you have problem to aol.com sign in then our executives are simply awesome they provide wonder full solution for all issues which makes easy to access your Aol account.

  53. We present excellent email support helpline specially in the form of printer customer support for USA/ Canada users. So in this aspect it is always recommended for users to consider technical support team for instant help and perfect solution.
    We help to setup your printer. We strongly suggest you through printer support number for future. Our printer support technical team help with live chat to Technical Support & Help with live chat and call.

  54. QuickBooks Enterprise Support Number Edition is not just an accounting software but a total ERP solution within itself. These days, it had been evident that Medium Scale Business and Industry specific business like Manufacturing, Contractors, Wholesalers, Retail, Professional Services etc.

  55. For several associated with business organizations, it is and it has always been a challenging task to manage the business accounts in an effective way by finding the appropriate solutions. The right solutions are imperative when it comes to development of the business. Therefore, QuickBooks is present for users around the world since the best tool to supply creative and innovative features for business account management to small and medium-sized business organizations. If you’re encountering any kind of QuickBooks’ related problem, you can get dozens of problems solved just by with the QuickBooks Desktop Support
    .

  56. QuickBooks users in many cases are present in situations where they should face a number of the performance plus some other errors due to various causes inside their computer system. If you need any help for QuickBooks errors from customer care to get the means to fix these errors and problems, it is simple to connection with QuickBooks Support to get instant help with the guidance of your technical experts.

  57. Every user can get 24/7 support services with our online technical experts using QuickBooks support phone number. When you’re stuck in a situation where you can’t find a way to eliminate a concern, all you need is to dial QuickBooks Help Number. Be patient; they will certainly inevitably and instantly solve your queries.

  58. Intuit anticipated to bridge the gap along with your accounting professionals, ultimately so long as full audit trail capabilities, double-entry accounting tasks and increased functions. By 2000, Intuit had industrialized Basic and Pro versions regarding the software and, in 2003, QuickBooks Tech Support Number underway offering industry-specific versions, with workflow procedures and reports designed for every one of these business types along side terminology linked to the trades.

  59. Unknown says:

    QuickBooks point of sale system performs many essential functions ranging from the accounting process to data processing. It also assists the small and medium businessman in a day to day bookkeeping processes. But, at times it falls prey to technical glitches like QuickBooks Error Code 176103 which says – ‘Unable to Activate product’.

  60. Unknown says:

    Firstly, click on file and choose the chance Open & Restore. Now open the file and then click on Update QuickBooks Tech Support Number File for New Version. And from now on maybe you are all set.

  61. Unknown says:

    But, I will be at your side. If you hire our service, you might be receiving the greatest solution. We're going to assure you as a result of error-free service. QuickBooks Customer 247 Support Phone Number is internationally recognized. You need to arrive at used to comprehend this help.

  62. Our QuickBooks Support are not restricted to the above list. A large number of people are successfully availing the outstanding popular features of our tech support team desk for QuickBooks. With a large client base, we have achieved the 100% satisfaction rate from customers across the world. You too can avail our support services on just a call. Don’t hesitate to contact us at QuickBooks Enterprise Support contact number in the event you encounter almost any technical bug in QuickBooks.

  63. If it gets burdensome for you to comprehend the instructions provided via QuickBooks payroll support phone number,QuickBooks Payroll Support Number

  64. Unknown says:

    Since outsourcing is very a costly affair, this is why the explanation for an alternative emerged. When this occurs, a far better choice is payroll support with accounting software like QuickBooks that assimilates the processing of payroll as a part of the program. QuickBooks Payroll Support is a renowned accounting software on the market which comes along the payroll process ensuring all of the financial QuickBooks Payroll Technical Support Number tax related activities of this business are completed effortlessly.

  65. Even if you are feeling that enough time is odd to call for help, just pick up your phone and dial us atQuickBooks Support Phone Number US because you can expect our support services 24*7. We genuinely believe that the show must go on and thus time is just not a problem for all those because problems try not to come with any pre-announcements.

  66. Unknown says:

    There can be occasions as soon as you might face some form of delay in reaching us, let’s say during the time of filing taxes because there is a lot of hush-hush then. We assure QuickBooks 247 Customer Support
    you that folks will revert for you personally in less time and work out us accessible to you at QuickBooks Premier Support Phone Number US.

Leave a Reply

Labels