Re: PG12 autovac issues

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: PG12 autovac issues
Дата
Msg-id CAMkU=1yCz0ww5Fk8K22Fx12vSACAaUhX8X8eQV5ETJ6Oq5bL5A@mail.gmail.com
обсуждение исходный текст
Ответ на PG12 autovac issues  (Justin King <kingpin867@gmail.com>)
Список pgsql-admin
On Tue, Mar 17, 2020 at 6:19 PM Justin King <kingpin867@gmail.com> wrote:
We have a database that isn't overly large (~20G), but gets incredibly
frequent updates.  Here's an example table:

feedi=# select * from pg_stat_all_tables where schemaname =
'production' and relname = 'tita';
 
n_tup_upd = 4207076934
n_tup_hot_upd = 4073821079
n_live_tup = 79942
n_dead_tup = 71969
n_mod_since_analyze = 12020
 
As you can see in this table, there are only ~80K rows, but billions
of updates. 

But how long were those billions of updates spread over?  You need to look at deltas, not absolute values. And note that almost all of those updates where HOT updates, which don't generate "vacuum debt"
 
What we have observed is that the frozenxid reaches the
200M mark fairly quickly because of the amount of activity.  What is
interesting is that this happens with the 'postgres' and 'template1'
databases as well and there is absolutely no activity in those
databases.

When the 'postgres' and/or 'template1' databases hit the
freeze_max_age, there are cases where it kicks off an aggressive
autovac of those tables which seems to prevent autovacs from running
elsewhere. 

Yes, it is a known long-outstanding bug (or malfeature) that one database reaching autovacuum_freeze_max_age will starve all other databases of autovac attention.  But since the introduction of the "freeze map" in 9.6, it is hard to see how this starvation due to an inactive database hitting autovacuum_freeze_max_age can last for any meaningful amount of time.  Maybe a shared catalog?

Oddly, this is not consistent, but that condition seems to
be required.  We have observed this across multiple PG12 servers (dev,
test, staging, production) all with similar workloads.

$ grep -i vacuum /var/log/postgresql/postgres.log | cut -b 1-9 | uniq -c

It is hard to figure out what the significance of the occurrence of the word 'vacuum' in the log file is, without being intimately familiar with your log files.  Could you interpret this some more for us?  How many of those are for 'tita'?  How many for databases other than your active one?

Cheers,

Jeff

В списке pgsql-admin по дате отправления:

Предыдущее
От: Justin King
Дата:
Сообщение: PG12 autovac issues
Следующее
От: Andres Freund
Дата:
Сообщение: Re: PG12 autovac issues