Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Fri, Aug 18, 2017 at 8:39 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Fri, Apr 7, 2017 at 10:51 PM, Claudio Freire <klaussfreire(at)gmail(dot)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.