How do I profile MySQL?

Question

What tools exist to profile MySQL, like how MSSQL 2000+ does with the SQL Profiler?

I'd want to trace things like SQL statements executed, execution times, execution plan, etc.

Answer 1

Look into enabling the Query Log and the Slow Query Log.

Answer 2

if you have query logging turned on on your production/test environment [ which is not necessary the case ] you can use mk-query-digest from maatkit toolkit. it'll help you to determine which queries are most frequent/longest taking etc.

Answer 3

Another commercial option is MySQL Query Analyzer which is part of the MySQL Enterprise Monitor. I've found it to be moderately useful in helping to profile oddball queries to figure out ways to improve their performance.

Answer 4

Here is an good article about the profiler of MySQL. Although take a look at the explain statement.

Answer 5

You can also check out MySQLTuner

Answer 6

+1 for Look into enabling the Query Log and the Slow Query Log. I just don't have the reputation to vote that up just yet ;)

Answer 7

I use this little script. It's always been useful to me, although not anything official.

http://genomewiki.ucsc.edu/index.php/Tuning-primer.sh

Answer 8

I've used several scripts and other tools which are all great, but i found Jet Profiler really good at giving real time monitoring and visualization of what's going on and how things are changing. The full version costs money but the restricted free version is also useful and gives you a good feel for what the full one can do.

Answer 9

Try to use Neor Profile. Tool for profiling all Mysql queries from web site or application. To get time, size, query.