Database performance monitoring and tuning is one of the biggest DBA responsibilities. The old maxim that if something can go wrong, it will, seems to be the general rule in the world of database administration. So, DBAs need effective tools and procedures to identify performance problems, determine the root cause of the problems, and provide assistance to optimize and tune the database environment for efficiency.
But let’s back up a minute. What is database performance? If we are going to monitor it, we should know what it is. A good definition for database performance is the optimization of resource usage to increase throughput and minimize contention, enabling the largest possible workload to be processed. Of course, there are many aspects that must be monitored in order to achieve database performance.
Which brings us to Navicat Monitor, a new database performance monitoring solution on the market from Navicat (whose other offerings I have discussed on this blog before). Navicat Monitor is an agentless remote server monitoring tool that currently can be used to monitor MySQL, MariaDB and Percona Server (with Microsoft SQL Server support upcoming on the horizon).
The server-based software runs on Windows, Mac or Linux and it can be accessed from anywhere via a web browser. Navicat Monitor can be installed on any local computer or virtual machine and does not require any software installation on the servers being monitored. Furthermore, access is supported on all of the most popular web browsers including Firefox, Chrome, Internet Explorer 11 or later, Microsoft Edge 39 or later and Safari 9.1.3 or later. Because the tool is accessed on the web it is easy to keep track of your servers around the world, any time and any place you have a web connection.
But what can Navicat Monitor do? Navicat Monitor collects pertinent database performance metrics such as CPU load, RAM usage, and many other metrics for each database instance being monitored. The product enables DBAs to quickly view all registered database instances and availability groups on a single web-based console. This makes it easy to monitor your entire MySQL/MariaDB database infrastructure from one place, regardless of the locations of the servers.
The interactive dashboard assists the DBA to quickly see how all the database instances are running. It shows all of your monitored instances with high-level summary information and the healthy status of your instances, using the familiar green/yellow/red indicators to depict the performance, availability and health of each instance. See Figure 1.
Figure 1. Navicat Monitor dashboard.
Modern DBAs are required to manage many database instances across many servers both on premises and in the cloud. Without a tool like Navicat Monitor to get access to all of these database instances through a consolidated interface assuring performance across hundreds of servers can be difficult, if not impossible to achieve.
Drilling down into an instance Navicat Monitor presents you with a slew of useful details, starting with the top 5 database and top 5 tables based on size. See Figure 2.
Figure 2. Instance Details
As you would expect from a performance monitor, you get a comprehensive view of each instance, starting with whether the system is up or down, and moving on to more detailed information such as CPU, memory and swap usage. See Figure 3.
Figure 3. More In-depth Instance Details
With Navicat Monitor, your DBAs can perform real-time analysis of your MySQL and MariaDB database servers, but you can also examine historical metrics over time. For example, Figure 4 shows CPU usage over the course of a day for a specific MySQL instance. Navicat Monitor maintains a repository to store alerts and metrics for historical analysis. The repository database can be an existing MySQL, MariaDB, PostgreSQL, or Amazon RDS instance. Using the historical data you can examine metrics and diagnose whether performance trends are deviating from expectations.
Figure 4. CPU Usage History
Alerting is also an integral capability of Navicat Monitor. DBAs can set powerful rules and get notifications via email, SMS, or SNMP. The product comes preconfigured with more than 40 fully-customizable alert policies. These rules can be customized to track the metrics and threshold most important to your environment. Navicat Monitor alerts are arranged by common themes and managed by advanced filters. DBAs can see the frequency with which each alter is triggered along with what action, if any, is needed to resolve the issue. You can even see who responded to each alert in the past. Figure 5 shows that you can view the Alert history over time for each instance.
Figure 5. Alert History
Of course, identifying performance issues is only part of the battle, but Navicat Monitor provides further assistance with advanced root cause analysis. You can drill down to uncover more in-depth information when an issue is found. Navicat Monitor includes a rich set of real-time and historical graphs that allow you to drill down into server statistic details. It gives you a detailed view of each server load and performance regarding its availability, disk usage, network I/O, table locks and more, which allows you to easily track the deviations and traffic among servers, as well as examine possible solutions and adjust your server settings.
You can even drill down to the query level, monitoring your queries in real with Navicat Monitor’s Query Analyzer feature. Query Analyzer displays summary information for all of your running queries enabling you to discover problematic queries. For example, it can quickly show you the top resource-consuming queries with cumulative execution time or the slowest queries with unacceptable response times. Query Analyzer can even help you to detect locking issues that are causing contention, such as deadlocks when two or more queries are blocking each other.
If you are tasked with managing the performance of MySQL and MariaDB take a look at Navicat Monitor. It may be able to alleviate the strain of managing multiple instances.
Note: Currently Navicat Monitor can be used to monitor MySQL 5.1.73 or later and MariaDB 10.0 or later.