MySQL Server Performance Tuning with Tips for Effective Optimization
MySQL, being the most popular database relational database management system, still requires optimization from time to time. More than that, in case of big and complex data sets, regular optimization actions are essential for proper system performance.
MySQL performance optimization generally involves configuring, profiling, and monitoring performance at several levels. To tune MySQL performance, you do not necessarily need to have extensive expertise and a deep understanding of SQL.
In this article, we will take you through the major performance tunning techniques so that you could ensure the stability, reliability, and speed of your database-driven applications.
Check recommended hardware and software requirements for MySQL
The first thing to be done, especially if you are an owner of a low-end PC, is to check the optimal hardware and software requirements for MySQL, as hardware limitations may have a significant impact on performance.
Minimal MySQL database server hardware requirements (for versions 5.7 - 8.0):
- 1Ghz processor
- 512MB RAM
- Hard disk space depending on the size of the database
It also stands to mention that it is better to use the most current official version of MySQL if possible.
Memory, disk, and CPU usage optimization
At the hardware level, you can take a bunch of actions to improve hardware and software resources.
Disk space If you're using a traditional hard disk drive (HDD) and looking for a performance enhancement, you should consider upgrading to SSD. The official MySQL documentation does not explicitly denote the disk space or memory settings required for running MySQL server efficiently as they primarily depend on the size of the potential database or databases. However, it would be a good idea to monitor your disk performance, using the sar and iostat system performance tools, for example. If disk usage is significantly higher than the usage of other resources, you should definitely add more storage or upgrade to faster one.
RAM Lack of memory can seriously affect database performance as well. It may seem trite, but if your server is regularly running low on memory and RAM Disk performance is not satisfying, it is worth adding more memory. When you run out of RAM, MySQL server caches physical memory which slows down performance. Thus, MySQL memory optimization is extremely important.
CPU MySQL CPU usage optimization should start with careful analysis of the MySQL processes taking place on your machine and the percentage of processor usage they require. CPU is not cheap to upgrade, however, if it is a bottleneck, an upgrade will be necessary.
Internet connection Network is a crucial part of MySQL infrastructure and it is important to trace and analyze network traffic to make sure you have sufficient resources to manage your workloads. Make sure you have a good and stable Internet connection for your MySQL server running properly.
Tools for software performance tuning
As we have already mentioned, you can optimize MySQL performance at the hardware and software levels. Let's now look at MySQL software performance tuning.
MySQL performance tuning in terms of software involves configuring MySQL server options, increasing the performance of MySQL queries, tuning MySQL indexes, switching to the MySQL InnoDB storage engine, etc. Let's consider all these in detail.
MySQL index usage for performance
Proper indexing for performance enhancement is not easy and requires a certain level of expertise, yet it is one of the best performance improvements you can make to your database.
MySQL uses indexes as a book index or roadmap to quickly find values for a given query. Without indexes, MySQL will scan the entire table row by row to find the relevant data. Thus, index optimization is aimed at speeding up data retrieval. Indexes are not visible to users and contain information about where the actual data is stored. It is also worth noting that MySQL index length for InnoDB tables has limits depending on the row format.
MySQL indexes are extremely useful for large datasets and index tuning is the right thing to do if your database is growing quickly. Indexes are particularly beneficial for the following operations: finding the rows matching a WHERE clause, retrieving data with JOINs, data sorting and grouping with the help of ORDER BY and GROUP BY.
So why not then insert as many indexes as you can? That would be a bad idea—unnecessary indexes occupy space and waste time of the system not to mention that they also add cost to queries as indexes need to be updated. So you have to find the right balance to achieve the optimal MySQL index usage.
Improve performance with InnoDB
One of the first tuning tips for those having a heavy load on their database would be to try switching to InnoDB from the MyISAM storage engine. Having a clustered index, with data in pages and consecutive physical blocks, InnoDB has better performance for large volumes of data as compared to MyISAM.
InnoDB also boasts a rich set of variables and advanced settings that can be configured to improve MySQL performance even further. InnoDB performance settings are more extensive and thus there are more ways to tune InnoDB for higher performance as against tuning MyISAM.
MySQL query optimization
Now let's have a look at how to optimize MySQL query for better performance and speed. For those who want to enhance MySQL queries, it would be a good idea to follow the following optimization techniques.
Add indexes to columns used in WHERE, ORDER BY, and GROUP BY clauses
In this way, you will increase the performance of MySQL query as MySQL server will fetch results from a database significantly faster.
Specify the necessary columns in the SELECT statements
Try to avoid using the SELECT * FROM as it retrieves all the columns of the table and thus causes the additional load on the server and slows down its performance.
Make it a rule to always specify the columns in the SELECT statements.
Use DISTINCT and UNION sparingly
Another good tip for query tuning is to use DISTINCT and UNION operators only when necessary as the queries with them lead to server overhead and generally increase
the response time. Consider replacing UNION with UNION ALL and DISTINCT with GROUP BY to bring more efficiency to the process.
Avoid using wildcards at the beginning of LIKE patterns
MySQL queries with LIKE operators often lead to the server performance downturn so they should be used carefully. MySQL can not use indexes when the LIKE
pattern starts with a wildcard, for example, '%xyz', and performs a full table scan in this case. You should bear this in mind when optimizing MySQL queries and
try using 'xyz%' instead whenever possible.
Use INNER JOINs instead of OUTER JOINs
Use OUTER JOIN only when necessary. MySQL does much more work fetching the results for OUTER JOINs as compared to INNER JOINs. We recommend you to check the performance
of your JOIN queries and in case it is not satisfying—start converting your OUTER JOINs into INNER JOINs when possible.
MySQL JOINs optimization can lead to dramatic
performance improvement.
Tune server options to boost performance
Now let's focus on how to optimize MySQL server options in terms of performance tuning. For this, you will need to adjust the configuration file (my.cnf/my.ini).
innodb_buffer_pool_size
This parameter specifies the amount of memory allocated by MySQL to the InnoDB buffer pool. The recommended value for this parameter is 70-80% of available
memory. The larger your data sets are the larger the value should be.
max_connection
This parameter defines the maximum permitted number of simultaneous client connections and has the default value of 151. In order to avoid getting the "Too many connections"
error, the value can be increased. However, bear in mind that too many open connections can affect performance.
query_cache_size
This parameter sets the total amount of memory allocated to the query cache. The optimal value for it depends primarily on your working case and needs to be
determined tentatively. The idea is to start very small—10MB—for example, then increase in small increments to 100-200MB. Adjusting query_cache_size,
remember to enable query cache (query-cache-type ON). Note that large query cache size can result in a serious performance decrease.
innodb_io_capacity
This parameter specifies the number of I/O operations per second allowed for the tasks performed in the background and has the default value of 200. Generally, the
value around 100 is suitable for average-level hard drives, while for faster and more modern storage devices higher values will be advantageous.
innodb_log_file_size
This parameter specifies the size in bytes for each MySQL redo log file in a log group and has the default value of 134,217,728 (about 128 MB). The innodb_log_files_in_group
parameter in its turn specifies the number of log files in the log group and has the default value of 2.
In case the innodb_log_file_size value is small for your workload and your application is write-intensive,
we recommend increasing it. However, too large innodb_log_file_size will increase the crash recovery time. So you will have to find its optimal size.
MySQL profiling and query optimization with
dbForge Studio for MySQL
dbForge Studio for MySQL comes with an advanced MySQL profiler that allows collecting the fullest statistics about executed queries, spotting slow queries, and troubleshooting performance issues of any kind.
With dbForge MySQL performance tuning tool, you can:
- Optimize queries with the EXPLAIN plan
- Monitor session statistics
- Compare query profiling results
- Identify the most expensive queries
MySQL performance optimization rules
- Always check the result of your optimization efforts on a testing environment
- Never optimize without benchmarking
- Optimize tables
- Only change one thing at a time
- Add performance monitoring to your daily routine
- Document the results