Re: Full statement logging problematic on larger machines?

On Wed, Mar 11, 2009 at 8:27 PM, Frank Joerdens <frank@joerdens.de> wrote:
> This works much better but once we are at about 80% of peak load -
> which is around 8000 transactions per second currently - the server goes
> into a tailspin in the manner described above and we have to switch off full
> logging.

First, don't use log_duration = on + log_statement = 'all' to log all
the queries, use log_min_duration_statement=0, it's less verbose.

I don't know if the logging integrated into PostgreSQL can bufferize
its output. Andrew? If not, you should try syslog instead and see if
asynchronous logging with syslog is helping (you need to prefix the
path with a dash to enable asynchronous logging). You can also try to
send the logs on the network via udp (and also tcp if you have an
enhanced syslog-like).

Another option is to log the duration of every query but not the text.
We used to have this sort of configuration to gather comprehensive
statistics and slowest queries on highly loaded servers (it's not
perfect though but it can be an acceptable compromise):
log_duration = on
log_min_duration_statement = 100

--
Guillaume