Re: [PERFORM] Setting vacuum_freeze_min_age really low
On Mon, Mar 25, 2013 at 4:31 PM, Josh Berkus j...@agliodbs.com wrote: In the past, setting vacuum_freeze_min_age (vfma) really low (say to 1 or 5) would have caused lots of extra writing work due to dirtying extra pages for freezing. This has been our stated reason to keep vfma high, despite the obvious advantage of freezing tuples while they're still in the cache. That, and Tom's concern about forensics, which I understand to be the larger sticking point. With the visibility map, though, vfma should only be dirtying pages which vacuum is already visiting because there's dirty tuples on the page. That is, pages which vacuum will probably dirty anyway, freezing or not. (This is assuming one has applied the 9.2.3 update.) I think this is probably not accurate, although I'll defer to someone with more real-world experience. I'd guess that it's uncommon for actively updated data and very-rarely-updated data to be mixed together on the same pages with any real regularity. IOW, the dirty pages probably don't have anything on them that can be frozen anyway. So, if the table's age is less than vacuum_freeze_table_age, we'll only scan pages not already marked all-visible. Regardless of vfma, we probably won't freeze much. On the other hand, if the table's age is at least vacuum_freeze_table_age, we'll scan the whole table and freeze a lotta stuff all at once. Again, whether vfma is high or low won't matter much: it's definitely less than vacuum_freeze_table_age. Basically, I would guess that both the costs and the benefits of changing this are pretty small. It would be nice to hear from someone who has tried it, though. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Setting vacuum_freeze_min_age really low
Hi, On 2013-03-25 13:31:17 -0700, Josh Berkus wrote: In the past, setting vacuum_freeze_min_age (vfma) really low (say to 1 or 5) would have caused lots of extra writing work due to dirtying extra pages for freezing. This has been our stated reason to keep vfma high, despite the obvious advantage of freezing tuples while they're still in the cache. With the visibility map, though, vfma should only be dirtying pages which vacuum is already visiting because there's dirty tuples on the page. That is, pages which vacuum will probably dirty anyway, freezing or not. (This is assuming one has applied the 9.2.3 update.) Given that, it seems like the cost of lowering vfma *should* be marginal. The only extra work done by a lower vfma should be: 1. extra cpu time to put in the froxenXIDs on vacuumed pages, and 2. dirtying the minority of pages which vacuum decided to scan, but not write to. It will also often enough lead to a page being frozen repeatedly which causes unneccessary IO and WAL traffic. If a page contains pages from several transactions its not unlikely that some tuples are older and some are newer than vfma. That scenario isn't unlikely because of two scenarios: - INSERT/UPDATE reusing space on older pages where tuples have been deleted. - When a backend extends a relation that page is *not* known to have free space to other relations. Until vacuum comes along for the first time only this backend will use its space. Given that busy clusters frequently burn loads of xids per second it is not uncommon to have a wide range of xids on such a page. And are there other costs I'm not thinking of? I think (but am not 100% sure right now) it would have another rather big cost: When a page contains freezable items, as determined by freeze_min_age, and we are doing a full table scan we won't skip buffers that we can't lock for cleanup. Instead we will wait and then lock them for cleanup. So I think this would be rather noticeably impact the speed of vacuum (since it waits more often) and concurrency (since we lock more buffers than before, even if they are actively used). Makes sense? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Setting vacuum_freeze_min_age really low
On 2013-05-09 12:09:04 -0400, Robert Haas wrote: On Mon, Mar 25, 2013 at 4:31 PM, Josh Berkus j...@agliodbs.com wrote: In the past, setting vacuum_freeze_min_age (vfma) really low (say to 1 or 5) would have caused lots of extra writing work due to dirtying extra pages for freezing. This has been our stated reason to keep vfma high, despite the obvious advantage of freezing tuples while they're still in the cache. That, and Tom's concern about forensics, which I understand to be the larger sticking point. FWIW I found having sensible xmin/xmax repeatedly really useful for debugging problems. Most problems don't get noticed within minutes so loosing evidence that fast is bad. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
