Detecting Mysql full-table scans with dtrace

Quicksearch

Disclaimer

The individual owning this blog works for Oracle in Germany. The opinions expressed here are his own, are not necessarily reviewed in advance by anyone but the individual author, and neither Oracle nor any other party necessarily agrees with them.

Navigation

< links for 2010-01-06 | The Deliverer's knowledge: Sun Cluster Step by Step - How to add an additional node to an existing single-node cluster? >

Detecting Mysql full-table scans with dtrace

Thursday, January 7. 2010

Robert Milkowski wrote an article about detecting a full-table scan with dtrace without app-specific probes: Identifying a Full Table Scan in MySQL with Dtrace. No need to use the slow query log to do this task. This helpful especially when the queries aren't slow at the moment because the database fits into the memory at the beginning

Posted by Joerg Moellenkamp in English, Solaris at 06:04 | Comments (3) | Trackbacks (0)
View as PDF: This entry | This month | Full blog

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

log_slow_queries = on
long_query_time = 0.1
log_queries_not_using_indexes = on

und ggf.

min_examined_row_limit = irgendwas

Das erste schaltet das Slow Log an (in 5.1 endlich auch ohne einen Neustart des Servers). long_query_time kann nun endlich auch auf Werte unter 1s gesetzt werden.

log_queries_not_using_indexes loggt Full Table Scans, und min_examined_row_limit sorgt dafür, daß solche Scans auf kleinen Tabellen nicht das Log cluttern.

#1 Isotopp (Homepage) on 2010-01-07 11:09 (Reply)

Das min_examined_row_limit kannte ich noch nicht .... was gelernt

#2 Joerg M. (Homepage) on 2010-01-07 11:11 (Reply)

log_slow_queries can't log queries which take less than 1s - at least not in mysql 5.0 and older (I'm not sure about newer ones). Then this is only example and one can extend the script to do much more.

For example see MySQL TOP - http://milek.blogspot.com/2010/01/mysql-top.html

#3 Robert Milkowski (Homepage) on 2010-01-19 00:37 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	E-Mail addresses will not be displayed and will only be used for E-Mail notifications. To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above: Standard emoticons like :-) and ;-) are converted to images. Enclosing asterisks marks text as bold (word), underscore are made via _word_.
	Remember Information? Subscribe to this entry