MySQL

·

MySQL metrics collection, which collects the following data:

• MySQL global status basic data collection
• Schema related data
• InnoDB related metrics
• Support custom query data collection

Configuration¶

Preconditions¶

• MySQL version 5.7+
• Create a monitoring account (in general, you need to log in with MySQL root account to create MySQL users)

CREATE USER 'datakit'@'localhost' IDENTIFIED BY '<UNIQUEPASSWORD>';

-- MySQL 8.0+ create the datakit user with the caching_sha2_password method
CREATE USER 'datakit'@'localhost' IDENTIFIED WITH caching_sha2_password by '<UNIQUEPASSWORD>';

• Authorization

GRANT PROCESS ON *.* TO 'datakit'@'localhost';
GRANT SELECT ON *.* TO 'datakit'@'localhost';
show databases like 'performance_schema';
GRANT SELECT ON performance_schema.* TO 'datakit'@'localhost';
GRANT SELECT ON mysql.user TO 'datakit'@'localhost';
GRANT replication client on *.*  to 'datakit'@'localhost';

Collector Configuration¶

[[inputs.mysql]]
  host = "localhost"
  user = "datakit"
  pass = "<PASS>"
  port = 3306
  # sock = "<SOCK>"
  # charset = "utf8"

  ## @param connect_timeout - number - optional - default: 10s
  # connect_timeout = "10s"

  ## Deprecated
  # service = "<SERVICE>"

  interval = "10s"

  ## @param inno_db
  innodb = true

  ## table_schema
  tables = []

  ## user
  users = []

  ## Set replication to true to collect replication metrics
  # replication = false
  ## Set group_replication to true to collect group replication metrics
  # group_replication = false

  ## Set dbm to true to collect database activity 
  # dbm = false

  ## Set true to enable election
  election = true

  [inputs.mysql.log]
    # #required, glob logfiles
    # files = ["/var/log/mysql/*.log"]

    ## glob filteer
    # ignore = [""]

    ## optional encodings:
    ## "utf-8", "utf-16le", "utf-16le", "gbk", "gb18030" or ""
    # character_encoding = ""

    ## The pattern should be a regexp. Note the use of '''this regexp'''
    ## regexp link: https://golang.org/pkg/regexp/syntax/#hdr-Syntax
    multiline_match = '''^(# Time|\d{4}-\d{2}-\d{2}|\d{6}\s+\d{2}:\d{2}:\d{2}).*'''

    ## grok pipeline script path
    pipeline = "mysql.p"

  ## Run a custom SQL query and collect corresponding metrics.
  # [[inputs.mysql.custom_queries]]
  #   sql = '''
  #     select ENGINE as engine,TABLE_SCHEMA as table_schema,count(*) as table_count 
  #     from information_schema.tables 
  #     group by engine,table_schema
  #   '''
  #   metric = "mysql_custom"
  #   tags = ["engine", "table_schema"]
  #   fields = ["table_count"] 

  ## Config dbm metric 
  [inputs.mysql.dbm_metric]
    enabled = true

  ## Config dbm sample 
  [inputs.mysql.dbm_sample]
    enabled = true  

  ## Config dbm activity
  [inputs.mysql.dbm_activity]
    enabled = true  

  ## TLS Config
  # [inputs.mysql.tls]
    # tls_ca = "/etc/mysql/ca.pem"
    # tls_cert = "/etc/mysql/cert.pem"
    # tls_key = "/etc/mysql/key.pem"

    ## Use TLS but skip chain & host verification
    # insecure_skip_verify = true

    ## by default, support TLS 1.2 and above.
    ## set to true if server side uses TLS 1.0 or TLS 1.1
    # allow_tls10 = false

  [inputs.mysql.tags]
    # some_tag = "some_value"
    # more_tag = "some_other_value"

Binlog Start¶

MySQL Binlog is not turned on. If you want to count the Binlog size, you need to turn on the Binlog function corresponding to MySQL:

-- ON: turn on, OFF: turn off
SHOW VARIABLES LIKE 'log_bin';

Binlog starts, see this or this answer.

Database Performance Metrics Collection¶

The database performance metrics come from MySQL's built-in database performance_schema, which provides a way to get the internal performance of the server at runtime. Through this database, DataKit can collect statistics of various metrics of historical query statements, execution plans of query statements and other related performance metrics. The collected performance metric data is saved as a log, and the sources are mysql_dbm_metric, mysql_dbm_sample and mysql_dbm_activity.

To turn it on, you need to perform the following steps.

• Modify the configuration file and start monitoring and collection

[[inputs.mysql]]

# Turn on database performance metric collection
dbm = true

...

# Monitor metric configuration
[inputs.mysql.dbm_metric]
  enabled = true

# Monitor sampling configuration
[inputs.mysql.dbm_sample]
  enabled = true

# Waiting for event collection
[inputs.mysql.dbm_activity]
  enabled = true   
...

• MySQL Configuration

Modify the configuration file (such as mysql.conf), open the MySQL Performance Schema, and configure the parameters:

[mysqld]
performance_schema = on
max_digest_length = 4096
performance_schema_max_digest_length = 4096
performance_schema_max_sql_text_length = 4096
performance-schema-consumer-events-statements-current = on
performance-schema-consumer-events-waits-current = on
performance-schema-consumer-events-statements-history-long = on
performance-schema-consumer-events-statements-history = on

• Account configuration

Account authorization

-- MySQL 5.6 & 5.7
GRANT REPLICATION CLIENT ON *.* TO datakit@'%' WITH MAX_USER_CONNECTIONS 5;
GRANT PROCESS ON *.* TO datakit@'%';

-- MySQL >= 8.0
ALTER USER datakit@'%' WITH MAX_USER_CONNECTIONS 5;
GRANT REPLICATION CLIENT ON *.* TO datakit@'%';
GRANT PROCESS ON *.* TO datakit@'%';

Create a database

CREATE SCHEMA IF NOT EXISTS datakit;
GRANT EXECUTE ON datakit.* to datakit@'%';
GRANT CREATE TEMPORARY TABLES ON datakit.* TO datakit@'%';

Create the stored procedure explain_statement to get the SQL execution plan

DELIMITER $$
CREATE PROCEDURE datakit.explain_statement(IN query TEXT)
    SQL SECURITY DEFINER
BEGIN
    SET @explain := CONCAT('EXPLAIN FORMAT=json ', query);
    PREPARE stmt FROM @explain;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

Create a separate stored procedure for the database that needs to collect execution plans (optional)

DELIMITER $$
CREATE PROCEDURE <db_name>.explain_statement(IN query TEXT)
    SQL SECURITY DEFINER
BEGIN
    SET @explain := CONCAT('EXPLAIN FORMAT=json ', query);
    PREPARE stmt FROM @explain;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
GRANT EXECUTE ON PROCEDURE <db_name>.explain_statement TO datakit@'%';

• consumers configuration

Method one (recommended): Dynamic configuration of performance_schema.events_* with DataKit requires the creation of the following stored procedure:

DELIMITER $$
CREATE PROCEDURE datakit.enable_events_statements_consumers()
    SQL SECURITY DEFINER
BEGIN
    UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name LIKE 'events_statements_%';
    UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name = 'events_waits_current';
END $$
DELIMITER ;

GRANT EXECUTE ON PROCEDURE datakit.enable_events_statements_consumers TO datakit@'%';

Method 2: Manually configure consumers

UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name LIKE 'events_statements_%';
UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name = 'events_waits_current';

Replication Metrics Collection¶

To collect replication metrics mysql_replication, you need to start MySQL replication. mysql_replication metrics are collected from the replication database, so you can confirm that the MySQL replication environment is working properly by entering them in the slave database:

SHOW SLAVE STATUS;

If the Slave_IO_Running and Slave_SQL_Running fields are Yes, the replication environment is working properly.

To capture group replication metrics such as count_transactions_in_queue, you need to add the group_replication plugin to the list of plugins loaded by the server at startup (group_replication has been supported since MySQL version 5.7.17). In the configuration file /etc/my.cnf for the replication database, add the line:

plugin_load_add ='group_replication.so'

You can confirm that the group replication plugin is installed by showing plugins;.

To turn it on, you need to perform the following steps.

• Modify the configuration file and start monitoring and collection

[[inputs.mysql]]

## Set replication to true to collect replication metrics
replication = true
## Set group_replication to true to collect group replication metrics
group_replication = true  
...

Metric¶

For all of the following data collections, the global election tags will added automatically, we can add extra tags in [inputs.mysql.tags] if needed:

 [inputs.mysql.tags]
  # some_tag = "some_value"
  # more_tag = "some_other_value"
  # ...

mysql¶

• tag

• metric list

mysql_replication¶

• tag

• metric list

mysql_schema¶

MySQL schema information

• tag

• metric list

mysql_innodb¶

• tag

• metric list

mysql_table_schema¶

MySQL table information

• tag

• metric list

mysql_user_status¶

MySQL user information

• tag

• metric list

Custom Object¶

Log¶

Version-1.4.6

mysql_dbm_metric¶

Record the number of executions of the query statement, wait time, lock time, and the number of rows queried.

• tag

• Metric list

mysql_dbm_sample¶

Select some of the SQL statements with high execution time, collect their execution plans, and collect various performance indicators during the actual execution process.

• tag

• Metric list

mysql_dbm_activity¶

Collect the waiting event of the current thread

• tag

• Metric list

mysql_replication_log¶

Record the replication string information.

• tag

• Metric list

MySQL Run Log¶

If you need to collect MySQL log, open the log-related configuration in the configuration. If you need to open MySQL slow query log, you need to open the slow query log. Execute the following statements in MySQL.

SET GLOBAL slow_query_log = 'ON';

-- Queries that do not use indexes are also considered a possible slow query
set global log_queries_not_using_indexes = 'ON';

[inputs.mysql.log]
    # Fill in the absolute path
    files = ["/var/log/mysql/*.log"]

Note: When using log collection, you need to install the DataKit on the same host as the MySQL service, or use other methods to mount the log on the machine where the DataKit is located.

MySQL logs are divided into normal logs and slow logs.

MySQL Normal Logs¶

Original log:

2017-12-29T12:33:33.095243Z         2 Query     SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%partitioned%';

The list of cut fields is as follows:

MySQL Slow Query Log¶

Original log:

# Time: 2019-11-27T10:43:13.460744Z
# User@Host: root[root] @ localhost [1.2.3.4]  Id:    35
# Query_time: 0.214922  Lock_time: 0.000184 Rows_sent: 248832  Rows_examined: 72
# Thread_id: 55   Killed: 0  Errno: 0
# Bytes_sent: 123456   Bytes_received: 0
SET timestamp=1574851393;
SELECT * FROM fruit f1, fruit f2, fruit f3, fruit f4, fruit f5

The list of cut fields is as follows:

FAQ¶

Why the measurement mysql_user_status is not collected for Aliyun RDS?¶

The measurement is collected from MySQL performance_schema. You should check if it is enabled by the SQL below：

show variables like "performance_schema";

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

If the value is OFF, please refer to the document to enable it.