pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Missing the point of autovacuum 3+ messages / 3 participants [nested] [flat]
* Missing the point of autovacuum @ 2007-01-06 18:59 Daryl Herzmann <akrherz@iastate.edu> 2007-01-06 19:33 ` Re: Missing the point of autovacuum Tobias Brox <tobias@nordicbet.com> 2007-01-06 19:46 ` Re: Missing the point of autovacuum Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 2 replies; 3+ messages in thread From: Daryl Herzmann @ 2007-01-06 18:59 UTC (permalink / raw) To: pgsql-performance Greetings, I've been running Postgresql for many years now and have been more than happy with its performance and stability. One of those things I've never quite understood was vacuuming. So I've been running 8.1.4 for a while and enabled 'autovacuum' when I first insalled 8.1.4 ... So in my mind, my database should stay 'clean'... As the months have gone by, I notice many of my tables having *lots* of unused item pointers. For example, There were 31046438 unused item pointers. Total free space (including removable row versions) is 4894537260 bytes. 580240 pages are or will become empty, including 7 at the end of the table. 623736 pages containing 4893544876 free bytes are potential move destinations. Perhaps I shouldn't be concerned with this? In all, I've got around 400 GB of data on postgresql, but am not sure how much of it is old data. Many of my tables have 100,000s of updates per day. Do I need to be running old fashioned 'vacuumdb' routinely as well? I guess I just don't understand why autovacuum is not automatically doing this for me and I have tables with so many unused item pointers. thanks! daryl ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Missing the point of autovacuum 2007-01-06 18:59 Missing the point of autovacuum Daryl Herzmann <akrherz@iastate.edu> @ 2007-01-06 19:33 ` Tobias Brox <tobias@nordicbet.com> 1 sibling, 0 replies; 3+ messages in thread From: Tobias Brox @ 2007-01-06 19:33 UTC (permalink / raw) To: Daryl Herzmann <akrherz@iastate.edu>; +Cc: pgsql-performance [Daryl Herzmann - Sat at 12:59:03PM -0600] > As the months have gone by, I notice many of my tables having *lots* of > unused item pointers. For example, Probably not the issue here, but we had some similar issue where we had many long-running transactions - i.e. some careless colleague entering "begin" into his psql shell and leaving it running for some days without entering "commit" or "rollback", plus some instances where the applications started a transaction without closing it. > Perhaps I shouldn't be concerned with this? In all, I've got around 400 > GB of data on postgresql, but am not sure how much of it is old data. I didn't count the zeroes, but autovacuum does have rules saying it will not touch the table until some percentages of it needs to be vacuumed off. This is of course configurable. > Do I need to be running old fashioned 'vacuumdb' routinely as well? I > guess I just don't understand why autovacuum is not automatically doing > this for me and I have tables with so many unused item pointers. If you have some period of the day with less activity than else, it is a good idea running an old-fashionated vacuum as well. The regular vacuum process will benefit from any work done by the autovacuum. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Missing the point of autovacuum 2007-01-06 18:59 Missing the point of autovacuum Daryl Herzmann <akrherz@iastate.edu> @ 2007-01-06 19:46 ` Tom Lane <tgl@sss.pgh.pa.us> 1 sibling, 0 replies; 3+ messages in thread From: Tom Lane @ 2007-01-06 19:46 UTC (permalink / raw) To: Daryl Herzmann <akrherz@iastate.edu>; +Cc: pgsql-performance Daryl Herzmann <akrherz@iastate.edu> writes: > As the months have gone by, I notice many of my tables having *lots* of > unused item pointers. For example, > There were 31046438 unused item pointers. > Total free space (including removable row versions) is 4894537260 bytes. > 580240 pages are or will become empty, including 7 at the end of the > table. > 623736 pages containing 4893544876 free bytes are potential move > destinations. This definitely looks like autovac is not getting the job done for you. The default autovac settings in 8.1 are very un-aggressive and many people find that they need to change the settings to make autovac vacuum more often. Combining autovac with the traditional approach of a cron job isn't a bad idea, either, if you have known slow times of day --- autovac doesn't currently have any concept of a maintenance window, so if you'd rather your vacuuming mostly happened at 2AM, you still need a cron job for that. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-01-06 19:46 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-01-06 18:59 Missing the point of autovacuum Daryl Herzmann <akrherz@iastate.edu> 2007-01-06 19:33 ` Tobias Brox <tobias@nordicbet.com> 2007-01-06 19:46 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox