Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group