Hi,

We recently put up a new 7.2.1 installation on Solaris 8 that serves a
24x7 e-commerce site. The system seems to run pretty well most of the
time but we see a consistent form of performance anomaly.

Watching pg_stat_activity the system spends most of it's time running
smoothly with queries clearing through sub-second. We have a production
job we run which immediately sent the site into a tailspin though.
Starting that job caused hundreds of select statements to queue up in
the pg_stat_activity view. This seems odd since MVCC would lead us to
believe that shouldn't happen. Readers shouldn't block wholesale like
that unless we're using DDL on the table or doing a vacuum per the
online docs at
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/locking-tables.html

Nevertheless, turning off foreign key constraint checking via:

update "pg_class" set "reltriggers" = 0 where "relname" = tablename

cleared up the problem, load fell off to normal in seconds. So how is it
that fk contraints apparently cause what look like table-level locks?
Or, at the very least, cause a heck of a lot of select statements to go
into a holding pattern for some reason?

(On a side note, it seems the current locking behavior might also
violate the I in ACID by causing an independent delete transaction to
actually "see" or be affected by the content of the transaction using FK
constraint locking before it's clear that the transaction will commit).

At any rate, being somewhat new to tuning at this load level for PG I'm
not sure if I'm supposed to be tinkering with max_lock_per_transaction
here. Could this be evidence of a lock starvation issue or something?
Guessing here and any input would be appreciated. Thanks in advance!


ss

Search Discussions

  • Alex Hayward at Aug 10, 2002 at 8:46 pm

    On 9 Aug 2002, Scott Shattuck wrote:

    Hi,

    We recently put up a new 7.2.1 installation on Solaris 8 that serves a
    24x7 e-commerce site. The system seems to run pretty well most of the
    time but we see a consistent form of performance anomaly.

    Watching pg_stat_activity the system spends most of it's time running
    smoothly with queries clearing through sub-second. We have a production
    job we run which immediately sent the site into a tailspin though.
    Starting that job caused hundreds of select statements to queue up in
    the pg_stat_activity view. This seems odd since MVCC would lead us to
    believe that shouldn't happen. Readers shouldn't block wholesale like
    that unless we're using DDL on the table or doing a vacuum per the
    online docs at
    http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/locking-tables.html

    Nevertheless, turning off foreign key constraint checking via:

    update "pg_class" set "reltriggers" = 0 where "relname" = tablename

    cleared up the problem, load fell off to normal in seconds. So how is it
    that fk contraints apparently cause what look like table-level locks?
    Or, at the very least, cause a heck of a lot of select statements to go
    into a holding pattern for some reason?
    If you insert/update a row which contains foreign keys then PostgreSQL
    will do a SELECT ... FOR UPDATE on the appropriate row in the primary key
    table. This will block any SELECT ... FOR UPDATE, UPDATE or DELETE
    statements affecting that row - including other foreign key checks. A
    commonly referenced primary key value can become the subject of quite a
    lot of lock contention; not to mention deadlocks.

    PostgreSQL will do this for EVERY field in the row which is being modified
    which has a foreign key constraint (unless that field is NULL). It will
    perform this check even if that field is not being changed.
    At any rate, being somewhat new to tuning at this load level for PG I'm
    not sure if I'm supposed to be tinkering with max_lock_per_transaction
    here. Could this be evidence of a lock starvation issue or something?
    Guessing here and any input would be appreciated. Thanks in advance!
    You might be best off just turning off the foreign key checks on your
    production server (or, at least, some of them) until someone gets round to
    coming up with some sort of fix for PostgreSQL.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 10, '02 at 12:04a
activeAug 10, '02 at 8:46p
posts2
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Scott Shattuck: 1 post Alex Hayward: 1 post

People

Translate

site design / logo © 2022 Grokbase