On 1/22/07, Heikki Linnakangas <heikki@enterprisedb.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