slow query log analyzers

views:

answers:

+1 Q:

slow query log analyzers

What kind of tools are available to read & interpret slow queries and missing indexes?

I am aware of MySQL Query Analyzer, can you suggest other tools which are simpler to configure and maintain (both open source and commercial)

Database environment: MySQL, H2

+1 A:

I have used mysqlreport command line tools with great success
http://hackmysql.com/mysqlreport

DeveloperChris 2010-07-09 05:15:35

+2 A:

I don't know of any graphical tools for the H2 database, but there is a command line tool to analyze the log file: http://h2database.com/html/performance.html#database_profiling - this will get you the list of SQL statements, the one that took the longest on top (the longest meaning the one with the highest total combined time). You also see the number of times each statement was run, and the total number of result rows. Once you know this, run "analyze explain select..." to find out the index the top queries use, plus the number of rows they read from the source tables.

Thomas Mueller 2010-07-09 12:23:18

@Thomas Mueller: Welcome to Stackoverflow!

trashgod 2010-07-10 04:03:24

I would like to profile the data which is generated by my JEE application. h2 is running on server mode and I would prefer to use the ConvertTraceFile just to generate the trace information.Is this possible? Appreciate your inputs

Samuel 2010-07-10 04:41:09

Yes, it should be relatively easy to do that. What you need to do is set the trace level to 2. The easiest way to set the trace level is to append the setting to the database URL, for example: jdbc:h2:~/test;TRACE_LEVEL_FILE=2 or jdbc:h2:tcp://localhost/~/test;TRACE_LEVEL_FILE=2. After running the test case, convert the .trace.db file using the ConvertTraceFile tool as documented. The trace file is located in the same directory as the database file. (I will add all that to the documentation).

Thomas Mueller 2010-07-10 09:06:47

@trashgod: Hello!

Thomas Mueller 2010-07-10 09:13:59

+1 A:

The simplest tool to configure is mysqldumpslow, since it's part of the mysql installation:

http://dev.mysql.com/doc/refman/5.1/en/mysqldumpslow.html

Ike Walker 2010-07-09 14:02:29

by the way the mysqldumpslow script is available in the scripts directory (its not mentioned in the doc)

Samuel 2010-07-10 02:46:04

ansaurus

tags:

views:

answers:

slow query log analyzers

related questions