On Fri, Aug 18, 2017 at 8:39 AM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Fri, Apr 7, 2017 at 10:51 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> Indeed they do, and that's what motivated this patch. But I'd need >> TB-sized tables to set up something like that. I don't have the >> hardware or time available to do that (vacuum on bloated TB-sized >> tables can take days in my experience). Scale 4000 is as big as I can >> get without running out of space for the tests in my test hardware. >> >> If anybody else has the ability, I'd be thankful if they did test it >> under those conditions, but I cannot. I think Anastasia's test is >> closer to such a test, that's probably why it shows a bigger >> improvement in total elapsed time. >> >> Our production database could possibly be used, but it can take about >> a week to clone it, upgrade it (it's 9.5 currently), and run the >> relevant vacuum. > > It looks like I won't be able to do that test with a production > snapshot anytime soon. > > Getting approval for the budget required to do that looks like it's > going to take far longer than I thought.
I finally had a chance to test the patch in a production snapshot. Actually, I tried to take out 2 birds with one stone, and I'm also testing the FSM vacuum patch. It shouldn't significantly alter the numbers anyway. So, while the whole-db throttled vacuum (as is run in production) is still ongoing, an interesting case already popped up. TL;DR, without the patch, this particular table took 16 1/2 hours more or less, to vacuum 313M dead tuples. With the patch, it took 6:10h to vacuum 323M dead tuples. That's quite a speedup. It even used significantly less CPU time as well. Since vacuum here is throttled (with cost-based delays), this also means it generated less I/O. We have more extreme cases sometimes, so if I see something interesting in what remains of the test, I'll post those results as well. The raw data: Patched INFO: vacuuming "public.aggregated_tracks_hourly_full" INFO: scanned index "aggregated_tracks_hourly_full_pkey_null" to remove 323778164 row versions DETAIL: CPU: user: 111.57 s, system: 31.28 s, elapsed: 2693.67 s INFO: scanned index "ix_aggregated_tracks_hourly_full_action_null" to remove 323778164 row versions DETAIL: CPU: user: 281.89 s, system: 36.32 s, elapsed: 2915.94 s INFO: scanned index "ix_aggregated_tracks_hourly_full_nunq" to remove 323778164 row versions DETAIL: CPU: user: 313.35 s, system: 79.22 s, elapsed: 6400.87 s INFO: "aggregated_tracks_hourly_full": removed 323778164 row versions in 7070739 pages DETAIL: CPU: user: 583.48 s, system: 69.77 s, elapsed: 8048.00 s INFO: index "aggregated_tracks_hourly_full_pkey_null" now contains 720807609 row versions in 10529903 pages DETAIL: 43184641 index row versions were removed. 5288916 index pages have been deleted, 4696227 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.03 s. INFO: index "ix_aggregated_tracks_hourly_full_action_null" now contains 720807609 row versions in 7635161 pages DETAIL: 202678522 index row versions were removed. 4432789 index pages have been deleted, 3727966 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s. INFO: index "ix_aggregated_tracks_hourly_full_nunq" now contains 720807609 row versions in 15526885 pages DETAIL: 202678522 index row versions were removed. 9052488 index pages have been deleted, 7390279 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.02 s. INFO: "aggregated_tracks_hourly_full": found 41131260 removable, 209520861 nonremovable row versions in 7549244 out of 22391603 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 245834316 There were 260553451 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 1329.64 s, system: 244.22 s, elapsed: 22222.14 s. Vanilla 9.5 (ie: what's in production right now, should be similar to master): INFO: vacuuming "public.aggregated_tracks_hourly_full" INFO: scanned index "aggregated_tracks_hourly_full_pkey_null" to remove 178956729 row versions DETAIL: CPU 65.51s/253.67u sec elapsed 3490.13 sec. INFO: scanned index "ix_aggregated_tracks_hourly_full_action_null" to remove 178956729 row versions DETAIL: CPU 63.26s/238.08u sec elapsed 3483.32 sec. INFO: scanned index "ix_aggregated_tracks_hourly_full_nunq" to remove 178956729 row versions DETAIL: CPU 340.15s/445.52u sec elapsed 15898.48 sec. INFO: "aggregated_tracks_hourly_full": removed 178956729 row versions in 3121122 pages DETAIL: CPU 168.24s/159.20u sec elapsed 5678.51 sec. INFO: scanned index "aggregated_tracks_hourly_full_pkey_null" to remove 134424729 row versions DETAIL: CPU 50.66s/265.19u sec elapsed 3977.15 sec. INFO: scanned index "ix_aggregated_tracks_hourly_full_action_null" to remove 134424729 row versions DETAIL: CPU 99.68s/326.44u sec elapsed 6580.22 sec. INFO: scanned index "ix_aggregated_tracks_hourly_full_nunq" to remove 134424729 row versions DETAIL: CPU 146.96s/358.86u sec elapsed 10464.69 sec. INFO: "aggregated_tracks_hourly_full": removed 134424729 row versions in 2072649 pages DETAIL: CPU 109.07s/37.12u sec elapsed 3601.39 sec. INFO: index "aggregated_tracks_hourly_full_pkey_null" now contains 870543969 row versions in 10529903 pages DETAIL: 134424771 index row versions were removed. 4358027 index pages have been deleted, 3662385 are currently reusable. CPU 0.02s/0.00u sec elapsed 2.42 sec. INFO: index "ix_aggregated_tracks_hourly_full_action_null" now contains 870543969 row versions in 7635161 pages DETAIL: 134424771 index row versions were removed. 3908583 index pages have been deleted, 3445049 are currently reusable. CPU 0.02s/0.00u sec elapsed 0.08 sec. INFO: index "ix_aggregated_tracks_hourly_full_nunq" now contains 870543969 row versions in 15526885 pages DETAIL: 218955943 index row versions were removed. 7710441 index pages have been deleted, 5928522 are currently reusable. CPU 0.02s/0.01u sec elapsed 0.19 sec. INFO: "aggregated_tracks_hourly_full": found 134159696 removable, 90271560 nonremovable row versions in 6113375 out of 22391603 pages DETAIL: 287 dead row versions cannot be removed yet. There were 126680434 unused item pointers. Skipped 0 pages due to buffer pins. 0 pages are entirely empty. CPU 1191.42s/2223.19u sec elapsed 59885.50 sec.