<div dir="ltr"><br /></div><div class="gmail_extra"><br /><br /><div class="gmail_quote">On Sat, Jul 20, 2013 at 6:28
PM,Greg Smith <span dir="ltr"><<a href="mailto:greg@2ndquadrant.com"
target="_blank">greg@2ndquadrant.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex"><div class="im">On 7/20/13 4:48 AM, didier wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> With your tests did you try
towrite the hot buffers first? ie buffers<br /> with a high refcount, either by sorting them on refcount or at
least<br/> sweeping the buffer list in reverse?<br /></blockquote><br /></div> I never tried that version. After a few
roundsof seeing that all changes I tried were just rearranging the good and bad cases, I got pretty bored with trying
newchanges in that same style.<div class="im"><br /><br /><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex"> by writing to the OS the less likely to be recycle buffers first it
may<br/> have less work to do at fsync time, hopefully they have been written by<br /> the OS background task during
thespread and are not re-dirtied by other<br /> backends.<br /></blockquote><br /></div> That is the theory. In
practicewrite caches are so large now, there is almost no pressure forcing writes to happen until the fsync calls show
up. It's easily possible to enter the checkpoint fsync phase only to discover there are 4GB of dirty writes ahead of
you,ones that have nothing to do with the checkpoint's I/O.<br /><br /> Backends are constantly pounding the write
cachewith new writes in situations with checkpoint spikes. The writes and fsync calls made by the checkpoint process
areonly a fraction of the real I/O going on. The volume of data being squeezed out by each fsync call is based on total
writesto that relation since the checkpoint. That's connected to the writes to that relation happening during the
checkpoint,but the checkpoint writes can easily be the minority there.<br /><br /> It is not a coincidence that the
nextfeature I'm working on attempts to quantify the total writes to each 1GB relation chunk. That's the most promising
pathforward on the checkpoint problem I've found.<div class="HOEnZb"><div class="h5"><br /><br /> -- <br /> Greg Smith
2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD<br /> PostgreSQL Training, Services, and 24x7 Support <a
href="http://www.2ndQuadrant.com"target="_blank">www.2ndQuadrant.com</a><br /></div></div></blockquote></div><br
/></div>