Re: io storm on checkpoints, postgresql 8.2.4, linux

On Thu, 23 Aug 2007, Dmitry Potapov wrote:

> I'm planning to do so, but before I need to take a look at postgresql source
> and dev documentation to find how exactly IO is done, to be able to explain
> the issue to linux kernel people.

I can speed that up for you.
http://developer.postgresql.org/index.php/Buffer_Cache%2C_Checkpoints%2C_and_the_BGW
outlines all the source code involved. Easiest way to browse through the
code is it via http://doxygen.postgresql.org/ , eventually I want to
update the page so it points right into the appropriate doxygen spots but
haven't gotten to that yet.

> By the way, does postgresql has a similar stall problem on freebsd/other
> OS'es? It would be interesting to study their approach to io smoothing if it
> doesn't.

There's some evidence that something about Linux aggrevates the problem;
check out
http://archives.postgresql.org/pgsql-hackers/2007-07/msg00261.php and the
rest of the messages in that thread. I haven't heard a report of this
problem from someone who isn't running Linux, but as it requires a certain
level of hardware and a specific type of work load I'm not sure if this is
coincidence or a cause/effect relationship.

> Is there any other changes in 8.3devel that can affect the results of
> such test?

The "all" component of the background writer was removed as it proved not
to be useful once checkpoint_completion_target was introduced. And the
LRU background writer keeps going while checkpoints are being trickled
out, in earlier versions that didn't happen.

The test I'd like to see more people run is to simulate their workloads
with checkpoint_completion_target set to 0.5, 0.7, and 0.9 and see how
each of those settings works relative to the 8.2 behavior.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD