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][