Detecting Mysql full-table scans with dtrace

I'm a member of

Quicksearch

Codenews Search

Disclaimer

The individual owning this blog works at Sun Microsystems GmbH in Germany, a subsidiary of Oracle. 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

View as PDF: This entry | This month | Full blog

Competition entry by David Cummins powered by Serendipity v1.0

Links in this article

Identifying a Full Table Scan
in MySQL with Dtrace

The LKSF book

The book with the consolidated Less known Solaris Tutorials is available for download here

Twitterfeeds

twitter.com/c0t0d0s0
just blogged: links for 2010-03-19: Gedanken eines Fliegenden: Freikoerperkultur ... http://bit.ly/c21ARU

twitter.com/codenews
6935782 need to manually increment build number one more time http://bit.ly/aMqEbX

twitter.com/SunPatches
128365-04 - Sun Crypto Accelerator 6000 1.1: Driver Patch. Available for SPARC since Mar/19/10. http://bit.ly/agl9Nw

twitter.com/SolPatchesX86
118192-04 - SunOS 5.9_x86: gtar patch. Available since Mar/19/10. http://bit.ly/cbnoJ7

twitter.com/SolPatchesSPARC
118191-04 - SunOS 5.9: gtar patch. Available since Mar/19/10. http://bit.ly/cb2Drj