From: Frank Joerdens <frank@joerdens.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Guillaume Smet <guillaume.smet@gmail.com>
Cc: pgsql-performance@postgresql.org, Nic Ferrier <nic@woome.com>
Cc: Avleen Vig <avleen@woome.com>
Cc: Mike Rogers <mike.rogers@woome.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
Subject: Re: Full statement logging problematic on larger machines?
Date: Sat, 23 May 2009 01:13:08 +0100
Message-ID: <7d10d2df0905221713l68dd20e0k31e23d76d7b01476@mail.gmail.com> (raw)
In-Reply-To: <7d10d2df0903201247r7ff71d1bsf655176a86ca5be4@mail.gmail.com>
References: <7d10d2df0903111227g2e40e742g5992cba5158dd4d2@mail.gmail.com>
<1d4e0c10903111659x6c96e6cau33ee121ee29a48a@mail.gmail.com>
<29255.1236822351@sss.pgh.pa.us>
<7d10d2df0903120638s3908aadbm5f285220fc738843@mail.gmail.com>
<7d10d2df0903201247r7ff71d1bsf655176a86ca5be4@mail.gmail.com>
On Fri, Mar 20, 2009 at 8:47 PM, Frank Joerdens <frank@joerdens.de> wrote:
> 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.
Reporting back on this eventually (hitherto, all our experimenting
appeared inconclusive): The patched 8.2 server did not appear to make
any difference, it still didn't work, performance was affected in the
same way as before.
However in the meantime we managed to go to 8.3 and now it does work *if*
synchronous_commit = off
And now I am wondering what that means exactly: Does it necessarily
follow that it's I/O contention on the disk subsystem because delayed
flushing to WAL - what asynchronous commit does - gives the server
just the window to insert the log line into the disk controller's
write cache, as the transaction commit's write and the log line write
would be otherwise simultaneous with synchronous commit? Does it
follow that if I put pg_xlog now on a separate spindle and/or
controller, it should work?
Somehow I think not, as the disk array isn't even near maxed out,
according to vmstat. Or is the disk cache just too small then?
Frank
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: frank@joerdens.de, tgl@sss.pgh.pa.us, guillaume.smet@gmail.com, nic@woome.com, avleen@woome.com, mike.rogers@woome.com, andrew@dunslane.net
Subject: Re: Full statement logging problematic on larger machines?
In-Reply-To: <7d10d2df0905221713l68dd20e0k31e23d76d7b01476@mail.gmail.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