HowTo: MySQL Performance Tuning

At first I want to clarify that this is only a guideline for tuning your MySQL Performance, due to the fact that every MySQL Server is used for custom purposes and needs. There can be huge differences for application use, like READ-heavy or WRITE-heavy databases.

I recommend advanced knowledge of MySQL to understand most of the things explained in the following part.

This post includes some basic tips to increase your MySQL Server performance. Maybe I will write some other posts about more specific topics, like buffers or caching.

Tips for MySQL performance optimization:

  1. Storage Engine:
  2. In my last post I described and explained the differences, advantages and disadvantages of the most popular MySQL storage engines: MyISAM vs. InnoDB. The choice of the storage engine that fits best to your needs is very important. If you have done the correct selection this can have huge performance enhancement and more features too!

  3. Table optimization:
  4. Another common problem is table fragmentation, which can have great performance impact too. The main problem with fragmentation is bad ordering of indexes on disks, which results in slower query processing due to I/O issues.
    On my github profile you will find a tool for checking and optimizing your tables: MySQL fragmentation tool.

    Here is a short explanation how you can use this:
    # Show table count of fragmented tables in your databases
    ./ --user $USER --password $PASS --check
    # Show fragmented tables in your databases
    ./ --user $USER --password $PASS --check --detail
    # Optimize all tables
    ./ --user $USER --password $PASS
    # Optimize all tables of one specific database
    ./ --user $USER --password $PASS --database $DB

    So for better performance you should optimize your tables regularly. But beware of using this with productive databases, because optimizing a table will lock it!

  5. Tools for analysis:
  6. This section is only for advanced users, because you need experience in MySQL server configuration. There are some tools with which you can analyze your MySQL server very well. With the help of them, you are able to find possible configuration failures or optimization targets. You find them on my github account too: MySQL Tuner and MySQL Report.
    MySQL Tuner itself displays tips and data in its output, whereas MySQL Report displays more detailed data. Besides MySQL Report has a really great guide in which their output is explained: Guide for MySQL Report.
    Both tools are really helpful, but they will not do the configuration for you. So be sure you know what you are doing if you follow their tips!

    My recommendation is taking a closer look on following topics:

    • Slow queries
    • You should ensure that the slow query log is enabled in your configuration. If you have slow queries try to get rid of them immediately! There is nothing worse than queries, which keep your MySQL server stuck.

    • Key buffer
    • The key buffer is very important for your indexes and you should always guarantee that there is enough free buffer space left. With the help of this the indexes of the tables are processed in memory and not on disks. So if you use them this will result in a huge performance boost.

    • Query cache
    • The query cache is used by SELECT-statements which for example get executed very often and their result changes very rarely. If they are cached the result comes out of the memory, which is much faster than from disk. So you should set the query cache value high enough to profit from this advantage.

    • InnoDB buffer pool
    • This is only available within the InnoDB storage engine and it should be set to the size of all InnoDB tables plus a margin, so that they can expand. The buffer pool is one huge advantage of InnoDB, because it reads additional data from memory too.

    Of course most of the options and variables depend on your custom purposes and vary due to that. Because of that you need to check the special output of the tools for possible problems by yourself.

I hope this tutorial gave you some hints and tips to optimize your MySQL server. If you have feedback, regards, corrections or questions please let me know and do not hesitate to comment!

If my time permits and there is some demand, I will write some posts on more specific topics of this post too.

MyISAM vs. InnoDB

Before talking about MySQL performance tuning, it has to be clear which MySQL storage engine should be taken. I will talk about the two most popular storage engines: MyISAM and InnoDB.

I recommend at least basic knowledge of MySQL to understand most of the things explained in the following part.

At first it is important to mention that there is no storage engine which is best of all. Every storage engine has its advantages and disadvantages, but if you clarify your needs correctly you will find the one that fits the best.
In fact you do not have to use one storage engine for your whole database, but you are able to combine the advantages by using both of them for different tables. So probably the way to the best performance and usability is using MyISAM and InnoDB!

Description of the most important MySQL storage engines:

  • MyISAM
    1. Features
    2. This storage engine is used with simple and easy database models and purposes in general. For me the most important feature of MyISAM is full-text search.

    3. Advantages
    4. It is the default storage engine of MySQL, because its simplicity is a great advantage. You do not have to take care of any complex database models and can easily create simple table designs.
      Another important point is the full-text indexing, already mentioned as feature. You are able to use this for search engines in forums, websites or web-shops.

    5. Disadvantages
    6. Due to the simplicity there are many things which MyISAM lacks, even if it is an advantage. MyISAM is missing of data integrity features such as foreign keys.
      Also the missing transactions feature is bad, because queries can mess up your complete table content!
      Another negative point is that this storage engine does table locking. This will surely reduce performance with WRITE-heavy tables.

    7. Fields of use
    8. MyISAM is the best storage engine for beginners or simple purposes, but I do not recommend using it with WRITE-heavy tables, due to table locking and the lack of data integrity and transactions.
      All in all MyISAM is mostly considered to be used in relation with tables of websites, content management systems, blogs, small search engines and so on. As you can see, these examples are all READ-heavy and so it is mostly used in this field.
      Of course this a general statement which depends on the size of the software or on specific tables. There are some cases on which other storage engines have better performance results with SELECT-statements, but this is not the standard.

  • InnoDB
    1. Features
    2. This storage engine is used with more complex database models and purposes in general. There are three main features which InnoDB has: Relational database design with foreign keys, transactions and row-level locking.

    3. Advantages
    4. In my opinion the most important advantage of InnoDB is the transaction feature. With this you can ensure the correctness of your data, even if a data manipulation query gets interrupted.
      Besides it supports row-level locking within tables, which is very important for the performance of WRITE-heavy tables.
      Another advantage is the data integrity with the help of foreign keys. With this you can build relational database models, needed for complex use.
      Moreover InnoDB not only keeps indexes, but also frequently accessed data in memory, what is possible with the buffer pool. Of course this reduces I/O on disks and due to this increases perfomance a lot!

    5. Disadvantages
    6. In opposite to MyISAM this storage engine does not support full-text indexing, which prevents you from doing full-text searches.
      Due to the fact that InnoDB maintains data integrity, designing database concepts will be more time-consuming.
      Another thing is that it also needs much more system resources, especially RAM. Only with this it is possible to guarantee good performance.

    7. Fields of use
    8. To summarize InnoDB is a storage engine for advanced users with databases which contain sensitive data.
      It is recommend to use it with WRITE-heavy databases, because of the transaction feature and data integrity.
      Nowadays you can use InnoDB for READ-heavy databases too, because the times of slow performance are really gone. But this gets only important with huge applications or websites with huge count of visitors and changes.

I hope the differences and advantages of both MyISAM and InnoDB get clear after reading this article. If you have feedback, regards, corrections or questions please let me know and do not hesitate to comment!

Besides this was the first step to and the base of my MySQL performance tuning post, which will come soon.

HowTo: Install Percona Server

As I promised in the last post, I am going to show you how to install the MySQL-Server solution of Percona.

There are two ways of installing Percona Server: Via package manager or from source. Maybe some of you think that it is not needed to install Percona Server from source and they are right. It is not really needed, but it has some advantages which you will see later on. For the sake of completeness I will show both ways.

This HowTo is based on my personal purposes and needs, so there are other possibilities to get Percona running for sure.
My favourite operation systems are Debian and Ubuntu and I currently use Percona-Server v5.5.15-21.0, but will probably upgrade in some weeks.

I recommend at least basic knowledge of MySQL and UNIX to understand most of the things explained in the following part.

The two ways of installing Pecona Server:

  1. Installion via packet manager
  2. The only thing you have to do is getting the signed key of Percona and adding the repositories to your source list. With this you are able to install the software automatically via package manager.
    You will need root access to do following actions.

    # Get the key
    $ gpg --keyserver hkp:// --recv-keys 1C4CBDCDCD2EFD2A
    $ gpg -a --export CD2EFD2A | sudo apt-key add -

    # Add the repositories
    # Please replace $RELEASE with your current OS release below
    $ echo -e "\n# Percona Server\ndeb $RELEASE main\ndeb-src $RELEASE main" >> /etc/apt/sources.list

    After that you are ready to install Percona Server yet!

    $ apt-get install percona-server-server-5.5

  3. Installation from source
  4. This version is more complex, but you have the possibility to install the application for custom purposes.
    You will need root access to do following actions.

    # Add mysql user
    $ useradd -s /bin/false -b /opt/mysql -d /opt/mysql -m mysql

    # Install MySQL client
    $ apt-get install mysql-client-5.1

    I prefer installing the client previously, because it already creates the MySQL configuration files like the my.cnf in /etc/mysql/ which is not created by this installation method. Indeed Percona provides different versions of my.cnf files for different purposes, but I will topic this in another tutorial.

    Now we can start with the basic installation.

    # Install required packages
    $ apt-get install automake libtool g++ ncurses-dev bison

    # Get and extract Percona source
    $ wget
    $ tar xvfz Percona-Server-5.5.15-rel21.0.tar.gz
    $ cd Percona-Server-5.5.15-rel21.0

    # Prepare build
    $ sh BUILD/
    $ ./configure --without-plugin-innobase --with-plugin-innodb_plugin --prefix=/opt/mysql

    # Create directory for logging
    $ mkdir /var/log/mysql
    $ chown mysql:mysql /var/log/mysql

    I prefer to install MySQL in /opt/mysql, so you can specify this with the –prefix option. The two other options trigger the usage of XtraDB instead of the build-in InnoDB storage engine. I wrote something about this in my last article of Percona Server. For increasing InnoDB performance it is very important to use this!

    # Build the sources
    $ make -j
    $ make install

    # Install basic database
    $ cd /opt/mysql
    $ ./scripts/mysql_install_db --user=mysql --basedir=/opt/mysql --datadir=/opt/mysql/data --verbose --log-error=/tmp/mysql.error.log --defaults-file=/etc/mysql/my.cnf

    # Set MySQL user privileges
    $ chown -R mysql:mysql /opt/mysql

    If you followed these steps correctly you should have a database which is almost ready to run. We will now create some files to be able to start and stop MySQL server easily and provide correct startup and shutdown behaviour with OS actions.

    # Use Percona support files
    $ echo "/opt/mysql/support-files/mysql.server start" > /etc/init.d/mysql_start
    $ echo "/opt/mysql/support-files/mysql.server stop" > /etc/init.d/mysql_stop
    $ chmod 755 /etc/init.d/mysql_*

    # Link them to rc directories
    $ ln -s ../init.d/mysql_start /etc/rc2.d/S19mysql
    $ ln -s ../init.d/mysql_stop /etc/rc0.d/K21mysql
    $ ln -s ../init.d/mysql_stop /etc/rc6.d/K21mysql

    The last thing you have to do before startup are small adjustments to your MySQL configuration. There have to be some necessary changes to get this working correctly. The advantage of my.cnf is that it has an include path for custom configuration files. So let us use this due to the fact that the content of these files overwrite my.cnf options.

    # Add custom paths
    $ echo -e "[mysqld]\n\nbasedir = /opt/mysql\ndatadir = /opt/mysql/data\n" > /etc/mysql/conf.d/mysql_additon.cnf

    # Required XtraDB config
    $ echo -e "innodb_file_per_table = 1\ninnodb_file_format = barracuda" >> /etc/mysql/conf.d/mysql_additon.cnf

    I just set the correct base- and data-dir of MySQL, because I prefer this setting. It is up to you how handle this. The XtraDB configuration settings I did are really important! The first one (innodb_file_per_table) is set to 1 and used for reducing file access and therefore reducing I/O. This becomes more important if you have slow disks, but is recommended by me anyways. The second one (innodb_file_format) is set to barracuda, which is the only supported table format for XtraDB.

    Now you should be able to start and stop your MySQL server with following commands:

    # Start
    $ /etc/init.d/mysql_start
    # Stop
    $ /etc/init.d/mysql_stop

I hope this tutorial helped you to install and configure your Percona Server. If you have feedback, regards, corrections or questions please let me know and do not hesitate to comment!

Keep in mind that there will follow guides for MySQL performance tuning and a backup solution for InnoDB and MyISAM tables.

Percona Server

A very interesting topic of server administration is the choice of a nice MySQL-Server for high performance purposes and solutions.
Many people underestimate this choice, which is surely a huge mistake! The correct research before setting up your database server is the first step into a really good direction.

Let us talk about the MySQL-Server solution of Percona a bit. This is my personal choice and I will give you some basic background information and nice to knows about it.

I recommend at least basic knowledge of MySQL to understand most of the things explained in the following part.

Most important reasons to choose Percona instead of “standard” MySQL solution:

Before I will go into detail, I want to show you a graphic from which shows where the story goes.

  1. Stability

  2. Stability here does not mean that the server is free of crashes, because you can truly say that this counts for all MySQL versions too. It means the stability on which Percona answeres database questions or executes queries and prepared statements. This really is a must have if you want to run powerul applications without lags or huge overload. Of course the benefit depends on the usage of your database server (READ- or WRITE-heavy), but this can never be a disadvantage. In the graphic above you can clearly see the amazing difference between MySQL- and Percona-Server!
    In my experience, with WRITE-heavy databases, it is really needed to guarantee a stable and consistent service.

  3. Performance
    • Over-all
    • As you can see in the graphic above and read in the article on Percona site, it is 40% faster than MySQL! To repeat this: Without doing anything you simply get much more efficiency! Now imagine this combined with Server SDDs and more performance changes you did manully. I think every additional word is wasted here.

    • InnoDB/XtraDB
    • As if the over-all performance was not enough, all InnoDB users will be happy to hear that Percona has been doing a great job in tuning InnoDB perfomance too. They developed a high performance InnoDB clone, called XtraDB. You can see the huge performance impact in following graphic from

There are more reasons to use Percona Server of course, but I think the most doubts are killed by explaining stability and performance advantages above. I hope you enjoyed reading this and maybe switch to Percona Server too. If you have feedback, regards or corrections please let me know!

Any time soon a tuturial for installing and setting up Percona will follow. Additionally I will show you how to optimize and tune your Percona- or MySQL-Server on your purposes in another tutorial.