Re: New vacuum option to do only freezing
От | Masahiko Sawada |
---|---|
Тема | Re: New vacuum option to do only freezing |
Дата | |
Msg-id | CAD21AoCjCK=r7ZUP+WciAiW_P2dBERKtBuQoD2pG3zD29rNhxg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: New vacuum option to do only freezing (Masahiko Sawada <sawada.mshk@gmail.com>) |
Ответы |
Re: New vacuum option to do only freezing
(Robert Haas <robertmhaas@gmail.com>)
|
Список | pgsql-hackers |
On Thu, Apr 18, 2019 at 3:12 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote: > > On Thu, Apr 18, 2019 at 4:20 AM Peter Geoghegan <pg@bowt.ie> wrote: > > > > On Wed, Apr 17, 2019 at 10:46 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > Yeah, if we wanted to expose these complications more directly, we > > > could think about adding or changing the main counters. I was wondering > > > about whether we should have it report "x bytes and y line pointers > > > freed", rather than counting tuples per se. > > > > It looks good idea to me. > > > I like that idea, but I'm pretty sure that there are very few users > > that are aware of these distinctions at all. It would be a good idea > > to clearly document them. > > I completely agreed. I'm sure that only a few user can do the action > of enabling index cleanup when the report says there are many dead > line pointers in the table. > > It brought me an another idea of reporting something like "x bytes > freed, y bytes can be freed after index cleanup". That is, we report > how much bytes including tuples and line pointers we freed and how > much bytes of line pointers can be freed after index cleanup. While > index cleanup is enabled, the latter value should always be 0. If the > latter value gets large user can be aware of necessity of doing index > cleanup. > Attached the draft version of patch based on the discussion so far. This patch fixes two issues: the assertion error topminnow reported and the contents of the vacuum logs. For the first issue, I've changed lazy_scan_heap so that it counts the tuples that became dead after HOT pruning in nkeep when index cleanup is disabled. As per discussions so far, it would be no problem to try to freeze tuples that ought to have been deleted. For the second issue, I've changed lazy vacuum so that it reports both the number of kilobytes we freed and the number of kilobytes can be freed after index cleanup. The former value includes the size of not only heap tuples but also line pointers. That is, when a normal tuple has been marked as either dead or redirected we count only the size of heap tuple, and when it has been marked as unused we count the size of both heap tuple and line pointer. Similarly when either a redirect line pointer or a dead line pointer become unused we count only the size of line pointer. The latter value we report could be non-zero only when index cleanup is disabled; it counts the number of bytes of dead line pointers we left. The advantage of this change is that user can be aware of both how many bytes we freed and how many bytes we left due to skipping index cleanup. User can be aware of the necessity of doing index cleanup by seeing the latter value. Also with this patch, we count only tuples that has been marked as unused as deleted tuples. The action of replacing a dead root tuple with a dead line pointer doesn't count for anything. It would be close to the meaning of "deleted tuples" and less confusion. We do that when actually marking rather than when recording because we could record and forget dead tuples. Here is the sample of the report by VACUUM VERBOSE. I used the following script and the vacuum report is changed by the patch. * Script DROP TABLE IF EXISTS test; CREATE TABLE test (c int primary key, d int); INSERT INTO test SELECT * FROM generate_series(1,10000); DELETE FROM test WHERE c < 2000; VACUUM (INDEX_CLEANUP FALSE, VERBOSE) test; VACUUM (INDEX_CLEANUP TRUE, VERBOSE) test; * HEAD (when index cleanup is disabled) INFO: vacuuming "public.test" INFO: "test": found 1999 removable, 8001 nonremovable row versions in 45 out of 45 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 504 There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. 0 tuples and 1999 item identifiers are left as dead. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM * Patched (when index cleanup is disabled) INFO: vacuuming "public.test" INFO: "test": 55 kB freed, 7996 bytes can be freed after index cleanup, table size: 360 kB DETAIL: found 8001 nonremovable row versions in 45 out of 45 pages. 0 dead row versions cannot be removed yet, oldest xmin: 1660 There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM * HEAD (when index cleanup is enabled) INFO: vacuuming "public.test" INFO: scanned index "test_pkey" to remove 1999 row versions DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: "test": removed 1999 row versions in 9 pages DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: index "test_pkey" now contains 8001 row versions in 30 pages DETAIL: 1999 index row versions were removed. 5 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "test": found 0 removable, 91 nonremovable row versions in 10 out of 45 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 504 There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. 0 tuples and 0 item identifiers are left as dead. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM * Patched (when index cleanup is enabled) INFO: vacuuming "public.test" INFO: scanned index "test_pkey" to remove 1999 row versions DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: "test": removed 1999 row versions in 9 pages DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: index "test_pkey" now contains 8001 row versions in 30 pages DETAIL: 1999 index row versions were removed. 5 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "test": 7996 bytes freed, table size: 360 kB DETAIL: found 91 nonremovable row versions in 10 out of 45 pages. 0 dead row versions cannot be removed yet, oldest xmin: 1660 There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM The patch doesn't address the concern that Tom had, which is it might not be safe in production to accumulate the dead line pointers without limit when the reloption is set to false. I think this is a separate issue from the above two issues so I'd like to discuss that after these are solved. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Вложения
В списке pgsql-hackers по дате отправления: