From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Justin King <kingpin867@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Fwd: PG12 autovac issues
Date: Tue, 17 Mar 2020 16:16:31 -0700
Message-ID: <1887ede9-c40f-88a7-1823-07a5da7dc600@aklaver.com> (raw)
In-Reply-To: <CAE39h23t8NNiU7Q2qy7Ap+gJOtCW4QysY5oPhGbn0b9pK0OTeA@mail.gmail.com>
References: <CAE39h22zPLrkH17GrkDgAYL3kbjvySYD1io+rtnAUFnaJJVS4g@mail.gmail.com>
<CAE39h20aY98L5yzFAmBTBbQX3RyX9Vxat=TFK1nAzEpWAJz_BA@mail.gmail.com>
<dc39a770-4db9-aec2-e54c-88e9b916973f@aklaver.com>
<CAE39h23t8NNiU7Q2qy7Ap+gJOtCW4QysY5oPhGbn0b9pK0OTeA@mail.gmail.com>
On 3/17/20 3:48 PM, Justin King wrote:
> On Tue, Mar 17, 2020 at 5:39 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> On 3/17/20 3:22 PM, Justin King wrote:
>>> Apologies, I accidentally sent this to the pgsql-admin list initially
>>> but intended it go here:
>>>
>>> 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';
>>> relid = 16786
>>> schemaname = production
>>> relname = tita
>>> seq_scan = 23365
>>> seq_tup_read = 403862091
>>> idx_scan = 26612759248
>>> idx_tup_fetch = 19415752701
>>> n_tup_ins = 24608806
>>> n_tup_upd = 4207076934
>>> n_tup_del = 24566916
>>> n_tup_hot_upd = 4073821079
>>> n_live_tup = 79942
>>> n_dead_tup = 71969
>>> n_mod_since_analyze = 12020
>>> last_vacuum = 2020-03-17 15:35:19.588859+00
>>> last_autovacuum = 2020-03-17 21:31:08.248598+00
>>> last_analyze = 2020-03-17 15:35:20.372875+00
>>> last_autoanalyze = 2020-03-17 22:04:41.76743+00
>>> vacuum_count = 9
>>> autovacuum_count = 135693
>>> analyze_count = 9
>>> autoanalyze_count = 495877
>>>
>>> As you can see in this table, there are only ~80K rows, but billions
>>> of updates. 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. 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.
>>
>> Is there anything in postgres and template1 besides what was created at
>> init?
>
> There is nothing in there at all besides system tables created at init.
>
>>
>> What are your settings for autovacuum?:
>>
>> https://www.postgresql.org/docs/12/runtime-config-autovacuum.html
>
> Here are the settings, these are the only ones that are not set to
> default with the exception of a few tables that have been overridden
> with a different value due to lots of updates and few rows:
And those values are?
More below.
>
> autovacuum = on
> log_autovacuum_min_duration = 0
> autovacuum_max_workers = 8
> autovacuum_naptime = 15s
> autovacuum_vacuum_threshold = 500
> autovacuum_analyze_threshold = 2500
> vacuum_cost_limit = 1000
Are either of the below set > 0?:
vacuum_cost_delay
autovacuum_vacuum_cost_delay
>
> We want fairly aggressive autovacs to keep table bloat limited -- the
> application latency suffers if it has to wade through dead tuples and
> staying near realtime is important in our environment.
>
> ** Also, it should be noted that the autovacuum_analyze_threshold is
> probably an incorrect value, we likely intended that to be 250 and
> just have now realized it after poking more at the configuration.
>
>>
--
Adrian Klaver
adrian.klaver@aklaver.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: adrian.klaver@aklaver.com, kingpin867@gmail.com, pgsql-general@lists.postgresql.org
Subject: Re: Fwd: PG12 autovac issues
In-Reply-To: <1887ede9-c40f-88a7-1823-07a5da7dc600@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox