A key part of keeping your MySQL database running smoothly is the regular monitoring of performance metrics. In fact, there are literally hundreds of metrics that can be measured that can enable you to gain real-time insight into your database’s health and performance. To this end, several MySQL monitoring tools have been developed to make performance monitoring easier. In today’s article we’ll use Monyog to measure a few of the more important metrics.
Contents
Getting Started
As I said in the introduction, there are quite a few performance monitoring tools for MySQL to choose from, but the one I use is called Monyog. Developed by Webyog, Monyog not only monitors one or more MySQL servers, it also advises you on how to tune the databases, find problems, and then fix them before they can become serious problems or costly outages.
Monyog utilizes “agent-less monitoring”, thus eliminating the need to install and maintain monitoring agents, which can be a complex administration task by itself. Instead, Monyog uses a normal MySQL connection for monitoring MySQL. To collect OS data from remote servers, Monyog uses SSH for Linux. This means Monyog can collect all monitoring data by using remote connections. This is a huge advantage that sets Monyog apart from all other MySQL monitoring and advisory tools because it doesn’t force you to install any components on your MySQL servers, making it totally unobtrusive. It utilizes no CPU cycles or memory on servers, leaving them free to do what they were meant to do. Sometimes you don’t have admin rights to the server box.
To install Monyog, simply navigate to the product page on the Webyog site, and click the “Download free trial” button. Monyog is supported on Microsoft Windows (2003 and higher) and on Linux (installers are based on the [originally Red Hat] .RPM standards along with a .tar package for Ubuntu and Debian systems). Keep in mind that “supported platforms” only refers to the platforms on which Monyog itself must be installed and not the platform that MySQL is installed on. If there is a MySQL server running, Monyog can connect to it!
Here are the Windows installation instructions.
Here are the Linux installation instructions.
Monitoring Performance
Monyog comes with 600+ pre-configured monitors and advisors. With so many measurable metrics to choose from, let’s narrow down the field to cover a couple of areas of performance and resource utilization:
- Query execution performance
- Connections
In the next sections, I’ll show you how to obtain the above metrics using Monyog’s simple and intuitive UI.
Query Execution Performance
In my 18-or-so years of supporting mission critical applications, the number one database-related complaint from users was that queries are running too slowly. A database’s work is running queries, so your first monitoring priority should be making sure that MySQL is executing queries as expected.
Monyog’s simple UI gives us the ability to monitor more servers with a clean interface, either from log files or via Real-time monitoring. Since this article is on Real-time monitoring, that’s what we’ll do here. Note that Real-time monitoring is best for short bursts of debugging because it does place some overhead on your database server(s). To show performance metrics:
- Click the (Real-time) Clock icon on the left-hand side of the screen.
- On the next screen:
- Select a server to monitor.
- You may then choose to start a new session or load a saved one. ( I already saved one )
Here is the Monyog real-time query monitoring screen:
Real-time Query Monitoring Screen
Monyog’s Query Analyzer feature is one of my favorites because it’s simple UI helps me identify potential bottlenecks quickly and easily. The Average Latency (shown above) provides the time taken by each query to execute.
If you’re not happy with a query’s performance, you can run the EXPLAIN command by clicking on the query and select the Explain tab on the Query Details screen:
Query Details screen
Connections
In MySQL, the global variable max_connections determines the maximum number of concurrent connections to MySQL. Generally, you’ll want to make sure that this variable is set to a high enough value to support your user base. Moreover, it’s important to design your applications in such a way that a MySQL connection is kept open for a very short period of time. You may also want to try pooling connections or switch to persistent connections, for example, by using mysql_pconnect() instead of mysql_connect(). Both these actions will help reduce the number of active MySQL connections.
Monyog reports the max_connections variable as the “Max allowed” metric on the Current Connections screen. It also divides that figure by the number of open connections to produce the Connection usage as a percentage:
Current Connections Screen
In addition to monitors, Monyog also supplies hundreds of advisors that automatically examine MySQL server’s configuration, security, and performance levels. These identify problems and provide the MySQL DBA with specific corrective actions. Here on the Connection History screen, we can see that the RDS –Dev server’s Percentage of max allowed reached connections is getting dangerously high:
Connection History Screen
Clicking on the monitor name brings up the Monitor Details screen. It contains many useful details about the monitor, including Advice text:
Monitor Details Screen
Pricing
Monyog is available in three flavors: Professional, Enterprise, and Ultimate, all of which come with an unconditional 90-day money back guarantee and free upgrades for 1 year. The cost of a perpetual license for one MySQL server with premium support runs $199 for Professional, $299 for Enterprise, and $399 for Ultimate. From there, additional licenses may be purchased at discounted rates:
- 2 MySQL Servers – up to 17% off
- 5 MySQL Servers – up to 25% off
- 10 MySQL Servers – up to 38% off
- 25 MySQL Servers – up to 50% off
- 50 MySQL Servers – up to 55% off
All of the above packages are also available without premium support, lowering the cost to $139 for Professional, $199 for Enterprise, and $299 for Ultimate.
Visit the Monyog website for more information on pricing.
Conclusion
In this article we utilized Monyog to measure a couple of the more important database metrics. In doing so, we saw how a professional grade monitoring tool can help keep our MySQL database(s) running smoothly. In a future article, we’ll examine some other important metrics.
[“Source-databasejournal”]
