MySQL Tools

This page contains links to various tools we found helpful to use in practice.
Some tools are written by us, others by third parties, yet another ones may be shipped with your operating system you just need to find they are there.

Maatkit
Maatkit is a great set of tools for MySQL Performance Analyses and maintainence. Must know and have for any MySQL User.

mysqladmin
mysqladmin extended -i100 -r is very nice way to look how MySQL performance counters increment and it can tell you a lot about server run status.

mysqlreport
MySQL Report is a tool which would look at status variables same as mysqladmin extended but will group them together nicely and provide some hints on what are good and bad values.

vmstat
vmstat 10 is a great tool to run and understand what system load is looking up. It is not too detailed but great for the glance view.

mysqlsla
This is nice tool to analyze slow query log. It reads bunch of different log formats and has various stats, and it was there before mk-log-parser appeared.

innotop
Innotop is great top like tool for MySQL and is helpful even if you do not use Innodb tables. Very nice to watch what happens to server in the real time.

filefrag
filefrag is the fool to check fragmentation of the file. Ever wondered how fragmented your tablespace or table on the file level ?

iostat
iostat tool is a great help investigating/troubleshooting IO problems – it will show you amount of IOs happening per device, their type, request size as well as queue size and response time.

mpstat
mpstat is a great tool to see details on CPU usage – how are different CPUs are being used, how many interrupts the handle etc. Helps to better understand and fine tune the load.

oprofile
oprofile is usually used for advanced MySQL tuning when the load is CPU bound – it will tell you where exactly inside MySQL or Kernel CPU time is spent.

iohist
iohist is a little tool to show histogram for IO response time. The main use for it is to see how response time is split for read and write request rather than seeing the average reported by iostat. These can be very different.

drtace
Dtrace is a great tool for system level performance analyses, with a catch of it being unavailable on Linux which limits it practical use dramatically.

fincore
fincore is a tool to check how well given file is cached in operation system cache. Very helpful to analyze caching of MyISAM tables for example.

sysbench
sysbench is a tool to check performance of system and MySQL. Helpful to check different hardware and OS characteristic.
Posted by peter on May 30, 2008 @ 6:16 pm
29 Comments »

1.
1. Bill Jones

If you are a part time MySQL DBA and looking for graphical monitoring tools, then Monyog is a nice alternative. It is not free though :-(

Comment :: May 31, 2008 @ 9:53 pm
2.
2. servertude

a new, alpha stage tool called jHeidi is available for linux, OSX and windows users.

Its a java-based clone of the popular windows mysql front end HeidiSQL.
The project are partners this is not a competing project.

If you use HeidiSQL on windows and want it for linux. Get it here http://jheidi.com

Comment :: June 1, 2008 @ 12:15 am
3.
3. benpi

A few more, in complement :

- blktrace : really, that’s _the_ tool for analyzing I/O activity (cause, performances, kernel paths, …) on Linux.
- iogrind : another I/O tracing tool
- SytemTAP : a bit like DTrace for Linux (but more oriented toward tracing kernel than userland)
- sar/sysstat : a very underestimated old unix tool. included (but not often activated) in most unixes, this records system informations (interrupts, i/o, load, network traffic, …) at regular intervals and allows you to query this data later on (ie. is very simple and efficient to answer questions like “why (io, cpu,…) did the system was slow two days ago at noon?”, and the data can be re-used to draw nice graphes/charts)
- ethtool/mii-tool : to check running features and functionalities of the network card (and his driver) : do it have negotiated full duplxe?, can I activate hardware checksum offload?, scatter-gather?, etc.
- hdparm : to gather infos about (and raw benchmark) ata drives
- pmap : when you need a detailled map of processes memory usage, this is your tool
- strace, ltrace, gdb, tcpdump, … for hard problems

Also, I tend to prefer htop over top, and mtr over ping/traceroute.

IMHO, MySQL (as opposed to PostgreSQL here) lacks a native way to watch the living locks at time t, in real time, as they appear (ie. to list all active locks (and not only the (misnamed) mysql’s lasts “deadlocks” (which are in effect just spinlocks)), and each requests & users holding those active locks, and the duration of those requests, the list of tables/indexes/… locked by those requests, etc.

ps: maatkit may be useful, but lack some polish (ie. he tends to loose config/passwds between each runs, to crash unexpectedly,…).

Comment :: June 1, 2008 @ 1:59 am
4.
4. Adam Kelly

Hi All,

I’m developing a binlog analysis tool for MySQL. It doesn’t have many features yet, but it may be useful to someone who needs text reports from their binlog files.

http://mysqlizer.sourceforge.net/

The whole script is one file, so there is no complex install, and only mysqlbinlog is required on the machine doing the analysis. (No mysql tools are required, if the binlogs are pre-processed).

Feel free to mail me any improvement requests (email on site)

Comment :: June 1, 2008 @ 6:24 pm
5.
5. Artem Russakovskii

Qot also looks interesting. http://ritmark.com. It’s an index analysis tool that can make suggestions on which indexes to create based on input queries. So far it’s in the alpha state, crashes, and doesn’t support complex queries but I do hope the author keeps developing it.

Comment :: June 2, 2008 @ 12:41 am
6.
6. Matthieu Huguet

A few more :

Dstat (combines vmstat, iostat, ifstat, netstat information and more)
http://dag.wieers.com/home-made/dstat/

If you use Nagios, there is this plugin :
http://www.consol.com/opensource/nagios/check-mysql-perf/

And with rrdtool (n2rrd + nagios + rrdtool) you can do some good graphs ;)
http://img91.imageshack.us/img91/8097/mynagios1yi4.png
http://img527.imageshack.us/img527/126/mynagios2ty3.png

Comment :: June 2, 2008 @ 3:41 am
7.
7. Erik Ljungstrom

fadvise (by the same author as fincore) is kind of useful in combination with fincore to see the rate at which filesystem caching is happening. If you as a one off need to scan a whole, sizeable and rarely used MyISAM table and don’t want it around hogging precious memory.
It’s sometimes also quite interesting to use in order to see how the disks are coping with a stone cold cache.

Comment :: June 2, 2008 @ 11:26 am
8.
8. peter

Wow guys you’re advising a lot of good tools, Please keep them coming – we will try to review them in the due time :)

Comment :: June 2, 2008 @ 7:15 pm
9.
9. Willy

Is there a tool/set of stats for mysql that provides the actual number of records/rows/pages accessed by a query including both in memory and disk. e.g. something similar to IO Statistics in MsSQL/Sybase?

Comment :: June 3, 2008 @ 2:58 am
10.
10. Jef

Dear Peter,

Does any technical documentation exists about storage engines and indexes used by MySQL? Especially MyIsam and InnoDB.

However there are some docs on mysql.org, they doesn’t explain exactly how it works.

I am using a database with one table containing about a billion records, then I need to make a theorical analysis on the storage engine, and on the B-Tree index. Maybe would you have have some advice …

Thanking you in advance,

Jef

Comment :: June 6, 2008 @ 4:35 pm
11.
11. Alexander Lyamin

actually HUGE downside of oprofile is that it doesnt track sleep()’ing time.
there was oprofile sleepmeter patch floating around couple years ago, but i failed to find it.
on other hand theres relatively new/supported patch from Nikita Danilov that allows same functionality:

http://linuxhacker.ru/~nikita/patches/2.6.14-rc5/07-proc-sleep.patch

Comment :: June 16, 2008 @ 1:12 pm
12.
12. Alexander Lyamin

Also, Dtrace is nothing stellar, but interface. You can achieve the very same functionality with linux kernel probes, but its more demanding. in a way…

Comment :: June 16, 2008 @ 1:15 pm
13.
13. Alexander Lyamin

Jef:
“one table containing about a billion records” , does the app accessing the table really require full blown SQL syntax ?
If answer is NO, then there’s much better options then mysql out there.

Comment :: June 16, 2008 @ 1:18 pm
14.
14. Angsuman Chakraborty

> If answer is NO, then there’s much better options then mysql out there.
Like?

Comment :: July 15, 2008 @ 11:57 am
15.
15. Devananda

I developed a Perl package to help with managing groups of MySQL servers; hopefully others will find it useful. Current components monitor replication, rotate binary logs, and allow execution of commands on groups of servers from a central server.

http://forge.mysql.com/wiki/ProjectPage_MyCAT

Comment :: July 17, 2008 @ 12:18 am
16.
16. peter

Devanada,

Thanks for sharing. Should take a look at it.

Comment :: July 20, 2008 @ 9:15 am
17.
17. Noah

I’d like to ask you guys something about sysbench, particularly about the OLTP complex test.

It seems that the test exercises deadlocks by design. The way the transactions are built seems to favor the occurrence of deadlocks, please correct me if I’m wrong.

So far I have made tests with MySQL and PostgreSQL, since both have MVCC the deadlocks didn’t arise. Is there a way to “disable” MVCC on MySQL? my idea is to evaluate the performance of MVCC versus locking system.

Thank you,

-Noah

Comment :: October 8, 2008 @ 9:32 am
18.
18. Bjorn

You might want to take a look at Jet Profiler for MySQL, a query/table/schema/user profiler:

http://www.jetprofiler.com/

Comment :: November 3, 2008 @ 8:49 am
19.
19. Windy

Check out the new Flipper app in the iTunes App Store for only $0.99. Flipper is an iPhone MySQL™ query editor. While not intended as a full desktop SQL development environment, it does enable the user to write complex queries and submit them over the network to MySQL databases.

Comment :: December 14, 2008 @ 12:03 pm
20.
20. Bob

It works pretty well for a mobile client. I like the ability to write multi line queries too.

Comment :: December 16, 2008 @ 11:03 am
21.
21. leon

Also, Dtrace is nothing stellar, but interface. You can achieve the very same functionality with linux kernel probes, but its more demanding. in a way…

Comment :: December 22, 2008 @ 5:58 am
22.
22. Brad

Hey I will refer this website to our Customers who need help on Mysql.

Keep it up Guys

Comment :: February 25, 2009 @ 10:12 am
23.
23. Kalle Ikkelä

myDBR (http://mydbr.nocsos.com) is a web based reporting system for MySQL that allows you to build and manage a scalable reporting environment. Even the more complex reports can be authored with ease.

Comment :: March 28, 2009 @ 4:04 pm
24.
24. aalney

I am not sure if this is place to post this question, but i am in desperate situation.My projects require me to log any action like the system shall record within each audit record the following information when it is available: (1) date and time of the event; (2) the component of the system (e.g. software component, hardware component) where the event occurred; (3) type of event (including: data description and patient identifier when relevant); (4) subject identity (e.g. user identity); and (5) the outcome (success or failure) of the event.

Can anybody help me out how to start on this.I am a newbie and did not work with logging tables before.Any info,documents,links,basic things to take care,how to set up,pre and post requirements, what should be my approach towards this.

thanks
alan

Comment :: April 7, 2009 @ 5:41 am
25.
25. dangby

Why can’t I find any info on a tool or methodology for being easily notified when MySQL has a table crash?
It seems silly that the application would have to build in handlers for every query to detect whether some table has crashed and notify someone about it…. why haven’t I seen some low level powerful support for this right in MySQL.

We turned on error logging and can get _some_ of the crashed tables to show up in the error log, but even then you would have to run something to watch the log and notify you. That would be better than nothing, but even that doesn’t seem to be on any SysAdmin’s radar or mentioned in documentation anywhere. Why are MySQL System Administrators slighted by the developers so much?

Am I missing something obvious?

Comment :: April 21, 2009 @ 11:58 am
26.
26. Oleg Burlaca

I’d like to suggest a web based mysql manager, it’s called AeroSQL: http://www.burlaca.com/aerosql/
There is a demo, video and screenshots. In the future it will feature administration functionality.

AeroSQL is a free web based tool for managing MySql servers written in PHP. The key feature of the product is an intuitive management of the database content. The result of SELECTs is displayed in a grid as in traditional desktop applications.

Comment :: May 2, 2009 @ 10:08 am
27.
27. Omid

Peter,

Thanks for the list. May I suggest to revise and update this list and add the tools mentioned by the users plus some of the many ‘new’ great tools introduced at the Percona conference to this list?

Comment :: May 18, 2009 @ 11:22 am
28.
28. Gyorgy

I created a SQL query tester tool. Let me know what do you think: http://code.google.com/p/sql-query-tester/

Comment :: June 1, 2009 @ 3:13 am
29.
29. Jim

My SQL outperformed any initial thoughts that I had… I am happy.

Comment :: August 24, 2009 @ 11:47 am