Mysql server optimization for InnoDB and MyISAM

→ Are you a new visitor? Please visit the page guidance for new visitors ←

Mysql server optimization

In my other articles I showed you how to install a mysql server on Centos or on Debian / Ubuntu and the process wasn’t that hard right? Well that’s how it should be, installing things on a server is a basic thing really, configuring and optimizing it for your needs its the hardest thing. Not that its not possible for anyone, but there are just so many options available currently that it is hard to keep track of what you need to change or set for your own server. In this article I’m going to show you some nice little tricks how you can quickly optimize your mysql server using custom values or predefined ones from the example configuration.

Mysql example configuration

I’m going to start with the predefined server configuration examples, in most mysql server installations these should come with a basic example configuration for small, medium or huge server resources. You can find these configuration files in the location /usr/share/mysql/ on centos, unfortunately I could not find them on my Debian test box so I assume these are not installed by default:

For those who did not find the sample configuration I thought of adding these for you and link them here. Please use below links to get to your exact page:

To use either of the above sample configuration you only need to backup the current configuration and copy the contents from the above files to your own:

After changing the mysql configuration file, you always need to restart mysql, so make sure you do this to apply your new settings:

Then you are all done. That’s basically it with how working with a sample mysql configuration files is, these are already made to work.

Using Mysqltunner.pl for tunning mysql performance

This could not have been missing from this article, this piece of script is not just useful, but also great! It reads the current settings, checks your server usage and recommends what you need to do, to optimize and speed mysql queries. I’ve been using this one for years now with great support overall.

You can download the script from this page:

http://mysqltuner.com

Currently this script is being hosted by github.com here:

https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

But anyway, use either links and copy the content to your server, I usually download it in /root/

This will download the mysqltunner.pl file. Now you just need to run the file using command:

You may be asked to enter your root password, you just need to allow the script to connect to your mysql server for inspection. The result should be similar as bellow:

You need to be wary about the points before the “Recomandation” section, for example in my case it sees:

[!!] Temporary tables created on disk: 38% (1K on disk / 3K total)

is a bit low, we have 3K temporary tables and there is 1k on disk. We increase the values of:

to an acceptable value for best performance. Please do not increase it too much. You need to keep track of your actual mysql memory usage so that you don’t go over your system limit.

Also keep in mind that on some systems it is not just your mysql server that is running, you may have a web server or a another service running there. That too will need ram and CPU to run! So don’t be hasty when adjusting the limits.

? Optimizing mysql for InnoDB storage engine

Pages: 1 2

Request an article ←