Re: [HACKERS] 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@bowt.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           |
200000000autovacuum_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                 |
-1log_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.   *****