Re: High Context-Switches on Linux 8.1.4 Server

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