Often the server admin has little control over the applications which uses MySQL and it is hard to find the bottlenecks. This blog post can’t bring the peace in the world, or help NASA to finally land on the Mars. Instead those tasks, I’ll try to solve something else and present my own experiences with MySQL storage engines (at least for MyISAM and InnoDB as the most popular).
Keep in mind that I’m not an expert in this field. When I have problems with MySQL, I dig with my both hands (and head) to solve them without casualties. Every app has its own queries and you’ll need to track them down via slow query log.
Before you do anything, keep in mind that default MySQL installation usually works fine. Unfortunately maximum performances can’t be achieved without adjustments to increase performance and stability.
Before you can do anything, you need to enable slow query log.
# touch /var/log/slow-query.log
to create slow-query.log file. Then add
log-slow-queries = /var/log/slow-query.log long_query_time = 4 log-queries-not-using-indexes
(for MySQL 5.0.x)
to /etc/my.cnf (inside [mysqld] section) and restart mysql. Wait at least 24-48 hours before you proceed to the next step.
(For MySQL 5.1.x check here: LINK)
High performance tuning scripts
MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.
# wget mysqltuner.pl # chmod +x mysqltuner.pl # ./mysqltuner.pl
Read output and try to follow the recommendations.
Tuning-primer is another script who takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…” to produce readable recommendations for tuning server variables.
# wget http://www.day32.com/MySQL/tuning-primer.sh # ./tuning-primer.sh
Also, read output and try to see what you can do to fix problems. Keep in mind that you need to restart mysql after you add something to /etc/my.cnf. In case something goes wrong, check mysql log (usually /var/log/mysqld.log
Mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL
To install mytop,
# wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz # tar -xvzf mytop-1.6.tar.gz # cd mytop-1.6 # perl Makefile.PL # make # make install
In case some Perl libraries are missing, you should install them via cpan.
For example, Term::ReadKey is required and you can get it via cpan.
# cpan (pres Enter several times until you get cpan shell) cpan> install Term::ReadKey cpan> quit
Usage: # mytop -d DATABASE -u USERNAME -p PASSWORD
(replace the DATABASE, USERNAME and PASSWORD with your parameters.
mysqlreport makes a friendly report of important MySQL status values. mysqlreport transforms the values from SHOW STATUS into an easy-to-read report that provides an in-depth understanding of how well MySQL is running. mysqlreport is a better alternative (and practically the only alternative) to manually interpreting SHOW STATUS.
# wget http://hackmysql.com/mysqlreport # chmod +x mysqlreport # ./mysqlreport --user root --password
Enter password for root and check output. More info can be found here. LINK