On 2017-07-20 05:52, Masahiko Sawada wrote:
On Thu, Jul 20, 2017 at 8:24 AM, Stephen Frost <sfr...@snowman.net>
wrote:
* Tom Lane (t...@sss.pgh.pa.us) wrote:
"Joshua D. Drake" <j...@commandprompt.com> writes:
> At PGConf US Philly last week I was talking with Jim and Jan about
> performance. One of the items that came up is that PostgreSQL can't run
> full throttle for long periods of time. The long and short is that no
> matter what, autovacuum can't keep up. This is what I have done:
Try reducing autovacuum_vacuum_cost_delay more, and/or increasing
autovacuum_vacuum_cost_limit.
Or get rid of the cost delay entirely and let autovacuum actually go
as
fast as it can when it's run. The assertion that it can't keep up is
still plausible, but configuring autovacuum to sleep regularly and
then
complaining that it's not able to keep up doesn't make sense.
Reducing the nap time might also be helpful if autovacuum is going as
fast as it can and it's able to clear a table in less than a minute.
There have been discussions on this list about parallel vacuum of a
particular table as well; to address this issue I'd encourage
reviewing
those discussions and looking at writing a patch to implement that
feature as that would address the case where the table is large enough
that autovacuum simply can't get through all of it before the other
backends have used all space available and then substantially
increased
the size of the relation (leading to vacuum on the table running for
longer).
Yeah, the parallel vacuum of a particular table might help this issue
unless disk I/O is bottle-neck. I'm planning work on that.
Regards,
--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
I wrote two days ago about vacuum ring buffer:
https://www.postgresql.org/message-id/8737e9bddb82501da1134f021bf4929a%40postgrespro.ru
Increasing Vacuum's ring buffer to size of Bulk Writer's one reduces
autovacuum time in 3-10 times.
(for both patched and unpatched version I used single non-default
setting
'autovacuum_cost_delay=2ms').
This is single line change, and it improves things a lot.
With regards,
--
Sokolov Yura
Postgres Professional: https://postgrespro.ru
The Russian Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
