From: | Vincent de Phily <vincent(dot)dephily(at)mobile-devices(dot)fr> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Speeding up an in-progress wraparound-preventing vacuum |
Date: | 2014-12-09 22:29:15 |
Message-ID: | 2115723.l95MbpnbDW@moltowork |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tuesday 09 December 2014 16:56:39 Tom Lane wrote:
> Vincent de Phily <vincent(dot)dephily(at)mobile-devices(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-12-09 22:32:30 | Re: Speeding up an in-progress wraparound-preventing vacuum |
Previous Message | Roxanne Reid-Bennett | 2014-12-09 22:18:04 | Re: Updating single/multiple fields of JSON document |