Date: Tue, 28 Aug 2007 16:34:04 -0500
From: Decibel! <decibel@decibel.org>
To: Erik Jones <erik@myemma.com>
Cc: Kenneth Marshall <ktm@rice.edu>, Dmitry Potapov <fortune.fish@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: io storm on checkpoints, postgresql 8.2.4, linux
Message-ID: <20070828213404.GH1386@nasby.net>
References: <878c83960708220833l72dbd461k827aea77ec7d6c32@mail.gmail.com>
	<20070822155719.GJ26562@it.is.rice.edu>
	<4837743A-95CB-4A8A-A107-531B96A223DD@myemma.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="MZf7D3rAEoQgPanC"
Content-Disposition: inline
In-Reply-To: <4837743A-95CB-4A8A-A107-531B96A223DD@myemma.com>
User-Agent: Mutt/1.5.13 (2006-08-11)


--MZf7D3rAEoQgPanC
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Aug 28, 2007 at 10:00:57AM -0500, Erik Jones wrote:
> >>    It seemed strange to me that our 70%-read db generates so much =20
> >>dirty
> >>pages that writing them out takes 4-8 seconds and grabs the full =20
> >>bandwidth.
> >>First, I started to tune bgwriter to a more aggressive settings, =20
> >>but this
> >>was of no help, nearly no performance changes at all. Digging into =20
> >>the issue
> >>further, I discovered that linux page cache was the reason. "Dirty"
> >>parameter in /proc/meminfo (which shows the amount of ready-to-=20
> >>write "dirty"
> >>data currently sitting in page cache) grows between checkpoints =20
> >>from 0 to
> >>about 100Mb. When checkpoint comes, all the 100mb got flushed out =20
> >>to disk,
> >>effectively causing a IO storm.
> >>
> >>    I found this (http://www.westnet.com/~gsmith/content/linux-=20
> >>pdflush.htm
> >><http://www.westnet.com/%7Egsmith/content/linux-pdflush.htm>) =20
> >>document and
> >>peeked into mm/page-writeback.c in linux kernel source tree. I'm =20
> >>not sure
> >>that I understand pdflush writeout semantics correctly, but looks =20
> >>like when
> >>the amount of "dirty" data is less than dirty_background_ratio*RAM/=20
> >>100,
> >>pdflush only writes pages in background, waking up every
> >>dirty_writeback_centisecs and writing no more than 1024 pages
> >>(MAX_WRITEBACK_PAGES constant). When we hit =20
> >>dirty_background_ratio, pdflush
> >>starts to write out more agressively.
> >>
> >>    So, looks like the following scenario takes place: postgresql =20
> >>constantly
> >>writes something to database and xlog files, dirty data gets to =20
> >>the page
> >>cache, and then slowly written out by pdflush. When postgres =20
> >>generates more
> >>dirty pages than pdflush writes out, the amount of dirty data in the
> >>pagecache is growing. When we're at checkpoint, postgres does fsync=20
> >>() on the
> >>database files, and sleeps until the whole page cache is written out.
> >>
> >>    By default, dirty_background_ratio is 2%, which is about 328Mb =20
> >>of 16Gb
> >>total. Following the curring pdflush logic, nearly this amount of =20
> >>data we
> >>face to write out on checkpoint effective stalling everything =20
> >>else, so even
> >>1% of 16Gb is too much. My setup experience 4-8 sec pause in =20
> >>operation even
> >>on ~100Mb dirty pagecache...
> >>
> >>     I temporaly solved this problem by setting =20
> >>dirty_background_ratio to
> >>0%. This causes the dirty data to be written out immediately. It =20
> >>is ok for
> >>our setup (mostly because of large controller cache), but it =20
> >>doesn't looks
> >>to me as an elegant solution. Is there some other way to fix this =20
> >>issue
> >>without disabling pagecache and the IO smoothing it was designed =20
> >>to perform?
> >
> >You are working at the correct level. The bgwriter performs the I/O =20
> >smoothing
> >function at the database level. Obviously, the OS level smoothing =20
> >function
> >needed to be tuned and you have done that within the parameters of =20
> >the OS.
> >You may want to bring this up on the Linux kernel lists and see if =20
> >they have
> >any ideas.
> >
> >Good luck,
> >
> >Ken
>=20
> Have you tried decreasing you checkpoint interval?  That would at =20
> least help to reduce the amount of data that needs to be flushed when =20
> Postgres fsyncs.

The downside to that is it will result in writing a lot more data to WAL
as long as full page writes are on.

Isn't there some kind of a timeout parameter for how long dirty data
will sit in the cache? It seems pretty broken to me to allow stuff to
sit in a dirty state indefinitely.
--=20
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

--MZf7D3rAEoQgPanC
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFG1JTMdO30qud8SkgRAtk2AJwIQJCAstBw+lZa/a++KDBP9kvWvgCgvWHG
Lg25pyfHSy3cw/4thK0qiO8=
=s3wh
-----END PGP SIGNATURE-----

--MZf7D3rAEoQgPanC--