As tables increase in size and more and more users come online, it becomes necessary to fine-tune the database server from time to time. The secret to knowing what adjustments to make is to perform regular monitoring. Most databases offer dozens, if not hundreds, of performance metrics that one can assess.
Database Performance Metrics
As a database administrator, the top priority is to keep the databases and dependent applications running smoothly at all times. The best weapon is the careful monitoring of key performance metrics. In a perfect world, one would want to be up-to-date regarding every aspect of the activity of the database. One would want to know how many events occurred, how big they were, when they happened, and how long they took to complete.
There is no shortage of tools that can monitor resource consumption, provide instantaneous status snapshots, and generate wait analysis and graphs. The challenge is that some metrics can be expensive to measure, and perhaps they can require much work to analyze.
This blog focuses on monitoring key performance metrics and describes how to:
- Examine the benefits of performance monitoring.
- Outline the primary performance metric categories.
- List the monitoring tools provided by MySQL
- Server variables
- Performance schema
- Sys schema
- Learn how to monitor:
- Transaction throughput
- Query execution performance
Further, this blog narrows down the field to the performance metrics that provide the most value for the effort. Also, it presents some tangible ways to capture and study them. It is by tracking the most useful metrics and reviewing them in the most informative ways that one balances paranoid over-monitoring and unforeseen firefighting crises. This blog focuses on monitoring database connections and buffer pool metrics.
Monitoring MySQL Performance
A database, including MySQL, back most applications. It is crucial to monitor databases effectively to keep databases and applications running smoothly. A good database monitoring plan can help one stay on top of:
- Performance: The main subject of this whitepaper, monitoring how the database performs can help detect bottlenecks and other issues before catastrophe strikes. Beyond helping one avert emergencies, performance metrics can assist one in deciding whether a performance increase is necessary or workable. For instance, by keeping a track record of query execution times, one could spot any suboptimal SQL statements and come up with improvements.
- Growth: Observe requirements in terms of users and traffic. Database usage needs to evolve faster than expected.
- Security: Ensure that one applied adequate security measures.
Database Metrics
Before going through identifying metrics to follow, perhaps one should start at the beginning and ask, What are metrics?
Metrics capture a value about the systems at a specific point in time. An example is the number of users logged into the database.
Therefore, systems collect metrics at regular intervals (such as once per second and one per minute), to monitor a system.
There are two essential categories of metrics: those that are most useful in identifying problems and those whose primary value is in investigating issues. This blog covers what data to collect. So, one can:
- Recognize potential issues before they occur.
- Investigate and understand performance issues.
Beyond metrics, there are other types of database monitoring that this blog does not address. These include tracking events and security.
Database Monitoring
How often one monitors different aspects of the database depends on how mission-critical it and the applications it supports are. A failure or disruption may cause a severe impact on the business operations and organization, or perhaps even result in catastrophe. Then, one needs to be on top of both performance and security issues at all times. One can reduce the need to monitor continually the performance dashboard to a weekly inspection. Accomplish this by setting up alerts to inform one of the critical issues in real-time.
Database Monitoring Tools
Specialized tools that provide real-time and periodic alerts should monitor database performance metrics. Real-time alerts are a must for mission-critical databases and databases with sensitive information susceptible to attack. So, one can take care of urgent issues as soon as they occur. Real-time preventive measures can protect the database from certain types of attacks, even before one has time to respond.
The database administrators, Information Technology operations staff, and users shared responsibility in performance monitoring since some factors that affect database performance lie beyond the database itself. It also makes sense to include some application developers informed. So, they can investigate the application side of things.
Database administrators need not monitor the applications that interact with the database. However, they must possess a general understanding of how developers implement applications and the architecture of these applications.
Performance Metrics
Work Metrics and Resource Metrics
The previous section described the two main uses of database metrics: problem identification and problem investigation. Likewise, there are two essential categories of metrics that pertain to performance: work metrics and resource metrics. For each system that is part of the software infrastructure, consider which work metrics and resource metrics apply and available. Also, collect whatever one can. One need not monitor every metric. However, some metrics may play a more significant role once one identifies performance issues (that is, during problem investigation).
The next two sections cover each of the two performance metric types in more detail.
Work Metrics
Work metrics gauge the top-level health of the database by measuring its useful output. One may break down work metrics into four subtypes:
- Throughput: The amount of work the system is doing per unit of time. Systems usually record throughput as an absolute number. Examples include the number of transactions or queries per second.
- Success: Represents the percentage of work that the system executed successfully (that is, the number of successful queries).
- Error: Captures the number of erroneous results, usually expressed as a rate of errors per unit of time. This yields errors per unit of work. The system often captured error metrics separately from success metrics when there are several potential sources of error, some of which are more serious or actionable than others.
- Performance: Quantifies how efficiently a component is doing its work. The most common performance metric is latency. Latency represents the time required to complete a unit of work. One can express latency as an average and as a percentile, such as 99% of requests returned within 0.1s.
The above metrics provide high-level but telling data that can help one answer the most critical questions about the internal health and performance of a system. That is to say:
- Is the database available and doing what we designed it to do?
- How fast is it producing work?
- What is the quality of that work?
Resource Metrics
Resources are hardware, software, and network components that the database requires to perform its job. Some resources are low level, such as physical components like CPU, memory, disks, and network interfaces. One can also consider higher-level resources such as the query cache and database waits as a resource and therefore monitor these higher-level resources.
Resource metrics are useful in helping one reconstruct a detailed picture of the state of the database, making them valuable for investigation and diagnosis of problems. Resource metrics cover four key areas:
- Utilization: The percentage of time that the database is busy or the percentage of the capacity of the database in use.
- Saturation: A measure of the amount of requested work that the database cannot yet service and waits in the queue.
- Errors: Represents internal errors that may or may not be observable in the database’s output.
- Availability: Denotes the percentage of time that the database responded to requests.
Both work and resource metrics include two other types of metrics:
- Work metrics:
- Database, transaction or query throughput
- Query execution performance
- Resource metrics:
- Connections
- Buffer pool usage
Throughput Metrics in MySQL
Throughput measures the speed of a database system. Systems typically express throughput as the number of transactions per second. Consider the following differences:
- Write transactions versus read transactions
- Sustained rates versus peak rates
- A 10-byte row versus a 1000-byte row
Because of these differences, it is best to measure:
- Database throughput (for the database)
- Transaction throughput (for any operation)
- Query throughput (for query execution)
Download our cheat sheet for MySQL performance tracking with code examples here!
Monyog
Monyog, a.k.a. SQL Diagnostic Manager for MySQL, is an industry-leading MySQL monitoring tool that helps database administrators effortlessly identify and solve performance issues in physical, virtual, and cloud environments.
Learn how Monyog can make monitoring your cloud-based MySQL databases easier, then start for free for 14 days.
SQLyog
Looking for more MySQL tools for the cloud? Consider SQLyog–it’s as versatile as it is powerful. With SQLyog, you can use a single tool to manage MySQL across physical, virtual, and cloud environments.
Get reliable database backups and data synchronization with SQLyog: a powerful MySQL GUI tool that helps database developers automatically compare and synchronize schema, schedule backups and queries, and so much more.
Try SQLyog Ultimate free for 14 days. See how it helps your database developers do higher quality work in less time.
