slow slow query log

From: "Vladimir Rusinov" <vladimir(at)greenmice(dot)info>
To: pgsql-admin(at)postgresql(dot)org
Subject: slow slow query log
Date: 2008-12-03 20:03:47
Message-ID: f6fdfb550812031203q3c750bb1g36899ab2d26ecae2@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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/

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message AmitKumar Jain 2008-12-03 22:34:52 autovacuum benchmarking ...
Previous Message Marc Fromm 2008-12-03 19:21:58 restore a table in a database