Re: Trying to minimize the impact of checkpoints

Added to TODO:

* Add an option to sync() before fsync()'ing checkpoint files

---------------------------------------------------------------------------

Tom Lane wrote:
> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> > I didn't save any of the charts done with 7.4, but the responsetime
> > spikes on checkpoints went up to 60 seconds without the bgwriter. If you
> > look at the last chart on this page
> > http://developer.postgresql.org/~wieck/vacuum_cost/
> > there are no spikes at all.
>
> I have been meaning to ask you to redo those charts with CVS tip, to see
> how things work now that checkpoints use fsync() instead of sync().
>
> There was talk earlier of providing an option to issue sync() before
> starting the loop that issues fsync() against each file we've written
> since the last checkpoint. The idea was that the sync() would cue the
> kernel to schedule I/O for all currently dirty buffers in the most
> efficient order, and then the fsync()s would merely ensure that Postgres
> waits until the I/O it needs is done. This should be optional since it
> would be a clear loser in systems where Postgres isn't the dominant
> cause of disk write traffic (since the sync would force much unneeded
> I/O). But in a system that's dedicated to one Postgres installation it
> seems like it might be a win, compared to doing just fsyncs which might
> cause the I/O to be done in a globally non-optimal order.
>
> On the other hand, if the bgwriter's trickle writes are getting the job
> done then there shouldn't be all that much work to do at checkpoint
> time, and so this might be all just theorizing with not much real-world
> effect.
>
> So, before troubling to create this option I'd like to see some
> evidence that it'd actually be worthwhile. Could you test it out?
> The place to put the sync() call would be at the top of mdsync() in
> storage/smgr/md.c.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073