未加星标

Real-time MySQL Performance Monitoring

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二03 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

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.

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!

Related Articles Applying MySQL’s GROUP_CONCAT Function to Multiple Columns Why You Should be Using the MySQL GROUP_CONCAT Function Listing Duplicate Values by Group Top 6 MySQL DBA Mistakes Selecting the Top N Results by Group in MySQL

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 MySQL Performance Monitoring
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:


Real-time MySQL Performance Monitoring
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:


Real-time MySQL Performance Monitoring
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:


Real-time MySQL Performance Monitoring
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:


Real-time MySQL Performance Monitoring
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 pur

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: MySQLSQLLinuxWindowsDebianCPUUbuntu
分页:12
转载请注明
本文标题:Real-time MySQL Performance Monitoring
本站链接:http://www.codesec.net/view/561344.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(50)