search
数据采集 采集源配置 MySQL 指标采集

MySQL 指标采集

简介

采集 mysql 的指标上报到 DataFlux 中,支持以下相关数据采集:

  • Global status
  • Global variables
  • Slave status
  • Binlog size
  • Process list
  • User Statistics
  • InnoDB metrics
  • Table I/O waits
  • Index I/O waits
  • Perf Schema table lock waits
  • Perf Schema event waits
  • Perf Schema events statements
  • File events statistics
  • Table schema statistics
  • Innodb事务锁冲突的会话明细
  • MySQL元锁个数以及相关信息
  • MySQL元锁会话明细
  • MySQL导致元锁冲突、长时间未提交的事务会话ID
  • Innodb 事务冲突锁信息

前置条件

配置

MySQL 数据库授权

# 创建监控账号
create user solarops identified by Zyadmin123;
GRANT SELECT, RELOAD, PROCESS, FILE, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT ON *.* TO 'solarops'@'%';
flush privileges;

配置采集器

进入 DataKit 安装目录下的 conf.d/db 目录,复制 mysqlMonitor.conf.sample 并命名为 mysqlMonitor.conf。示例如下:

** 注意:在使用MariaDB时,需要将以下配置product设置MariaDB

[[inputs.mysqlMonitor]]
# specify servers via a url matching:
#  [username[:password]@][protocol[(address)]]/[?tls=[true|false|skip-verify|custom]]
#  see https://github.com/go-sql-driver/mysql#dsn-data-source-name
#  e.g.
#    servers = ["user:passwd@tcp(127.0.0.1:3306)/?tls=false"]
#    servers = ["user@tcp(127.0.0.1:3306)/?tls=false"]
# product support MySQL and MariaDB, default MySQL
 product = "MySQL"
# If no servers are specified, then localhost is used as the host.
 servers = ["root:root@tcp(127.0.0.1:3306)/"]
# scan interval
 interval = "10s"
# Selects the metric output format.
# if the list is empty, then metrics are gathered from all database tables
 table_schema_databases = []
# gather metrics from INFORMATION_SCHEMA.TABLES for databases provided above list
 gather_table_schema = true
# gather thread state counts from INFORMATION_SCHEMA.PROCESSLIST
 gather_process_list = true
# gather user statistics from INFORMATION_SCHEMA.USER_STATISTICS
 gather_user_statistics = true
# gather auto_increment columns and max values from information schema
 gather_info_schema_auto_inc = true
# gather metrics from INFORMATION_SCHEMA.INNODB_METRICS
 gather_innodb_metrics = true
# gather metrics from SHOW SLAVE STATUS command output
 gather_slave_status = true
# gather metrics from SHOW BINARY LOGS command output
 gather_binary_logs = true
# gather metrics from PERFORMANCE_SCHEMA.GLOBAL_VARIABLES
 gather_global_variables = true
# gather metrics from PERFORMANCE_SCHEMA.GLOBAL_STATUS
 gather_global_status = true
# gather metrics from PERFORMANCE_SCHEMA.TABLE_IO_WAITS_SUMMARY_BY_TABLE
 gather_table_io_waits = true
# gather metrics from PERFORMANCE_SCHEMA.TABLE_LOCK_WAITS
 gather_table_lock_waits = true
# gather metrics from PERFORMANCE_SCHEMA.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE
 gather_index_io_waits = true
# gather metrics from PERFORMANCE_SCHEMA.EVENT_WAITS
 gather_event_waits = true
# gather metrics from PERFORMANCE_SCHEMA.FILE_SUMMARY_BY_EVENT_NAME
 gather_file_events_stats = true
# gather metrics from PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_SUMMARY_BY_DIGEST
 gather_perf_events_statements = true
# the limits for metrics form perf_events_statements
 perf_events_statements_digest_text_limit = 120
 perf_events_statements_limit = 250
 perf_events_statements_time_limit = 86400
# Use TLS but skip chain & host verification
 [inputs.mysqlMonitor.tags]
 tags1 = "value1"
`

重新启动 DataKit 即可。

采集指标

指标集数据分类

  • Global statuses - all numeric and boolean values of SHOW GLOBAL STATUSES
  • Global variables - all numeric and boolean values of SHOW GLOBAL VARIABLES
  • Slave status - metrics from SHOW SLAVE STATUS the metrics are gathered when the single-source replication is on. If the multi-source replication is set, then everything works differently, this metric does not work with multi-source replication.
    • slave_column name
  • Binary logs - all metrics including size and count of all binary files. Requires to be turned on in configuration.
    • binary_size_bytes(int, number)
    • binary_files_count(int, number)
  • Process list - connection metrics from processlist for each user. It has the following fields
    • connections(int, number)
  • User Statistics - connection metrics from user statistics for each user. It has the following fields
    • access_denied
    • binlog_bytes_written
    • busy_time
    • bytes_received
    • bytes_sent
    • commit_transactions
    • concurrent_connections
    • connected_time
    • cpu_time
    • denied_connections
    • empty_queries
    • hostlost_connections
    • other_commands
    • rollback_transactions
    • rows_fetched
    • rows_updated
    • select_commands
    • server
    • table_rows_read
    • total_connections
    • total_ssl_connections
    • update_commands
    • user
  • Perf Table IO waits - total count and time of I/O waits event for each table and process. It has following fields:
    • table_io_waits_total_fetch(float, number)
    • table_io_waits_total_insert(float, number)
    • table_io_waits_total_update(float, number)
    • table_io_waits_total_delete(float, number)
    • table_io_waits_seconds_total_fetch(float, milliseconds)
    • table_io_waits_seconds_total_insert(float, milliseconds)
    • table_io_waits_seconds_total_update(float, milliseconds)
    • table_io_waits_seconds_total_delete(float, milliseconds)
  • Perf index IO waits - total count and time of I/O waits event for each index and process. It has following fields:
    • index_io_waits_total_fetch(float, number)
    • index_io_waits_seconds_total_fetch(float, milliseconds)
    • index_io_waits_total_insert(float, number)
    • index_io_waits_total_update(float, number)
    • index_io_waits_total_delete(float, number)
    • index_io_waits_seconds_total_insert(float, milliseconds)
    • index_io_waits_seconds_total_update(float, milliseconds)
    • index_io_waits_seconds_total_delete(float, milliseconds)
  • Info schema autoincrement statuses - autoincrement fields and max values for them. It has following fields:
    • auto_increment_column(int, number)
    • auto_increment_column_max(int, number)
  • InnoDB metrics - all metrics of information_schema.INNODB_METRICS with a status "enabled"
  • Perf table lock waits - gathers total number and time for SQL and external lock waits events for each table and operation. It has following fields. The unit of fields varies by the tags.
    • read_normal(float, number/milliseconds)
    • read_with_shared_locks(float, number/milliseconds)
    • read_high_priority(float, number/milliseconds)
    • read_no_insert(float, number/milliseconds)
    • write_normal(float, number/milliseconds)
    • write_allow_write(float, number/milliseconds)
    • write_concurrent_insert(float, number/milliseconds)
    • write_low_priority(float, number/milliseconds)
    • read(float, number/milliseconds)
    • write(float, number/milliseconds)
  • Perf events waits - gathers total time and number of event waits
    • events_waits_total(float, number)
    • events_waits_seconds_total(float, milliseconds)
  • Perf file events statuses - gathers file events statuses
    • file_events_total(float,number)
    • file_events_seconds_total(float, milliseconds)
    • file_events_bytes_total(float, bytes)
  • Perf events statements - gathers attributes of each event
    • events_statements_total(float, number)
    • events_statements_seconds_total(float, millieconds)
    • events_statements_errors_total(float, number)
    • events_statements_warnings_total(float, number)
    • events_statements_rows_affected_total(float, number)
    • events_statements_rows_sent_total(float, number)
    • events_statements_rows_examined_total(float, number)
    • events_statements_tmp_tables_total(float, number)
    • events_statements_tmp_disk_tables_total(float, number)
    • events_statements_sort_merge_passes_totales(float, number)
    • events_statements_sort_rows_total(float, number)
    • events_statements_no_index_used_total(float, number)
  • Table schema - gathers statistics of each schema. It has following measurements
    • info_schema_table_rows(float, number)
    • info_schema_table_size_data_length(float, number)
    • info_schema_table_size_index_length(float, number)
    • info_schema_table_size_data_free(float, number)
    • info_schema_table_version(float, number)
  • metadata lock session - gathers metadata lock session, It has following fields
    • id
    • user
    • host
    • db
    • command
    • conn_time
    • state
    • info
  • metadata lock info - gathers metadata lock info, It has following fields
    • count
    • id
  • metadata lock transaction id, It has following fileds
    • id
  • innodb blocking transaction id, It has following fileds
    • id
    • user
    • host
    • db
    • command
    • conn_time
    • state
    • info
    • trx_id
    • trx_state
    • trx_started
    • trx_requested_lock_id
    • trx_wait_started
    • trx_weight
    • trx_mysql_thread_id
    • trx_query
    • trx_operation_state
    • trx_tables_in_use
    • trx_tables_locked
    • trx_lock_structs
    • trx_lock_memory_bytes
    • trx_rows_locked
    • trx_rows_modified
    • trx_concurrency_tickets
    • trx_isolation_level
    • trx_unique_checks
    • trx_foreign_key_checks
    • trx_last_foreign_key_error
    • trx_adaptive_hash_latched
    • trx_adaptive_hash_timeout
    • trx_is_read_only
    • trx_autocommit_non_locking
    • countnum
  • innodb lock waits, It has following fileds
    • lock_id
    • lock_trx_id
    • lock_mode
    • lock_type
    • lock_table
    • lock_index
    • lock_space
    • lock_page
    • lock_rec
    • lock_data

Tags

  • All measurements has following tags
    • server (the host name from which the metrics are gathered)
    • tags (customer defined tags)
    • metricType (metrics type)
      • globalVariables
      • slaveStatus
      • globalVariables
      • globalStatus
      • processListStatus
      • userConnectionsCount
      • userStatisticsStatus
      • perfTableIOWait
      • perfIndexIOWait
      • schemaAutoIncStatus
      • innoDBMetric
      • perfTableLockWait
      • perfEventWaits
      • perfFileEventsStatus
      • perfEventsStatements
      • mysql_innodb_blocking_trx_id
      • mysql_innodb_lock_waits
      • mysql_metadatalock_info
      • mysql_metadatalock_session
      • mysql_metadatalock_trx_id
  • Process list measurement has following tags
    • user (username for whom the metrics are gathered)
  • User Statistics measurement has following tags
    • user (username for whom the metrics are gathered)
  • Perf table IO waits measurement has following tags
    • schema
    • name (object name for event or process)
  • Perf index IO waits has following tags
    • schema
    • name
    • index
  • Info schema autoincrement statuses has following tags
    • schema
    • table
    • column
  • Perf table lock waits has following tags
    • schema
    • table
    • sql_lock_waits_total(fields including this tag have numeric unit)
    • external_lock_waits_total(fields including this tag have numeric unit)
    • sql_lock_waits_seconds_total(fields including this tag have millisecond unit)
    • external_lock_waits_seconds_total(fields including this tag have millisecond unit)
  • Perf events statements has following tags
    • event_name
  • Perf file events statuses has following tags
    • event_name
    • mode
  • Perf file events statements has following tags
    • schema
    • digest
    • digest_text
  • Table schema has following tags
    • schema
    • table
    • component
    • type
    • engine
    • row_format
    • create_options