DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:sender:in-reply-to:references:date
	:x-google-sender-auth:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	b=kHKhhu6vRL8VmOnH6KdK2Mw6PZApreUyk4VFnFmtB49B8FTZOGoE9L+3+OyEU/NV6k
	qyIUyt6R0mXHEjJ/s57wafnsddEUJzP1YGfapbzB2qByGDmuVJtf79U6LyvOCe3es3uv
	ijAvu66u/nG6hcgIWba4JcgeadJVPENy8dAko=
MIME-Version: 1.0
In-Reply-To: <7d10d2df0903120638s3908aadbm5f285220fc738843@mail.gmail.com>
References: <7d10d2df0903111227g2e40e742g5992cba5158dd4d2@mail.gmail.com>
	<1d4e0c10903111659x6c96e6cau33ee121ee29a48a@mail.gmail.com>
	<29255.1236822351@sss.pgh.pa.us>
	<7d10d2df0903120638s3908aadbm5f285220fc738843@mail.gmail.com>
Date: Fri, 20 Mar 2009 19:47:29 +0000
Message-ID: <7d10d2df0903201247r7ff71d1bsf655176a86ca5be4@mail.gmail.com>
Subject: Re: Full statement logging problematic on larger machines?
From: Frank Joerdens <frank@joerdens.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Guillaume Smet <guillaume.smet@gmail.com>,
 pgsql-performance@postgresql.org,
	Nic Ferrier <nic@woome.com>, Avleen Vig <avleen@woome.com>,
	Mike Rogers <mike.rogers@woome.com>, Andrew Dunstan <andrew@dunslane.net>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Thu, Mar 12, 2009 at 1:38 PM, Frank Joerdens <frank@joerdens.de> wrote:
> On Thu, Mar 12, 2009 at 1:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> [...]
>> You could try changing _IOLBF
>> to _IOFBF near the head of postmaster/syslogger.c and see if that helps.

The patched server is now running on live, and we'll be watching it
over the weekend with

log_duration = off
log_min_duration_statement = 1000
log_statement = 'ddl'

and then run a full logging test early next week if there are no
problems with the above settings.

Can you explain again what the extent of multiplexed messages I'll
have to expect is? What exactly is the substance of the tradeoff?
Occasionally the server will write the same line twice? Don't really
follow why ...

And the next problem is that now unfortunately the entire comparison
is obfuscated and complicated by a release we did on Monday which has
had a strange effect: Quite extreme load average spikes occurring
frequently that do not seem to impact query speed - not much anyway or
if they do then in a random intermittent manner that's not become
apparent (yet) - CPU usage is actually marginally down, locks
significantly down, and all other relevant metrics basically unchanged
like context switches and memory usage profile. Now, it *seems* that
the extra load is caused by idle (sic!) backends (*not* idle in
transaction even) consuming significant CPU when you look at htop. I
don't have a theory as to that right now. We use pgbouncer as a
connection pooler. What could make idle backends load the server
substantially?

Regards,

Frank