Thread: Re: [COMMITTERS] pgsql: Add pages deleted from pending list to FSM
[ moving thread to -hackers ]
Fujii Masao <masao.fujii@gmail.com> writes:
> So autoanalyze still doesn't call IndexFreeSpaceMapVacuum().
> That is, only backend can clean the list in INSERT-only workload.
> I don't think that this is desirable. Because the backend will
> periodically take a big performance penalty.
> So I'm thinking that even autoanalyze should call IndexFreeSpaceMapVacuum()
> to clean the list in a background, in order to avoid such spikes in
> INSERT response time. Thought?
It seems quite bizarre for auto-analyze to do that. auto-vacuum, sure,
but I do not think this should get plugged into ANALYZE.
Maybe we need to rethink the rules for what is autovacuum vs. autoanalyze,
or come up with some third thing that the autovac daemon does for indexes.
regards, tom lane
-- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/
Reply to both emails in one
Fujii:
> So autoanalyze still doesn't call IndexFreeSpaceMapVacuum().
Fixed, see patch
> ginvacuumcleanup calls RecordFreeIndexPage() twice for the same block.
fixed too
Tom:
> It seems quite bizarre for auto-analyze to do that. auto-vacuum, sure,
> but I do not think this should get plugged into ANALYZE.
I agree, but we want to have pending list shorter as possible.
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
Attachment
On Fri, Sep 18, 2015 at 6:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
[ moving thread to -hackers ]
Fujii Masao <masao.fujii@gmail.com> writes:
> So autoanalyze still doesn't call IndexFreeSpaceMapVacuum().
> That is, only backend can clean the list in INSERT-only workload.
> I don't think that this is desirable. Because the backend will
> periodically take a big performance penalty.
Calling IndexFreeSpaceMapVacuum is only need to reuse the space freed up by cleaning the pending list.
The list is still getting cleaned and truncated by autoanalyze, it is just that the space is not getting marked for reuse.
When I wrote this, my thought was that a table vacuum is going to call IndexFreeSpaceMapVacuum() from another place in its code and so should not call it here as well. I neglected to consider the autoanalyze case.
> So I'm thinking that even autoanalyze should call IndexFreeSpaceMapVacuum()
> to clean the list in a background, in order to avoid such spikes in
> INSERT response time. Thought?
But it already is cleaning the list. If that space is not marked as reusable, that causes index bloat, but doesn't cause latency spikes.
It seems quite bizarre for auto-analyze to do that. auto-vacuum, sure,
but I do not think this should get plugged into ANALYZE.
It may be odd that autoanalyze cleans the list at all (which this patch doesn't change), but given that it does clean the list I don't see why it would be bizarre to make the cleaned-up space re-usable.
Cheers,
Jeff
On Sat, Sep 19, 2015 at 5:20 AM, Jeff Janes <jeff.janes@gmail.com> wrote: > On Fri, Sep 18, 2015 at 6:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> [ moving thread to -hackers ] >> >> Fujii Masao <masao.fujii@gmail.com> writes: >> > So autoanalyze still doesn't call IndexFreeSpaceMapVacuum(). >> > That is, only backend can clean the list in INSERT-only workload. >> > I don't think that this is desirable. Because the backend will >> > periodically take a big performance penalty. > > > Calling IndexFreeSpaceMapVacuum is only need to reuse the space freed up by > cleaning the pending list. > > The list is still getting cleaned and truncated by autoanalyze, it is just > that the space is not getting marked for reuse. > > When I wrote this, my thought was that a table vacuum is going to call > IndexFreeSpaceMapVacuum() from another place in its code and so should not > call it here as well. I neglected to consider the autoanalyze case. > > >> >> > So I'm thinking that even autoanalyze should call >> > IndexFreeSpaceMapVacuum() >> > to clean the list in a background, in order to avoid such spikes in >> > INSERT response time. Thought? > > > But it already is cleaning the list. If that space is not marked as > reusable, that causes index bloat, but doesn't cause latency spikes. Yep, you're right. The problem here is an index bloat. >> It seems quite bizarre for auto-analyze to do that. auto-vacuum, sure, >> but I do not think this should get plugged into ANALYZE. > > > It may be odd that autoanalyze cleans the list at all (which this patch > doesn't change), but given that it does clean the list I don't see why it > would be bizarre to make the cleaned-up space re-usable. Agreed. It's odd to clean up the list but not mark the pages as re-usable. Regards, -- Fujii Masao