Обсуждение: Logging SQL queries?

Hello all:

I want to do some profiling of a large application I run under postgres.
Ideally I'd like to capture just the queries that are submitted to the
database. It would be rather annoying to have to log them from the
application layer, so I'm hoping there's some suitable log-level setting on
the back end that can do this. Something that could log only the queries to
a different specific file would be excellent, but any flavor of this would
be good.

Thanks,

steve

SET DEBUG_PRINT_QUERY TO 1
it might be
SET DEBUG_PRINT_QUERY TO ON

Had to do this a couple weeks back to debug something.

That should work it'll print your queries to the standard log file but the
experts may have a way to get it into another file for you.

Good Luck
Phil
----- Original Message -----
From: "Steve Lane" <slane@fmpro.com>
To: "PostgreSQL General Mailing List" <pgsql-general@postgresql.org>
Sent: Tuesday, July 16, 2002 11:40 PM
Subject: [GENERAL] Logging SQL queries?


> Hello all:
>
> I want to do some profiling of a large application I run under postgres.
> Ideally I'd like to capture just the queries that are submitted to the
> database. It would be rather annoying to have to log them from the
> application layer, so I'm hoping there's some suitable log-level setting
on
> the back end that can do this. Something that could log only the queries
to
> a different specific file would be excellent, but any flavor of this would
> be good.
>
> Thanks,
>
> steve
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

You can use the -d option with your start script

On Wed, 17 Jul 2002, Phil Geer wrote:

> SET DEBUG_PRINT_QUERY TO 1
> it might be
> SET DEBUG_PRINT_QUERY TO ON
>
> Had to do this a couple weeks back to debug something.
>
> That should work it'll print your queries to the standard log file but the
> experts may have a way to get it into another file for you.
>
> Good Luck
> Phil
> ----- Original Message -----
> From: "Steve Lane" <slane@fmpro.com>
> To: "PostgreSQL General Mailing List" <pgsql-general@postgresql.org>
> Sent: Tuesday, July 16, 2002 11:40 PM
> Subject: [GENERAL] Logging SQL queries?
>
>
> > Hello all:
> >
> > I want to do some profiling of a large application I run under postgres.
> > Ideally I'd like to capture just the queries that are submitted to the
> > database. It would be rather annoying to have to log them from the
> > application layer, so I'm hoping there's some suitable log-level setting
> on
> > the back end that can do this. Something that could log only the queries
> to
> > a different specific file would be excellent, but any flavor of this would
> > be good.
> >
> > Thanks,
> >
> > steve
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

On Tue, Jul 16, 2002 at 10:40:14PM -0500,
 Steve Lane <slane@fmpro.com> wrote
 a message of 17 lines which said:

> Ideally I'd like to capture just the queries that are submitted to the
> database. It would be rather annoying to have to log them from the
> application layer, so I'm hoping there's some suitable log-level setting on
> the back end that can do this.

A related question: does anyone has a function to call syslog()? I
would like to set up triggers which logs with the syslog system, but
only some events (so debug_print_query is not for me).

I cannot write this function in Python (because it exists only as a
safe language), I can do it in Perl but Perl in PostgreSQL does not
have global variables so I have to openlog() each time (I'm afraid,
although I didn't benchmark, that it may slow down the
system.). Before I study C or Tcl, does anyone has a working solution?

[I noticed contrib/pg_logger but it lacks documentation :-(]

On Thu, Jul 18, 2002 at 12:16:01PM +0200, Stephane Bortzmeyer wrote:
> A related question: does anyone has a function to call syslog()? I
> would like to set up triggers which logs with the syslog system, but
> only some events (so debug_print_query is not for me).

Actually, this brings up a point. I was tracking down why the performance on
one of our servers running 7.2 was dismal. I eventually tracked to the
syslog() generated by postgres.

The thing is, when syslog logs a message it called sync() on the file
(unless explicitly disabled by prefixing the filename with a hyphen). So
with query logging it was calling sync() around 3 times per query, which not
surprisingly kills performance.

Has anyone else come across this or is it just me?
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Hi,

[...]
> Has anyone else come across this or is it just me?

It's not only you. I wanted to know, why syslogd ate 70% of my CPU time
and saw that it just logs enormous amounts of data to /var/log/postgres.
So the first thing was to put a hyphen in front of the logfile's name in
/etc/syslogd.conf and the second best thing was to set the debug level of
postgres to a more sane value.

After doing this and restarting both syslogd and postgres, the performance
was five to six times better than before. So, maybe we can have a note
about that in the postgres manual?


Kind regards
... Ralph ...

Stephane Bortzmeyer wrote:
...
> I cannot write this function in Python (because it exists only as a
> safe language), I can do it in Perl but Perl in PostgreSQL does not
> have global variables so I have to openlog() each time (I'm afraid,
> although I didn't benchmark, that it may slow down the
> system.). Before I study C or Tcl, does anyone has a working solution?

You can easily do it Perl, (I do :-) use the Unix::Syslog module.  Just
call openlog() once at the beginning and off you go, able to call
syslog() anywhere you need to.

Ignoring the argument about whether global vars are good or bad, global
vars in Perl are merely fully [package] qualified named vars.
Therefore, you can do:

$main::mylog = "some value";

and if you always reference it that way, e.g.
    print "Log file is $main::mylog\n"
you can use it anywhere in your program; that is in any file and in any
function (truly global).

HTH,
Kevin

On Thu, 2002-07-18 at 08:07, Martijn van Oosterhout wrote:
>
> Actually, this brings up a point. I was tracking down why the performance on
> one of our servers running 7.2 was dismal. I eventually tracked to the
> syslog() generated by postgres.
>

I've just set up a new 7.2.1 installation (compiled from source) and
noticed that i'm getting debug messages to syslog.  i have debug level
set to 0 and the syslog entries are still commented out in
postgresql.conf

I don't know why this is happening.  Any advice on how to shut off the
debugging?

Thanks

--

Tom Jenkins
Development InfoStructure
http://www.devis.com

On Thu, 2002-07-18 at 17:17, Tom Jenkins wrote:
> On Thu, 2002-07-18 at 08:07, Martijn van Oosterhout wrote:
> >
> > Actually, this brings up a point. I was tracking down why the performance on
> > one of our servers running 7.2 was dismal. I eventually tracked to the
> > syslog() generated by postgres.
> >
>
> I've just set up a new 7.2.1 installation (compiled from source) and
> noticed that i'm getting debug messages to syslog.  i have debug level
> set to 0 and the syslog entries are still commented out in
> postgresql.conf
>
> I don't know why this is happening.  Any advice on how to shut off the
> debugging?
>

*sigh*... sorry folks, found the problem just after sending the
message.  turns out i was seeing the 6.5 messages (that i'm replacing
with 7.2.1)

--

Tom Jenkins
Development InfoStructure
http://www.devis.com

Hi,

just my two cents worth tho. I'd love having some kind of "internal log
facility", which means being able to do the follwing:

a) using log functions in every place where common SQL is allowed, which
counts for
    - selects, updates, inserts, deletes (in transaction blocks)
    - triggers
    - functions

b) setting a log level

c) setting which log levels (and greater) really get logged

For example:

    CREATE FUNCTION foobar RETURNS INTEGER AS '
    DECLARE
    ...
    BEGIN
     ...
     IF ...
       LOG('executing if-branch in foobar for value = ' || someval, 2);
     END IF;
     ...
    ...
    END;
    ' LANGUAGE 'plpgsql';

So within any application or psql session one can decide what gets logged
by using:

    SET LOGLEVEL TO 1;

which means logging all messages with a level of 1 or higher.

Just a proposal for future enhancement probably. Open for any comments,
requests... what do others think about that debugging possibility?


Kind regards
... Ralph ...

On Thu, Jul 18, 2002 at 02:42:32PM -0500,
 Kevin Brannen <kevinb@nurseamerica.net> wrote
 a message of 25 lines which said:

> You can easily do it Perl, (I do :-) use the Unix::Syslog module.

OK, that's what I do, except that I use Sys::Syslog (Unix::Syslog does
not seem to work at my place). Here is the code:

CREATE FUNCTION log_creation (TEXT) RETURNS BOOLEAN AS '
      use Sys::Syslog;
      openlog "PostgreSQL", "pid", "local7";
      syslog "info", "Created: %s", $_[0];
      closelog;
      return 1;
' LANGUAGE plperlu;
-- Installation on Debian needs:
-- apt-get install libpgperl
-- /usr/lib/postgresql/bin/createlang plperlu ${MYDATABASE}

CREATE FUNCTION do_log () RETURNS OPAQUE AS '
   BEGIN
       PERFORM log_creation(NEW.name);
       RETURN NEW;
   END;
' LANGUAGE PLPGSQL;

CREATE TRIGGER do_log
  AFTER INSERT ON Domains
  FOR EACH ROW
  EXECUTE PROCEDURE do_log();


> Ignoring the argument about whether global vars are good or bad, global
> vars in Perl are merely fully [package] qualified named vars.
> Therefore, you can do:
>
> $main::mylog = "some value";
>
> and if you always reference it that way, e.g.
>     print "Log file is $main::mylog\n"

Thanks but, anyway, openlog does not return anything, so global
variables would not help. I just call openlog each time, hoping it is
not too much overhead.