Search This Blog

Loading...

Friday, June 19, 2015

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?

Part 2 of this series will guide you through taking actions based on MySQLTuner recommendations.

Tuesday, June 9, 2015

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.

Tuesday, May 26, 2015

Useful sed tricks to customize configuration files



A typical software installation goes like this. You install the software using apt-get install or yum install.
Then, you manually edit the software's configuration file in order to satisfy your requirements. If you have to repeat the install on multiple machines, this quickly becomes tedious.


Instead of manually editing the file, I run a text manipulation command such as sed or awk to make the required changes. Then, I script the procedure by inserting the commands in a bash script file.


The scripting of configuration changes serves multiple purposes:


  • It is a permanent record of the configuration changes.

  • It is readily repeatable on the same or a different machine.


Below, I illustrate 2 sed tricks to make configuration changes to the Apache webserver. The target configuration file is /etc/apache2/apache2.conf.


Before you make any change, please first backup the original configuration file.



$ sudo cp /etc/apache2/apache2.conf /etc/apache2/apache2.conf.orig


Replacing first occurrence of a string


The default apache2.conf file contains the following line:


Timeout 300


Below is the sed command to change the first occurrence of Timeout in the file to 100.



$ sudo sed -i "0,/^Timeout\s/ s/^Timeout\s\+[0-9]\+/Timeout 100/" /etc/apache2/apache2.conf


The -i parameter tells sed to edit the file in place - that is, directly in apache2.conf.


0,/^Timeout\s/ specifies the range of lines over which the sed command is to be executed. In this example, the starting line is the first line (line 0). The finishing line is the line returned by a search for the word Timeout which appears at the beginning of a line (^) and followed by a whitespace (\s).


The line range parameter limits the change to only the first occurrence of Timeout in the file. If you leave out the line range, each occurrence of Timeout in the file will be modified. In many scenarios, leaving it out is OK because the parameter occurs only once in the configuration file.


For some configuration files, a parameter can occur multiples times, in different sections. Next, I illustrate how to limit the change to within a particular section in the configuration file.


Replacing a string within a target section


The MaxClients parameter occurs in 3 sections within the apache2.conf file:


  • mpm_prefork_module

  • mpm_worker_module

  • mpm_event_module


I want to change the MaxClients parameter within the mpm_prefork_module only.


The default mpm_prefork_module is like this:



<IfModule mpm_prefork_module>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 150
MaxRequestsPerChild 0
</IfModule>


Note that a section is delimited by the opening <IfModule> and closing </IfModule> statements.


The following sed command modifies the value of MaxClients to 18 within the mpm_prefork_module section.

$ sudo sed -i "/<IfModule mpm_prefork_module>/,\@</IfModule>@ s/MaxClients\s\+[0-9]\+/MaxClients 18/" /etc/apache2/apache2.conf


The line range is defined by the /<IfModule ... >/,\@</IfModule>@ clause in the above statement. The opening line in the line range is specified by a search for the <IfModule ... > pattern. The closing line is specified by the search pattern \@</IfModule>@.


An explanation of the closing line pattern is warranted. The slash (/) character is part of the search pattern for the closing line (</IfModule>). However, the slash is also the default delimiter for sed. Therefore, we must use a different delimiter (@) for the closing-line search pattern. Note that the first @ is escaped (\@).


The s/MaxClients.../MaxClients 18/ clause changes the value of MaxClients to 18.


Conclusion


The above are examples of how you can use sed to script common scenarios of changing configuration files. You can achieve the same result using other tools such as awk or perl. Please use the comment system to let us know your own examples.


If you are interested to learn more about sed, please read my earlier posts on the tool:


Tuesday, May 12, 2015

How to add and delete a user from a Linux group

Being in the right Linux group expedites many system administration tasks, and will save you time. For example, the Apache web server logs have the following file ownership and permissions.

# ls -al /var/log/apache2/*.log
-rw-r----- 1 root adm 882984 May 11 12:14 /var/log/apache2/access.log
-rw-r----- 1 root adm 418 May 11 01:55 /var/log/apache2/error.log
...

To read the Apache logs, you need root permissions. However, there is a shortcut that does not require you to run sudo. Note that adm - the admin group for Debian-based systems - is the group owner of the log files. So, if you become a member of adm, you don't need to sudo to read the log files.

To add peter to the adm group, execute any of the following commands:

  • $ sudo usermod -aG adm peter

  • $ sudo gpasswd -a peter adm

To verify that peter is now a member of the adm group, execute any of the following commands:

  • $ id -nG peter
    peter adm www-data

    You may be tempted, as I was, to not specify peter in the above command. Don't skip the parameter. Without the user parameter, you won't see the effect of the change in group membership - unless you log out and log back in. If you are running X, it means you have to log out of X, not just opening a new command shell window within the same X session.

  • $ groups peter
    peter : peter adm www-data

    Again, specify peter in the command. Otherwise, you must log out and then log back in before executing the command.

  • $ grep adm /etc/group
    adm:x:4:peter

If you have made a mistake, and now want to remove peter from the adm group, run any of the following commands:

  • $ sudo gpasswd -d peter adm
    Removing user peter from group adm

  • $ sudo deluser peter adm
    Removing user `peter' from group `adm' ...
    Done.

Besides the adm group, you should consider adding yourself to the www-data group. The Apache web server runs under the www-data user account on Debian systems. As a member of the www-data group, you can more easily modify web server files and directories.

An earlier post on group membership appears here.

Wednesday, April 29, 2015

How to schedule background jobs using crontab

The cron daemon is a great user tool to automate tasks that don't require human intervention. Users pre-specify jobs to run in the background at particular times, for example, every Monday, Wednesday and Friday at 2am.

To use cron, each user creates his own crontab ('cron table') file. The command to examine one's crontab file is crontab -l.

$ crontab -l
MAILTO=commandolinux@gmail.com
0 2 * * 1,3,5 /home/peter/backups/backupWP.sh 2>&1

The MAILTO line specifies the email address to which cron sends the output of command execution. Please refer to my earlier post on how to set up an SMTP server to forward your emails.

The second crontab line specifies that the backupWP.sh script should be executed at 2am every Monday, Wednesday and Friday. The syntax may look complicated. Fortunately, you can use the on-line Crontab Generator to craft the crontab statements. If you want to learn the syntax, click here instead.

Create crontab

Your crontab file is initially empty. To create the file from scratch, run the crontab command and type in the crontab statements.

$ crontab

Alternatively, put the statements into a temporary file, say /tmp/cron, and run this command:

$ cat /tmp/cron | crontab -

Edit crontab

If you want to modify crontab contents after they are created, run this command:

$ crontab -e

The command opens the crontab file in your default text editor. It is the most versatile way to modify crontab. You can use it to create, modify, and delete crontab statements. Don't forget to save the file after you finish editing.

The downside for this edit command is the time and overhead of starting the text editor. You can append a new statement directly by using the command in the next section.

Add to crontab

When I was new to crontab, I made the mistake of trying to append a statement by running crontab without any argument. That actually replaced everything in the crontab file with the new input.

The correct command to append a new statement is:

$ (crontab -l; echo "30 04 * * 4 /home/peter/backups/report.sh 2>&1") | crontab -

The trick is to run 2 commands in a subshell grouped by the round brackets. The first command, crontab -l, fetches the existing crontab statements. The echo command echoes the new statement to be appended. The collective output from both commands are piped to crontab standard input.

Empty crontab

To erase all crontab contents, execute the following command:

$ crontab -r

Conclusion

You may use crontab to schedule regular maintenance and backup tasks. Once it is set up, the crontab file tends to be static. But, if you ever need to add another task, or change the scheduled times, the commands introduced in this post will come in handy.