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
freelist_buffers.patch
Description: Binary data
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings