Re: Tracing SQL statements per page/request

We do have statement logging enabled, though so far we keep the limit at
200 ms.  The 200 settings has been used to check for obviously bad
performance in a single query, as opposed to this new goal of getting a
general feel for the "weight" of an URL request (in terms of # of
queries).

We could change the log setting, but there would still be some issues...

First off a single web server handles many page requests simultaneously.
So, all these queries would be jumbled together in the log file, all
using the same DB and coming from the same source IP address.  Would
there be a way to tell them apart?  Or is this approach only good if you
make sure to only do a single page request at a time?

Also, it would be nice to be able to fetch the generated queries and
actually display them at the bottom of the web page (when running in
debug mode).  It may be possible to achieve this by having the web app
grep the postgresql log file, but that's kind of a pain.  It would be
much nicer if the web app had some hook from the postgres JDBC driver
(which it's already using) to get at this info.

Finally, with respect to the 200 ms threshold, if we dropped it to 0 ms,
our log file would be huge.  So, it would be nice if we could collect
the SQL statements only when requested (e.g. only when the web page is
viewed in debug mode), and without having to muck with postgres.

Maybe I'm asking for too much, but let me know if any of this is
possible.

Thanks!

--S


-----Original Message-----
From: John R Pierce [mailto:pierce@hogranch.com]
Sent: Wednesday, August 20, 2008 2:48 PM
To: Shahaf Abileah
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Tracing SQL statements per page/request

Shahaf Abileah wrote:
>
> I'm looking for a general-purpose way to track and report the number
> of SQL statements issued to Postgres during the processing of a single

> page (a single URL0 on my company's website (www.redfin.com
> <http://www.redfin.com/>).
>
> Does the Postgres JDBC driver have any features to help me do this?
>
> If not, can someone recommend another approach? In case it helps, we
> use hibernate as our O-R mapping so hibernate is responsible for
> generating most (not all) of our SQL queries (some are still issued by

> direct SQL). Also, we use the PostGIS JDBC driver, which is a wrapper
> around the standard PostgreSQL JDBC driver.
>

enable statement logging on the postgres server, then filter (grep) the
logfile to just list the connection and database you are interested in
and pipe that to wc -l