Wednesday, August 6, 2008

MySQL Profiling and Performance tools

I'm working with a MySQL database that has been growing, and has reached about 2 Gb these days. So needless to say we're getting more interested in profiling the database. I started a search for some tools and this is the list thus far, in no particular order.

1. MyTop
The first tool I came across was mytop, which is a little tool inspired by 'top'. I also found this quick tutorial tutorial on how to interpret the screens. It does require depend on some Perl libraries, which you can install using MCPAN:
sudo perl -MCPAN -e shell

then at the cpan prompt:
install DBI
install DBD::mysql
install Term::ReadKey
install Term::ANSIColor
install Time::HiRes

In the directory where you extracted mytop execute:
perl Makefile.PL
make
make test
sudo make install


2. Maatkit
MySQL toolkit is now called Maatkit. Maatkit can be found on sourceforge and more recently on code.google. It looks like it can come in very handy with running replicated databases. Peter Zeitsev has nice blog entries on what it can do, or you can just read the docs.

3. MySQL Query Profiling
Recently MySQL added a profiling tool in the server itself called MySQL Query Profiler. This tool provides very detailed information on where the time is spend for a query.

4. MyProfi
MyProfi is a log analyzer and profiler. Extracts the most popular queries grouping them by their normalized form and shows the statistics for each group. Helps you recognize the most frequently run queries to be able to optimize overall db performance.

No comments: