Tom Lane wrote:
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 ...
Understood. This legacy apps wraps every single transaction (even read
only ones) inside of BEGIN; END; blocks. We do about 90+ percent reads
to our database, and at 300+ queries a second that could quickly add up.
Does this sound like we should investigate this area more?
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.
Interesting. One other thing to note, this application in particular
accounts for only 4 percent of total queries and if we disable the
application the database runs like a champ. The only other huge
variable I can think of is this app's gratuitous use of cursors.
I haven't read too much about Postgres performance especially when
dealing with cursors, but could this be a variable? We are considering
modifying the app and removing all use of cursors and wonder if we're
wasting our time or not.
Thanks for the help.
--
Sumbry][
