Thread: Which query analiser tools are available?

Hi,

I am struggling with the performance of a JBoss based J2EE application
with CMP 2.1. beans and using PostgreSQL as database back-end.

Because JBoss is largely responsible for the SQL queries that are send
to the back-end , I would like to see the queries that are actually
received by PostgreSQL (insert, select, update and delete), together
with the number of times they are called, the average execution time,
total execution time etc.

I have tried PQA (http://pgfoundry.org/projects/pqa/) but that does not
seem to work with PostgreSQL 8.1.5 on Debian Etch: I get output saying
"Continuation for no previous query" and no statistics. As I don't know
anything about Ruby, I am lost here.

Can I "repair" PQA somehow (without resorting to a crash course "Ruby
for *extreme* Ruby dummies") or are there any other, preferably "Open
Source" (or extremely cheap ;-)) and multi-platform (Linux and Windows
2000 +), tools that can gather the statistics that I want?

One last question: can I get the select queries in PostgreSQL *without*
all the internal PostgreSQL selects that appear in the log files if I
set log_statement to "ddl" or "all" or should I try to catch these by a
judiciously chosen log_min_duration_statement ?

TIA

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl

Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> schrieb:
> Because JBoss is largely responsible for the SQL queries that are send
> to the back-end , I would like to see the queries that are actually
> received by PostgreSQL (insert, select, update and delete), together
> with the number of times they are called, the average execution time,
> total execution time etc.

You can use pgfouine for such job:
http://pgfouine.projects.postgresql.org/

And you can play with config-parameters like:

- log_statement = [none, mod, ddl, all]
- log_min_duration_statement = X (logs all statements runs longer then X
  ms)
- stats_command_string = on


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°