Thread: Tracing SQL statements per page/request
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).
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.
Thanks,
--Shahaf
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? > >