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.
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:
In the directory where you extracted mytop execute:
sudo make install
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.
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.