Re: Increase Vacuum ring buffer.

On 2017-07-20 20:59, Robert Haas wrote:
> If you want something changed, it's your job to do that testing.

I've been testing for two weeks before I wrote to pgsql-hackers. And I
wrote some highlevel results in first letter.
I haven't noticed transactions slowdown from increased vacuum ring
buffer.
But:
- my workload could be too synthetic,
- I've measured total performed transactions, and sampled time of random
ones.
So probably my measurement were not exhaustive. And definitely I could
not imagine whole set of interesting workloads. And given every test
run for at least 5 hours (and, in fact, test run on master for 20 hours,
cause autovacuum doesn't finish faster on that synthetic workload)
I will spend at least half-year if I test all possible workloads.

That is why I asked community to test it on workloads people consider
interesting.

I may measure by my self, if some tells me what workload he wants to be
tested.

Two previous discussions of the topic were killed without any evidence
of
testing at all, only with theoretical doubts. Is it fair? Why "probably
it
is bad" is better than "probably it is good"?

You are one of leadership. I know it is not your job to test every tiny
change a school boy proposed. But here is a lot of people, who waits for
your word. Instead of cooling rush and closing discussions, you may just
say: "please, someone test it with that particular workload".

> I don't think this discussion is really going anywhere unless you are
> willing to admit that increasing VACUUM performance could have some
> downsides. If you're not willing to admit that, there's not a lot to
> talk
> about here.

I can admit many things. I've seen how autovacuum drops pgbench
performance
from 100000tps down to 1500tps cause of contention on CLogControlLock.
(btw
my LWLock patch improves it to 3000tps).

But that is not a reason autovacuum should be intentionally slow. How
Stephen Frost said, that is what vacuum_cost_limit and vacuum_cost_delay
are
for. (and, certainly, it is reason to improve CLog and SLRU).

> OK, but I have helped *many* customers whose problem was that vacuum
> ran too fast and blew data out of the OS cache causing query response
> times to go through the roof.

When there is no garbage, increasing autovacuum ring buffer changes
almost
nothing. When there is garbage, current small ring buffer leads to a
storm
of fsyncs. Frequent fsyncs slows down hdd a lot, and then hdd isn't
capable
to satisfy queries and refill OS cache. Will you admit it?

> I've also run into many customers whose problem that vacuum ran too
> slowly, and generally raising vacuum_cost_limit fixes that problem just
> fine.

Probably with increased ring buffer there is no need in raising
vacuum_cost_limit. Will you admit it?

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