X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 95ABB3A3C59 for ; Tue, 26 Oct 2004 21:50:58 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 36060-03 for ; Tue, 26 Oct 2004 20:50:55 +0000 (GMT) Received: from mail1.catalyst.net.nz (godel.catalyst.net.nz [202.49.159.12]) by svr1.postgresql.org (Postfix) with ESMTP id 98E143A3AFF for ; Tue, 26 Oct 2004 21:50:53 +0100 (BST) Received: from leibniz.catalyst.net.nz ([202.49.159.7] helo=lamb.mcmillan.net.nz) by mail1.catalyst.net.nz with asmtp (TLS-1.0:DHE_RSA_3DES_EDE_CBC_SHA:24) (Exim 4.34) id 1CMYHK-0000aP-VK; Wed, 27 Oct 2004 09:50:51 +1300 Received: from lamb.mcmillan.net.nz (lamb.mcmillan.net.nz [127.0.0.1]) by lamb.mcmillan.net.nz (Postfix) with ESMTP id 97FB7AC21B39; Wed, 27 Oct 2004 09:50:50 +1300 (NZDT) Subject: Re: can't handle large number of INSERT/UPDATEs From: Andrew McMillan To: Anjan Dave Cc: pgsql-performance@postgresql.org In-Reply-To: <4BAFBB6B9CC46F41B2AD7D9F4BBAF7850985D5@vt-pe2550-001.vantage.vantage.com> References: <4BAFBB6B9CC46F41B2AD7D9F4BBAF7850985D5@vt-pe2550-001.vantage.vantage.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-tIJWZPEUWnLHsT0/hc/i" Date: Wed, 27 Oct 2004 09:50:50 +1300 Message-Id: <1098823850.6440.89.camel@lamb.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/438 X-Sequence-Number: 8914 --=-tIJWZPEUWnLHsT0/hc/i Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Mon, 2004-10-25 at 16:53 -0400, Anjan Dave wrote: > Hi, >=20 >=20=20 >=20 > I am dealing with an app here that uses pg to handle a few thousand > concurrent web users. It seems that under heavy load, the INSERT and > UPDATE statements to one or two specific tables keep queuing up, to > the count of 150+ (one table has about 432K rows, other has about > 2.6Million rows), resulting in =E2=80=98wait=E2=80=99s for other queries,= and then > everything piles up, with the load average shooting up to 10+.=20 Hi, We saw a similar problem here that was related to the locking that can happen against referred tables for referential integrity. In our case we had referred tables with very few rows (i.e. < 10) which caused the insert and update on the large tables to be effectively serialised due to the high contention on the referred tables. We changed our app to implement those referential integrity checks differently and performance was hugely boosted. Regards, Andrew. ------------------------------------------------------------------------- Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 Chicken Little was right. ------------------------------------------------------------------------- --=-tIJWZPEUWnLHsT0/hc/i Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQBBfripjJA0f48GgBIRAqKAAJ0esyvcdqOaBIK+gYL+D7Ql6MOUDwCgvOgO 4Fuwb8gBnPHOHrLZGBFJlMU= =8UWV -----END PGP SIGNATURE----- --=-tIJWZPEUWnLHsT0/hc/i--