Walking in Light with Christ – Faith, Computing, Diary

Improving MySQL performance is crucial for improving a website responce times, reduce server load and improve overall work efficiency of a mysql database server.

I've seen however many Linux System administrators who does belittle or completely miss the significance of tuning a newly installed MySQL server installation.
The reason behind that is probably caused by fact that many people think MySQL config variables, would not significantly improve performance and does not pay back for optimization efforts. Moreover there are a bunch of system admins who has to take care for numerous services so they don't have time to get good knowledge to optimize MySQL servers.
Thus many admins and webmasters nowdays, think optimizations depend mostly on the side of the website programmers.
It's also sometimes falsely believed that optimizing a MySQL server could reduce the overall server stability.

With the boom of Internet website building and internet marketing, many webmasters emerged and almost anybody with almost no knowledge on GNU/Linux or minimal or no knowledge on PHP can start his Online store, open a blog or create a website powered by some CMS like joomla.
Thus nowdays many servers even doesn't have a hired system administrators but are managed by people whose knowledge on *Nix is almost next to zero, this is another reason why dozens of MySQL installations online are a default ones and are not taking a good advantage of the server hardware.

The incrase of website visitors leads people servers expectations for hardware also to grow, thus many companies simply buy a new hardware instead of taking the few time to investigate on how current server hardware can be utilized better.
In that manner of thought I though it will be a good idea to write this small article on Tuning mysql servers with two scripts Tuning-primer.sh and mysqltuner.pl.
The scripts are ultra easy to use and does not require only a minimal knowledge on MySQL, Linux or (*BSD *nix if sql is running on BSD).
Tuning-primer.sh and mysqltuner.pl are therefore suitable for a quick MySQL server optimizations to even people who are no computer experts.

I use this two scripts for MySQL server optimizations on almost every new configured GNU/Linux with a MySQL backend.
Use of the script comes to simply download with wget, lynx, curl or some other web client and execute it on the server host which is already running the MySQL server.

Here is an example of how simple it is to run the scripts to Optimize MySQL:

debian:~# perl mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden >major@mhtx.net<
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 256M) [see warning above] join_buffer_size (> 256.0K, or always use indexes with joins) table_cache (> 7200)


You see there are plenty of things, the script reports, for the unexperienced most of the information can be happily skipped without need to know the cryptic output, the section of importance here is Recommendations for some clarity, I've made this section to show up bold.

The most imporant things from the Recommendations script output is actually the lines who give suggestions for incrase of certain variables for MySQL.In this example case this are the last three variables:
query_cache_size,
join_buffer_size and
table_cache

All of these variables are tuned from /etc/mysql/my.cnf (on Debian) and derivatives distros and from /etc/my.cnf on RHEL, CentOS, Fedora and the other RPM based Linux distributions.

On some custom server installs my.cnf is also located in /usr/local/mysql/etc/ or some other a bit more unstandard location

Anyways now having the Recommendation from the script, it's necessery to edit my.cnf and try to double the values for the suggested variables.

First, I check if all the suggested variables are existent in my config with grep , if they're not then I'll simply add the variable with doubled size of the suggested values.
P.S: One note here is sometimes some values which are configured, are the default value for the MySQL server and does not have a record in my.cnf

debian:~# grep -E 'query_cache_size|join_buffer_size|table_cache' /etc/mysql/my.cnf table_cache = 7200
query_cache_size = 256M
join_buffer_size = 262144

All of my variables are in the config so, now edit my.cnf and set values to:
table_cache = 14400
query_cache_size = 512M
join_buffer_size = 524288


I always, however preserve the old variable's value, because sometimes raising the value might create problem and the MySql server might be unable to restart properly.
Thus before going with adding the new values make sure the old ones are commented with # , e.g.:
#table_cache = 7200
#query_cache_size = 256M
#join_buffer_size = 262144


I would recommend vim as editor of choice while editing my.cnf as vim completely rox If you're not acquainted to vim use nano or mcedit or your editor of choice :

debian:~# vim /etc/mysql/my.cnf
...


Assuming that the changes are made, it's time to restart MySQL to make sure the new values are read by the SQL server.

debian:~# /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
Checking for tables which need an upgrade, are corrupt or were not closed cleanly.


If mysql server fails, however to restart, make sure immediately you reverse back the changed variables to the commented values and restart once again via mysql init script to make server load.

Afterwards start adding the values one by one until find out which one is causing the mysqld to fail.

Now the second script (Tuning-primer.sh) is also really nice for MySQL performance optimizations are necessery. However it's less portable (as it's written in bash scripting language).
Consider running this script among different GNU/Linux distributious (especially the newer ones) might produce errors.
Tuning-primer.sh requires some minor code changes to be able to run on FreeBSD, NetBSD and OpenBSD *nices.

The way Tuning-primer.sh works is precisely like mysqltuner.pl , one runs it it gives some info about current running MySQL server and based on certain factors gives suggestions on how increasing or decresing certain my.cnf variables could reduce sql query bottlenecks, solve table locking issues as well as generally improve INSERT, UPDATE query times.

Here is an example output from tuning-primer.sh run on another server:

server:~# wget http://www.pc-freak.net/files/Tuning-primer.sh
...
server:~# sh Tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

As seen from script output, there are certain variables which might be increased a bit for better SQL performance, one such variable as suggested is key_buffer_size- (You could increase key_buffer_size)

Now the steps to make the tunings to my.cnf are precisely the same as with mysqltuner.pl, e.g.:
1. Preserve old config variables which will be changed by commenting them
2. Double value of current variables in my.cnf suggested by script
3. Restart Mysql server via /etc/init.d/mysql restart cmd.
4. If mysql runs fine monitor mysql performance with mtop or mytop for at least 15 mins / half an hour.

if all is fine run once again the tuning scripts to see if there are no further improvement suggestions, if there are more follow the 4 steps described procedure once again.

It's also a good idea that these scripts are periodically re-run on the server like once per few months as changes in SQL queries amounts and types will require changes in MySQL operational variables.
The authors of these nice scripts has done great job and have saved us a tons of nerves time, downtimes and money spend on meaningless hardware. So big thanks for the awesome scripts guys
Finally after hopefully succesful deployment of changes, enjoy the incresed SQL server performance