Thread: Vacuum DB
Being a relative newcomer to PostgreSQL, can someone give me quick tour of the vacuum function. The application that I built uses PostgreSQL as a statistics database (running on RedHat 6.2). It will typically make 10000-20000 SQL updates/inserts per hour. This is fine at first, but as the day progresses, the updates start falling behind, and the CPU usage by the postmaster process starts heading toward 70-80%. When I do a vacuum, the updates speed up dramatically, and the cpu usage goes back down to about 10%.
The questions are:
Is there a general rule on how often to run a Vacuum?
Is there an Auto Vacuum setting to have the postmaster to this automatically?
Is there a better approach to keeping this situation from happening (like bundling many updates into a single transaction)?
Any info would be appreciated.
John Sokel
On Thu, 31 Aug 2000, Sokel, John wrote: > It will typically make > 10000-20000 SQL updates/inserts per hour. This is fine at first, but as the > day progresses, the updates start falling behind, and the CPU usage by the > postmaster process starts heading toward 70-80%. When I do a vacuum, the > updates speed up dramatically, and the cpu usage goes back down to about > 10%. > > The questions are: > Is there a general rule on how often to run a Vacuum? Probably, but you should be able to calculate this. It takes X amount of time to do a vacuum (which is probably a function of how much work is needed by the vacuum), and you have the dbase response time as a function of how many inserts since the last vacuum. You do inserts until you (at least) gain enough time back by doing a vacuum. > Is there an Auto Vacuum setting to have the postmaster to this > automatically? Cron. > Is there a better approach to keeping this situation from happening (like > bundling many updates into a single transaction)? No idea. Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca
On Thu, 31 Aug 2000 ghaverla@freenet.edmonton.ab.ca wrote: > On Thu, 31 Aug 2000, Sokel, John wrote: > > > It will typically make > > 10000-20000 SQL updates/inserts per hour. This is fine at first, but as the > > day progresses, the updates start falling behind, and the CPU usage by the > > postmaster process starts heading toward 70-80%. When I do a vacuum, the > > updates speed up dramatically, and the cpu usage goes back down to about > > 10%. > > > > The questions are: > > Is there a general rule on how often to run a Vacuum? > > Probably, but you should be able to calculate this. It takes > X amount of time to do a vacuum (which is probably a function > of how much work is needed by the vacuum), and you have the > dbase response time as a function of how many inserts since > the last vacuum. You do inserts until you (at least) gain > enough time back by doing a vacuum. > > > Is there an Auto Vacuum setting to have the postmaster to this > > automatically? > > Cron. > > > Is there a better approach to keeping this situation from happening (like > > bundling many updates into a single transaction)? > > No idea. Bundling many updates into a single transaction will help. I'm not an expert but I guess that's because every update statement is a transaction of its own, will all locking overhead. So you will notice a performance improvement. But you'll have to VACUUM you db on a regular base anyway. The vacuum issue is unrelated to how many updates you pack into a transition, i think... > > Matter Realisations http://www.materialisations.com/ > Gordon Haverland, B.Sc. M.Eng. President > 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 > 780/481-8019 ghaverla @ freenet.edmonton.ab.ca > > > > .TM. -- ____/ ____/ / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _____/ _____/ _/ Colombo@ESI.it