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
changedis that it's RAID5 volume now, instead of a RAID10)
-anjan
-----Original Message-----
From: Rod Taylor [mailto:pg@rbt.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.