Re: Piggybacking vacuum I/O

On 1/22/07, Heikki Linnakangas <heikki(at)enterprisedb(dot)com> wrote:
>
> I've been looking at the way we do vacuums.
>
> The fundamental performance issue is that a vacuum generates
> nheapblocks+nindexblocks+ndirtyblocks I/Os. Vacuum cost delay helps to
> spread the cost like part payment, but the total is the same. In an I/O
> bound system, the extra I/O directly leads to less throughput.
>
>
Another source of I/O is perhaps the CLOG read/writes for checking
transaction status. If we are talking about large tables like accounts in
pgbench or customer/stock in DBT2, the tables are vacuumed much later than
the actual UPDATEs. I don't have any numbers to prove yet, but my sense is
that CLOG pages holding the status of many of the transactions might have
been already flushed out of the cache and require an I/O. Since the default
CLOG SLRU buffers is set to 8, there could be severe CLOG SLRU thrashing
during VACUUM as the transaction ids will be all random in a heap page.

Would it help to set the status of the XMIN/XMAX of tuples early enough such
that the heap page is still in the buffer cache, but late enough such that
the XMIN/XMAX transactions are finished ? How about doing it when the
bgwriter is about to write the page to disk ? Assuming few seconds of life
of a heap page in the buffer cache, hopefully most of the XMIN/XMAX
transactions should have completed and bgwriter can set XMIN(XMAX)_COMMITTED
or XMIN(XMAX)_INVALID for most of the tuples in the page. This would save us
CLOG I/Os later, either during subsequent access to the tuple and/or
vacuum.

Any thoughts ?

Thanks,
Pavan

EnterpriseDB http://www.enterprisedb.com