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:
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”.
If you want to improve MySQL database performance by preventing double buffering, make sure this setting is at O_DIRECT.
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.
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:
Keep this at “ON” in order to ensure a separate InnoDB table space for each table present in the database.
Don’t want database statistics to update constantly, and consequently, slow down read speeds? Ensure this setting is turned off, in that case.
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!