Improve MySQL Database Performance through Multiple Parameters

MySQL tuning is no trivial task – it takes some work. However, Database Administrators know that there are a few parameters through which they can greatly enhance the speed and output of the database.

If you are in search of ways to improve MySQL database performance, you will find some of the best in this blog. Each of the parameters mentioned here contain important settings that you can make changes to without much effort. Keep in mind that default values may vary according to the version of MySQL on your system. 

Performance Tuning in SQL MySQL: Main Categories

Here are the three major types of performance tuning in SQL MySQL, one of which DBAs usually focus on:

  • Hardware-based performance tuning
  • Tuning through Optimum techniques and practices 
  • Workload-based tuning

Hardware-based Performance Tuning in MySQL

Certain variables can be set according to the hardware specifications of your device. These include:

innodb_flush_log_at_trx_commit

For maximum durability, set it to “1”. If performance is your main concern, adjust this value to either “2” or “0”. However, doing so will result in lesser durability than if the value is set to “1”. 

innodb_flush_method

If you want to improve MySQL database performance by preventing double buffering, make sure this setting is at O_DIRECT.

innodb_buffer_pool_size

This size parameter is typically set within 50 to 70 percent of the overall RAM. You can proceed with tuning by checking on the buffer pool usage from time to time using a monitoring tool. 

innodb_log_file_size

The size of the file log is usually set in the 128M – 2G range. It is supposed to be sufficiently spacious to store approximately sixty minutes of logs and enable MySQL to flush processes, place checkpoints, and reorganize writes for sequential I/O. Again, refer to a tuning tool like Tosska SQL Tuning Expert (TSEM™) for MySQL® for further insight on whether or not the log file size needs to be adjusted. 

Tuning through Optimum Techniques and Practices

This category involves using the best MySQL practices for performance tuning in SQL MySQL: 

innodb_file_per_table

Keep this at “ON” in order to ensure a separate InnoDB table space for each table present in the database. 

innodb_stats_on_metadata

Don’t want database statistics to update constantly, and consequently, slow down read speeds? Ensure this setting is turned off, in that case. 

innodb_buffer_pool_instances

The recommended value for this is “8”. On the other hand, if the buffer pool size is less than 1G, then set it to “1”.

query_cache_type & query_cache_size

Disabling the query cache is considered useful in improving MySQL database performance. You can disable it by setting both query_cache_type and query_cache_size to zero.

Workload-based Performance Tuning for MySQL

This kind of performance tuning in SQL MySQL is relative; it depends on the workload, which is why additional details regarding the specific workload are needed. Thankfully, gathering such information is much more convenient, thanks to reliable MySQL graphing and tuning tools like Tosska SQL Tuning Expert (TSEM™). Tosska’s tools are designed to display an extensive range of metrics and give users insights and allocate resources accordingly. 

Experts suggest making changes to the innodb_buffer_pool_size parameter first. Consider the following metrics to decide whether this setting has to be raised or lowered – 

  • Your device’s RAM
  • Buffer pool size
  • The number of free pages available

Once this is done, you can improve MySQL database performance be observing the InnoDB Log File usage metrics; as mentioned already, the log file settings are generally adjusted in order to store around an hour of log data. If the data written exceeds the originally set capacity, then this setting has to be increased and MySQL rebooted. The query “Show engine innodb status” is useful in assessing what size will be ideal for the InnoDB log file.

If it starts to get burdensome, you can rely on Tosska’s tuning tools for MySQL. Visit our website for our top-of-the-line tools and to get in touch with our experts to know more about them!