A short introduction in performance optimization and debugging of MySQL databases

The following text gives you a short introduction about possibilities to optimize the speed of read and write operations in a MySQL database. Furthermore some possibilities are explained how to debug MySQL.

Hint: Generally it makes sense to use as much RAM as possible on the system because it is a lot faster than the hard drive. With more RAM it is possible to cache more MySQL data. This is a huge performance improvement and will have impact on the settings listed below.

In MySQL it is possible to change settings during runtime. These changes are temporarily and are only for the current instance of MySQL. You must change the settings again for the next instance or if the database is restarted.

But there is the possibility to make changes permanent in the my.cnf (for Unix-based systems and Windows systems) or my.ini (Windows systems only) file.

On Linux Debian based systems the my.cnf file is located in /etc/my.cnf. The my.ini file can be stored under C:\my.ini. Any other places on your system are also possible. You can get a more precise overview here.

Here are some examples for MySQL settings for development and production systems.

Settings for development-systems

Logging

The MySQL logs store all activities of MySQL (start, stop, insert, update, delete, ...). The logs can give you really useful informations about your database but writing of the logs is also slowing down the performance.

So it is recommended to think first before enabling every log possibility. Especially in production systems it is maybe not the best idea but for development it is very helpful.

The logging is disabled per default but you can enable it with the following entries in your my.cnf (my.ini):

   -The bin_log saves among other informations changes on the database, the table structures or the duration of operations.

   - The slow_query_log stores all MySQL requests where the duration tooks longer than the time set in „long_query_time“. There is no general rule for the size of the „long_query_time“. It depends how much requests to the database took to much time. But for a website for example to total time of delivery should not take longer than one second.

   - The error_log saves informations about errors occuring during the runtime of a MySQL-Server

  - The general_log could be helpful for determining requests to the MySQL-Server or to find out which users are connected to the database.

Settings for all systems

Optimizing caching for read performance

To improve the read performance of MySQL the previous mentioned settings can be used in the my.cnf (my.ini). Please consider that the query cache is disabled by default since MySQL version 5.6.

   - Query Cache: should be named "result cache" because it is caching the results of the queries (+hash, + affected tables). This improves the read performance (select operations). It caches the results of the select queries. It works fine if many identical select-queries are performed. Therefore it is useful to write many identical or nearly similar select queries or to use stored procedures. If the queries are not similar because the order of the statements is different the hash value will be different. If this is the case the cache can not be used because the previous result will not be found because of the different hash value.

The query cache is not used for insert, update or delete statements.

If the number of threads is higher than 10, the query cache becomes a bottle neck. In this case it is better to disable the query cache.

Recommendation: Monitoring of the amount of threads and the caching

   - Table Definition Cache: This cache is used to store the table definitions, so these do not have to be checked over and over again. The table definitions will be stored in the cache in the memory which makes it much more faster.

   - Table Open Cache: This is used to store the amount of tables that are open by MySQL simultaneously. This should be as high as possible.  

  - InnoDB buffer pool size: This is one of the most important setting because it reduces the read and write access to the hard drive when accessing tables. The table data is stored in the RAM and all read and write access will be handled in the RAM before the results are written physically to the disc. The RAM is much more faster than the disc - that is why this cache should get as much RAM as possible, especially if it is a dedicated MySQL server.  

Improving the write performance

   - InnoDB log file size: This setting is for the size of the innodb files iblog0 and iblog1. These files should ideally be big enough to handle 60 seconds or up to one hour of sql statements.