Обсуждение: vacuum verbose no longer reveals anything about pins
Hi, I was dismayed to learn that VACUUM VERBOSE on a table no longer tells you anything about whether any pages were skipped due to pins. Now the obvious explanation for that is that we no longer skip pages entirely just because we find that they are pinned. But I think failing to fully process a page due to a pin is still a noteworthy event, and I think that VACUUM VERBOSE should tell you how many times that happened. -- Robert Haas EDB: http://www.enterprisedb.com
On Thu, Jun 30, 2022 at 5:57 AM Robert Haas <robertmhaas@gmail.com> wrote: > I was dismayed to learn that VACUUM VERBOSE on a table no longer tells > you anything about whether any pages were skipped due to pins. VACUUM VERBOSE will show a dedicated line that reports on the number of pages that we couldn't get a cleanup lock on, if and only if we couldn't do useful work as a result. In practice this means pages that had one or more fully DEAD tuples that couldn't be removed due to our inability to prune. In my view this is strictly better than reporting on the number of "skipped due to pins" pages. In the case of any pages that we couldn't get a cleanup lock on that didn't have any DEAD tuples (pages that are not reported on at all), VACUUM hasn't missed any work whatsoever. It even does heap vacuuming, which doesn't require a cleanup lock in either the first or the second heap pass. What's the problem with not reporting on that? -- Peter Geoghegan
On Thu, Jun 30, 2022 at 11:33 AM Peter Geoghegan <pg@bowt.ie> wrote: > On Thu, Jun 30, 2022 at 5:57 AM Robert Haas <robertmhaas@gmail.com> wrote: > > I was dismayed to learn that VACUUM VERBOSE on a table no longer tells > > you anything about whether any pages were skipped due to pins. > > VACUUM VERBOSE will show a dedicated line that reports on the number > of pages that we couldn't get a cleanup lock on, if and only if we > couldn't do useful work as a result. In practice this means pages that > had one or more fully DEAD tuples that couldn't be removed due to our > inability to prune. In my view this is strictly better than reporting > on the number of "skipped due to pins" pages. Ah, I missed that. I think that in the test case I was using, there was a conflicting pin but there were no dead tuples, so that line wasn't present in the output. > In the case of any pages that we couldn't get a cleanup lock on that > didn't have any DEAD tuples (pages that are not reported on at all), > VACUUM hasn't missed any work whatsoever. It even does heap vacuuming, > which doesn't require a cleanup lock in either the first or the second > heap pass. What's the problem with not reporting on that? Maybe nothing. I just thought you'd completely removed all reporting on this, and I'm glad that's not so. Thanks for the quick response. -- Robert Haas EDB: http://www.enterprisedb.com
On Thu, Jun 30, 2022 at 8:43 AM Robert Haas <robertmhaas@gmail.com> wrote: > Ah, I missed that. I think that in the test case I was using, there > was a conflicting pin but there were no dead tuples, so that line > wasn't present in the output. Even if there was a DEAD tuple, your test would still have to account for opportunistic pruning before the cursor acquires its blocking pin (I'm guessing that you're using a cursor for this). The earlier pruning could turn the DEAD tuple into either an LP_UNUSED item (in the case of a heap-only tuple) or an LP_DEAD stub line pointer. As I said, any existing LP_DEAD items will get put in the dead_items array by lazy_scan_noprune, very much like it would in the cleanup-lock-acquired/lazy_scan_prune case. > Maybe nothing. I just thought you'd completely removed all reporting > on this, and I'm glad that's not so. The important idea behind all this is that VACUUM now uses a slightly more abstract definition of scanned_pages. This is far easier to work with at a high level, especially in the instrumentation code used by VACUUM VERBOSE. You may have also noticed that VACUUM VERBOSE/autovacuum logging actually shows the number of scanned pages in Postgres 15, for the first time -- even though that's very basic information. The revised definition of scanned_pages enabled that enhancement. We are no longer encumbered by the existence of a no-cleanup-lock-page special case. -- Peter Geoghegan