Subject: Re: Fwd: PG12 autovac issues
To: Justin King <kingpin867@gmail.com>
Cc: pgsql-general@lists.postgresql.org
References: 
 <CAE39h22zPLrkH17GrkDgAYL3kbjvySYD1io+rtnAUFnaJJVS4g@mail.gmail.com>
 <CAE39h20aY98L5yzFAmBTBbQX3RyX9Vxat=TFK1nAzEpWAJz_BA@mail.gmail.com>
 <dc39a770-4db9-aec2-e54c-88e9b916973f@aklaver.com>
 <CAE39h23t8NNiU7Q2qy7Ap+gJOtCW4QysY5oPhGbn0b9pK0OTeA@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <1887ede9-c40f-88a7-1823-07a5da7dc600@aklaver.com>
Date: Tue, 17 Mar 2020 16:16:31 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CAE39h23t8NNiU7Q2qy7Ap+gJOtCW4QysY5oPhGbn0b9pK0OTeA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

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