Обсуждение: 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

Shahaf Abileah <shahaf 'at' redfin.com> writes:

> 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

in pg is a good idea, as already suggested, but if many other
unrelated clients also access pg and thus it's difficult to
isolate the queries you're interested in, why not
hibernate.show_sql + appropriate logging on your direct SQL
layer?

--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36

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

Using hibernate.show_sql sounds like a reasonable idea.  I've used this
option to display the SQL in standard out before.  Is there a way to
pipe that output into a different logger (e.g. an in-memory logger that
is specific to the current request)?  Also, I've noticed that the
hibernate.show_sql option doesn't display the parameter *values*;
instead, it has a bunch of '?' to represent the real values (which, I
presume, are substituted somewhere along the way).  Is there a way to
configure hibernate logging to log the statement with real values
instead of placeholders?

Thanks!

--S


-----Original Message-----
From: Guillaume Cottenceau [mailto:gc@mnc.ch]
Sent: Thursday, August 21, 2008 2:29 AM
To: Shahaf Abileah
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Tracing SQL statements per page/request

Shahaf Abileah <shahaf 'at' redfin.com> writes:

> 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

in pg is a good idea, as already suggested, but if many other
unrelated clients also access pg and thus it's difficult to
isolate the queries you're interested in, why not
hibernate.show_sql + appropriate logging on your direct SQL
layer?

--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent
Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36

Hi,

better use log4j instead of hibernate.show_sql so you can log to a
different logger (fi file).
activate debug for category org.hibernate.SQL to show sql statements and
org.hibernate.type for binded parameters.

regards
Thomas


Shahaf Abileah schrieb:
> Using hibernate.show_sql sounds like a reasonable idea.  I've used this
> option to display the SQL in standard out before.  Is there a way to
> pipe that output into a different logger (e.g. an in-memory logger that
> is specific to the current request)?  Also, I've noticed that the
> hibernate.show_sql option doesn't display the parameter *values*;
> instead, it has a bunch of '?' to represent the real values (which, I
> presume, are substituted somewhere along the way).  Is there a way to
> configure hibernate logging to log the statement with real values
> instead of placeholders?
>
> Thanks!
>
> --S
>
>
> -----Original Message-----
> From: Guillaume Cottenceau [mailto:gc@mnc.ch]
> Sent: Thursday, August 21, 2008 2:29 AM
> To: Shahaf Abileah
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Tracing SQL statements per page/request
>
> Shahaf Abileah <shahaf 'at' redfin.com> writes:
>
>
>> 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
>
> in pg is a good idea, as already suggested, but if many other
> unrelated clients also access pg and thus it's difficult to
> isolate the queries you're interested in, why not
> hibernate.show_sql + appropriate logging on your direct SQL
> layer?
>
>