From: Donald C. Sumbry ][ <sumbry@revver.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: High Context-Switches on Linux 8.1.4 Server
Date: Mon, 07 Aug 2006 12:27:42 -0700
Message-ID: <44D7942E.7050406@revver.com> (raw)
In-Reply-To: <3795.1154975681@sss.pgh.pa.us>
References: <44D6EDFA.4030802@revver.com>
<19629.1154955137@sss.pgh.pa.us>
<44D7616D.6080906@revver.com>
<3795.1154975681@sss.pgh.pa.us>
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][
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sumbry@revver.com, tgl@sss.pgh.pa.us
Subject: Re: High Context-Switches on Linux 8.1.4 Server
In-Reply-To: <44D7942E.7050406@revver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox