Message-ID: <44D7942E.7050406@revver.com>
Date: Mon, 07 Aug 2006 12:27:42 -0700
From: "Donald C. Sumbry ][" <sumbry@revver.com>
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: High Context-Switches on Linux 8.1.4 Server
References: <44D6EDFA.4030802@revver.com> <19629.1154955137@sss.pgh.pa.us>
	<44D7616D.6080906@revver.com> <3795.1154975681@sss.pgh.pa.us>
In-Reply-To: <3795.1154975681@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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