Subject: Re: High CPU Load
From: =?ISO-8859-1?Q?J=E9r=F4me?= BENOIS <benois@argia-engineering.fr>
To: Evgeny Gridasov <eugene@i-free.ru>
Cc: Dave Dutcher <dave@tridecap.com>,
	pgsql-performance@postgresql.org, Xavier Milliard <milliard@argia.fr>
In-Reply-To: <20060914204739.4a4371f8.eugene@i-free.ru>
References: <004001c6d80e$bdef5bc0$8300a8c0@tridecap.com>
	<1158246565.5226.59.camel@localhost.localdomain>
	<20060914204739.4a4371f8.eugene@i-free.ru>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-Ji9vt3cTsvvE2HxMccQn"
Date: Thu, 14 Sep 2006 23:37:21 +0200
Message-Id: <1158269841.5167.9.camel@localhost.localdomain>
Mime-Version: 1.0


--=-Ji9vt3cTsvvE2HxMccQn
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi Evgeny,

Le jeudi 14 septembre 2006 =C3=A0 20:47 +0400, Evgeny Gridasov a =C3=A9crit=
 :
> J=C3=A9r=C3=B4me,
>=20
> How many concurrent connections do you have?
I have between 300 and 400 concurrent connections.

> Because You've got only 2GB of ram this is important! Postgres process
> takes some bytes in memory =3D) .. I don't exactly how many,
> but thinking if it is about 2Mb you'll get about 1Gb of ram used only by
> postgres' processes (for 512 connections)!
> Don't forget about your 512Mb shared memory setting,
> postgres shared libraries and the OS filesystem cache...
>=20
> I hope your postgres binaries are not statically linked?
no, i not use static binaries

> Try using connection pooling in your software, or add some RAM, it's chea=
p.
> And I think that work_mem of 65536 is too high for your system...

I already use connection pool but i have many servers in front of database =
server.

Ok i will test new lower work_mem tomorrow.

--=20
J=C3=A9r=C3=B4me,

python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in 'sioneb@gnireenigne-aigra.rf'.split('@')])"
> On Thu, 14 Sep 2006 17:09:25 +0200
> J=C3=A9r=C3=B4me BENOIS <benois@argia-engineering.fr> wrote:
>=20
> > Hi Dave,
> > Le jeudi 14 septembre 2006 =C3=A0 10:02 -0500, Dave Dutcher a =C3=A9cri=
t :
> > > > -----Original Message-----
> > > > From: pgsql-performance-owner@postgresql.org=20
> > > > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20
> > > > J=C3=A9r=C3=B4me BENOIS
> > > >=20
> > >  explain analyze select distinct
> > > > INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_
> > > > VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from (((=20
> > > > select distinct ei_id as EIID from mpng2_ei_attribute as=20
> > > > reqin1 where reqin1.CATEGORYATTR_ID =3D 0 AND reqin1.TEXT_VALUE=20
> > > > ilike '' and ei_id in ( select distinct ei_id as EIID from=20
> > > > mpng2_ei_attribute as reqin2 where reqin2.CATEGORYATTR_ID =3D 0=20
> > > > AND reqin2.TEXT_VALUE ilike '' and ei_id in ( select distinct=20
> > > > ei_id as EIID from mpng2_ei_attribute as reqin3 where=20
> > > > reqin3.NAME =3D '' AND reqin3.STRING_VALUE =3D '' ) ) ) ) ) as=20
> > > > req0 join mpng2_ei_attribute on req0.eiid =3D=20
> > > > mpng2_ei_attribute.ei_id order by ei_id asc;
> > >=20
> > >=20
> > > That is a lot of distinct's.  Sorts are one thing that can really use=
 up
> > > CPU.  This query is doing lots of sorts, so its not surprising the CP=
U usage
> > > is high. =20
> > >=20
> > > On the subqueries you have a couple of cases where you say "... in (s=
elect
> > > distinct ...)"  I don=E2=80=99t think the distinct clause is necessar=
y in that case.
> > > I'm not a hundred percent sure, but you might want to try removing th=
em and
> > > see if the query results are the same and maybe the query will execut=
e
> > > faster.
> >=20
> > Thanks for your advice, but the load was good with previous version of
> > postgres -> 7.4.6 on the same server and same datas, same application,
> > same final users ...
> >=20
> > So we supect some system parameter, but which ?
> >=20
> > With vmstat -s is showing a lot of "pages swapped out", have you an
> > idea ?
> >=20
> > Thanls a lot,
>=20
>=20

--=-Ji9vt3cTsvvE2HxMccQn
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: Ceci est une partie de message
	=?ISO-8859-1?Q?num=E9riquement?= =?ISO-8859-1?Q?_sign=E9e?=

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

iD8DBQBFCcuQCDKlmlhpwnERAoRUAJ0bwJF0IAXU0P/Nu0+xHLJv1keS/ACfXtKB
f9Cf3TZhsGMB/7BtenLxBHs=
=0eg1
-----END PGP SIGNATURE-----

--=-Ji9vt3cTsvvE2HxMccQn--