MySQL has been one of the leading open source databases for the last couple of decades, and it underpins potentially millions of applications, from tiny prototypes to internet-scale ecommerce solutions. The beauty of MySQL is that it can be tuned as the application grows. For example, you can add higher availability options like clustering without having to refactor the application.
The key to making sure that MySQL is optimized for your environment is to monitor the logs and metrics that it produces. At the very minimum, every MySQL instance produces log files that contain basic errors generated by queries and fatal errors in the system itself. There are, however, even more valuable metrics for those looking to assess the performance of MySQL. These metrics can be exposed manually via SQL queries or automatically via AIOps platforms and other monitoring products designed to improve operational intelligence within the organization.
Location of Metrics in MySQL
There are three areas that you need to pay attention to when monitoring the performance of MySQL, and each one has a different role in helping the server perform at its best.
- Server variables - This is where MySQL loads its configuration, including things like cache sizes and maximum connections.
- Sys schema - This is a series of procedures, functions, and views that gives you insight into how the MySQL database instance is running. This includes current connections, queries that are being executed, buffer sizes, and any existing locks.
- Performance schema - This needs to be explicitly enabled in MySQL, unlike sys and server variables (which are always on). It provides low-level access to all of the metrics that make up the information in the sys schema. If you need more information to get to the bottom of a performance issue, this is where you will look for those extra details. For example, you can see what individual threads are doing here instead of looking at connection and query-level information in the sys schema.
Types of Metrics to Watch
Your connections are the first thing you need to watch. A metric called “max_used_connections” will tell you the maximum number of connections to your server since it was last restarted. It’s a good idea to keep this about 10% below the limit just in case. To see the currently configured value, query “max_connections” from server variables. If you want to increase “max_connections,” all you have to do is set a higher value in the server variable – just make sure that the server has enough capacity, since each connection has its own buffers and therefore takes up a little physical RAM.
You should also watch a metric called “aborted_connections,” which will show you all of the failed attempts to connect to the server. If this number is increasing, it could indicate anything from a bad password to a DOS attack. To see what your connections are doing in real time, you can watch the active threads. For example, “threads_connected” will tell you how many connections are active, and “SHOW PROCESSLIST” will give you details about every connected thread.
Questions and Queries
Next, you should monitor how well MySQL is performing the tasks that it’s asked to do by watching the number of questions and queries. A question is the number of clients that have done some work, and a query is the raw number of queries that have actually been performed. For complex transactions, the number of queries can be drastically higher than the number of questions. In addition to the raw number, there is a server variable called “long_query_time” that defines the maximum query time (in seconds); any query that takes longer than this will be added to “slow_queries.” You can also write them to a file for additional troubleshooting.
The read-only queries are often recorded under the “com_select” variable, while the queries that normally write are split between “com_insert,” “com_update,” and “com_delete.” By tracking these numbers, you can see how read- or write-heavy your database is; this will allow you to adjust everything from buffer sizes to the type of disk that you use in order to increase performance.
It is also vital to watch how MySQL is handling its buffers (MySQL’s term for caches). A good place to start is to configure the buffer size to be about 80% of the memory that’s available on a system – assuming that the server is dedicated to MySQL. This will allow for the best overall caching performance by preventing the buffers from writing to swap spaces (which will kill performance just as quickly as not having enough memory allocated in the first place). In MySQL 5.7.5 and higher, using the metric “innodb_buffer_pool_size” is the easiest way to see and update the current value.
To see the overall status of the innodb backend, including buffer usage as well as hits and misses, you can run “SHOW ENGINE INNODB STATUS.” All of the metrics on this status page can be tracked independently, and many have underpinning configuration items that can be adjusted to improve overall performance. This status command is the best way to see a quick snapshot of the overall health of the storage subsystem used by MySQL.
Learning these metrics is a great way to help you tune the performance of your MySQL instance. However, monitoring them manually is not practical if you are trying to build trends and respond to alerts before they impact your clients. This is where continuous intelligence solutions come in. By collecting logs and actively querying your chosen metrics, Sumo Logic’s MySQL application will learn how your environment normally behaves in order to provide insights that will enable your operational support teams to resolve issues before they become problems.
Complete visibility for DevSecOps
Reduce downtime and move from reactive to proactive monitoring.