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: Heikki Linnakangas on 24 May 2010 09:30 On 22/05/10 16:35, Tom Lane wrote: > Josh Berkus<josh(a)agliodbs.com> writes: >> From a discussion at dinner at pgcon, I wanted to send this to the list >> for people to poke holes in it: > > Somebody (I think Joe or Heikki) poked a big hole in this last night at > the Royal Oak. Me. > Although the scheme would get rid of the need to replace > old XIDs with FrozenXid, it does not get rid of the need to set hint > bits before you can truncate CLOG. Hmm, we don't rely on setting hint bits to truncate CLOG anymore (http://archives.postgresql.org/pgsql-committers/2006-11/msg00026.php). It's the replacement of xids with FrozenXid that matters, the hint bits are really just hints. Doesn't change the conclusion, though: you still need to replace XIDs with FrozenXids to truncate the clog. Conceivably we could keep around more than 2^32 transactions in clog with this scheme, but then you need a lot more space for the clog. But perhaps it would be better to do that than to launch anti-wraparound vacuums, or to refuse more updates in the extreme cases. > So in your example of an insert-only > table that's probably never read again, there's still a minimum of one > update visit required on every old page. Now that's still better than > two update visits ... but we could manage that already, just by tweaking > vacuum's heuristics about when to freeze vs when to set hint bits. (As also discussed in the Royal Oak) I think we should simply not dirty a page when a hint bit is updated. Reading a page from disk is expensive, setting hint bits on the access is generally cheap compared to that. But that is orthogonal to the idea of a per-page XID epoch. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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 24 May 2010 10:14 Heikki Linnakangas <heikki.linnakangas(a)enterprisedb.com> writes: > (As also discussed in the Royal Oak) I think we should simply not dirty > a page when a hint bit is updated. Reading a page from disk is > expensive, setting hint bits on the access is generally cheap compared > to that. But that is orthogonal to the idea of a per-page XID epoch. I'm not sure it's cheap. What you suggest would result in a substantial increase in clog accesses, which means (1) more I/O and (2) more contention. Certainly it's worth experimenting with, but it's no guaranteed win. 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: Josh Berkus on 24 May 2010 12:44 > I'm not sure it's cheap. What you suggest would result in a substantial > increase in clog accesses, which means (1) more I/O and (2) more > contention. Certainly it's worth experimenting with, but it's no > guaranteed win. It seems like there's a number of issues we could fix by making the CLOG more efficient somehow -- from the elimination of hint bits to the ability to freeze pages without writing them. Not, of course, that I have any idea how to do that. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- 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 24 May 2010 15:49 Excerpts from Josh Berkus's message of vie may 21 17:57:35 -0400 2010: > Problem: currently, if your database has a large amount of "cold" data, > such as 350GB of 3-year-old sales transactions, in 8.4 vacuum no longer > needs to touch it thanks to the visibility map. However, every > freeze_age transactions, very old pages need to be sucked into memory > and rewritten just in order to freeze those pages. This can have a huge > impact on system performance, and seems unjustified because the pages > are not actually being used. I think this is nonsense. If you have 3-years-old sales transactions, and your database has any interesting churn, tuples those pages have been frozen for a very long time *already*. The problem is vacuum reading them in so that it can verify there's nothing to do. If we want to avoid *reading* those pages, this solution is useless: > Suggested resolution: we would add a 4-byte field to the *page* header > which would track the XID wraparound count. because you still have to read the page. I think what you're looking for is for this Xid wraparound count to be stored elsewhere, not inside the page. That way vacuum can read it and skip the page without reading it altogether. I think a "freeze map" has been mentioned downthread. I remember mentioning some time ago that we could declare some tables as frozen, i.e. "not needing vacuum". This strikes me as similar, except at the page level rather than table level. -- Álvaro Herrera <alvherre(a)alvh.no-ip.org> -- 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: Heikki Linnakangas on 25 May 2010 04:41
On 24/05/10 22:49, Alvaro Herrera wrote: > Excerpts from Josh Berkus's message of vie may 21 17:57:35 -0400 2010: > >> Problem: currently, if your database has a large amount of "cold" data, >> such as 350GB of 3-year-old sales transactions, in 8.4 vacuum no longer >> needs to touch it thanks to the visibility map. However, every >> freeze_age transactions, very old pages need to be sucked into memory >> and rewritten just in order to freeze those pages. This can have a huge >> impact on system performance, and seems unjustified because the pages >> are not actually being used. > > I think this is nonsense. If you have 3-years-old sales transactions, > and your database has any interesting churn, tuples those pages have > been frozen for a very long time *already*. The problem is vacuum > reading them in so that it can verify there's nothing to do. If we want > to avoid *reading* those pages, this solution is useless: > >> Suggested resolution: we would add a 4-byte field to the *page* header >> which would track the XID wraparound count. > > because you still have to read the page. What's missing from the suggestion is that relfrozenxid and datfrozenxid also need to be expanded to 8-bytes. That way you effectively have 8-byte XIDs, which means that you never need to vacuum to avoid XID wraparound. You still need to freeze to truncate clog, though, but if you have the disk space, you can now do that every 100 billion transactions for example if you wish. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |