Skip to content

Instantly share code, notes, and snippets.

@sgnl

sgnl/README.md

Last active February 10, 2022 11:25
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save sgnl/0973e4709eee64a8b91bc38dd71f9e05 to your computer and use it in GitHub Desktop.
Save sgnl/0973e4709eee64a8b91bc38dd71f9e05 to your computer and use it in GitHub Desktop.
Download ZIP
Telegraf MySQL Plugin Installation Guide
Raw
README.md

Overview

The MySQL Input Plugin for Telegraf gathers data from a MySQL server.

Setup

Create user for the Telegraf agent to connect with

For connections that are local to the MySQL server, use localhost as your hostname:

-- MySQL version 8.0
mysql> CREATE USER 'telegraf-client'@'localhost' IDENTIFIED WITH mysql_native_password by '<UNIQUEPASSWORD>';
-- MySQL version 5.7
mysql> CREATE USER 'telegraf-client'@'75.80.194.115' IDENTIFIED BY '<UNIQUEPASSWORD>';

More about mysql_native_password in MySQL version 8.0 read the documentation here.

important note for remote connections: use the hostname or IP of where your Telegraf agent will connect from, e.g.:

-- MySQL version 5.7
mysql> CREATE USER 'telegraf-client'@'ip-address-here-pplaceholder' IDENTIFIED BY '<UNIQUEPASSWORD>';

Privileges and limiting connections for the newly created user

Before you start, here's a query you can run to obtain the value for the max_connections variable:

-- MySQL version 5.7 and 8.0
mysql> SHOW VARIABLES like 'max_connections';

Now that you have that piece of information you can continue to configure the user

-- MySQL version 5.7

mysql> GRANT REPLICATION CLIENT ON *.* TO 'telegraf-client'@'localhost';
-- MySQL version 5.7
mysql> GRANT PROCESS ON *.* TO 'telegraf-client'@'localhost';
-- MySQL 8.0
mysql> GRANT REPLICATION CLIENT ON *.* TO 'telegraf-client'@'localhost';
mysql> GRANT PROCESS ON *.* TO 'telegraf-client'@'localhost';
mysql> ALTER USER 'telegraf-client'@'localhost' WITH MAX_USER_CONNECTIONS 5;
-- MySQL 5.7
mysql> GRANT REPLICATION CLIENT ON *.* TO 'telegraf-client'@'localhost' WITH MAX_USER_CONNECTIONS 5;
mysql> GRANT PROCESS ON *.* TO 'telegraf-client'@'localhost';

More information about REPLICATION CLIENT and PROCESS can be found in the privileges-provided section of your version's manual

Telegraf performance_schema configuration

The Telegraf MySQL plugin has configuration options you can toggle on or off for gathering data from the performance_schema table, refer to the example Telegraf configuration in a section found below.

  ## ... config snippet example ...

  ## gather metrics from PERFORMANCE_SCHEMA.TABLE_IO_WAITS_SUMMARY_BY_TABLE
  # gather_table_io_waits = false

  ## gather metrics from PERFORMANCE_SCHEMA.TABLE_LOCK_WAITS
  # gather_table_lock_waits = false

  ## gather metrics from PERFORMANCE_SCHEMA.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE
  # gather_index_io_waits = false

  ## gather metrics from PERFORMANCE_SCHEMA.EVENT_WAITS
  # gather_event_waits = false

  ## gather metrics from PERFORMANCE_SCHEMA.FILE_SUMMARY_BY_EVENT_NAME
  # gather_file_events_stats = false

  ## gather metrics from PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_SUMMARY_BY_DIGEST
  # gather_perf_events_statements = false
-- MySQL version 5.7 and 8.0
mysql> GRANT REPLICATION CLIENT ON *.* TO 'telegraf-client'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show databases like 'performance_schema';
+-------------------------------+
| Database (performance_schema) |
+-------------------------------+
| performance_schema            |
+-------------------------------+
1 row in set (0.01 sec)

mysql> GRANT SELECT ON performance_schema.* TO 'telegraf-client'@'localhost';
Query OK, 0 rows affected (0.05 sec)

And that should be it. Now you can start to add connection details to your Telegraf configuration and start gathering data!

Telegraf Configuration

[[inputs.mysql]]
## 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"]
#
## If no servers are specified, then localhost is used as the host.
servers = ["${MYSQL_USERNAME}:${MYSQL_PASSWORD}@tcp(${MYSQL_ADDRESS})/"]

## Selects the metric output format.
##
## This option exists to maintain backwards compatibility, if you have
## existing metrics do not set or change this value until you are ready to
## migrate to the new format.
##
## If you do not have existing metrics from this plugin set to the latest
## version.
##
## Telegraf >=1.6: metric_version = 2
##           <1.6: metric_version = 1 (or unset)
metric_version = 2

## if the list is empty, then metrics are gathered from all databasee tables
# table_schema_databases = []

## gather metrics from INFORMATION_SCHEMA.TABLES for databases provided above list
# gather_table_schema = false

## gather thread state counts from INFORMATION_SCHEMA.PROCESSLIST
# gather_process_list = false

## gather user statistics from INFORMATION_SCHEMA.USER_STATISTICS
# gather_user_statistics = false

## gather auto_increment columns and max values from information schema
# gather_info_schema_auto_inc = false

## gather metrics from INFORMATION_SCHEMA.INNODB_METRICS
# gather_innodb_metrics = false

## gather metrics from SHOW SLAVE STATUS command output
# gather_slave_status = false

## gather metrics from SHOW BINARY LOGS command output
# gather_binary_logs = false

## gather metrics from SHOW GLOBAL VARIABLES command output
# gather_global_variables = true

## gather metrics from PERFORMANCE_SCHEMA.TABLE_IO_WAITS_SUMMARY_BY_TABLE
# gather_table_io_waits = false

## gather metrics from PERFORMANCE_SCHEMA.TABLE_LOCK_WAITS
# gather_table_lock_waits = false

## gather metrics from PERFORMANCE_SCHEMA.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE
# gather_index_io_waits = false

## gather metrics from PERFORMANCE_SCHEMA.EVENT_WAITS
# gather_event_waits = false

## gather metrics from PERFORMANCE_SCHEMA.FILE_SUMMARY_BY_EVENT_NAME
# gather_file_events_stats = false

## gather metrics from PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_SUMMARY_BY_DIGEST
# gather_perf_events_statements = false

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

## Some queries we may want to run less often (such as SHOW GLOBAL VARIABLES)
##   example: interval_slow = "30m"
# interval_slow = ""

## Optional TLS Config (will be used if tls=custom parameter specified in server uri)
# tls_ca = "/etc/telegraf/ca.pem"
# tls_cert = "/etc/telegraf/cert.pem"
# tls_key = "/etc/telegraf/key.pem"
## Use TLS but skip chain & host verification
# insecure_skip_verify = false

Data collected

Metrics

  • 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.
  • 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 tags
    • 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)

Tags

  • All measurements has following tags
    • server (the host name from which the metrics are gathered)
  • 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

Further Reading

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment