Re: pg 8.1.2 performance issue

On Saturday March 25 2006 9:36 pm, Ed L. wrote:
> I have a performance riddle, hoping someone can point me in a
> helpful direction.  We have a pg 8.1.2 cluster using
> Apache::Sessions and experiencing simple UPDATEs taking
> sometimes 30+ seconds to do a very simply update, no foreign
> keys, no triggers:
>
>         Table "public.sessions"
>   Column   |     Type      | Modifiers
> -----------+---------------+-----------
>  id        | character(32) | not null
>  a_session | text          |
> Indexes:
>     "sessions_pkey" PRIMARY KEY, btree (id)

The table has 6800 rows over 18000 pages, and is getting a
minimum of many tens of thousands of updates per day with
queries like this:

    UPDATE sessions SET a_session = ? WHERE id = ?

Ed

>
>
> This is on an HP ia64 11.23 box with what appears to be gobs
> of surplus CPU, I/O (it's on a SAN), and RAM, pretty high
> query volume from 180 concurrent client connections.
>
> Wondering if it is a locking issue, I set up logging to
> capture existing locks every 10s with this query:
>
> SELECT now(), dbu.usename as locker, l.mode as locktype, CASE
> l.granted WHEN true THEN 'granted' ELSE 'pending' END as
> status, pg_stat_get_backend_pid(S.backendid)
> as pid, l.transaction as xid,
> db.datname||'.'||n.nspname||'.'|| r.relname as relation, case
> l.mode when 'AccessShareLock' then 1 when 'RowShareLock' then
> 2 when 'Row
> ExclusiveLock' then 3 when 'ShareUpdateExclusiveLock' then 4
> when 'ShareLock' then 5 when 'ShareRowExclusiveLock' then 6
> when 'ExclusiveLock' then 7 else 100 end as
>  exclusivity, pg_stat_get_backend_activity(S.backendid) as
> query FROM pg_user dbu,
>      (SELECT pg_stat_get_backend_idset() AS backendid) AS S,
>      pg_database db, pg_locks l, pg_class r, pg_namespace n
> WHERE db.oid = pg_stat_get_backend_dbid(S.backendid)
>   AND dbu.usesysid = pg_stat_get_backend_userid(S.backendid)
>   AND l.pid = pg_stat_get_backend_pid(S.backendid)
>   AND l.relation = r.oid
>   AND l.database = db.oid
>   AND r.relnamespace = n.oid
> ORDER BY exclusivity DESC, db.datname, n.nspname, r.relname,
> l.mode;"
>
>
> I see what appear to be many single transactions holding
> RowExclusiveLocks for sometimes 40-50 seconds while their
> query shows "<IDLE> in transaction".
>
>  2006-03-25 20:04:01.063873-08 | www | RowExclusiveLock |
> granted
>
> | 17192 | 270205914 | db1.public.sessions |           3 |
> | <IDLE>
>
> in transaction
>  2006-03-25 20:04:11.128632-08 | www | RowExclusiveLock |
> granted
>
> | 17192 | 270205914 | db1.public.sessions          |
> | 3 <IDLE> in transaction
>
>  2006-03-25 20:04:21.215896-08 | www | RowExclusiveLock |
> granted
>
> | 17192 | 270205914 | db1.public.sessions          |
> | 3 <IDLE> in transaction
>
> I'm thinking that means the client is simply tweaking a row
> and then failing to commit the change for 40-50 seconds.  Is
> that consistent?  Is there something else obvious here to
> explain the delays?  Does this sound like a browser stop
> button issue where they may be aborting the query that has the
> lock, and then issuing another that waits on the first?  Other
> suggestions?
>
> Thanks,
> Ed