Hello,
I'm testing the recently changes of WAL entries for freezing-tuples.
VACUUM FREEZE took more time. The cause seems to be flushing WAL buffers.
Vacuuming processes free buffers into freelist. The buffers in freelist is
preferentially used on next allocation of buffers. Then, if the buffer is
dirty, the allocator must write it before reuse. However, there are few buffers
in freelist typically, buffers made dirty recently are reused too soon
-- The WAL entries for the dirty buffer has not been flushed yet, so the
allocator flushes WAL, writes the buffer, and finally reuses it.
One solution is always keeping some buffers in freelist. If there were
N buffers in freelist, the necessity of WAL-flusing was reduced to 1/N,
because all WAL entries are flushed when we do one of them.
The attached patch is an experimental implementation of the above. Keeping
32 buffers seems to be enough when executed separately. With some background
jobs, other numbers may be better.
N | time | XLogWrite/XLogFlush
---+-------+---------------------
1 | 68.2s | 25.6%
8 | 57.4s | 10.8%
32 | 54.0s | 3.4%
[initial data]
$ pgbench -s 40 -i;
# VACUUM FREEZE
[test]
# UPDATE accounts SET aid=aid WHERE random() < 0.005;
# checkpoint;
# VACUUM FREEZE accounts;
I cannot see the above problem in non-freeze vacuum. The number buffers
in freelist increases on index-vacuuming phase. When the vacuum found
seldom used buffers (refcount==0 and usage_count==0), they are added into
freelist. So the WAL entries generated in index-vacuuming or heap-vacuuming
phase are not so serious. However, entries for FREEZE are generated in
heap-scanning phase, it is before index-vacuuming.
Are there any better fixes? Comments welcome.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center