Thread: Queries running the longest?
Good day everyone.
Firstly I would like to thank everyone in advance for all the help over the last couple of weeks on this mailing list as it has helped me out a lot in my efforts to try and understand Postgresql better.
I am however once again here with some more questions which I hope someone can assist me with as I can not seem to find good documentation on this as yet...
As part of our daily health checks and efforts to assist our client to improve their performance on their postgresql databases, we need to look at the queries that takes the longest to run and then use explain or other methods to try and improve the queries, add indexes where required, etc...
However, I am trying to find out how I will be able to track / see these queries in order for me to be able to investigate these.
the client have a total of 100 databases and to manually monitor these will be too time consuming considering that we have many clients to look after each day.
Can anyone perhaps point me in the right direction here, even if it is in the form of documentation that will assist me in this.
Regards
Machiel
Firstly I would like to thank everyone in advance for all the help over the last couple of weeks on this mailing list as it has helped me out a lot in my efforts to try and understand Postgresql better.
I am however once again here with some more questions which I hope someone can assist me with as I can not seem to find good documentation on this as yet...
As part of our daily health checks and efforts to assist our client to improve their performance on their postgresql databases, we need to look at the queries that takes the longest to run and then use explain or other methods to try and improve the queries, add indexes where required, etc...
However, I am trying to find out how I will be able to track / see these queries in order for me to be able to investigate these.
the client have a total of 100 databases and to manually monitor these will be too time consuming considering that we have many clients to look after each day.
Can anyone perhaps point me in the right direction here, even if it is in the form of documentation that will assist me in this.
Regards
Machiel
On 19 August 2010 08:58, Machiel Richards <machielr@rdc.co.za> wrote: > Good day everyone. > > > Firstly I would like to thank everyone in advance for all the > help over the last couple of weeks on this mailing list as it has helped me > out a lot in my efforts to try and understand Postgresql better. > > I am however once again here with some more questions which I > hope someone can assist me with as I can not seem to find good documentation > on this as yet... > > > As part of our daily health checks and efforts to assist our > client to improve their performance on their postgresql databases, we need > to look at the queries that takes the longest to run and then use explain or > other methods to try and improve the queries, add indexes where required, > etc... > > > However, I am trying to find out how I will be able to track / > see these queries in order for me to be able to investigate these. > > the client have a total of 100 databases and to manually monitor > these will be too time consuming considering that we have many clients to > look after each day. > > > Can anyone perhaps point me in the right direction here, even if it is > in the form of documentation that will assist me in this. > > Hi Machiel, If you look in postgresql.conf, you'll see an option called log_min_duration_statement. If you set this to a minimum amount of time (in milliseconds) that the query must run before it's logged (of course you need logging enabled first), you can then analyze the log though something like pgFouine (http://pgfouine.projects.postgresql.org/) which will be able to rank the longest-running queries for you. Note that pgFouine requires that you configure your log format beforehand, so check the pgFouine documentation for how to do that. Alternatively, if you really don't wish to use pgFouine, just set your log_min_duration_statement setting to a value over which queries would become unacceptably long. Then just manually examine the logs. Regards -- Thom Brown Registered Linux user: #516935
You can decide a threshold response time for all queries which you want to investigate .
Lets say you want to investigate all queries which take more than 200ms then set log_min_duration to 200 in postgresql.conf and it will start logging all queries taking more time than ther threshold in the server log.
--Ashish
On 08/19/2010 01:28 PM, Machiel Richards wrote:
Lets say you want to investigate all queries which take more than 200ms then set log_min_duration to 200 in postgresql.conf and it will start logging all queries taking more time than ther threshold in the server log.
--Ashish
On 08/19/2010 01:28 PM, Machiel Richards wrote:
Good day everyone.
Firstly I would like to thank everyone in advance for all the help over the last couple of weeks on this mailing list as it has helped me out a lot in my efforts to try and understand Postgresql better.
I am however once again here with some more questions which I hope someone can assist me with as I can not seem to find good documentation on this as yet...
As part of our daily health checks and efforts to assist our client to improve their performance on their postgresql databases, we need to look at the queries that takes the longest to run and then use explain or other methods to try and improve the queries, add indexes where required, etc...
However, I am trying to find out how I will be able to track / see these queries in order for me to be able to investigate these.
the client have a total of 100 databases and to manually monitor these will be too time consuming considering that we have many clients to look after each day.
Can anyone perhaps point me in the right direction here, even if it is in the form of documentation that will assist me in this.
Regards
Machiel
-- With Regards Ashish Karalkar
Thom, thanks for your reply. Is there a way to get a plan for a running SQL statement? The Dark Database(TM) has the following tables, which make the life of a DBA much easier: SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 19 14:17:12 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> desc v$sql Name Null? Type ----------------------------------------- -------- ---------------------------- SQL_TEXT VARCHAR2(1000) SQL_FULLTEXT CLOB SQL_ID VARCHAR2(13) SHARABLE_MEM NUMBER PERSISTENT_MEM NUMBER RUNTIME_MEM NUMBER SORTS NUMBER LOADED_VERSIONS NUMBER OPEN_VERSIONS NUMBER USERS_OPENING NUMBER FETCHES NUMBER EXECUTIONS NUMBER PX_SERVERS_EXECUTIONS NUMBER END_OF_FETCH_COUNT NUMBER USERS_EXECUTING NUMBER LOADS NUMBER FIRST_LOAD_TIME VARCHAR2(19) INVALIDATIONS NUMBER PARSE_CALLS NUMBER DISK_READS NUMBER DIRECT_WRITES NUMBER BUFFER_GETS NUMBER APPLICATION_WAIT_TIME NUMBER CONCURRENCY_WAIT_TIME NUMBER CLUSTER_WAIT_TIME NUMBER USER_IO_WAIT_TIME NUMBER PLSQL_EXEC_TIME NUMBER JAVA_EXEC_TIME NUMBER ROWS_PROCESSED NUMBER COMMAND_TYPE NUMBER OPTIMIZER_MODE VARCHAR2(10) OPTIMIZER_COST NUMBER OPTIMIZER_ENV RAW(895) OPTIMIZER_ENV_HASH_VALUE NUMBER PARSING_USER_ID NUMBER PARSING_SCHEMA_ID NUMBER PARSING_SCHEMA_NAME VARCHAR2(30) KEPT_VERSIONS NUMBER ADDRESS RAW(8) TYPE_CHK_HEAP RAW(8) HASH_VALUE NUMBER OLD_HASH_VALUE NUMBER PLAN_HASH_VALUE NUMBER CHILD_NUMBER NUMBER SERVICE VARCHAR2(64) SERVICE_HASH NUMBER MODULE VARCHAR2(64) MODULE_HASH NUMBER ACTION VARCHAR2(64) ACTION_HASH NUMBER SERIALIZABLE_ABORTS NUMBER OUTLINE_CATEGORY VARCHAR2(64) CPU_TIME NUMBER ELAPSED_TIME NUMBER OUTLINE_SID NUMBER CHILD_ADDRESS RAW(8) SQLTYPE NUMBER REMOTE VARCHAR2(1) OBJECT_STATUS VARCHAR2(19) LITERAL_HASH_VALUE NUMBER LAST_LOAD_TIME VARCHAR2(19) IS_OBSOLETE VARCHAR2(1) CHILD_LATCH NUMBER SQL_PROFILE VARCHAR2(64) PROGRAM_ID NUMBER PROGRAM_LINE# NUMBER EXACT_MATCHING_SIGNATURE NUMBER FORCE_MATCHING_SIGNATURE NUMBER LAST_ACTIVE_TIME DATE BIND_DATA RAW(2000) TYPECHECK_MEM NUMBER SQL> desc v$sql_plan Name Null? Type ----------------------------------------- -------- ---------------------------- ADDRESS RAW(8) HASH_VALUE NUMBER SQL_ID VARCHAR2(13) PLAN_HASH_VALUE NUMBER CHILD_ADDRESS RAW(8) CHILD_NUMBER NUMBER TIMESTAMP DATE OPERATION VARCHAR2(30) OPTIONS VARCHAR2(30) OBJECT_NODE VARCHAR2(40) OBJECT# NUMBER OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_ALIAS VARCHAR2(65) OBJECT_TYPE VARCHAR2(20) OPTIMIZER VARCHAR2(20) ID NUMBER PARENT_ID NUMBER DEPTH NUMBER POSITION NUMBER SEARCH_COLUMNS NUMBER COST NUMBER CARDINALITY NUMBER BYTES NUMBER OTHER_TAG VARCHAR2(35) PARTITION_START VARCHAR2(5) PARTITION_STOP VARCHAR2(5) PARTITION_ID NUMBER OTHER VARCHAR2(4000) DISTRIBUTION VARCHAR2(20) CPU_COST NUMBER IO_COST NUMBER TEMP_SPACE NUMBER ACCESS_PREDICATES VARCHAR2(4000) FILTER_PREDICATES VARCHAR2(4000) PROJECTION VARCHAR2(4000) TIME NUMBER QBLOCK_NAME VARCHAR2(30) REMARKS VARCHAR2(4000) OTHER_XML CLOB SQL> There is also a little something called "wait event interface" which tells me what is my SQL waiting for and where is the time spent. I am fully aware that there is one database to rule them all, one database to find them, one database to bring them all and in the vendor dependency bind them, but this things make a life of a DBA much, much easier. Thom Brown wrote: > On 19 August 2010 08:58, Machiel Richards <machielr@rdc.co.za> wrote: > >> Good day everyone. >> >> >> Firstly I would like to thank everyone in advance for all the >> help over the last couple of weeks on this mailing list as it has helped me >> out a lot in my efforts to try and understand Postgresql better. >> >> I am however once again here with some more questions which I >> hope someone can assist me with as I can not seem to find good documentation >> on this as yet... >> >> >> As part of our daily health checks and efforts to assist our >> client to improve their performance on their postgresql databases, we need >> to look at the queries that takes the longest to run and then use explain or >> other methods to try and improve the queries, add indexes where required, >> etc... >> >> >> However, I am trying to find out how I will be able to track / >> see these queries in order for me to be able to investigate these. >> >> the client have a total of 100 databases and to manually monitor >> these will be too time consuming considering that we have many clients to >> look after each day. >> >> >> Can anyone perhaps point me in the right direction here, even if it is >> in the form of documentation that will assist me in this. >> >> >> > > Hi Machiel, > > If you look in postgresql.conf, you'll see an option called > log_min_duration_statement. If you set this to a minimum amount of > time (in milliseconds) that the query must run before it's logged (of > course you need logging enabled first), you can then analyze the log > though something like pgFouine > (http://pgfouine.projects.postgresql.org/) which will be able to rank > the longest-running queries for you. Note that pgFouine requires that > you configure your log format beforehand, so check the pgFouine > documentation for how to do that. > > Alternatively, if you really don't wish to use pgFouine, just set your > log_min_duration_statement setting to a value over which queries would > become unacceptably long. Then just manually examine the logs. > > Regards > -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions