[HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages
in [PgSql]
|
Prev: [HACKERS] (9.1) btree_gist support for searching on "not equals"
Next: [HACKERS] beta testing - planner bug - ERROR: XX000: failed to build any 2-way joins
From: Josh Berkus on 21 May 2010 17:57 From a discussion at dinner at pgcon, I wanted to send this to the list for people to poke holes in it: 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. Suggested resolution: we would add a 4-byte field to the *page* header which would track the XID wraparound count. Any page whose wraparound count was not equal to the current one would be considered to have all frozen tuples. This would remove the necessity to read and write old pages just to freeze them, a humongous gain for databases with long data retention horizons, let alone data warehouses. All xids on the page would, necessarily, need to belong to the same wraparound; if a page gets updated and its wraparound count (hereafter WCID) is lower than current, all tuples on the page would be frozen before any data is written to it. XIDs which were before the max_freeze horizon on a page which was being written anyway would be frozen as they are now. Obvious issues: (1) In a case of rows written close to the wraparound point, this would cause a set of tuples to be frozen sooner than they would be in the current system. (2) It's not clear what to do with a page where there are XIDs which are just before wraparound (like XID # 2.4b) which are still visible and receives a write with a new cycle xid (#1). (3) This will require changing the page structure, with all that entails. So it should probably be done when we're making another change (like adding CRCs). -- -- 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 |