Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 253DC64FFBC for ; Wed, 3 Dec 2008 16:29:00 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 79428-03 for ; Wed, 3 Dec 2008 16:28:56 -0400 (AST) X-Greylist: delayed 00:25:03.224858 by SQLgrey-1.7.6 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.189]) by mail.postgresql.org (Postfix) with ESMTP id 24A4264FEBD for ; Wed, 3 Dec 2008 16:28:55 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id c7so1891995nfi.23 for ; Wed, 03 Dec 2008 12:28:53 -0800 (PST) Received: by 10.210.142.10 with SMTP id p10mr10490749ebd.95.1228334628004; Wed, 03 Dec 2008 12:03:48 -0800 (PST) Received: by 10.210.117.5 with HTTP; Wed, 3 Dec 2008 12:03:47 -0800 (PST) Message-ID: Date: Wed, 3 Dec 2008 23:03:47 +0300 From: "Vladimir Rusinov" To: pgsql-admin@postgresql.org Subject: slow slow query log MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_10019_2286723.1228334627978" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200812/22 X-Sequence-Number: 30855 ------=_Part_10019_2286723.1228334627978 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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/ ------=_Part_10019_2286723.1228334627978 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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/
------=_Part_10019_2286723.1228334627978--