X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id EB61E9FB1E0 for ; Thu, 14 Sep 2006 11:00:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 39804-05 for ; Thu, 14 Sep 2006 11:00:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp-msa-out19.orange.fr (smtp19.orange.fr [80.12.242.17]) by postgresql.org (Postfix) with ESMTP id 4A6509FA15C for ; Thu, 14 Sep 2006 11:00:18 -0300 (ADT) Received: from [192.168.1.10] (LSt-Amand-152-32-6-178.w82-127.abo.wanadoo.fr [82.127.21.178]) by mwinf1911.orange.fr (SMTP Server) with ESMTP id 3FE9A1C000B7; Thu, 14 Sep 2006 16:00:16 +0200 (CEST) X-ME-UUID: 20060914140016261.3FE9A1C000B7@mwinf1911.orange.fr Subject: Re: High CPU Load From: =?ISO-8859-1?Q?J=E9r=F4me?= BENOIS To: Guillaume Smet Cc: pgsql-performance@postgresql.org In-Reply-To: <1d4e0c10609140646g6ad1ab4w51dec49afba11fd7@mail.gmail.com> References: <1158239339.5226.17.camel@localhost.localdomain> <1d4e0c10609140646g6ad1ab4w51dec49afba11fd7@mail.gmail.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-LvguBtPQgy20hL6XOvny" Date: Thu, 14 Sep 2006 16:00:13 +0200 Message-Id: <1158242414.5226.31.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.6.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/150 X-Sequence-Number: 20836 --=-LvguBtPQgy20hL6XOvny Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi Guillaume, Le jeudi 14 septembre 2006 =C3=A0 15:46 +0200, Guillaume Smet a =C3=A9crit = : > On 9/14/06, J=C3=A9r=C3=B4me BENOIS wrote: > > I migrated Postgres server from 7.4.6 to 8.1.4, But my server is > > completely full, by moment load average > 40 > > All queries analyzed by EXPLAIN, all indexes are used .. IO is = good ... >=20 > What is the bottleneck? Are you CPU bound? Do you have iowait? Do you > swap? Any weird things in vmstat output? the load average goes up and goes down between 1 and 70, it's strange. IO wait and swap are good. I have just very high CPU load. And it's user land time. top output :=20 top - 15:57:57 up 118 days, 9:04, 4 users, load average: 8.16, 9.16, 15.51 Tasks: 439 total, 7 running, 432 sleeping, 0 stopped, 0 zombie Cpu(s): 87.3% us, 6.8% sy, 0.0% ni, 4.8% id, 0.1% wa, 0.2% hi, 0.8% si Mem: 2076404k total, 2067812k used, 8592k free, 13304k buffers Swap: 1954312k total, 236k used, 1954076k free, 1190296k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 15667 postgres 25 0 536m 222m 532m R 98.8 11.0 1:39.29 postmaster 19533 postgres 25 0 535m 169m 532m R 92.9 8.3 0:38.68 postmaster 16278 postgres 25 0 537m 285m 532m R 86.3 14.1 1:37.56 postmaster 18695 postgres 16 0 535m 171m 532m S 16.1 8.5 0:14.46 postmaster 18092 postgres 16 0 544m 195m 532m R 11.5 9.7 0:31.87 postmaster 16896 postgres 15 0 534m 215m 532m S 6.3 10.6 0:27.13 postmaster 4835 postgres 15 0 535m 147m 532m S 2.6 7.3 1:27.20 postmaster 4836 postgres 15 0 536m 154m 532m S 2.0 7.6 1:26.07 postmaster 4833 postgres 15 0 535m 153m 532m S 1.0 7.6 1:26.54 postmaster 4839 postgres 15 0 535m 148m 532m S 1.0 7.3 1:25.10 postmaster 15083 postgres 15 0 535m 44m 532m S 1.0 2.2 0:16.13 postmaster Vmstat output : procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 4 0 236 13380 13876 1192036 0 0 0 0 1 1 19 6 70 5 4 0 236 13252 13876 1192036 0 0 10 0 0 0 92 8 0 0 16 0 236 13764 13884 1192096 0 0 52 28 0 0 91 9 0 0 4 0 236 11972 13904 1192824 0 0 320 17 0 0 92 8 0 0 4 0 236 12548 13904 1192892 0 0 16 0 0 0 92 8 0 0 9 0 236 11908 13912 1192884 0 0 4 38 0 0 91 9 0 0 8 0 236 8832 13568 1195676 0 0 6975 140 0 0 91 9 0 0 8 0 236 10236 13588 1193208 0 0 82 18 0 0 93 7 0 0 6 0 236 9532 13600 1193264 0 0 76 18 0 0 92 8 0 0 10 1 236 11060 13636 1193432 0 0 54 158 0 0 91 9 0 0 6 0 236 10204 13636 1193432 0 0 8 0 0 0 92 8 0 0 8 1 236 10972 13872 1192720 0 0 28 316 0 0 91 9 0 0 6 0 236 11004 13936 1192724 0 0 4 90 0 0 92 8 0 0 7 0 236 10300 13936 1192996 0 0 150 0 0 0 92 8 0 0 11 0 236 11004 13944 1192988 0 0 16 6 0 0 91 8 0 0 17 0 236 10732 13996 1193208 0 0 118 94 0 0 91 9 0 0 6 0 236 10796 13996 1193820 0 0 274 0 0 0 91 9 0 0 24 0 236 9900 13996 1193820 0 0 8 0 0 0 92 8 0 0 13 0 236 9420 14016 1194004 0 0 100 98 0 0 92 8 0 0 8 0 236 9276 13944 1188976 0 0 42 0 0 0 92 8 0 0 3 0 236 14524 13952 1188968 0 0 0 38 0 0 77 8 16 0 3 0 236 15164 13960 1189164 0 0 92 6 0 0 65 7 28 0 3 0 236 16380 13968 1189156 0 0 8 36 0 0 57 7 36 0 1 0 236 15604 14000 1189260 0 0 38 37 0 0 39 6 54 1 1 0 236 16564 14000 1189328 0 0 0 0 0 0 38 5 57 0 1 1 236 14900 14024 1189372 0 0 28 140 0 0 47 7 46 0 1 1 236 10212 14100 1195280 0 0 2956 122 0 0 21 3 71 5 5 0 236 13156 13988 1192400 0 0 534 6 0 0 19 3 77 1 0 0 236 8408 13996 1197016 0 0 4458 200 0 0 18 2 78 2 1 0 236 9784 13996 1195588 0 0 82 0 0 0 16 3 81 0 0 0 236 10728 14028 1195556 0 0 30 118 0 0 11 2 87 1 Thanks for your help, --=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('@')])" > > My configuration is correct ? > > work_mem =3D 65536 >=20 > If you have a lot of concurrent queries, it's probably far too much. > That said, if you don't swap, it's probably not the problem. >=20 > -- > Guillaume >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >=20 --=-LvguBtPQgy20hL6XOvny 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) iD8DBQBFCWBsCDKlmlhpwnERAoUpAJ0SI4/ZNdLDb6ZvY96Y1KDwygLMhwCcCvLp 7AN3wMMaB2/nqVf5/JyI8zw= =nVLz -----END PGP SIGNATURE----- --=-LvguBtPQgy20hL6XOvny--