>> Is this from LWLock or spinlock contention?
> Over a 20 second interval, I've got about 85 select()s and 6,230
> semop()s. 2604 read()s vs 16 write()s.
OK, so mostly LWLocks then.
>> Do you have any long-running transactions,
> Not long-running. We do have a badly behaving legacy app that is
> leaving some backends "idle in transaction" They're gone pretty quickly
> so I can't kill them fast enough, but running a pg_stat_activity will
> always show at least a handful. Could this be contributing?
Sorry, I was unclear: it's the age of your oldest transaction that
counts (measured by how many xacts started since it), not how many
cycles it's consumed or not.
With the 8.1 code it's possible for performance to degrade pretty badly
once the age of your oldest transaction exceeds 16K transactions. You
were not specific enough about the behavior of this legacy app to let
me guess where you are on that scale ...
> Based on the number of semop's we're getting it does look like
> shared_memory may be getting thrased - any suggestions? We did try
> lowering shared_memory usage in half the previous day,
Unlikely to help --- if it is the pg_subtrans problem, the number of
buffers involved is set by a compile-time constant.
regards, tom lane