Re: Full statement logging problematic on larger machines?

On Fri, Mar 20, 2009 at 8:47 PM, Frank Joerdens <frank(at)joerdens(dot)de> wrote:
> On Thu, Mar 12, 2009 at 1:38 PM, Frank Joerdens <frank(at)joerdens(dot)de> wrote:
>> On Thu, Mar 12, 2009 at 1:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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