On Sun, Apr 21, 2013 at 6:46 AM, sunil virmani <sunhcl@xxxxxxxxx> wrote: > Hi, > > My databases are updated regularly so I am vacuuming frequently (every one > hour). Recently i also added template1 database to avoid over wrapping > problem. But somehow i am seeing strange behavior. > > Most of the time all db vacuuming finish in 30 secs. > > but once in a day or two > - My actual DB is taking less than 30 secs for vacuuming. > - Sometime template1 is taking 5 mins for vacuuming. > - Queries become exceptionally slow at that time for 5 mins ( specially > during the end). > > I am wondering what could be the reason of long time of template1 vacumming > sometime and slow query at end of vacumming. > > Do we need to template1 analyze regularly? What is ideal frequency of > template1 vacuuming only and analyze? > > My DB version is little old - 8.1.18. Well upgrade as soon as possible. 9.1 is pretty darn stable. There are two possible things that cause this kind of slowdown. One is a checkpoint. This is where postgresql writes out its own dirty buffers, and the other is a back OS level write flush. Both of these will cause your system to slow to a crawl. The fix for checkpointing is to adjust your postgresql.conf file's completion target and other settings, many of which, like completion target, do not exist in 8.1. Increasing checkpoint segments and checkpoint timeouts may help here. Depending on your OS you may or may not be able to reduce the two dirty*ratio settings, vm.dirty_background_ratio and vm.dirty_ratio. On many servers reducing these to 0 or something under 5 is a good first step. In almost no circumstance is a high setting good for large memory, database, or file server machines. Another possibility is that your kswap daemon is going nuts and swapping for no reason. Turning off swap can stop it. You'll see lots of so/si in iostat when that's happening, but no real reason for it. (i.e. no memory pressure, plenty free memory etc) I'm gonna just assume since you're running an old postgres you're probably not on more modern numa hardware and don't have an issue with zone_reclaim_mode = 1 that I've seen before. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
