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 BF00D3A3C24 for ; Tue, 26 Oct 2004 09:18:30 +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 52214-10 for ; Tue, 26 Oct 2004 08:18:14 +0000 (GMT) Received: from mra02.ex.eclipse.net.uk (mra02.ex.eclipse.net.uk [212.104.129.89]) by svr1.postgresql.org (Postfix) with ESMTP id 042813A3B28 for ; Tue, 26 Oct 2004 09:18:13 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mra02.ex.eclipse.net.uk (Postfix) with ESMTP id 740E840639F; Tue, 26 Oct 2004 09:12:03 +0100 (BST) Received: from mra02.ex.eclipse.net.uk ([127.0.0.1]) by localhost (mra02.ex.eclipse.net.uk [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 11507-01-42; Tue, 26 Oct 2004 09:12:01 +0100 (BST) Received: from dev1 (unknown [82.152.145.238]) by mra02.ex.eclipse.net.uk (Postfix) with ESMTP id 6C5494070A9; Tue, 26 Oct 2004 09:11:55 +0100 (BST) From: "Rod Dutton" To: "'Anjan Dave'" Cc: Subject: FW: can't handle large number of INSERT/UPDATEs Date: Tue, 26 Oct 2004 09:19:33 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Thread-Index: AcS62HV8K9yQuVetTcSgq4oyu4L6aAAWftkw Message-Id: <20041026081155.6C5494070A9@mra02.ex.eclipse.net.uk> X-Virus-Scanned: by Eclipse VIRUSshield at eclipse.net.uk 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/427 X-Sequence-Number: 8903 >>Eliminate that contention point, and you will have solved your problem. I agree, If your updates are slow then you will get a queue building up. Make sure that:- 1) all your indexing is optimised. 2) you are doing regular vacuuming (bloated tables will cause a slow down due to swapping). 3) your max_fsm_pages setting is large enough - it needs to be big enough to hold all the transactions between vacuums (+ some spare for good measure). 4) do a full vacuum - do one to start and then do one after you have had 2&3 (above) in place for a while - if the full vacuum handles lots of dead tuples then your max_fsm_pages setting is too low. 5) Also try reindexing or drop/recreate the indexes in question as... "PostgreSQL is unable to reuse B-tree index pages in certain cases. The problem is that if indexed rows are deleted, those index pages can only be reused by rows with similar values. For example, if indexed rows are deleted and newly inserted/updated rows have much higher values, the new rows can't use the index space made available by the deleted rows. Instead, such new rows must be placed on new index pages. In such cases, disk space used by the index will grow indefinitely, even if VACUUM is run frequently. " Are your updates directly executed or do you use stored procs? We had a recent problem with stored procs as they store a "one size fits all" query plan when compiled - this can be less than optimum in some cases. We have a similar sounding app to yours and if tackled correctly then all the above will make a massive difference in performance. Rod -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Rod Taylor Sent: 25 October 2004 22:19 To: Anjan Dave Cc: Postgresql Performance Subject: Re: [PERFORM] can't handle large number of INSERT/UPDATEs On Mon, 2004-10-25 at 16:53, Anjan Dave wrote: > Hi, > > > > 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 ?wait?s for other queries, and then This isn't an index issue, it's a locking issue. Sounds like you have a bunch of inserts and updates hitting the same rows over and over again. Eliminate that contention point, and you will have solved your problem. Free free to describe the processes involved, and we can help you do that. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly