Thread: slow slow query log
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/
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/
On Mittwoch 03 Dezember 2008 Vladimir Rusinov wrote: > 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. I'd like to have that info too. If you run a bayes database from spamassassin, a single log entry can take 50+ lines. A restriction to the number of lines, or length, would be very nice. mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: www.keyserver.net Key-ID: 1C1209B4