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

Overview

For the monitoring platform of MySQL, I believe there are a lot of implementation: monitoring based on Sky Rabbit, as well as secondary development based on zabbix. I believe that many colleagues should have started to play. The selection on my side is the implementation of prometheus + granafa. In short, my current production environment uses prometheus, and granafa meets the needs of my daily work. In the introduction and installation, you can refer to here: https://blog.51cto.com/cloumn/detail/77

1. First of all, let's take a look at our monitoring results and mysql master / slave.

2. Mysql status:

3. Buffer pool status:

Exporter related deployment

1. Install exporter

[root@controller2 opt] # https://github.com/prometheus/mysqld_exporter/releases/download/v0.10.0/mysqld_exporter-0.10.0.linux-amd64.tar.gz[root@controller2 opt] # tar-xf mysqld_exporter-0.10.0.linux-amd64.tar.gz

2. Add mysql account:

GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *. * TO 'exporter'@'%' IDENTIFIED BY' localhost';flush privileges

3. Edit the configuration file:

[root@controller2 mysqld_exporter-0.10.0.linux-amd64] # cat / opt/mysqld_exporter-0.10.0.linux-amd64/.my.cnf [client] user=exporterpassword=123456

4. Set the configuration file:

[root@controller2 mysqld_exporter-0.10.0.linux-amd64] # cat / etc/systemd/system/mysql_exporter.service [Unit] Description=mysql Monitoring SystemDocumentation=mysql Monitoring system [service] ExecStart=/opt/mysqld_exporter-0.10.0.linux-amd64/mysqld_exporter\-collect.info_schema.processlist\-collect.info_schema.innodb_tablespaces\-collect.info_schema.innodb_metrics\-collect. Perf_schema.tableiowaits\-collect.perf_schema.indexiowaits\-collect.perf_schema.tablelocks\-collect.engine_innodb_status\-collect.perf_schema.file_events\-collect.info_schema.processlist\-collect.binlog_size\-collect.info_schema.clientstats\-collect.perf_schema.eventswaits\ -config.my-cnf=/opt/mysqld_exporter-0.10.0.linux-amd64/.my.cnf [Install] WantedBy=multi-user.target

5. Add configuration to prometheus server

-job_name: 'mysql' static_configs:-targets: [' 192.168.1.11 targets 9104']

6. Test to see if a value has been returned:

Http://192.168.1.12:9104/metrics

Normally, through mysql_up, we can query whether the upside-down mysql monitoring has been in effect and whether it has been activated.

# HELP mysql_up Whether the MySQL server is up.#TYPE mysql_up gaugemysql_up 1 Monitoring related metrics

When doing anything monitoring, we should always understand what we want to monitor and what metrics can better monitor our services. In mysql, we can usually measure the operation of mysql by the following metrics: mysql master-slave operation, query throughput, slow query, connection count, buffer pool usage, query execution performance and so on.

Master-slave replication operation metrics:

1. Master-slave replication thread monitoring:

In most cases, many enterprises use a master-slave replication environment, so it is very important to monitor two threads. In mysql, we usually use commands:

MariaDB [(none)] > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 172.16.1.1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000045 Read_Master_Log_Pos: 72904854 Relay_Log_File: mariadb-relay-bin.000127 Relay_Log_Pos: 72905142 Relay_Master_Log_File: mysql-bin.000045 Slave_IO_Running: Yes Slave_SQL_Running: Yes

# Slave_IO_Running and Slave_SQL_Running threads are normal, so our replication cluster is healthy.

In the sample data returned in MySQLD Exporter, mysql_slave_status_slave_sql_running is used to obtain the health status of the master-slave cluster.

# HELP mysql_slave_status_slave_sql_running Generic metric from SHOW SLAVE STATUS.# TYPE mysql_slave_status_slave_sql_running untypedmysql_slave_status_slave_sql_running {channel_name= "", connection_name= "", master_host= "172.16.1.1", master_uuid= ""} 1

2. The lag time of master-slave replication:

There is also a key parameter, Seconds_Behind_Master, in using show slave status. Seconds_Behind_Master represents the delay between SQL thread and IO thread on slave. We all know that in a MySQL replication environment, slave first pulls the binlog locally from the master (via IO thread), and then replays the binlog through SQL thread, while Seconds_Behind_Master represents the difference between the unfinished part of the local relaylog. So if the local relaylog pulled by slave (actually binlog, just used to be called relaylog on slave) has been executed, what you will see through show slave status will be 0

Seconds_Behind_Master: 0

The relevant status is obtained by mysql_slave_status_seconds_behind_master in the sample data returned in MySQLD Exporter.

# HELP mysql_slave_status_seconds_behind_master Generic metric from SHOW SLAVE STATUS.# TYPE mysql_slave_status_seconds_behind_master untypedmysql_slave_status_seconds_behind_master {channel_name= ", connection_name=", master_host=" 172.16.1.1 ", master_uuid="} 0 query throughput:

When it comes to throughput, how do we measure it in that way?

Generally speaking, we can insert, query, delete, update and other operations of mysql.

To achieve throughput, MySQL has an internal counter called Questions (a server status variable, according to MySQL parlance), which is incremented every time the client sends a query statement. The client-centric perspective brought about by Questions metrics is often easier to explain than the relevant Queries counters. As part of the stored program, the latter also counts the number of executed statements and the number of times instructions such as PREPARE and DEALLOCATE PREPARE are run as part of the server-side preprocessing statement. You can query it through the command:

MariaDB [(none)] > SHOW GLOBAL STATUS LIKE "Questions"; +-+-+ | Variable_name | Value | +-+ | Questions | 15071 | +-+-+

The size of the current Questions counter is reflected by mysql_global_status_questions in the sample data returned in MySQLD Exporter:

# HELP mysql_global_status_questions Generic metric from SHOW GLOBAL STATUS.# TYPE mysql_global_status_questions untypedmysql_global_status_questions 13253

Of course, because prometheus has a very rich query language, we can use this cumulative counter to query the query growth rate within a short period of time, and we can do relevant threshold alarm processing, such as querying the query within 2 minutes:

Rate (mysql_global_status_ employees [2m])

Of course, the total amount above, we can monitor the decomposition of read and write instructions respectively, so as to better understand the workload of the database and find possible bottlenecks. Typically, read queries are fetched by Com_select metrics, while write queries may increase the value of one of the three state variables, depending on the instruction:

Writes = Com_insert + Com_update + Com_delete

Let's get the insertion through the command:

MariaDB [(none)] > SHOW GLOBAL STATUS LIKE "Com_insert"; +-+-+ | Variable_name | Value | +-+ | Com_insert | 10578 | +-+-+

From the monitoring sample returned from / metrics in MySQLD Exporter, you can obtain the number of instructions executed by various types of instructions in the current instance through global_status_commands_total:

# HELP mysql_global_status_commands_total Total number of executed MySQL commands.# TYPE mysql_global_status_commands_total countermysql_global_status_commands_total {command= "create_trigger"} 0mysql_global_status_commands_total {command= "create_udf"} 0mysql_global_status_commands_total {command= "create_user"} 1mysql_global_status_commands_total {command= "create_view"} 0mysql_global_status_commands_total {command= "dealloc_sql"} 0mysql globalization status Commands_total {command= "delete"} 3369mysql_global_status_commands_total {command= "delete_multi"} 0 slow query performance

In terms of query performance, slow query is also an important indicator of query alarm. MySQL also provides a counter for Slow_queries. When the execution time of the query exceeds the value of long_query_time, the counter will be + 1, and its default value is 10 seconds. You can query the current long_query_time settings in MySQL with the following instruction:

MariaDB [(none)] > SHOW VARIABLES LIKE 'long_query_time' +-+-+ | Variable_name | Value | +-+-+ | long_query_time | 10.000000 | +-+-+ 1 row in set (10.000000 sec)

# of course we can also change the time

MariaDB [(none)] > SET GLOBAL long_query_time = 5th query OK, 0 rows affected (0.00 sec)

Then we just query the number of Slow_queries in the MySQL instance through the sql language:

MariaDB [(none)] > SHOW GLOBAL STATUS LIKE "Slow_queries"; +-+-+ | Variable_name | Value | +-+-+ | Slow_queries | 0 | +-+-+ 1 row in set (0 sec)

In the sample data returned by MySQLD Exporter, the current Slow_queries value is displayed by mysql_global_status_slow_queries metric:

# HELP mysql_global_status_slow_queries Generic metric from SHOW GLOBAL STATUS.# TYPE mysql_global_status_slow_queries untypedmysql_global_status_slow_queries 0

Similarly, according to the Prometheus slow query statement, we can also query his growth rate over a certain period of time:

Rate (mysql_global_status_slow_ queries [5m])

Number of connections monitoring

It is important to monitor client connections because once available connections are exhausted, new client connections are rejected. The default number of connections for MySQL is limited to 151.

MariaDB [(none)] > SHOW VARIABLES LIKE 'max_connections';+-+-+ | Variable_name | Value | +-+-+ | max_connections | 151 | +-+-+

Of course, we can change the form of the configuration file to increase this value. Corresponding to this is the current number of connections. When our current connection exceeds the maximum value set by the system, we often see Too many connections (too many connections). Let me check the current number of connections:

MariaDB [(none)] > SHOW GLOBAL STATUS LIKE "Threads_connected"; +-+-+ | Variable_name | Value | +-+ | Threads_connected | 41 | +-+-

Of course, mysql also provides Threads_running as an indicator to help you separate threads that are actively processing queries at any time from connections that are available but idle.

MariaDB [(none)] > SHOW GLOBAL STATUS LIKE "Threads_running"; +-+-+ | Variable_name | Value | +-+ | Threads_running | 10 | +-+-+

If the server does reach the max_connections limit, it starts to reject new connections. In this case, the Connection_errors_max_connections metric starts to increase, and so does the Aborted_connects metric that tracks all failed connection attempts.

Among the sample data returned by MySQLD Exporter:

# HELP mysql_global_variables_max_connections Generic gauge metric from SHOW GLOBAL VARIABLES.# TYPE mysql_global_variables_max_connections gaugemysql_global_variables_max_connections 151

# indicates the maximum number of connections

# HELP mysql_global_status_threads_connected Generic metric from SHOW GLOBAL STATUS.# TYPE mysql_global_status_threads_connected untypedmysql_global_status_threads_connected 41

# indicates the current number of connections

# HELP mysql_global_status_threads_running Generic metric from SHOW GLOBAL STATUS.# TYPE mysql_global_status_threads_running untypedmysql_global_status_threads_running 1

# indicates the number of currently active connections

# HELP mysql_global_status_aborted_connects Generic metric from SHOW GLOBAL STATUS.# TYPE mysql_global_status_aborted_connects untypedmysql_global_status_aborted_connects 31

# accumulate the number of all connections

# HELP mysql_global_status_connection_errors_total Total number of MySQL connection errors.# TYPE mysql_global_status_connection_errors_total countermysql_global_status_connection_errors_total {error= "internal"} "errors caused inside the server, such as mysql_global_status_connection_errors_total {error=" max_connections "}" exceeding the connection

Of course, according to the prom expression, we can query the number of connections currently available:

Mysql_global_variables_max_connections-mysql_global_status_threads_connected

Query the number of mysq rejected connections

Mysql_global_status_aborted_connects

Buffer pool condition:

MySQL's default storage engine, InnoDB, uses an area of memory called a buffer pool to cache data for data tables and indexes. Buffer pool metrics are resource metrics rather than working metrics, and the former is more used to investigate (rather than detect) performance problems. If database performance starts to decline and disk Icano continues to climb, expanding the buffer pool can often lead to a rebound in performance.

By default, the size of the buffer pool is usually relatively small, 128MiB. However, MySQL recommends that it be expanded to 80% of the physical memory of the dedicated database server. We can check it out:

MariaDB [(none)] > show global variables like 'innodb_buffer_pool_size' +-+ | Variable_name | Value | +-+-+ | innodb_buffer_pool_size | 134217728 | +- -+

The sample data returned by MySQLD Exporter is represented by mysql_global_variables_innodb_buffer_pool_size.

# HELP mysql_global_variables_innodb_buffer_pool_size Generic gauge metric from SHOW GLOBAL VARIABLES.# TYPE mysql_global_variables_innodb_buffer_pool_size gaugemysql_global_variables_innodb_buffer_pool_size 1.34217728e+08Innodb_buffer_pool_read_requests records the number of requests that normally read data from the buffer pool. You can view MariaDB [(none)] > SHOW GLOBAL STATUS LIKE "Innodb_buffer_pool_read_requests" with the following directive +-- +-+ | Variable_name | Value | +-- +-+ | Innodb_buffer _ pool_read_requests | 38465 | +-- +

The sample data returned by MySQLD Exporter is represented by mysql_global_status_innodb_buffer_pool_read_requests.

# HELP mysql_global_status_innodb_buffer_pool_read_requests Generic metric from SHOW GLOBAL STATUS.# TYPE mysql_global_status_innodb_buffer_pool_read_requests untypedmysql_global_status_innodb_buffer_pool_read_requests 2.7711547168e+10

When the buffer pool is not satisfied, MySQL can only read data from disk. Innodb_buffer_pool_reads records the number of requests to read data from disk. Generally speaking, reading data from memory is much faster than reading from disk, so if the value of Innodb_buffer_pool_reads starts to increase, it may mean that there is a problem with the performance of the database. You can only view the number of Innodb_buffer_pool_reads through the following

MariaDB [(none)] > SHOW GLOBAL STATUS LIKE "Innodb_buffer_pool_reads" +-- +-+ | Variable_name | Value | +-+-+ | Innodb_buffer_pool_reads | 138 | +-+- -+ 1 row in set (0.00 sec)

The sample data returned by MySQLD Exporter is represented by mysql_global_status_innodb_buffer_pool_read_requests.

# HELP mysql_global_status_innodb_buffer_pool_reads Generic metric from SHOW GLOBAL STATUS.# TYPE mysql_global_status_innodb_buffer_pool_reads untypedmysql_global_status_innodb_buffer_pool_reads 138

Through the above monitoring metrics and the actual monitoring scenarios, we can use PromQL to quickly establish multiple monitoring items. You can view the growth rate of disk reads in two minutes:

Rate (mysql_global_status_innodb_buffer_pool_ reads [2m])

Official template ID

Above are some of the metrics we briefly listed. Let's use granafa to add monitoring charts to MySQLD_Exporter:

Master-slave master group monitoring (template 7371): related mysql status monitoring 7362: buffer pool status 7365: simple alarm rules

In addition to the relevant templates, there are no alarm rules, so our monitoring is not perfect. Here is a list of our monitoring alarm rules.

Groups:- name: MySQL-rules rules:-alert: MySQL Status expr: up = = 0 for: 5s labels: severity: warning annotations: summary: "{{$labels.instance}}: MySQL has stop!!" Description: "check the running status of MySQL database"-alert: MySQL Slave IO Thread Status expr: mysql_slave_status_slave_io_running = = 0 for: 5s labels: severity: warning annotations: summary: "{{$labels.instance}}: MySQL Slave IO Thread has stop!!" Description: "detect the running status of MySQL master-slave IO threads"-alert: MySQL Slave SQL Thread Status expr: mysql_slave_status_slave_sql_running = = 0 for: 5s labels: severity: warning annotations: summary: "{{$labels.instance}}: MySQL Slave SQL Thread has stop!!" Description: "check the running status of MySQL master-slave SQL threads"-alert: MySQL Slave Delay Status expr: mysql_slave_status_sql_delay = = 30 for: 5s labels: severity: warning annotations: summary: "{{$labels.instance}}: MySQL Slave Delay has more than 30s!!" Description: "detect MySQL master-slave delay status"-alert: Mysql_Too_Many_Connections expr: rate (mysql_global_status_threads_ connected [5m]) > 200for: 2m labels: severity: warning annotations: summary: "{{$labels.instance}}: too many connections" description: "{{$labels.instance}}: too many connections Please process (current value is: {{$value}}) "- alert: Mysql_Too_Many_slow_queries expr: rate (mysql_global_status_slow_ queries [5m]) > 3 for: 2m labels: severity: warning annotations: summary:" {{$labels.instance}}: slow query is a bit too much. Please check the processing "description:" {{$labels.instance}}: Mysql slow_queries is more than 3 per second, (current value is: {{$value}) "

2. Add rules to prometheus:

Rule_files:-"rules/*.yml"

3. Open web ui and we can see that the rule takes effect:

Summary

Monitoring the relevant status of mysql everywhere has been completed, you can improve your monitoring according to more monitoring indicators of mysql, of course, this set is I use the online environment, you can refer to.

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.