Re: postgresql latency & bgwriter not doing its job

On 08/25/2014 01:23 PM, Fabien COELHO wrote:
>
> Hello pgdevs,
>
> I've been playing with pg for some time now to try to reduce the maximum
> latency of simple requests, to have a responsive server under small to
> medium load.
>
> On an old computer with a software RAID5 HDD attached, pgbench simple
> update script run for a some time (scale 100, fillfactor 95)
>
> pgbench -M prepared -N -c 2 -T 500 -P 1 ...
>
> gives 300 tps. However this performance is really +1000 tps for a few
> seconds followed by 16 seconds at about 0 tps for the checkpoint induced
> IO storm. The server is totally unresponsive 75% of the time. That's
> bandwidth optimization for you. Hmmm... why not.

So I think that you're confusing the roles of bgwriter vs. spread
checkpoint. What you're experiencing above is pretty common for
nonspread checkpoints on slow storage (and RAID5 is slow for DB updates,
no matter how fast the disks are), or for attempts to do spread
checkpoint on filesystems which don't support it (e.g. Ext3, HFS+). In
either case, what's happening is that the *OS* is freezing all logical
and physical IO while it works to write out all of RAM, which makes me
suspect you're using Ext3 or HFS+.

Making the bgwriter more aggressive adds a significant risk of writing
the same pages multiple times between checkpoints, so it's not a simple fix.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com