Re: Vacuum, Freeze and Analyze: the big picture

On Mon, Jun 03, 2013 at 11:27:57AM +0300, Ants Aasma wrote:
> > I can't rule that out. Personally, I've always attributed it to the
> > fact that it's (a) long and (b) I/O-intensive. But it's not
> > impossible there could also be bugs lurking.
>
> It could be related to the OS. I have no evidence for or against, but
> it's possible that OS write-out routines defeat the careful cost based
> throttling that PostgreSQL does by periodically dumping a large
> portion of dirty pages into the write queue at once. That does nasty
> things to query latencies as evidenced by the work on checkpoint
> spreading.

In other contexts I've run into issues relating to large continuous
writes stalling. The issue is basically that the Linux kernel allows
(by default) writes to pile up until they reach 5% of physical memory
before deciding that the sucker who wrote the last block becomes
responsible for writing the whole lot out. At full speed of course.
Depending on the amount of memory and the I/O speed of your disks this
could take a while, and interfere with other processes.

This leads to extremely bursty I/O behaviour.

The solution, as usual, is to make it more aggressive, so the
kernel background writer triggers at 1% memory.

I'm not saying that's the problem here, but it is an example of a
situation where the write queue can become very large very quickly.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer