On Wed, Feb 11, 2009 at 11:30 PM, Brad Nicholson <bnichols@xxxxxxxxxxxxxxx> wrote: > 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. I think i can take this route and monitor the io activity during vacuums. thanks everyone for their suggestions. -- mallah. >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
