Re: Sorted writes in checkpoint

On Fri, 2007-06-15 at 18:33 +0900, ITAGAKI Takahiro wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> 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