MySQL database administrators are tasked with maintaining high-performing systems that address the needs of their user community. Other activities like ensuring security and creating backups are important, but degraded performance is an issue that draws attention from multiple entities. Everyone from occasional users to upper management will get involved when systems start slowing down.
MySQL is a complex database management system with many variables and configuration options. A large set of metrics is available from which DBAs can obtain insight into database operation and performance. They present plenty of rabbit holes that a DBA can go down that may not produce the desired level of results. Not all metrics are equally important for performance tuning.
An IDERA whitepaper providing extensive information about what to focus on when monitoring MySQL databases is recommended reading for DBAs responsible for managing the platform. In this post, we will look at some of the highlights of the paper that may be point the way toward performance improvements. We strongly suggest that MySQL DBAs take the time to read the complete paper.
Which Metrics Matter?
Metrics capture a value or characteristic of the observed system at specific times. Monitoring collects metrics regularly at set intervals so they can be compared and analyzed. Metrics can be broadly categorized into two categories: those used to identify problems and those used to investigate a problem. Collecting the right set of metrics lets you recognize potential issues and take proactive measures to prevent problems. Metrics are also essential for investigating and understanding the underlying causes of degraded performance.
Performance metrics can be further differentiated as being concerned with the work performed by a database or the resources it requires.
Work metrics
Work metrics measure the output of a database to gauge its health. These metrics furnish information about the availability of a database, how fast it is operating, and the quality of work produced. It is useful to consider four subtypes of work metrics.
- Throughput measures the amount of work done per unit of time such as the number of transactions executed each second.
- Success tracks the percentage of successful work activities. A perfect success rate would be 100%.
- Errors are usually measured in the rate of errors per unit of work. Multiple potential errors can be responsible for system problems so they need to be separated and made available for further investigation.
- Performance monitors the efficiency of system components. One of the most common performance metrics studied is latency. This metric represents the time needed to complete a unit of work.
Resource metrics
Resource metrics look at the hardware, software, and networks used by a database. They range from low-level resources like memory and disk space to high-level entities like the query cache. These metrics are important when investigating and diagnosing problems. Fours key areas are addressed by resource metrics.
- Utilization metrics provide percentages that express how busy a database is and how much of its capacity is being used.
- Saturation measures the amount of work that has been requested but is in a queue waiting to be executed.
- Error metrics are related to internal errors not uncovered with work metrics.
- Availability is the percentage of time that a database responds to queries.
The Best Metrics to Monitor
Focusing on a subset of the metrics available regarding MySQL databases produces a firm foundation for performance tuning. Here are some of the best specific work and resource metrics to monitor.
Work metrics that need to be captured include information on database, transaction, and query throughput. A monitoring effort would look at the MySQL status variables that measure questions and queries. The threads_running variable can identify queries not completing in time and slow_queries finds potential problems.
Resource metrics of importance include those related to connections such as the threads_connected, threads_running, and connections metrics. Connection errors can be measured and categorized as caused by internal to external factors. Valuable information is also available through the InnoDB buffer pool metrics. These can be seen as the output of the SHOW ENGINE INNODB STATUS command.
These metrics are accessible through scripts executed by a DBA who can then manually perform comparison and analysis on the output in an attempt to uncover issues and trends that may result in problems. This is a labor-intensive undertaking. A better solution is using a monitoring application to streamline the process.
Take Advantage of Dedicated Monitoring Tools
SQL Diagnostic Manager for MySQL is a dedicated and agentless MySQL monitoring solution that helps DBAs find and address the issues that drag down system performance. The application provides real-time monitoring across all on-premises or cloud-based MySQL and MariaDB instances. Teams can monitor slow and locked queries, database replication, and security issues with the ability to investigate the causes of any problems.
A flexible dashboard offers a unified view of all monitored servers and can be customized to display any available database setting or metrics. Alerts can be configured based on best practices and triggered by metric thresholds. Deliver alerts by email or SMS messages so the relevant staff is notified immediately if problems occur. Generate alerts on disk problems, security issues, locks, and deadlocks.
The information provided by SQL Diagnostic Manager for MySQL gives DBAs an edge when looking to improve database performance. IDERA offers a free trial of the product, so download it today and see what it can you and your database users.