>>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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster