Improving MySQL stability & performance using MySQLTuner - part 1

MySQL is the database engine behind many web applications on the Internet today. While it is relatively straightforward to install, configuring MySQL to best support your particular application requires expertise and the right tools. This post introduces MySQLTuner, a command-line program which offers suggestions to optimize MySQl performance and stability.

MySQLTuner is a read-only script: it won't actually write to the MySQL configuration file. Based on your database's past usage, it recommends new values to assign to specific MySQL configuration variables. It is your responsibility to understand each recommended change and its possible ramifications, select the changes you want to make, and to make them in a controlled manner.

Installing MySQLTuner

Before you install MySQLTuner, make sure that it supports your MySQL version. You can find the up-to-date compatibility information on its website.

To identify the MySQL version on your database server, run this command:


$ mysqladmin -u root -p version
...
Server version 5.5.43-0+deb7u1
...


MySQLTuner is a PERL script that you can install from the standard Debian and Ubuntu repositories. You can install it using the following command:

$ sudo apt-get install mysqltuner


The prepackaged MySQLTuner may not be the latest release available. If you want the latest, or you run a Linux distro other than Debian/Ubuntu, you can install the up-to-date version by downloading it directly. Simply download the PERL script to a directory of your choice using the command:


$ wget http://mysqltuner.pl/ -O mysqltuner.pl


Running MySQLTuner

Your database should be up longer than 1 day before you run MySQLTuner. This is because MySQLTuner bases its recommendations on past database usage. The more data it has to analyze, the more accurate is its recommendations. If MySQLTuner is run on a database that has been restarted in the last day, you will get a warning message: 'MySQL started within last 24 hours - recommendations may be inaccurate'.

To run the script, enter the following:


$ perl mysqltuner.pl


Analyzing output

MySQLTuner reports statistics about the database, and makes tuning recommendations. The top section of the report gives you useful database metrics, many of them actionable. The bottom section provides tuning suggestions for the MySQL configuration file.

You should thoroughly research a suggested configuration change before deciding to implement it. To change a configuration variable, edit the file /etc/mysql/my.cnf.

After you make a MySQL configuration change, restart the MySQL service.


$ sudo service mysql restart


Scheduling runs

Database tuning is not a 'once and done' type of task. Conditions change over time. A good practice is to schedule regular MySQLTuner runs using crontabs.

The following schedules MySQLTuner to run every Thursday at 4:30 am.


$ (crontab -l; echo "30 04 * * 4 perl <path-to-script>/mysqltuner.pl --nocolor 2>&1") | crontab -


Please refer to my earlier post for details about crontabs.

By default, MySQLTuner prompts the user for the database login credentials. For a cronjob to run MySQLTuner, you may provide the database account and password in the user-specific MySQL configuration file.


$ cat >> /home/peter/.my.cnf <<< '
[client]
user=<db_user>
password=<db_pass>'


Finally, protect the db login credentials by tightening up access permissions for the .my.cnf file.


$ chmod 600 /home/peter/.my.cnf


What's next?

The remainder of this series will guide you through taking actions based on MySQLTuner recommendations.

• Part 2: defragment a database
• Part 3: minimize database memory footprint

Double asterisk brings double expedience to pathname expansion

If you are a Linux command-line user, most likely, you are familiar with the use of the single asterisk ('*') in pathname expansion (aka globbing). How the asterisk behaves is standardized across all shells (bash, zsh, tcsh, etc). For example, the ls * command lists the files and the immediate sub-directories of the current directory.


$ ls *


The single asterisk, however, is not recursive: it does not traverse beyond the target directory. You may use the find command to generate a recursive listing of pathnames. A simpler solution is the use of the double asterisk ('**').

Unlike the single asterisk, the double asterisk is not standardized. Different shells introduced the feature at different times with slightly different behavior. This post focuses on the use of '**' for the bash shell.

The double asterisk feature for bash first appears with bash4. To find out which bash version you are running, execute the following command:


$ bash --version
GNU bash, version 4.2.37(1)-release (x86_64-pc-linux-gnu)
...


Before you use '**', you must first enable the globstar shell option:


$ shopt -s globstar


With globstar enabled, you may use '**' for pathname expansion.


$ ls **/abc.txt


In the above example, the ls command returns any occurrence of the file abc.txt in the current directory and sub-directories.

Notes:

1. By default, the double asterisk does not expand to include a hidden file. For example, the following command will not find .htaccess because it is a hidden file.

   
$ ls **/.htaccess


   To allow hidden files in '**' output, enable the dotglob shell option:

   
$ shopt -s dotglob


2. When you do a pathname expansion using '*' or '**', you run the risk that a returned filename is the same as a command-line flag, e.g., -r. To mitigate that risk, precede '**' with '--' as below. The double dash marks the spot where command-line flags end, and positional parameters begin.

   
$ ls -- **


3. Under bash, '**' expands to follow symbolic links. This behavior, however, is shell-specific. For zsh, expanding the double asterisk does not follow a symbolic link.

The double dash is a useful tool to add to your everyday command-line usage.