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

  ## Metric name in metric_exclude_list will not be collected, such as ["mysql_schema", "mysql_user_status"]
  metric_exclude_list = [""]

  ## collect mysql object
  [inputs.mysql.object]
    ## Set true to enable mysql object collection
    enabled = true

    ## interval to collect mysql object which will be greater than collection interval
    interval = "600s"

  [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"] 
  #   interval = "10s"

  ## 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¶

• Tags

• Metrics

mysql_replication¶

• Tags

• Metrics

mysql_schema¶

MySQL schema information

• Tags

• Metrics

mysql_innodb¶

• Tags

• Metrics

mysql_table_schema¶

MySQL table information

• Tags

• Metrics

mysql_user_status¶

MySQL user information

• Tags

• Metrics

collector¶

• Tags

• Metrics

Object¶

database¶

MySQL object metrics( Version-1.74.0)

• Tags

• Metrics

message Metric Field Structure¶

The basic structure of the message field is as follows:

{
  "setting": {
    "auto_generate_certs": "ON",
    ...
  },

  "databases": [ # databases information
    {
      "name": "db1",
      "default_character_set_name": "utf8mb4",
      "default_collation_name": "utf8mb4_general_ci",
      "tables": [ # tables information
        {
          "name": "table1",
          "columns": [], # columns information
          "indexes": [], # indexes information
          "foreign_keys": [], # foreign keys information
          "partitions": [] # partitions information
        }
        ...
      ]
    }
    ...
  ]
}

setting¶

The setting field contains data sourced from the performance_schema.global_variables table, which holds global variable information for the MySQL server. Detailed fields can be referenced in the MySQL Documentation

databases¶

The databases field stores information about all databases on the MySQL server. Each database entry includes the following:

tables¶

The tables field contains information about all tables in the database. Each table entry includes:

Details of the list fields:

• tables.columns field

The tables.columns field contains information about all columns in the table. Each column entry includes:

• tables.indexes

The tables.indexes field contains information about all indexes in the table. Each index entry includes:

The index column information field indexes.columns contains details about the columns included in the index. Each column entry includes:

• tables.foreign_keys

The tables.foreign_keys field contains information about all foreign keys in the table. Each foreign key entry includes:

• tables.partitions

The tables.partitions field contains information about all partitions in the table. Each partition entry includes:

The subpartition information field partitions.subpartitions contains details about subpartitions. Each subpartition entry includes:

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.

• Tags

• Metrics

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.

• Tags

• Metrics

mysql_dbm_activity¶

Collect the waiting event of the current thread

• Tags

• Metrics

mysql_replication_log¶

Record the replication string information.

• Tags

• Metrics

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.