Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about how to build MYSQL PMM and analyze the details. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Now everything has to be short and fast, which means to be good, fast and without problems. Well, if you want to monitor MYSQL, want such a thing PMM monitor and mannagement) is a good choice.

First of all, this open source monitoring product supports MYSQL,POSTGRESQL, MONGODB, ProxySQL, and basically all the company's mainstream open source databases. At the same time, another special feature of this product is that it monitors itself. What do you mean? many monitoring products have good monitoring of the connected system, but there is no monitoring of their own status alone. This product will monitor its own database and system status.

Not to mention how to install, online installation is also a lot, but in fact, the installation still needs to be mentioned, because I installed on the test system, is very smooth, but to the production system is another matter.

Roughly installing this system will encounter the following problems

1 because the firewall is not turned off, after the data collection terminal is installed, the configuration items in the DOCKER system are written to IPTABLE, and the data forwarding of the monitoring system and external clients in the DOCKER fails. The specific response is that after installing the client, the data connection status between the client and the data collection terminal is DOWN, even if you are shutting down the firewall, it is irreparable and can only be reinstalled.

2 the reason for the network segment, in fact, the database server in a company may exist in multiple network segments, but the collection of monitoring is only in one network segment, so it is necessary to set up the 4-7 layers of the network and get through some settings about the firewall. as for how to get through, just ask the network Daniel in your company.

3 there is no user name and password for installing PMM by default, but in fact, it is best for a system to have a user name and password. Because PMM is the installation mode of DOCER, the user name and password here can only be done during initialization, otherwise it can only be redone.

4 according to the requirements of the official documentation, if you do not have a professional DOCKER Operation, it is best to provide sufficient space for the OPT directory according to the documentation to prevent problems in some areas you do not understand.

After skipping these pits, what's left is basically

1 part of the monitoring projects have, but no monitoring data

2 slow query has no data

Let's take a look at the first question (the following MYSQL is PERCONA 5.7.23). The following are just a few indicators selected. In fact, Innodb metrics advanced has no data after installation.

How on earth can we have the data?

SET GLOBAL innodb_monitor_enable=all

After executing the above statement in MYSQL, immediately most of the places in the PMM that show NO DATA POINTS will begin to accept and display the data.

But even if you do this, there are still some graphics that don't have data to display no data point.

What's going on. It is believed that there are at least two graphics that do not have data.

1 innodb online DDL

2 innodb Defragmentation

Why, because these two graphics only serve MariaDB, if you are using the official version or the PERCONA version of MYSQL, there will be data display.

I still remember writing a SHELL script a few years ago to count the number of row insert, delete, update and other information over a period of time. Now if we look at this monitoring innodb row operations, we can see the IN OUT status of the system at a glance.

After basically figuring out these problems, here is the mastery of some monitoring graphics and indicators. (this issue must be endless, so it doesn't matter where it is written.)

1 innodb checkpoint age, what I pay more attention to in this picture is not the red line in the picture, but uncheckpointed bytes. The size of this number indicates how many dirty pages still exist in the system.

The relationship between the values in the following figure, imagine if you have a lot of logs that have not done checkpoint, and the logs have not been closed, you need to think about what went wrong, and pay attention to the load of the system, as well as the Ibank O situation.

Of course, after looking at the picture above, I immediately want to look at the following picture to see how much log is written per second.

The relevant innodb_buffer_pool_read_requests should also be paid attention to, which means that the number of requests for data from disk in a certain period of time can be obtained by combining the two parameters innodb_buffer_pool_reads and Innodb_buffer_pool_read_requests, and whether there is a lack of memory to define an indicator.

Of course, deadlocks are also easy to find in innodb metrics.

There are also some good features, such as the storage space occupied by the table and the number of rows per table (which is not necessarily very accurate here). The relevant information comes from the VIEW in information_schema.

In addition, if the database you are using is the PERCONA version of MYSQL, the monitoring will also provide the interface of mysql user statistics status.

For example

That user creates more connections, the traffic of that user is relatively high, and the number of rows of data that that user searches, the CPU used, and so on, can be obtained from PMM.

After reading the above, do you have any further understanding of how to build MYSQL PMM and analyze the details? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.