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.
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:
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.
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:
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:
Beyond metrics, there are other types of database monitoring that this blog does not address. These include tracking events and security.
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.
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.
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 gauge the top-level health of the database by measuring its useful output. One may break down work metrics into four subtypes:
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:
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:
Both work and resource metrics include two other types of metrics:
Throughput measures the speed of a database system. Systems typically express throughput as the number of transactions per second. Consider the following differences:
Because of these differences, it is best to measure:
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.
