ServerAdminBlog

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.

Exec

# 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

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

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

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

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