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.