Re: [HACKERS] Increase Vacuum ring buffer.

On 2017-07-21 20:41, Sokolov Yura wrote:
> On 2017-07-21 19:32, Robert Haas wrote:
>> On Fri, Jul 21, 2017 at 4:19 AM, Sokolov Yura
>> <funny.falcon@postgrespro.ru> wrote:
>>> 
>>> Probably with increased ring buffer there is no need in raising
>>> vacuum_cost_limit. Will you admit it?
>> 
>> No, I definitely won't admit that.  With default settings autovacuum
>> won't write more than ~2.3MB/s if I remember the math correctly, so if
>> you've got a 1TB table you're probably going to need a bigger value.
>> 
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
> 
> I've seed autovacuum process spending >50% of its time in fsync
> (with current ring buffer) (but I used autovacuum_cost_delay=2ms).
> fsync could lasts up to second on hdd if there is concurrent IO.
> Even on ssd fsync could be really noticeable.
> 
> But, I agree that for 1TB table autovacuum_cost_limit still should
> be increased, even with larger ring buffer.
> 
> 
> My friend noticed, that I didn't said why I bother with autovacuum.
> Our customers suffers from table bloating. I've made synthetic
> bloating test, and started experiments with modifying micro- and
> auto-vacuum. My first attempts were to update FSM early (both in
> micro and autovacuum) and update it upto root, not only low level.
> 
> Then I looked to strace of autovacuum process, and noticed storm
> of fsync. I catched backtraces with gdb rooting on fsync, and
> found that evicting dirty pages from small ring buffer it the
> reason.
> 
> After some experiments with combining my "early fsm update" and
> size of ring buffer, I understood that increasing ring buffer
> gives most of benefits: autovacuum runs faster, and bloating is
> greatly reduced. On extreme case, 400mb table bloats to 17GB
> on master, and only to 5GB with faster autovacuum.
> 
> I used custom scripts, and that is why my statistic is not full.
> Though, I didn't found performance reduction. In fact, it looks
> like tests with "larger autovacuum ring" did more queries per hour
> than tests against master.
> 
> I will run pgbench for weekend, so latencies and percentiles
> will be collected.
> 
> With regards,
> --
> Sokolov Yura aka funny_falcon
> Postgres Professional: https://postgrespro.ru
> The Russian Postgres Company

Default pgbench script wasn't able to trigger autovacuum of
pgbench_accounts table in 8 hours (scale 400, 40 clients, 900tps
average), so weekend testing were not useful.

I will re-run with custom script for next day-two.

-- 
Sokolov Yura aka funny_falcon
Postgres Professional: https://postgrespro.ru
The Russian Postgres Company