Обсуждение: 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 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.
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 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. > > 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:As depesz mentioned, there's a log_min_duration GUC, that allows you to
> 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 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.
>
> just enable logging of queries.
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
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? 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 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. > just enable logging of queries. > > Best regards, > > depesz >
What is a GUC and how do I use it? 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 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. >> 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
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 thatjust enable logging of queries.
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.
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
What is a GUC and how do I use it? 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 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. >> 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 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 > > 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 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. >>> 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/