monitoring sql queries
I am in need of a tool or method to see each/every SQL query that hits
the PostgreSQL database. By query I mean the query in SQL syntax with
all the parameters passed.
What I want to do is:
1) see the query
2) Determine how long the query takes to execute
3) Possibly log both of the above to a log file
Is there any tool internal to PostgreSQL that would allow me to do this?
I cannot monitor it from the code that is actually connecting & sending
the query.
any help or pointers would be greatly appreciated.
J.V.
On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote:
I am in need of a tool or method to see each/every SQL query that
hits the PostgreSQL database. By query I mean the query in SQL
syntax with all the parameters passed.What I want to do is:
1) see the query
2) Determine how long the query takes to execute
3) Possibly log both of the above to a log fileIs there any tool internal to PostgreSQL that would allow me to do this?
I cannot monitor it from the code that is actually connecting &
sending the query.any help or pointers would be greatly appreciated.
just enable logging of queries.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On 17 Listopad 2011, 17:32, hubert depesz lubaczewski wrote:
On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote:
I am in need of a tool or method to see each/every SQL query that
hits the PostgreSQL database. By query I mean the query in SQL
syntax with all the parameters passed.What I want to do is:
1) see the query
2) Determine how long the query takes to execute
3) Possibly log both of the above to a log fileIs there any tool internal to PostgreSQL that would allow me to do this?
I cannot monitor it from the code that is actually connecting &
sending the query.any help or pointers would be greatly appreciated.
just enable logging of queries.
As depesz mentioned, there's a log_min_duration GUC, that allows you to
log queries that exceed some time interval. If you want to log all
queries, you may set this to 0 but it may easily fill your log with
garbage.
There are two contrib modules that might help you - pg_stat_statements and
auto_explain. The former one is probably more interesting in this case.
Tomas
On Thu, Nov 17, 2011 at 11:46 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
On 17 Listopad 2011, 17:32, hubert depesz lubaczewski wrote:
On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote:
I am in need of a tool or method to see each/every SQL query that
hits the PostgreSQL database. By query I mean the query in SQL
syntax with all the parameters passed.What I want to do is:
1) see the query
2) Determine how long the query takes to execute
3) Possibly log both of the above to a log fileIs there any tool internal to PostgreSQL that would allow me to do this?
I cannot monitor it from the code that is actually connecting &
sending the query.any help or pointers would be greatly appreciated.
just enable logging of queries.
As depesz mentioned, there's a log_min_duration GUC, that allows you to
log queries that exceed some time interval. If you want to log all
queries, you may set this to 0 but it may easily fill your log with
garbage.
Just as a warning, on heavily-loaded systems, this logging can have a
significant impact to your performance. Not so much because it's logging,
but due to the fact that your log-files may start requiring more disk I/O
than the actual database. If you are going to do this under any serious
load, I would recommend separating 'pg_log' on to a separate [set of]
physical disk[s].
--Scott
Show quoted text
There are two contrib modules that might help you - pg_stat_statements and
auto_explain. The former one is probably more interesting in this case.Tomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
How is this accomplished?
Is it possible to log queries to a table with additional information?
1) num rows returned (if a select)
2) time to complete the query
3) other info?
How is enabling this actually done?
Show quoted text
On 11/17/2011 9:32 AM, hubert depesz lubaczewski wrote:
On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote:
I am in need of a tool or method to see each/every SQL query that
hits the PostgreSQL database. By query I mean the query in SQL
syntax with all the parameters passed.What I want to do is:
1) see the query
2) Determine how long the query takes to execute
3) Possibly log both of the above to a log fileIs there any tool internal to PostgreSQL that would allow me to do this?
I cannot monitor it from the code that is actually connecting&
sending the query.any help or pointers would be greatly appreciated.
just enable logging of queries.
Best regards,
depesz
What is a GUC and how do I use it?
Show quoted text
On 11/17/2011 9:46 AM, Tomas Vondra wrote:
On 17 Listopad 2011, 17:32, hubert depesz lubaczewski wrote:
On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote:
I am in need of a tool or method to see each/every SQL query that
hits the PostgreSQL database. By query I mean the query in SQL
syntax with all the parameters passed.What I want to do is:
1) see the query
2) Determine how long the query takes to execute
3) Possibly log both of the above to a log fileIs there any tool internal to PostgreSQL that would allow me to do this?
I cannot monitor it from the code that is actually connecting&
sending the query.any help or pointers would be greatly appreciated.
just enable logging of queries.
As depesz mentioned, there's a log_min_duration GUC, that allows you to
log queries that exceed some time interval. If you want to log all
queries, you may set this to 0 but it may easily fill your log with
garbage.There are two contrib modules that might help you - pg_stat_statements and
auto_explain. The former one is probably more interesting in this case.Tomas
On Thu, 17 Nov 2011 14:32:22 -0700
"J.V." <jvsrvcs@gmail.com> wrote:
How is this accomplished?
The best way that I know if is to use pgFouine.
The documentation for pgFouine should get you started.
HTH,
Bill
On Thu, Nov 17, 2011 at 4:32 PM, J.V. <jvsrvcs@gmail.com> wrote:
How is this accomplished?
Is it possible to log queries to a table with additional information?
1) num rows returned (if a select)
This isn't logged
2) time to complete the query
This is logged
3) other info?
Take a look at the log_line_prefix parameter
How is enabling this actually done?
You enable this by using a GUC (global unified config) variable in the
postgresql.conf file called log_min_duration_statement.
--Scott
Show quoted text
On 11/17/2011 9:32 AM, hubert depesz lubaczewski wrote:
On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote:
I am in need of a tool or method to see each/every SQL query that
hits the PostgreSQL database. By query I mean the query in SQL
syntax with all the parameters passed.What I want to do is:
1) see the query
2) Determine how long the query takes to execute
3) Possibly log both of the above to a log fileIs there any tool internal to PostgreSQL that would allow me to do this?
I cannot monitor it from the code that is actually connecting&
sending the query.any help or pointers would be greatly appreciated.
just enable logging of queries.
Best regards,
depesz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
On 17 Listopad 2011, 22:34, J.V. wrote:
What is a GUC and how do I use it?
It just means there's a config option log_min_duration_statement that you
can set in postgresql.conf. Set it e.g. to 100, reload the configuration
(e.g. by restarting the server or sending HUP signal to the process) and
all queries exceeding 100ms will be logged.
Tomas
Show quoted text
On 11/17/2011 9:46 AM, Tomas Vondra wrote:
On 17 Listopad 2011, 17:32, hubert depesz lubaczewski wrote:
On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote:
I am in need of a tool or method to see each/every SQL query that
hits the PostgreSQL database. By query I mean the query in SQL
syntax with all the parameters passed.What I want to do is:
1) see the query
2) Determine how long the query takes to execute
3) Possibly log both of the above to a log fileIs there any tool internal to PostgreSQL that would allow me to do
this?I cannot monitor it from the code that is actually connecting&
sending the query.any help or pointers would be greatly appreciated.
just enable logging of queries.
As depesz mentioned, there's a log_min_duration GUC, that allows you to
log queries that exceed some time interval. If you want to log all
queries, you may set this to 0 but it may easily fill your log with
garbage.There are two contrib modules that might help you - pg_stat_statements
and
auto_explain. The former one is probably more interesting in this case.Tomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Nov 17, 2011 at 02:32:22PM -0700, J.V. wrote:
How is this accomplished?
Is it possible to log queries to a table with additional information?
1) num rows returned (if a select)
2) time to complete the query
3) other info?How is enabling this actually done?
please check:
http://www.depesz.com/index.php/2011/05/06/understanding-postgresql-conf-log/
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/