On Fri, 2007-06-15 at 18:33 +0900, ITAGAKI Takahiro wrote: > "Simon Riggs" <[EMAIL PROTECTED]> wrote: > > > > tests | pgbench | DBT-2 response time (avg/90%/max) > > > ---------------------------+---------+----------------------------------- > > > LDC only | 181 tps | 1.12 / 4.38 / 12.13 s > > > + BM_CHECKPOINT_NEEDED(*) | 187 tps | 0.83 / 2.68 / 9.26 s > > > + Sorted writes | 224 tps | 0.36 / 0.80 / 8.11 s > > > > I'm very surprised by the BM_CHECKPOINT_NEEDED results. What percentage > > of writes has been saved by doing that? > > How long was the write phase of the checkpoint, how long > > between checkpoints? > > > > I can see the sorted writes having an effect because the OS may not > > receive blocks within a sufficient time window to fully optimise them. > > That effect would grow with increasing sizes of shared_buffers and > > decrease with size of controller cache. How big was the shared buffers > > setting? What OS scheduler are you using? The effect would be greatest > > when using Deadline. > > I didn't tune OS parameters, used default values. > In terms of cache amounts, postgres buffers were larger than kernel > write pool and controller cache. that's why the OS could not optimise > writes enough in checkpoint, I think. > > - 200MB <- RAM * dirty_background_ratio > - 128MB <- Controller cache > - 2GB <- postgres shared_buffers > > I forget to gather detail I/O information in the tests. > I'll retry it and report later. > > RAM 2GB > Controller cache 128MB > shared_buffers 1GB > checkpoint_timeout = 15min > checkpoint_write_percent = 50.0 > > RHEL4 (Linux 2.6.9-42.0.2.EL) > vm.dirty_background_ratio = 10 > vm.dirty_ratio = 40 > vm.dirty_expire_centisecs = 3000 > vm.dirty_writeback_centisecs = 500 > Using cfq io scheduler
Sounds like sorting the buffers before checkpoint is going to be a win once we go above about ~128MB. We can do a simple test on NBuffers, rather than have a sort_blocks_at_checkoint (!) GUC. But it does seem there is a win for larger settings of shared_buffers. Does performance go up in the non-sorted case if we make shared_buffers smaller? Sounds like it might. We should check that first. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly