Re: Speeding up an in-progress wraparound-preventing vacuum - Mailing list pgsql-general

From Vincent de Phily
Subject Re: Speeding up an in-progress wraparound-preventing vacuum
Date
Msg-id 2115723.l95MbpnbDW@moltowork
Whole thread Raw
In response to Re: Speeding up an in-progress wraparound-preventing vacuum  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Speeding up an in-progress wraparound-preventing vacuum
List pgsql-general
On Tuesday 09 December 2014 16:56:39 Tom Lane wrote:
> Vincent de Phily <vincent.dephily@mobile-devices.fr> writes:
> > It reads about 8G of the table (often doing a similar number of writes,
> > but
> > not always), then starts reading the pkey index and the second index (only
> > 2 indexes on this table), reading both of them fully (some writes as
> > well, but not as many as for the table), which takes around 8h.
> >
> > And the cycle apparently repeats: process a few more GB of the table, then
> > go reprocess both indexes fully. A rough estimate is that it spends ~6x
> > more time (re)processing the indexes as it does processing the table
> > (looking at data size alone the ratio would be 41x, but the indexes go
> > faster). I'm probably lucky to only have two indexes on this table.
> >
> > Is that the expected behaviour ?
>
> Yes.  It can only remember so many dead tuples at a time, and it has
> to go clean the indexes when the dead-TIDs buffer fills up.

Fair enough. And I guess it scans the whole index each time because the dead
tuples are spread all over ?

What happens when vacuum is killed before it had time to go though the index
with its dead-TID buffer ? Surely the index isn't irreversibly bloated; and
whatever is done then could be done in the normal case ?

It still feels like a lot of wasted IO.

> You could
> increase maintenance_work_mem to increase the size of that buffer.

Will do, thanks.

--
Vincent de Phily



pgsql-general by date:

Previous
From: Roxanne Reid-Bennett
Date:
Subject: Re: Updating single/multiple fields of JSON document
Next
From: Tom Lane
Date:
Subject: Re: Speeding up an in-progress wraparound-preventing vacuum