A short introduction in performance optimization and debugging of MySQL databases

Philip Bönisch

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):

[mysqld]
log-bin --> bin_log will be activated
log --> general_log will be activated
log-error --> error_log will be activated
log-slow-queries --> slow_queries_log will be activated

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

log_bin=/var/log/mysql/mysql_bin.log → path where the log-file should be saved

   - 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.

log_slow_queries = /var/log/mysql/mysql-slow.log → path where the slow_queries_log-file should be saved
long_query_time = 2 → determines at which execution time the logging should log

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

log_error=/var/log/mysql/mysql_error.log → path where the error_log-file should be saved

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

general_log_file = /var/log/mysql/mysql.log → path where the general_log-file should be saved

 

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.

query_cache_type = 1 → enabled the query cache
query_cache_limit = 1M → maximum size of a single result to be cached
query_cache_size = 32M → maximum total size of all cached results

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.

show global status like 'thread%'; → shows how many threads are running.

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_definition_cache = 400 → should be big enough to store the whole amount of tables in all databases on the server.
 
select count(*) from information_schema.tables; → show the total amount of tables on this server

   - 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.  

table_open_cache = 400 → how much tables can MySQL open simultaneously.
                         Calculation: Amount of simultaneous MySQL connections * 1 to 20 Tables

  - 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.  

innodb_buffer_pool_size = should be 50% – 80% of the available RAM

 

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.

Innodb_log_file_size = 1MB – 4GB → should be as big as possible. But the bigger this size is the more time it takes afterwards for recovery if there is a crash.

 

There is a general rule to calculate the size of the InnoDB log files:
innodb_os_log_written / uptime = ?kb per second
kb per second * 60 seconds * 60 minutes = X
consider a possible peak, so also add a high rate, default 5
=> kb per second * 60 seconds * 60 minutes * peak (5) = X

 

To change the settings on a server please consider the following steps:
  1. service mysql stop
  2. move or delegate the files iblog0 and iblog1
  3. change the my.cnf (my.ini) settings
  4. service mysql start - the both log files should be restored again

 

   - innodb_flush_log_at_trx_commit: This setting determines when the log buffer should be written from RAM to disc.

possible values:

   0: once per second (good if there is much traffic) - the log buffer will be written once per second into the log file and the cache in the RAM is cleared. Exception: if there is a transaction in progress.
   1: (default) the log buffer will be written to the log file on every commit of a transaction and the buffer will be cleared in RAM.
   2: the data will be written simultaneous to the RAM log buffer and the log file on disc. The log buffer in the RAM will be written to disc on every commit and the buffer in the RAM will be cleared once a second. 
 

Conclusion

The default settings of MySQL are not the best choice. You should modify the settings fitting to your environment depending if it is a dedicated database server, the size of the RAM etc. You should also consider the type of environment, a dev or test environment probably does not need as much RAM as the production environment where much more users are accessing the system at the same time. It is also recommended to optimize the production environment for read and write performance. A test or development system is maybe not the first priority, here the system needs to be optimized for debugging purposes. Another important setting is the consideration of Master/Slave setups and the optimization of the replication. Also keep in mind to monitor your production environment, especially the most important values like the amount of simultaneous threads or the usage of cahces and RAM. So you can see easily if there is a bottleneck that needs to be improved. As you can see it is important to keep always an eye on you database(s).

 

Our expert

Philip Bönisch

Any questions or input? Reach out to our experts!

Send e-mail

Philip Bönisch, a home-grown talent, has been working at Cocomore as Senior Frontend Developer since 2009. Due to his versatility he also works in the backend as Drupal-developer and develops extensive Web-applications.
Philip in 3 (+1) words: There are only solutions.

Related Content