Robert, Andres, > That, and Tom's concern about forensics, which I understand to be the > larger sticking point. I don't buy the idea that we should cause regular recurring performance issues for all of our users in order to aid diagnosing the kind of issues which happen 1% of the time to 2% of our users. > 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. Right, but the pages which were dirtied *anyway* will get frozen. > 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. Right. > 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. Well, I have, but I don't exactly have empirical testing results from it. That's really the sticking point here: can we measurably demonstrate that lowering vfma makes autovacuum freeze happen less often, and do less work when it does? Realistically, I think that's waiting on me having time to do some lengthy performance testing. > 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: Nobody has yet explained to me where this extra WAL and IO traffic would come from. vfma only takes effect if the page is being vacuumed *anyway*. And if the page is being vacuumed anyway, the page is being rewritten anyway, and it doesn't matter how many changes we make on that page, except as far as CPU time is concerned. As far as IO is concerned, an 8K page is an 8K page. No? The only time I can imagine this resulting in extra IO is if vacuum is regularly visiting pages which don't have any other work to do, but do have tuples which could be frozen if vfma was lowered. I would tend to think that this would be a tiny minority of pages, but testing may be the only way to answer that. > 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). Well, that behavior sounds like something we should maybe fix, regardless of whether we're lowering the default vfma or not. --Josh Berkus -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
