Subject: Re: Context switch storm
From: Andreas Kostyrka <andreas@kostyrka.org>
To: Richard Huxton <dev@archonet.com>
Cc: Cosimo Streppone <cosimo@streppone.it>,
	Postgresql Performance list <pgsql-performance@postgresql.org>
In-Reply-To: <454B4438.9080105@archonet.com>
References: <efc1d7c07cf.454afe8d@brturbo.com.br> <454B3B75.6@archonet.com>
	<454B3EDC.3090502@streppone.it>  <454B4438.9080105@archonet.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-f5CFdf2/12kjpD5VJG9/"
Date: Fri, 03 Nov 2006 14:53:02 +0100
Message-Id: <1162561983.16439.3.camel@andi-lap>
Mime-Version: 1.0


--=-f5CFdf2/12kjpD5VJG9/
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

The solution for us has been twofold:

upgrade to the newest PG version available at the time while we waited
for our new Opteron-based DB hardware to arrive.

Andreas


Am Freitag, den 03.11.2006, 13:29 +0000 schrieb Richard Huxton:
> Cosimo Streppone wrote:
> > Richard Huxton wrote:
> >=20
> >> creimer@brturbo.com.br wrote:
> >>>
> >>> The average context switching for this server as vmstat shows is 1
> >>> but when the problem occurs it goes to 250000.
> >>
> >> You'll tend to see it when you have multiple clients and most queries=20
> >> can use RAM rather than disk I/O. My understanding of what happens is=20
> >> that PG requests data from RAM - it's not in cache so the process gets=
=20
> >> suspended to wait. The next process does the same, with the same=20
> >> result.   You end up with lots of processes all fighting over what=20
> >> data is in the cache and no-one gets much work done.
> >=20
> > Does this happen also with 8.0, or is specific to 8.1 ?
>=20
> All versions suffer to a degree - they just push the old Xeon in the=20
> wrong way. However, more recent versions *should* be better than older=20
> versions. I believe some work was put in to prevent contention on=20
> various locks which should reduce context-switching across the board.
>=20
> > I seem to have the same exact behaviour for an OLTP-loaded 8.0.1 server
>=20
> upgrade from 8.0.1 - the most recent is 8.0.9 iirc
>=20
> > when I raise `shared_buffers' from 8192 to 40000.
> > I would expect an increase in tps/concurrent clients, but I see an aver=
age
> > performance below a certain threshold of users, and when concurrent use=
rs
> > get above that level, performance starts to drop, no matter what I do.
>=20
> Are you seeing a jump in context-switching in top? You'll know when you=20
> do - it's a *large* jump. That's the key diagnosis. Otherwise it might=20
> simply be your configuration settings aren't ideal for that workload.
>=20
> > Server logs and io/vm statistics seem to indicate that there is little
> > or no disk activity but machine loads increases to 7.0/8.0.
> > After some minutes, the problem goes away, and performance returns
> > to acceptable levels.
>=20
> That sounds like it. Query time increases across the board as all the=20
> clients fail to get any data back.
>=20
> > When the load increases, *random* database queries show this "slowness"=
,
> > even if they are perfectly planned and indexed.
> >=20
> > Is there anything we can do?
>=20
> Well, the client I saw it with just bought a dual-opteron server and=20
> used their quad-Xeon for something else. However, I do remember that 8.1=20
> seemed better than 7.4 before they switched. Part of that might just=20
> have been better query-planning and other efficiences though.
>=20

--=-f5CFdf2/12kjpD5VJG9/
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: Dies ist ein digital signierter Nachrichtenteil

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQBFS0m+HJdudm4KnO0RArHdAJ9RKcRUQdPnqzlkTsGOIVejGrB8vACgpFhs
wR0M9w/R8Cs60KvlkMfjVI0=
=LHAm
-----END PGP SIGNATURE-----

--=-f5CFdf2/12kjpD5VJG9/--