Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ULvLe-0006q7-97 for pgsql-performance@arkaria.postgresql.org; Sat, 30 Mar 2013 12:57:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1ULvLd-0002o8-8x for pgsql-performance@arkaria.postgresql.org; Sat, 30 Mar 2013 12:57:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ULvLb-0002o0-1k for pgsql-performance@postgresql.org; Sat, 30 Mar 2013 12:57:27 +0000 Received: from moutng.kundenserver.de ([212.227.17.8]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ULvLV-0002ny-LF for pgsql-performance@postgresql.org; Sat, 30 Mar 2013 12:57:26 +0000 Received: from obelix.localnet (AToulouse-556-1-245-183.w86-217.abo.wanadoo.fr [86.217.241.183]) by mrelayeu.kundenserver.de (node=mrbap2) with ESMTP (Nemesis) id 0M7m6a-1UYw1c1HVI-00vcG2; Sat, 30 Mar 2013 13:57:16 +0100 From: =?iso-8859-15?q?C=E9dric_Villemain?= Reply-To: cedric@2ndquadrant.com Organization: 2ndQuadrant To: pgsql-performance@postgresql.org Subject: Re: Postgresql performance degrading... how to diagnose the root cause Date: Sat, 30 Mar 2013 13:57:10 +0100 User-Agent: KMail/1.13.7 (Linux/3.2.0-4-rt-amd64; KDE/4.8.4; x86_64; ; ) Cc: Franck Routier , Guillaume Cottenceau References: <5155A33A.4090000@axege.com> <5155B3BB.4080209@axege.com> In-Reply-To: <5155B3BB.4080209@axege.com> X-KMail-Markup: true MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart1645330.ocsHTD5hyl"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <201303301357.15510.cedric@2ndquadrant.com> X-Provags-ID: V02:K0:V37XyG1mfkExTPof/SoJbVBMaL3BL1jHHfHsO6bIdMV Uzsw8Ht0AJo/J/V0nmkM5dyjKxzo1XEI9T5yvy3Q/m16yiEUnR 5UQVIu6ydO8CZ+GLESOMgTmfkzkzpTU27hXNeK+mkG17LRcBJ2 HIQXz3fTCREOeayFlexxUMAJxDwpW0FT/VRIKCC7Bx1c519net A/Ks6ToEy+055oeThKJIE/VFezyo1KxQMyfbJXybweG0tiidQZ VD+YBq6FzrF+qPu/sEg4XF6Zzv8cXZjJJ8MNzhDwU2DmpASOqh DPZFpKxUHd1zsm9S44S/5swPbvkpfVGIyQsWv/UiAg7X8XP/J+ rOg0I1pYJRm1EYcvczCWrfZOCjeSW7vPuDuf0kSLPbbILzxqam vzFQyE9zvyuNQ== X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --nextPart1645330.ocsHTD5hyl Content-Type: multipart/alternative; boundary="Boundary-01=_mEuVRCiHCuWJzOz" Content-Transfer-Encoding: 7bit --Boundary-01=_mEuVRCiHCuWJzOz Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: quoted-printable > > I don't know that tcp-b does >=20 > tpcb.jar is a java implementation of the http://www.tpc.org/tpcb/ > benchmark. It is not particularly representative of my workload, but > gives a synthetic, db-agnostic, view of the system performance. > We use it to have quick view to compare differents servers (different > OS, different RDBMS, etc...). =46or information, pgbench is a sort of limited TPC-B benchmark. > That said, the test wil create tables, load them with data, and perform > some transactions on them. > The point that makes me wonder what happens, is that the test run on my > main database is slow, while the same test run on a database on its own > is quick. Do you mean when you run it against already existing data vs its own TPC-B = DB? > This is the same postgresql cluster (same postgresql.conf), same > tablespace (so same disks), same hardware obviously. >=20 > Regarding the server activity, it seems quite flat : iostat shows that > disks are not working much (less than 5%), top shows only one active > core, and load average is well under 1... >=20 > > http://wiki.postgresql.org/wiki/Show_database_bloat >=20 > How do I interpret the output of this query ? Is 1.1 bloat level on a > table alarming, or quite ok ? quite ok. The threshold for maintenance task is around 20%. I wonder about your system catalogs (pg_type, pg_attribute, ...) You can use low level tool provided by PostgreSQL to help figure what's goi= ng=20 wrong. pg_buffercache, pg_stattuple come first to explore your cached data and the= =20 block content. Or some weird database configuration ? (parameters in PostgreSQL can be set= =20 per DB, per role, etc...) =2D-=20 C=E9dric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - D=E9veloppement, Expertise et Formation --Boundary-01=_mEuVRCiHCuWJzOz Content-Type: text/html; charset="iso-8859-15" Content-Transfer-Encoding: quoted-printable

> > I= don't know that tcp-b does

>

> tpcb.j= ar is a java implementation of the http://www.tpc.org/tpcb/

> benchm= ark. It is not particularly representative of my workload, but

> gives = a synthetic, db-agnostic, view of the system performance.

> We use= it to have quick view to compare differents servers (different

> OS, di= fferent RDBMS, etc...).

&nb= sp;

For informa= tion, pgbench is a sort of limited TPC-B benchmark.

&nb= sp;

> That s= aid, the test wil create tables, load them with data, and perform

> some t= ransactions on them.

> The po= int that makes me wonder what happens, is that the test run on my

> main d= atabase is slow, while the same test run on a database on its own

> is qui= ck.

&nb= sp;

Do you mean= when you run it against already existing data vs its own TPC-B DB?

&nb= sp;

> This i= s the same postgresql cluster (same postgresql.conf), same

> tables= pace (so same disks), same hardware obviously.

>

> Regard= ing the server activity, it seems quite flat : iostat shows that

> disks = are not working much (less than 5%), top shows only one active

> core, = and load average is well under 1...

>

> > h= ttp://wiki.postgresql.org/wiki/Show_database_bloat

>

> How do= I interpret the output of this query ? Is 1.1 bloat level on a

> table = alarming, or quite ok ?

&nb= sp;

quite ok. T= he threshold for maintenance task is around 20%.

I wonder ab= out your system catalogs (pg_type, pg_attribute, ...)

&nb= sp;

You can use= low level tool provided by PostgreSQL to help figure what's going wrong.

pg_bufferca= che, pg_stattuple come first to explore your cached data and the block cont= ent.

&nb= sp;

Or some wei= rd database configuration ? (parameters in PostgreSQL can be set per DB, pe= r role, etc...)

--

C=E9dric Vi= llemain +33 (0)6 20 30 22 52

http://2ndQ= uadrant.fr/

PostgreSQL:= Support 24x7 - D=E9veloppement, Expertise et Formation

--Boundary-01=_mEuVRCiHCuWJzOz-- --nextPart1645330.ocsHTD5hyl Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) iQEcBAABAgAGBQJRVuEmAAoJENsgH0yzVBebQ1YH/R2wVbKSaLL2f+jC5cVH2Ol6 VyG7XTkRvgnGF51DgpSvXcDD7Ebht/RqUkoFYTTwW2TXtIdqukw/tCdOWImxKgr7 rsjCn/JjA3BKc/MbqsvANCo24wzu1uF5lJKid7kEk5YOCGaX5vJYGGnoNUHfRBac 7uPRMiXdAA/q9BF1eQ4vG7ovQvT6PZ9BEc+caL2lAkb8Yu/WEg5OEQQud7VeC/f5 E9EdklwqNZ51uB4jz3z+R4Q+6NkyGCf10uLDP2b4/PzJ+F4DD8SrwXe/10P2W4Cp bqZw3X8xuTrYzyVjWtnZmcjSQ182xVtcL9lH8qer9Tdt4bLMovIfP5lzKVMzA/Y= =jlVJ -----END PGP SIGNATURE----- --nextPart1645330.ocsHTD5hyl--