Re: [Lsf-pc] Linux kernel impact on PostgreSQL performance

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Jan Kara <jack(at)suse(dot)cz> wrote:
>
>> Just to get some idea about the sizes - how large are the
>> checkpoints we are talking about that cause IO stalls?
>
> Big.

To quantify that, in a production setting we were seeing pauses of
up to two minutes with shared_buffers set to 8GB and default dirty
page settings for Linux, on a machine with 256GB RAM and 512MB
non-volatile cache on the RAID controller.  To eliminate stalls we
had to drop shared_buffers to 2GB (to limit how many dirty pages
could build up out-of-sight from the OS), spread checkpoints to 90%
of allowed time (almost no gap between finishing one checkpoint and
starting the next) and crank up the background writer so that no
dirty page sat unwritten in PostgreSQL shared_buffers for more than
4 seconds. Less aggressive pushing to the OS resulted in the
avalanche of writes I previously described, with the corresponding
I/O stalls.  We approached that incrementally, and that's the point
where stalls stopped occurring.  We did not adjust the OS
thresholds for writing dirty pages, although I know of others who
have had to do so.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company