From: | Feike Steenbergen <feikesteenbergen(at)gmail(dot)com> |
---|---|
To: | Peter Geoghegan <pg(at)bowt(dot)ie> |
Cc: | PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Skip index cleanup if autovacuum did not do any work |
Date: | 2017-11-29 11:39:17 |
Message-ID: | CAK_s-G0Yr2x1ePLR3GFYHp18krDAXYC+tyM1Y6wcRb9PuWSMog@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 28 November 2017 at 23:17, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> BTW, a good short term solution for you might be to change the vacuum
> cost delay settings. They're pretty conservative by default.
>
> There is a good chance that your indexes are mostly in memory even on
> large tables, and B-Tree indexes are read sequentially during VACUUM.
Unfortunately we have already tuned autovacuum to be more aggressive.
Our indexes are large (more than 1TB of indexes for a given table) and
the very frequent wraparound requires us to be doing > 25MB/s just to
do a lot of index vacuuming, which in turn is a significant chunk of the I/O
of the whole system, and is filling memory with unused pages.
I was hoping to find a silver bullet, but it seems it's a bit more complex.
Perhaps there is a way to skip any index maintenance if we can determine
that the heap is all-frozen, all-visible, so for stale partitions, even if only
limited to btree indexes. I'll see if I can review the patch in progress.
Thanks for the advice,
regards,
Feike
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Khandekar | 2017-11-29 11:55:34 | Re: [HACKERS] UPDATE of partition key |
Previous Message | Beena Emerson | 2017-11-29 11:35:39 | Re: [HACKERS] Runtime Partition Pruning |