Idea for getting rid of VACUUM FREEZE on cold pages
in [PgSql]
|
Prev: [HACKERS] beta testing - planner bug - ERROR: XX000: failed to build any 2-way joins
Next: [HACKERS] beta testing - pg_upgrade bug fix - double free
From: Russell Smith on 2 Jun 2010 06:38 On 28/05/10 04:00, Josh Berkus wrote: >> Consider a table that is >> regularly written but append-only. Every time autovacuum kicks in, >> we'll go and remove any dead tuples and then mark the pages >> PD_ALL_VISIBLE and set the visibility map bits, which will cause >> subsequent vacuums to ignore the all-visible portions of the table... >> until anti-wraparound kicks in, at which point we'll vacuum the entire >> table and freeze everything. >> >> If, however, we decree that you can't write a new tuple into a >> PD_ALL_VISIBLE page without freezing the existing tuples, then you'll >> still have the small, incremental vacuums but those are pretty cheap, >> > That only works if those pages were going to be autovacuumed anyway. In > the case outlined above (which I've seen at 3 different production sites > this year), they wouldn't be; a table with less than 2% updates and > deletes does not get vacuumed until max_freeze_age for any reason. For > that matter, pages which are getting autovacuumed are not a problem, > period; they're being read and written and freezing them is not an issue. > > I'm not seeing a way of fixing this common issue short of overhauling > CLOG, or of creating a freeze_map. Darn. > Don't you not get a positive enough effect by adjusting the table's autovacuum_min_freeze_age and autovacuum_max_freeze_age. If you set those numbers small, it appears to me that you would get very quickly to a state where the vacuum would example only the most recent part of the table rather than the whole thing. Does that give you enough of a win that it stops the scanning and writing of the whole table which reduces the performance problem being experienced. It's not a complete solution, but does it go someway? Regards Russell -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Alvaro Herrera on 2 Jun 2010 13:51 Excerpts from Russell Smith's message of mié jun 02 06:38:35 -0400 2010: > Don't you not get a positive enough effect by adjusting the table's > autovacuum_min_freeze_age and autovacuum_max_freeze_age. If you set > those numbers small, it appears to me that you would get very quickly to > a state where the vacuum would example only the most recent part of the > table rather than the whole thing. The problem is that vacuum doesn't know that a certain part of the table is already frozen. It needs to scan it completely anyways. If we had a "frozen" map, we could mark pages that are completely frozen and thus do not need any vacuuming; but we don't (I don't recall the reasons for this. Maybe it's just that no one has gotten around to it, or maybe there's something else). -- Álvaro Herrera <alvherre(a)commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 2 Jun 2010 14:05 Alvaro Herrera <alvherre(a)commandprompt.com> writes: > The problem is that vacuum doesn't know that a certain part of the table > is already frozen. It needs to scan it completely anyways. If we had a > "frozen" map, we could mark pages that are completely frozen and thus do > not need any vacuuming; but we don't (I don't recall the reasons for > this. Maybe it's just that no one has gotten around to it, or maybe > there's something else). Offhand I think the reason is that you'd have to trust the frozen bit to be 100% correct (or at least never set to 1 in error). Currently, both the FSM and visibility forks are just hints, and we won't suffer data corruption if they're wrong; so we don't get too tense about WAL logging or fsync'ing updates. I believe Heikki is looking into what it'd take to make the visibility map 100% reliable, in connection with the desire for index-only scans. If we get that and the overhead isn't too terrible maybe we could build a frozen-status map the same way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 2 Jun 2010 14:16 On Wed, Jun 2, 2010 at 2:05 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre(a)commandprompt.com> writes: >> The problem is that vacuum doesn't know that a certain part of the table >> is already frozen. It needs to scan it completely anyways. If we had a >> "frozen" map, we could mark pages that are completely frozen and thus do >> not need any vacuuming; but we don't (I don't recall the reasons for >> this. Maybe it's just that no one has gotten around to it, or maybe >> there's something else). > > Offhand I think the reason is that you'd have to trust the frozen bit > to be 100% correct (or at least never set to 1 in error). Currently, > both the FSM and visibility forks are just hints, and we won't suffer > data corruption if they're wrong; so we don't get too tense about WAL > logging or fsync'ing updates. I believe Heikki is looking into what > it'd take to make the visibility map 100% reliable, in connection with > the desire for index-only scans. If we get that and the overhead isn't > too terrible maybe we could build a frozen-status map the same way. We could, but I think we'd be better off just freezing at the time we mark the page PD_ALL_VISIBLE and then using the visibility map for both purposes. Keeping around the old xmin values after every tuple on the page is visible to every running transaction is useful only for forensics, and building a whole new freeze map just to retain that information longer (and eventually force a massive anti-wraparound vacuum) seems like overkill. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Alvaro Herrera on 2 Jun 2010 15:10
Excerpts from Robert Haas's message of mié jun 02 14:16:33 -0400 2010: > We could, but I think we'd be better off just freezing at the time we > mark the page PD_ALL_VISIBLE and then using the visibility map for > both purposes. Keeping around the old xmin values after every tuple > on the page is visible to every running transaction is useful only for > forensics, and building a whole new freeze map just to retain that > information longer (and eventually force a massive anti-wraparound > vacuum) seems like overkill. Reducing the xid wraparound horizon "a bit" is reasonable, but moving it all the way forward to OldestXmin is a bit much, methinks. Besides, there's another argument for not freezing tuples immediately: they may be updated shortly thereafter, causing extra churn for no gain. I'd prefer a setting that would tell the system to freeze all tuples that fall within a safety range whenever any tuple in the page is frozen -- weren't you working on a patch to do this? (was it Jeff Davis?) (BTW maybe instead of separate visibility and freeze maps we could have two bits in the visibility map?) -- Álvaro Herrera <alvherre(a)commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |