Re: Single pass vacuum - take 1

On Thu, Jul 14, 2011 at 11:46 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

>
>
> Hi Pavan,
>
> I'd say that seems way too complex for such a small use case and we've
> only just fixed the bugs from 8.4 vacuum map complexity. The code's
> looking very robust now and I'm uneasy that such changes are really
> worth it.
>
>
Thanks Simon for looking at the patch.

I am not sure if the use case is really narrow. Today, we dirty the pages in
both the passes and also emit WAL records. Just the heap scan can take a
very long time for large tables, blocking the autovacuum worker threads from
doing useful work on other tables. If I am not wrong, we use ring buffers
for vacuum which would most-likely force those buffers to be written/read
twice to the disk.

Which part of the patch you think is very complex ? We can try to simplify
that. Or are you seeing any obvious bugs that I missed ? IMHO taking out a
phase completely from vacuum (as this patch does) can simplify things.

> You're trying to avoid Phase 3, the second pass on the heap. Why not
> avoid the write in Phase 1 if its clear that we'll need to come back
> again in Phase 3? So we either do a write in Phase 1 or in Phase 3,
> but never both? That minimises the writes, which are what hurt the
> most.
>
>
You can possibly do the work in the Phase 3, but that doesn't avoid the
second scan.

> We can reduce the overall cost simply by not doing Phase 2 and Phase 3
> if the number of rows to remove is too few, say < 1%.
>

If you have set the vacuum parameters such that it kicks in when there are
say 5% updates/deletes, you would most likely have that much work to do
anyways.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com