On Wed, 2009-02-11 at 22:57 +0530, Rajesh Kumar Mallah wrote: > On Wed, Feb 11, 2009 at 10:03 PM, Grzegorz Jaśkiewicz <gryzman@xxxxxxxxx> wrote: > > On Wed, Feb 11, 2009 at 2:57 PM, Rajesh Kumar Mallah > > <mallah.rajesh@xxxxxxxxx> wrote: > > > >>> vacuum_cost_delay = 150 > >>> vacuum_cost_page_hit = 1 > >>> vacuum_cost_page_miss = 10 > >>> vacuum_cost_page_dirty = 20 > >>> vacuum_cost_limit = 1000 > >>> autovacuum_vacuum_cost_delay = 300 > >> > >> why is it not a good idea to give end users control over when they > >> want to run it ? > > > > Effectively, you have control over autovacuum via these params. > > You have to remember, that autovacuum doesn't cost much, and it makes > > planner know more about data. > > It's not there to clean up databases, as you might imagine - it is > > there to update stats, and mark pages as free. > > > > So make sure you tweak that config fist, because I have a funny > > feeling that you just think that vacuuming bogs down your machine, and > > _can_ be turned off without any bad consequences, which is simply not > > true. > > our usage pattern is such that peak activity (indicated by load average) > during day time is 10 times during night hours. Autovacuum just puts > more pressure to the system. If less stressing version is used then > it shall take longer to complete one cycle, which would mean less > performance for longer time . Less performance queues up queries > and encourages people to re submit their queries which again > adds to bogging up the system. That's not exactly how it works in practise, if tuned properly. It may take longer, but it is less intensive while running. We had one system that had spikes happening due to the exact case you described - there were noticeably high IO wait times while certain tables were being vacuumed. We set the cost delay and the wait times dropped to the point where it was non-issue. Vacuums take twice as long, but there is no measurable impact to the performance. > In our case i feel the hardware is bit underscaled as compared to > load thats why i think running in lean hours is best of both worlds > no performance sacrifices and intelligent vacuuming. That is a different issue altogether. Not vacuuming a running system at all during peak hours is not considered intelligent vacuuming IMHO. There are plenty of use cases where small, frequent vacuums keep tables under control at a very low cost. Letting them go for extended periods of time without vacuuming causes bloat and eventual slowdowns to table access which manifest in higher IO usage across the board. If you really are dead set on vacuuming only at night, you may want to do a careful analysis of which tables need to be vacuumed and when, and trigger manual vacuums from cron. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
