Re: can't handle large number of INSERT/UPDATEs

From: "Anjan Dave" <adave(at)vantage(dot)com>
To: "Rod Taylor" <pg(at)rbt(dot)ca>
Cc: "Postgresql Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: can't handle large number of INSERT/UPDATEs
Date: 2004-10-26 17:42:21
Message-ID: 4BAFBB6B9CC46F41B2AD7D9F4BBAF7850985DC@vt-pe2550-001.vantage.vantage.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

It probably is locking issue. I got a long list of locks held when we ran select * from pg_locks during a peak time.

relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+------------------+---------
17239 | 17142 | | 3856 | AccessShareLock | t
| | 21196323 | 3875 | ExclusiveLock | t
16390 | 17142 | | 3911 | AccessShareLock | t
16595 | 17142 | | 3782 | AccessShareLock | t
17227 | 17142 | | 3840 | AccessShareLock | t
17227 | 17142 | | 3840 | RowExclusiveLock | t
...
...

Vmstat would show a lot of disk IO at the same time.

Is this pointing towards a disk IO issue? (to that end, other than a higher CPU speed, and disabling HT, only thing changed is that it's RAID5 volume now, instead of a RAID10)

-anjan

-----Original Message-----
From: Rod Taylor [mailto:pg(at)rbt(dot)ca]
Sent: Monday, October 25, 2004 5:19 PM
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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Rod Taylor 2004-10-26 17:48:42 Re: can't handle large number of INSERT/UPDATEs
Previous Message Tom Lane 2004-10-26 16:39:44 Re: Large Database Performance suggestions