From: Vladimir Rusinov <vladimir@greenmice.info>
To: pgsql-admin@postgresql.org
Subject: slow slow query log
Date: Wed, 3 Dec 2008 23:03:47 +0300
Message-ID: <f6fdfb550812031203q3c750bb1g36899ab2d26ecae2@mail.gmail.com> (raw)
Hi. [Sorry, English is not my native, so it might be hard to understand me]
We have following in postgresql.conf:
log_destination = 'syslog'
log_min_duration_statement = 2000
log_connections = off
log_disconnections = off
log_line_prefix = 'user=%u,db=%d '
log_statement = 'none'
And sometimes we have large updates like UPDATE table SET data=$1 WHERE
id=123, where $1 is big binary blob (up to 5 megabytes). (Yes, we know that
storing binary data is not good idea, this is our design error, which we are
trying to fix, but this is long process).
Of course this updates goes to slow query log.
And for example right now I can see two such updates (they are running 10+
minutes and using 100% on two cores of our cpu).
And the most interesting: I can see in postgresql log that they were already
finished (I can see it's duration) and now dumping full query text to log
file. Lines on log looks like
Dec 3 08:37:09 db1 postgres[17786]: [4-199722]
246\260hgW\037Q{I\014M\3367\246\326\253\324ZNq\202&.\372\374\275\310\025\026\227k\246Y\311\014\307:\356\206\343\371\015\273\307\
so, this is escaped binary data.
Is there any way to disable dumping query parameters to query log? It would
be ok for us, if this would be just UPDATE table SET data=$1 WHERE id=$2 on
log, we don't need full text on log files.
--
Vladimir Rusinov
http://greenmice.info/
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-admin@postgresql.org
Cc: vladimir@greenmice.info
Subject: Re: slow slow query log
In-Reply-To: <f6fdfb550812031203q3c750bb1g36899ab2d26ecae2@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