Re: autovacuum can't keep up, bloat just continues to rise

On 07/20/2017 08:58 PM, Joshua D. Drake wrote:
> On 07/19/2017 07:57 PM, Tom Lane wrote:
>> Peter Geoghegan <pg(at)bowt(dot)ie> writes:

> Test 1: 55G /srv/main
> TPS: 955
>
> Test 2: 112G /srv/main
> TPS: 531 (Not sure what happened here, long checkpoint?)
>
> Test 3: 109G /srv/main
> TPS: 868
>
> Test 4: 143G
> TPS: 840
>
> Test 5: 154G
> TPS: 722
>
> I am running the query here:
>
> https://wiki.postgresql.org/wiki/Index_Maintenance#Summarize_keyspace_of_a_B-Tree_index

This query didn't finish after 12 hours. Here is the new set:

name | setting
-------------------------------------+-----------
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 12
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_naptime | 10
autovacuum_vacuum_cost_delay | 0
autovacuum_vacuum_cost_limit | 5000
autovacuum_vacuum_scale_factor | 0.1
autovacuum_vacuum_threshold | 50
autovacuum_work_mem | -1
log_autovacuum_min_duration | -1

I have only ran one test but it is pretty telling:

Test 1: 60G /srv/main
TPS: 914

Test 2: 92G /srv/main
TPS: Still running

I will post a update after the third or fourth test depending on the
numbers. I created this instance exactly for these tests so if someone
wants to poke around I can give access.

Thanks,

JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
***** Unless otherwise stated, opinions are my own. *****