Author: Robert Agar
The tremendous popularity of the open-source relational database solution known as MySQL shows no signs of slowing down. As of June 2020, the only solution used more often than MySQL was Oracle. The widespread acceptance of this database platform is spurred on by its low cost of ownership, ease of use, and a large community of experienced users.
What this means for database professionals is that they are very likely to be exposed to MySQL systems as they navigate their careers. There are millions of MySQL databases out there, and if you are a DBA, you will probably be tasked with maintaining some of them. Your responsibilities will include taking backups, creating user credentials, and tuning the database so it performs efficiently. Sounds like exactly what you signed up for, right?
Whether you have extensive experience with MySQL or are approaching your first system with some trepidation, there are always new tricks that you can learn that will help you optimize database performance. An instructive IDERA whitepaper is available, suggesting several methods that can be used to improve the responsiveness of your MySQL databases.
Configuration Changes to Improve Performance
We will cherry-pick some of the most relevant information from the whitepaper and hope that you will take the time to read it in full and add its tips to your tuning toolbox. With the exception of the storage engine, implementing these tips is done by modifying the MySQL configuration file.
Choosing the storage engine
The two storage engines that are used most often in MySQL implementations are InnoDB and MyISAM. There are others available that lend themselves to specific scenarios. Since MySQL 5.5, InnoDB is the default storage engine and it has several advantages over MyISAM. DBAs should keep this in mind if they have the option to use either engine. The benefits of using InnoDB include:
- Support for low-level locking which aids performance;
- Transaction support for financial applications;
- Support for relationship constraints;
- Efficient management of indexes and base tables with a memory buffer pool.
The Slow Query Log
The log is disabled by default when you install MySQL. Enabling this log file provides visibility into the slow-running queries that drag down system performance. The ability to identify these problem queries is an essential part of streamlining your system’s response time. The threshold that indicates a query is executing slowly can be set to whatever value makes sense for a particular application.
An associated parameter that can be enabled will log queries that are not using indexes. This information can be instrumental in pointing you to potential indexing problems with your database. It provides a great starting place for performance optimization.
Database connections
MySQL sets the number of concurrent database connections to 151 by default. On busy systems, this may not be enough and can result in long wait times for the database users. The max_connection parameter can be adjusted to resolve this issue if memory and disk speed is available. Try increasing this number in increments of 100 until you reach a level that satisfies user requests.
InnoDB Variables
Multiple variables impact the operation of the InnoDB storage engine. Modifying the values of these variables can substantially affect system performance.
InnoDB_O_Capacity – This variable represents the maximum number of I/O operations that can be performed per second. The default is 200 which may not be enough for busy systems. Care needs to be taken that you tune this value based on the speed of the disk subsystem in use. Setting it too high or too low can negatively impact database performance.
InnoDB_Buffer_Pool_Size – The MySQL memory buffer pool is used to cache requests and improve performance. A larger memory pool is preferred and you should strive for providing as much memory as possible. Installing MySQL on a separate server is recommended so the maximum amount of memory can be allocated to the storage engine.
InnoDB_Log_File_Size – The default for this important parameter is set to 48 MB which is too small for most databases. Tuning this value correctly can result in major performance gains. This parameter controls how often a checkpoint flush is performed. Reducing the occurrences of checkpoint flushes improves performance. You should aim for a value that results in no more that one flush per hour.
More parameters are discussed in the white paper. They all contribute to streamlining your MySQL systems. It’s worth a read if you are having issues with database performance.
Managing MySQL Databases
IDERA’s SQL Diagnostic Manager for MySQL is a comprehensive monitoring tool that helps you identify performance issues in your MySQL environment. You can monitor and generate alerts on problems with your cloud or on-premises MySQL and MariaDB instances as well as track changes to configuration files. The tool provides the visibility into the inner-working of your databases that you need to keep things running smoothly.
Some of the specific issues addressed by this monitoring platform include the ability to monitor disk space and locks so you can provision storage appropriately. Security vulnerabilities and tuning opportunities are identified and expert advice is offered to alleviate the problems. Managing user access to the MySQL environment can be accomplished from a unified dashboard that increases the productivity of the database team.
Armed with the knowledge provided by SQL Diagnostic Manager for MySQL and the tips in the whitepaper, you will be well-equipped to address performance issues affecting your databases. They will help you find a path toward optimizing your systems and eliminating slow response time.
