Karl Wright wrote: > Alvaro Herrera wrote: > >Karl Wright wrote: > > > >>This particular run lasted four days before a VACUUM became essential. > >>The symptom that indicates that VACUUM is needed seems to be that the > >>CPU usage of any given postgresql query skyrockets. Is this essentially > >>correct? > > > >Are you saying you weren't used to run VACUUM all the time? If so, > >that's where the problem lies. > > Postgresql 7.4 VACUUM runs for so long that starting it with a cron job > even every 24 hours caused multiple instances of VACUUM to eventually be > running in my case. So I tried to find a VACUUM schedule that permitted > each individual vacuum to finish before the next one started. A vacuum > seemed to require 4-5 days with this particular database - or at least > it did for 7.4. So I had the VACUUM schedule set to run every six days.
How large is the database? I must admit I have never seen a database that took 4 days to vacuum. This could mean that your database is humongous, or that the vacuum strategy is wrong for some reason. You know that you can run vacuum on particular tables, right? It would be probably a good idea to run vacuum on the most updated tables, and leave alone those that are not or little updated (hopefully the biggest; this would mean that an almost-complete vacuum run would take much less than a whole day). Or maybe vacuum was stuck waiting on a lock somewhere. > I will be experimenting with 8.1 to see how long it takes to complete a > vacuum under load conditions tonight. You can also turn autovacuum on in 8.1, which might help quite a bit with finding a good vacuum schedule (you would need a bit of tuning it though, of course). In any case, if you are struggling for performance you are strongly adviced to upgrade to 8.2. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "No single strategy is always right (Unless the boss says so)" (Larry Wall) ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
