Re: autovac issue with large number of tables

Hi,

On Wed, Sep 2, 2020 at 2:10 AM Kasahara Tatsuhito
<kasahara(dot)tatsuhito(at)gmail(dot)com> wrote:
> > I wonder if we could have table_recheck_autovac do two probes of the stats
> > data. First probe the existing stats data, and if it shows the table to
> > be already vacuumed, return immediately. If not, *then* force a stats
> > re-read, and check a second time.
> Does the above mean that the second and subsequent table_recheck_autovac()
> will be improved to first check using the previous refreshed statistics?
> I think that certainly works.
>
> If that's correct, I'll try to create a patch for the PoC

I still don't know how to reproduce Jim's troubles, but I was able to reproduce
what was probably a very similar problem.

This problem seems to be more likely to occur in cases where you have
a large number of tables,
i.e., a large amount of stats, and many small tables need VACUUM at
the same time.

So I followed Tom's advice and created a patch for the PoC.
This patch will enable a flag in the table_recheck_autovac function to use
the existing stats next time if VACUUM (or ANALYZE) has already been done
by another worker on the check after the stats have been updated.
If the tables continue to require VACUUM after the refresh, then a refresh
will be required instead of using the existing statistics.

I did simple test with HEAD and HEAD + this PoC patch.
The tests were conducted in two cases.
(I changed few configurations. see attached scripts)

1. Normal VACUUM case
- SET autovacuum = off
- CREATE tables with 100 rows
- DELETE 90 rows for each tables
- SET autovacuum = on and restart PostgreSQL
- Measure the time it takes for all tables to be VACUUMed

2. Anti wrap round VACUUM case
- CREATE brank tables
- SELECT all of these tables (for generate stats)
- SET autovacuum_freeze_max_age to low values and restart PostgreSQL
- Consumes a lot of XIDs by using txid_curent()
- Measure the time it takes for all tables to be VACUUMed

For each test case, the following results were obtained by changing
autovacuum_max_workers parameters to 1, 2, 3(def) 5 and 10.
Also changing num of tables to 1000, 5000, 10000 and 20000.

Due to the poor VM environment (2 VCPU/4 GB), the results are a little unstable,
but I think it's enough to ask for a trend.

===========================================================================
[1.Normal VACUUM case]
tables:1000
autovacuum_max_workers 1: (HEAD) 20 sec VS (with patch) 20 sec
autovacuum_max_workers 2: (HEAD) 18 sec VS (with patch) 16 sec
autovacuum_max_workers 3: (HEAD) 18 sec VS (with patch) 16 sec
autovacuum_max_workers 5: (HEAD) 19 sec VS (with patch) 17 sec
autovacuum_max_workers 10: (HEAD) 19 sec VS (with patch) 17 sec

tables:5000
autovacuum_max_workers 1: (HEAD) 77 sec VS (with patch) 78 sec
autovacuum_max_workers 2: (HEAD) 61 sec VS (with patch) 43 sec
autovacuum_max_workers 3: (HEAD) 38 sec VS (with patch) 38 sec
autovacuum_max_workers 5: (HEAD) 45 sec VS (with patch) 37 sec
autovacuum_max_workers 10: (HEAD) 43 sec VS (with patch) 35 sec

tables:10000
autovacuum_max_workers 1: (HEAD) 152 sec VS (with patch) 153 sec
autovacuum_max_workers 2: (HEAD) 119 sec VS (with patch) 98 sec
autovacuum_max_workers 3: (HEAD) 87 sec VS (with patch) 78 sec
autovacuum_max_workers 5: (HEAD) 100 sec VS (with patch) 66 sec
autovacuum_max_workers 10: (HEAD) 97 sec VS (with patch) 56 sec

tables:20000
autovacuum_max_workers 1: (HEAD) 338 sec VS (with patch) 339 sec
autovacuum_max_workers 2: (HEAD) 231 sec VS (with patch) 229 sec
autovacuum_max_workers 3: (HEAD) 220 sec VS (with patch) 191 sec
autovacuum_max_workers 5: (HEAD) 234 sec VS (with patch) 147 sec
autovacuum_max_workers 10: (HEAD) 320 sec VS (with patch) 113 sec

[2.Anti wrap round VACUUM case]
tables:1000
autovacuum_max_workers 1: (HEAD) 19 sec VS (with patch) 18 sec
autovacuum_max_workers 2: (HEAD) 14 sec VS (with patch) 15 sec
autovacuum_max_workers 3: (HEAD) 14 sec VS (with patch) 14 sec
autovacuum_max_workers 5: (HEAD) 14 sec VS (with patch) 16 sec
autovacuum_max_workers 10: (HEAD) 16 sec VS (with patch) 14 sec

tables:5000
autovacuum_max_workers 1: (HEAD) 69 sec VS (with patch) 69 sec
autovacuum_max_workers 2: (HEAD) 66 sec VS (with patch) 47 sec
autovacuum_max_workers 3: (HEAD) 59 sec VS (with patch) 37 sec
autovacuum_max_workers 5: (HEAD) 39 sec VS (with patch) 28 sec
autovacuum_max_workers 10: (HEAD) 39 sec VS (with patch) 29 sec

tables:10000
autovacuum_max_workers 1: (HEAD) 139 sec VS (with patch) 138 sec
autovacuum_max_workers 2: (HEAD) 130 sec VS (with patch) 86 sec
autovacuum_max_workers 3: (HEAD) 120 sec VS (with patch) 68 sec
autovacuum_max_workers 5: (HEAD) 96 sec VS (with patch) 41 sec
autovacuum_max_workers 10: (HEAD) 90 sec VS (with patch) 39 sec

tables:20000
autovacuum_max_workers 1: (HEAD) 313 sec VS (with patch) 331 sec
autovacuum_max_workers 2: (HEAD) 209 sec VS (with patch) 201 sec
autovacuum_max_workers 3: (HEAD) 227 sec VS (with patch) 141 sec
autovacuum_max_workers 5: (HEAD) 236 sec VS (with patch) 88 sec
autovacuum_max_workers 10: (HEAD) 309 sec VS (with patch) 74 sec
===========================================================================

The cases without patch, the scalability of the worker has decreased
as the number of tables has increased.
In fact, the more workers there are, the longer it takes to complete
VACUUM to all tables.
The cases with patch, it shows good scalability with respect to the
number of workers.

Note that perf top results showed that hash_search_with_hash_value,
hash_seq_search and
pgstat_read_statsfiles are dominant during VACUUM in all patterns,
with or without the patch.

Therefore, there is still a need to find ways to optimize the reading
of large amounts of stats.
However, this patch is effective in its own right, and since there are
only a few parts to modify,
I think it should be able to be applied to current (preferably
pre-v13) PostgreSQL.

The patch and reproduce scripts were attached.

Thoughts ?

Best regards,

--
Tatsuhito Kasahara
kasahara.tatsuhito _at_ gmail.com