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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])