Re: [PERFORM] investigating slow queries through pg_stat_activity
* Dan Harris [EMAIL PROTECTED] wrote: Hi, I've got some queries generated by my application that will, for some reason, run forever until I kill the pid. Yet, when I run the queries manually to check them out, they usually work fine. If you can change your application, you could try to encapsulate the queries into views - this makes logging and tracking down problems much easier. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] investigating slow queries through pg_stat_activity
I've got some queries generated by my application that will, for some reason, run forever until I kill the pid. Yet, when I run the queries manually to check them out, they usually work fine. To get more information about these queries, I'm writing a utility to take snapshots of pg_stat_activity every 5 minutes. If it finds a query that runs for longer than 15 minutes, it will trap the query so I can run 'explain analyze' on it and see where the weakness is. However, the problem I have is that pg_stat_activity only returns the first n (255?) characters of the SQL as current_query, so it gets chopped off at the end. I would very much like to find out how I can get the *entire* query that is active. Is this possible? Also, I'm sure some people will respond with turn on query logging.. I've explored that option and the formatting of the log file and the fact that EVERY query is logged is not what I'm after for this project. The infinite-running queries are unpredictable and may only happen once a week. Logging 24/7 in anticipation of one of these occurrences is not something I'd like to do. Thanks, Dan Harris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] investigating slow queries through pg_stat_activity
Dan Harris [EMAIL PROTECTED] writes: However, the problem I have is that pg_stat_activity only returns the first n (255?) characters of the SQL as current_query, so it gets chopped off at the end. I would very much like to find out how I can get the *entire* query that is active. Is this possible? I think the limit is ~1000 characters in 8.0 and later. However, you can't realistically have unlimited because of constraints of the stats messaging mechanism. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] investigating slow queries through pg_stat_activity
On 6/20/05, Dan Harris [EMAIL PROTECTED] wrote: Also, I'm sure some people will respond with turn on query logging.. I've explored that option and the formatting of the log file and the fact that EVERY query is logged is not what I'm after for this project. You don't have to log every query. You can set log_min_duration_statement in postgresql.conf to log only the queries that exceed a certain amount of time. From the manual at http://www.postgresql.org/docs/8.0/static/runtime-config.html: log_min_duration_statement (integer) Sets a minimum statement execution time (in milliseconds) that causes a statement to be logged. All SQL statements that run for the time specified or longer will be logged with their duration. Setting this to zero will print all queries and their durations. Minus-one (the default) disables the feature. For example, if you set it to 250 then all SQL statements that run 250ms or longer will be logged. Enabling this option can be useful in tracking down unoptimized queries in your applications. Only superusers can change this setting. George Essig ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
