MySQL
Datadog の調査レポート: サーバーレスの状態 レポート: サーバーレスの状態

MySQL

Supported OS: Linux Mac OS Windows

MySQL ダッシュボード

概要

Datadog Agent は MySQL データベースから、次のような多数のメトリクスを収集できます (一例)。

  • クエリスループット
  • クエリパフォーマンス (平均クエリ実行時間、低速なクエリなど)
  • 接続数 (現在開かれている接続、中断された接続、エラーなど)
  • InnoDB (バッファプールメトリクスなど)

カスタム SQL クエリを使用して、独自のメトリクスを作成することもできます。

注: MariaDB は MySQL の “互換製品” なので、このインテグレーションは MariaDB とも互換性があります。

セットアップ

インストール

MySQL チェックは Datadog Agent パッケージに含まれています。MySQL サーバーに追加でインストールする必要はありません。

MySQL の準備

各 MySQL サーバーで、Datadog Agent 用のデータベースユーザーを作成します。

mysql> CREATE USER 'datadog'@'localhost' IDENTIFIED BY '<一意のパスワード>';
Query OK, 0 rows affected (0.00 sec)

mySQL 8.0+ の場合は、ネイティブのパスワードハッシュ化メソッドを使用して datadog ユーザーを作成します。

mysql> CREATE USER 'datadog'@'localhost' IDENTIFIED WITH mysql_native_password by '<一意のパスワード>';
Query OK, 0 rows affected (0.00 sec)

: @'localhost' はローカル接続専用です。リモート接続には Agent のホスト名/IP を使用してください。詳細については、MySQL のドキュメントを参照してください。

次のコマンドを使用して、ユーザーが問題なく作成されたことを検証します。<一意のパスワード> は上記で作成したパスワードに置き換えます。

mysql -u datadog --password=<UNIQUEPASSWORD> -e "show status" | \
grep Uptime && echo -e "\033[0;32mMySQL user - OK\033[0m" || \
echo -e "\033[0;31mCannot connect to MySQL\033[0m"
mysql -u datadog --password=<一意のパスワード> -e "show slave status" && \
echo -e "\033[0;32mMySQL grant - OK\033[0m" || \
echo -e "\033[0;31mMissing REPLICATION CLIENT grant\033[0m"

Agent がメトリクスを収集するには、いくつかの権限が必要です。次のように、限られた権限のみをユーザーに付与してください。

mysql> GRANT REPLICATION CLIENT ON *.* TO 'datadog'@'localhost' WITH MAX_USER_CONNECTIONS 5;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT PROCESS ON *.* TO 'datadog'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MySQL 8.0 以降の場合は、max_user_connections を次のように設定します。

mysql> ALTER USER 'datadog'@'localhost' WITH MAX_USER_CONNECTIONS 5;
Query OK, 0 rows affected (0.00 sec)

有効になると、追加の権限を付与することで、performance_schema データベースからメトリクスを収集できます。

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

mysql> GRANT SELECT ON performance_schema.* TO 'datadog'@'localhost';
Query OK, 0 rows affected (0.00 sec)

コンフィグレーション

ホストで実行されている Agent 用にこのチェックを構成する場合は、以下の手順に従ってください。コンテナ環境の場合は、コンテナ化セクションを参照してください。

ホスト

MySQL のメトリクスログの収集を開始するには、Agent の構成ディレクトリのルートにある conf.d/ フォルダーの mysql.d/conf.yaml ファイルを編集します。使用可能なすべての構成オプションの詳細については、サンプル mysql.d/conf.yaml を参照してください。

メトリクスの収集
  • MySQL メトリクスを収集するには、mysql.d/conf.yaml に次の構成ブロックを追加します。

    init_config:
    
    instances:
    - server: 127.0.0.1
      user: datadog
      pass: "<YOUR_CHOSEN_PASSWORD>" # from the CREATE USER step earlier
      port: "<YOUR_MYSQL_PORT>" # e.g. 3306
      options:
        replication: false
        galera_cluster: true
        extra_status_metrics: true
        extra_innodb_metrics: true
        extra_performance_metrics: true
        schema_size_metrics: false
        disable_innodb_metrics: false

: パスワードに特殊文字が含まれる場合は、単一引用符で囲んでください。

extra_performance_metrics を収集するには、MySQL サーバーで performance_schema が有効になっている必要があります。それ以外の場合は、extra_performance_metricsfalse に設定します。performance_schema の詳細については、MySQL ドキュメントを参照してください

datadog ユーザーは、localhost ではなく host: 127.0.0.1 として MySQL インテグレーション構成内にセットアップされる必要があります。または、sock を使用することもできます。

カスタムメトリクスのオプションなど、使用可能なすべての構成オプションの詳細については、サンプル mysql.yaml を参照してください。

Agent を再起動すると、Datadog への MySQL メトリクスの送信が開始されます。

ログの収集

Agent バージョン 6.0 以降で利用可能

  1. MySQL は、デフォルトでは /var/log/syslog 内のすべてをログに記録しますが、これには、読み取りのルートアクセス許可が必要です。ログへのアクセス可能性を高めるには、以下の手順に従ってください。

    • /etc/mysql/conf.d/mysqld_safe_syslog.cnf を編集して、行を削除またはコメントにします。
    • /etc/mysql/my.cnf を編集し、次の行を追加して、一般ログ、エラーログ、および低速なクエリログを有効にします。

      [mysqld_safe]
      log_error = /var/log/mysql/mysql_error.log
      
      [mysqld]
      general_log = on
      general_log_file = /var/log/mysql/mysql.log
      log_error = /var/log/mysql/mysql_error.log
      slow_query_log = on
      slow_query_log_file = /var/log/mysql/mysql_slow.log
      long_query_time = 2
    • ファイルを保存し、次のコマンドを使用して MySQL を再起動します。 service mysql restart

    • Agent が /var/log/mysql ディレクトリとその中のすべてのファイルに対する読み取りアクセス許可を持つことを確認します。logrotate 構成もチェックして、これらのファイルが考慮され、アクセス許可が正しく設定されていることを確認します。

    • /etc/logrotate.d/mysql-server の内容は次のようになります。

      /var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql_slow.log {
             daily
             rotate 7
             missingok
             create 644 mysql adm
             Compress
      }
  2. Datadog Agent で、ログの収集はデフォルトで無効になっています。以下のように、datadog.yaml ファイルでこれを有効にします。

    logs_enabled: true
  3. MySQL のログの収集を開始するには、次の構成ブロックを mysql.d/conf.yaml ファイルに追加します。

    logs:
     - type: file
       path: "<ERROR_LOG_FILE_PATH>"
       source: mysql
       service: "<SERVICE_NAME>"
    
     - type: file
       path: "<SLOW_QUERY_LOG_FILE_PATH>"
       source: mysql
       service: "<SERVICE_NAME>"
       log_processing_rules:
         - type: multi_line
           name: new_slow_query_log_entry
           pattern: "# Time:"
           # If mysqld was started with `--log-short-format`, use:
           # pattern: "# Query_time:"
    
     - type: file
       path: "<GENERAL_LOG_FILE_PATH>"
       source: mysql
       service: "<SERVICE_NAME>"
       # For multiline logs, if they start by the date with the format yyyy-mm-dd uncomment the following processing rule
       # log_processing_rules:
       #   - type: multi_line
       #     name: new_log_start_with_date
       #     pattern: \d{4}\-(0?[1-9]|1[012])\-(0?[1-9]|[12][0-9]|3[01])

    カスタムメトリクスのオプションなど、使用可能なすべての構成オプションの詳細については、サンプル mysql.yaml を参照してください。

  4. Agent を再起動します

コンテナ化

コンテナ環境の場合は、オートディスカバリーのインテグレーションテンプレートのガイドを参照して、次のパラメーターを適用してください。

メトリクスの収集
パラメーター
<INTEGRATION_NAME>mysql
<INIT_CONFIG>空白または {}
<INSTANCE_CONFIG>{"server": "%%host%%", "user": "datadog","pass": "<UNIQUEPASSWORD>"}

<UNIQUEPASSWORD> をラベルではなく環境変数として渡す方法については、オートディスカバリーテンプレート変数に関するドキュメントを参照してください。

ログの収集

Agent バージョン 6.0 以降で利用可能

Datadog Agent で、ログの収集はデフォルトで無効になっています。有効にする方法については、Docker ログ収集を参照してください。

パラメーター
<LOG_CONFIG>{"source": "mysql", "service": "mysql"}

検証

Agent の status サブコマンドを実行し、Checks セクションで mysql を探します。

収集データ

メトリクス

mysql.info.schema.size
(gauge)
Size of schemas in MiB
Shown as mebibyte
mysql.galera.wsrep_cluster_size
(gauge)
The current number of nodes in the Galera cluster.
Shown as node
mysql.innodb.buffer_pool_free
(gauge)
The number of free pages in the InnoDB Buffer Pool.
Shown as page
mysql.innodb.buffer_pool_total
(gauge)
The total number of pages in the InnoDB Buffer Pool.
Shown as page
mysql.innodb.buffer_pool_used
(gauge)
The number of used pages in the InnoDB Buffer Pool.
Shown as page
mysql.innodb.buffer_pool_utilization
(gauge)
The utilization of the InnoDB Buffer Pool.
Shown as fraction
mysql.innodb.current_row_locks
(gauge)
The number of current row locks.
Shown as lock
mysql.innodb.data_reads
(gauge)
The rate of data reads.
Shown as read
mysql.innodb.data_writes
(gauge)
The rate of data writes.
Shown as write
mysql.innodb.mutex_os_waits
(gauge)
The rate of mutex OS waits.
Shown as event
mysql.innodb.mutex_spin_rounds
(gauge)
The rate of mutex spin rounds.
Shown as event
mysql.innodb.mutex_spin_waits
(gauge)
The rate of mutex spin waits.
Shown as event
mysql.innodb.os_log_fsyncs
(gauge)
The rate of fsync writes to the log file.
Shown as write
mysql.innodb.row_lock_time
(gauge)
Fraction of time spent (ms/s) acquiring row locks.
Shown as fraction
mysql.innodb.row_lock_waits
(gauge)
The number of times per second a row lock had to be waited for.
Shown as event
mysql.net.connections
(gauge)
The rate of connections to the server.
Shown as connection
mysql.net.max_connections
(gauge)
The maximum number of connections that have been in use simultaneously since the server started.
Shown as connection
mysql.net.max_connections_available
(gauge)
The maximum permitted number of simultaneous client connections.
Shown as connection
mysql.performance.com_delete
(gauge)
The rate of delete statements.
Shown as query
mysql.performance.com_delete_multi
(gauge)
The rate of delete-multi statements.
Shown as query
mysql.performance.com_insert
(gauge)
The rate of insert statements.
Shown as query
mysql.performance.com_insert_select
(gauge)
The rate of insert-select statements.
Shown as query
mysql.performance.com_replace_select
(gauge)
The rate of replace-select statements.
Shown as query
mysql.performance.com_select
(gauge)
The rate of select statements.
Shown as query
mysql.performance.com_update
(gauge)
The rate of update statements.
Shown as query
mysql.performance.com_update_multi
(gauge)
The rate of update-multi.
Shown as query
mysql.performance.created_tmp_disk_tables
(gauge)
The rate of internal on-disk temporary tables created by second by the server while executing statements.
Shown as table
mysql.performance.created_tmp_files
(gauge)
The rate of temporary files created by second.
Shown as file
mysql.performance.created_tmp_tables
(gauge)
The rate of internal temporary tables created by second by the server while executing statements.
Shown as table
mysql.performance.kernel_time
(gauge)
Percentage of CPU time spent in kernel space by MySQL.
Shown as percent
mysql.performance.key_cache_utilization
(gauge)
The key cache utilization ratio.
Shown as fraction
mysql.performance.open_files
(gauge)
The number of open files.
Shown as file
mysql.performance.open_tables
(gauge)
The number of of tables that are open.
Shown as table
mysql.performance.qcache_hits
(gauge)
The rate of query cache hits.
Shown as hit
mysql.performance.questions
(gauge)
The rate of statements executed by the server.
Shown as query
mysql.performance.slow_queries
(gauge)
The rate of slow queries.
Shown as query
mysql.performance.table_locks_waited
(gauge)
The total number of times that a request for a table lock could not be granted immediately and a wait was needed.
mysql.performance.threads_connected
(gauge)
The number of currently open connections.
Shown as connection
mysql.performance.threads_running
(gauge)
The number of threads that are not sleeping.
Shown as thread
mysql.performance.user_time
(gauge)
Percentage of CPU time spent in user space by MySQL.
Shown as percent
mysql.replication.seconds_behind_master
(gauge)
The lag in seconds between the master and the slave.
Shown as second
mysql.replication.slave_running
(gauge)
A boolean showing if this server is a replication slave that is connected to a replication master.
mysql.replication.slaves_connected
(gauge)
Number of slaves connected to a replication master.
mysql.performance.queries
(gauge)
The rate of queries.
Shown as query
mysql.performance.com_replace
(gauge)
The rate of replace statements.
Shown as query
mysql.performance.com_load
(gauge)
The rate of load statements.
Shown as query
mysql.net.aborted_clients
(gauge)
The number of connections that were aborted because the client died without closing the connection properly.
Shown as connection
mysql.net.aborted_connects
(gauge)
The number of failed attempts to connect to the MySQL server.
Shown as connection
mysql.performance.bytes_sent
(gauge)
The number of bytes sent to all clients.
Shown as byte
mysql.performance.bytes_received
(gauge)
The number of bytes received from all clients.
Shown as byte
mysql.performance.qcache_inserts
(gauge)
The number of queries added to the query cache.
Shown as query
mysql.performance.qcache_lowmem_prunes
(gauge)
The number of queries that were deleted from the query cache because of low memory.
Shown as query
mysql.myisam.key_read_requests
(gauge)
The number of requests to read a key block from the MyISAM key cache.
Shown as read
mysql.myisam.key_reads
(gauge)
The number of physical reads of a key block from disk into the MyISAM key cache. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.
Shown as read
mysql.myisam.key_write_requests
(gauge)
The number of requests to write a key block to the MyISAM key cache.
Shown as write
mysql.myisam.key_writes
(gauge)
The number of physical writes of a key block from the MyISAM key cache to disk.
Shown as write
mysql.myisam.key_buffer_size
(gauge)
Size of the buffer used for index blocks.
Shown as byte
mysql.performance.qcache_size
(gauge)
The amount of memory allocated for caching query results.
Shown as byte
mysql.performance.table_open_cache
(gauge)
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.
mysql.performance.thread_cache_size
(gauge)
How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there.
Shown as byte
mysql.innodb.row_lock_current_waits
(gauge)
The number of row locks currently being waited for by operations on InnoDB tables.
mysql.innodb.buffer_pool_dirty
(gauge)
The total current number of bytes held in dirty pages in the InnoDB buffer pool.
Shown as byte
mysql.innodb.buffer_pool_read_requests
(gauge)
The number of logical read requests.
Shown as read
mysql.innodb.buffer_pool_reads
(gauge)
The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.
Shown as read
mysql.binlog.cache_disk_use
(gauge)
The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.
Shown as transaction
mysql.binlog.cache_use
(gauge)
The number of transactions that used the binary log cache.
Shown as transaction
mysql.performance.handler_commit
(gauge)
The number of internal COMMIT statements.
Shown as operation
mysql.performance.handler_delete
(gauge)
The number of internal DELETE statements.
Shown as operation
mysql.performance.handler_prepare
(gauge)
The number of internal PREPARE statements.
Shown as operation
mysql.performance.handler_read_first
(gauge)
The number of internal READ_FIRST statements.
Shown as operation
mysql.performance.handler_read_key
(gauge)
The number of internal READ_KEY statements.
Shown as operation
mysql.performance.handler_read_next
(gauge)
The number of internal READ_NEXT statements.
Shown as operation
mysql.performance.handler_read_prev
(gauge)
The number of internal READ_PREV statements.
Shown as operation
mysql.performance.handler_read_rnd
(gauge)
The number of internal READ_RND statements.
Shown as operation
mysql.performance.handler_read_rnd_next
(gauge)
The number of internal READ_RND_NEXT statements.
Shown as operation
mysql.performance.handler_rollback
(gauge)
The number of internal ROLLBACK statements.
Shown as operation
mysql.performance.handler_update
(gauge)
The number of internal UPDATE statements.
Shown as operation
mysql.performance.handler_write
(gauge)
The number of internal WRITE statements.
Shown as operation
mysql.performance.opened_tables
(gauge)
The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.
Shown as table
mysql.performance.qcache_total_blocks
(gauge)
The total number of blocks in the query cache.
Shown as block
mysql.performance.qcache_free_blocks
(gauge)
The number of free memory blocks in the query cache.
Shown as block
mysql.performance.qcache_free_memory
(gauge)
The amount of free memory for the query cache.
Shown as byte
mysql.performance.qcache_not_cached
(gauge)
The number of noncached queries (not cacheable, or not cached due to the query_cache_type setting).
Shown as query
mysql.performance.qcache_queries_in_cache
(gauge)
The number of queries registered in the query cache.
Shown as query
mysql.performance.select_full_join
(gauge)
The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
Shown as operation
mysql.performance.select_full_range_join
(gauge)
The number of joins that used a range search on a reference table.
Shown as operation
mysql.performance.select_range
(gauge)
The number of joins that used ranges on the first table. This is normally not a critical issue even if the value is quite large.
Shown as operation
mysql.performance.select_range_check
(gauge)
The number of joins without keys that check for key usage after each row. If this is not 0, you should carefully check the indexes of your tables.
Shown as operation
mysql.performance.select_scan
(gauge)
The number of joins that did a full scan of the first table.
Shown as operation
mysql.performance.sort_merge_passes
(gauge)
The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.
Shown as operation
mysql.performance.sort_range
(gauge)
The number of sorts that were done using ranges.
Shown as operation
mysql.performance.sort_rows
(gauge)
The number of sorted rows.
Shown as operation
mysql.performance.sort_scan
(gauge)
The number of sorts that were done by scanning the table.
Shown as operation
mysql.performance.table_locks_immediate
(gauge)
The number of times that a request for a table lock could be granted immediately.
mysql.performance.threads_cached
(gauge)
The number of threads in the thread cache.
Shown as thread
mysql.performance.threads_created
(count)
The number of threads created to handle connections. If Threads_created is big, you may want to increase the thread_cache_size value.
Shown as thread
mysql.performance.table_cache_hits
(gauge)
The number of hits for open tables cache lookups.
Shown as hit
mysql.performance.table_cache_misses
(gauge)
The number of misses for open tables cache lookups.
Shown as miss
mysql.innodb.buffer_pool_data
(gauge)
The total number of bytes in the InnoDB buffer pool containing data. The number includes both dirty and clean pages.
Shown as byte
mysql.innodb.buffer_pool_pages_data
(gauge)
The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and clean pages.
Shown as page
mysql.innodb.buffer_pool_pages_dirty
(gauge)
The current number of dirty pages in the InnoDB buffer pool.
Shown as page
mysql.innodb.buffer_pool_pages_flushed
(gauge)
The number of requests to flush pages from the InnoDB buffer pool.
Shown as page
mysql.innodb.buffer_pool_pages_free
(gauge)
The number of free pages in the InnoDB buffer pool.
Shown as page
mysql.innodb.buffer_pool_pages_total
(gauge)
The total size of the InnoDB buffer pool, in pages.
Shown as page
mysql.innodb.buffer_pool_read_ahead
(gauge)
The number of pages read into the InnoDB buffer pool by the read-ahead background thread.
Shown as page
mysql.innodb.buffer_pool_read_ahead_evicted
(gauge)
The number of pages read into the InnoDB buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries.
Shown as page
mysql.innodb.buffer_pool_read_ahead_rnd
(gauge)
The number of random read-aheads initiated by InnoDB. This happens when a query scans a large portion of a table but in random order.
Shown as operation
mysql.innodb.buffer_pool_wait_free
(count)
When InnoDB needs to read or create a page and no clean pages are available, InnoDB flushes some dirty pages first and waits for that operation to finish. This counter counts instances of these waits.
Shown as wait
mysql.innodb.buffer_pool_write_requests
(gauge)
The number of writes done to the InnoDB buffer pool.
Shown as write
mysql.innodb.current_transactions
(gauge)
Current InnoDB transactions
Shown as transaction
mysql.innodb.data_fsyncs
(gauge)
The number of fsync() operations per second.
Shown as operation
mysql.innodb.data_pending_fsyncs
(gauge)
The current number of pending fsync() operations.
Shown as operation
mysql.innodb.data_pending_reads
(gauge)
The current number of pending reads.
Shown as read
mysql.innodb.data_pending_writes
(gauge)
The current number of pending writes.
Shown as write
mysql.innodb.data_read
(gauge)
The amount of data read per second.
Shown as byte
mysql.innodb.data_written
(gauge)
The amount of data written per second.
Shown as byte
mysql.innodb.dblwr_pages_written
(gauge)
The number of pages written per second to the doublewrite buffer.
Shown as page
mysql.innodb.dblwr_writes
(gauge)
The number of doublewrite operations performed per second.
Shown as byte
mysql.innodb.history_list_length
(gauge)
History list length as shown in the TRANSACTIONS section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.log_waits
(gauge)
The number of times that the log buffer was too small and a wait was required for it to be flushed before continuing.
Shown as wait
mysql.innodb.log_write_requests
(gauge)
The number of write requests for the InnoDB redo log.
Shown as write
mysql.innodb.log_writes
(gauge)
The number of physical writes to the InnoDB redo log file.
Shown as write
mysql.innodb.hash_index_cells_total
(gauge)
Total number of cells of the adaptive hash index
mysql.innodb.hash_index_cells_used
(gauge)
Number of used cells of the adaptive hash index
mysql.innodb.ibuf_free_list
(gauge)
Insert buffer free list, as shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.ibuf_segment_size
(gauge)
Insert buffer segment size, as shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.ibuf_size
(gauge)
Insert buffer size, as shown in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.pending_aio_log_ios
(gauge)
As shown in the FILE I/O section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.pending_aio_sync_ios
(gauge)
As shown in the FILE I/O section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.pending_ibuf_aio_reads
(gauge)
As shown in the FILE I/O section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.pending_normal_aio_reads
(gauge)
As shown in the FILE I/O section of the SHOW ENGINE INNODB STATUS output.
Shown as read
mysql.innodb.pending_normal_aio_writes
(gauge)
As shown in the FILE I/O section of the SHOW ENGINE INNODB STATUS output.
Shown as write
mysql.innodb.pending_buffer_pool_flushes
(gauge)
As shown in the FILE I/O section of the SHOW ENGINE INNODB STATUS output.
Shown as flush
mysql.innodb.pending_log_flushes
(gauge)
As shown in the FILE I/O section of the SHOW ENGINE INNODB STATUS output.
Shown as flush
mysql.innodb.pending_log_writes
(gauge)
As shown in the FILE I/O section of the SHOW ENGINE INNODB STATUS output.
Shown as write
mysql.innodb.checkpoint_age
(gauge)
Checkpoint age as shown in the LOG section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.lsn_current
(gauge)
Log sequence number as shown in the LOG section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.lsn_flushed
(gauge)
Flushed up to log sequence number as shown in the LOG section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.lsn_last_checkpoint
(gauge)
Log sequence number last checkpoint as shown in the LOG section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.pending_checkpoint_writes
(gauge)
As shown in the FILE I/O section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.queries_inside
(gauge)
As shown in the FILE I/O section of the SHOW ENGINE INNODB STATUS output.
Shown as query
mysql.innodb.queries_queued
(gauge)
As shown in the FILE I/O section of the SHOW ENGINE INNODB STATUS output.
Shown as query
mysql.innodb.read_views
(gauge)
As shown in the FILE I/O section of the SHOW ENGINE INNODB STATUS output.
mysql.myisam.key_buffer_bytes_unflushed
(gauge)
MyISAM key buffer bytes unflushed.
Shown as byte
mysql.myisam.key_buffer_bytes_used
(gauge)
MyISAM key buffer bytes used.
Shown as byte
mysql.innodb.mem_adaptive_hash
(gauge)
As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output.
Shown as byte
mysql.innodb.mem_dictionary
(gauge)
As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output.
Shown as byte
mysql.innodb.mem_total
(gauge)
As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output.
Shown as byte
mysql.innodb.mem_additional_pool
(gauge)
As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output.
Shown as byte
mysql.innodb.mem_file_system
(gauge)
As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.mem_lock_system
(gauge)
As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.mem_page_hash
(gauge)
As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.mem_recovery_system
(gauge)
As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.os_log_pending_fsyncs
(gauge)
Number of pending InnoDB log fsync (sync-to-disk) requests.
Shown as operation
mysql.innodb.os_log_pending_writes
(gauge)
Number of pending InnoDB log writes.
Shown as write
mysql.innodb.os_log_written
(gauge)
Number of bytes written to the InnoDB log.
Shown as byte
mysql.innodb.pages_created
(gauge)
Number of InnoDB pages created.
Shown as page
mysql.innodb.pages_read
(gauge)
Number of InnoDB pages read.
Shown as page
mysql.innodb.pages_written
(gauge)
Number of InnoDB pages written.
Shown as page
mysql.innodb.rows_deleted
(gauge)
Number of rows deleted from InnoDB tables.
Shown as row
mysql.innodb.rows_inserted
(gauge)
Number of rows inserted into InnoDB tables.
Shown as row
mysql.innodb.rows_read
(gauge)
Number of rows read from InnoDB tables.
Shown as row
mysql.innodb.rows_updated
(gauge)
Number of rows updated in InnoDB tables.
Shown as row
mysql.innodb.s_lock_os_waits
(gauge)
As shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output
mysql.innodb.s_lock_spin_rounds
(gauge)
As shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.s_lock_spin_waits
(gauge)
As shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.
Shown as wait
mysql.innodb.x_lock_os_waits
(gauge)
As shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.
Shown as wait
mysql.innodb.x_lock_spin_rounds
(gauge)
As shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.
mysql.innodb.x_lock_spin_waits
(gauge)
As shown in the SEMAPHORES section of the SHOW ENGINE INNODB STATUS output.
Shown as wait
mysql.galera.wsrep_local_recv_queue_avg
(gauge)
Shows the average size of the local received queue since the last status query.
mysql.galera.wsrep_flow_control_paused
(gauge)
Shows the fraction of the time, since FLUSH STATUS was last called, that the node paused due to Flow Control.
Shown as fraction
mysql.galera.wsrep_cert_deps_distance
(gauge)
Shows the average distance between the lowest and highest sequence number, or seqno, values that the node can possibly apply in parallel.
mysql.galera.wsrep_local_send_queue_avg
(gauge)
Show an average for the send queue length since the last FLUSH STATUS query.
mysql.performance.qcache.utilization
(gauge)
Fraction of the query cache memory currently being used.
Shown as fraction
mysql.performance.digest_95th_percentile.avg_us
(gauge)
Query response time 95th percentile per schema.
Shown as microsecond
mysql.performance.query_run_time.avg
(gauge)
Avg query response time per schema.
Shown as microsecond
mysql.binlog.disk_use
(gauge)
Total binary log file size.
Shown as byte

このチェックは、デフォルトではすべてのメトリクスを収集しません。以下のブール値構成オプションを true に設定することで、対応するメトリクスが有効になります。

extra_status_metrics は、次のメトリクスを追加します。

メトリクス名メトリクスタイプ
mysql.binlog.cache_disk_useゲージ (GAUGE)
mysql.binlog.cache_useゲージ (GAUGE)
mysql.performance.handler_commitレート (RATE)
mysql.performance.handler_deleteレート (RATE)
mysql.performance.handler_prepareレート (RATE)
mysql.performance.handler_read_firstレート (RATE)
mysql.performance.handler_read_keyレート (RATE)
mysql.performance.handler_read_nextレート (RATE)
mysql.performance.handler_read_prevレート (RATE)
mysql.performance.handler_read_rndレート (RATE)
mysql.performance.handler_read_rnd_nextレート (RATE)
mysql.performance.handler_rollbackレート (RATE)
mysql.performance.handler_updateレート (RATE)
mysql.performance.handler_writeレート (RATE)
mysql.performance.opened_tablesレート (RATE)
mysql.performance.qcache_total_blocksゲージ (GAUGE)
mysql.performance.qcache_free_blocksゲージ (GAUGE)
mysql.performance.qcache_free_memoryゲージ (GAUGE)
mysql.performance.qcache_not_cachedレート (RATE)
mysql.performance.qcache_queries_in_cacheゲージ (GAUGE)
mysql.performance.select_full_joinレート (RATE)
mysql.performance.select_full_range_joinレート (RATE)
mysql.performance.select_rangeレート (RATE)
mysql.performance.select_range_checkレート (RATE)
mysql.performance.select_scanレート (RATE)
mysql.performance.sort_merge_passesレート (RATE)
mysql.performance.sort_rangeレート (RATE)
mysql.performance.sort_rowsレート (RATE)
mysql.performance.sort_scanレート (RATE)
mysql.performance.table_locks_immediateゲージ (GAUGE)
mysql.performance.table_locks_immediate.rateレート (RATE)
mysql.performance.threads_cachedゲージ (GAUGE)
mysql.performance.threads_created単調増加 (MONOTONIC)

extra_innodb_metrics は、次のメトリクスを追加します。

メトリクス名メトリクスタイプ
mysql.innodb.active_transactionsゲージ (GAUGE)
mysql.innodb.buffer_pool_dataゲージ (GAUGE)
mysql.innodb.buffer_pool_pages_dataゲージ (GAUGE)
mysql.innodb.buffer_pool_pages_dirtyゲージ (GAUGE)
mysql.innodb.buffer_pool_pages_flushedレート (RATE)
mysql.innodb.buffer_pool_pages_freeゲージ (GAUGE)
mysql.innodb.buffer_pool_pages_totalゲージ (GAUGE)
mysql.innodb.buffer_pool_read_aheadレート (RATE)
mysql.innodb.buffer_pool_read_ahead_evictedレート (RATE)
mysql.innodb.buffer_pool_read_ahead_rndゲージ (GAUGE)
mysql.innodb.buffer_pool_wait_free単調増加 (MONOTONIC)
mysql.innodb.buffer_pool_write_requestsレート (RATE)
mysql.innodb.checkpoint_ageゲージ (GAUGE)
mysql.innodb.current_transactionsゲージ (GAUGE)
mysql.innodb.data_fsyncsレート (RATE)
mysql.innodb.data_pending_fsyncsゲージ (GAUGE)
mysql.innodb.data_pending_readsゲージ (GAUGE)
mysql.innodb.data_pending_writesゲージ (GAUGE)
mysql.innodb.data_readレート (RATE)
mysql.innodb.data_writtenレート (RATE)
mysql.innodb.dblwr_pages_writtenレート (RATE)
mysql.innodb.dblwr_writesレート (RATE)
mysql.innodb.hash_index_cells_totalゲージ (GAUGE)
mysql.innodb.hash_index_cells_usedゲージ (GAUGE)
mysql.innodb.history_list_lengthゲージ (GAUGE)
mysql.innodb.ibuf_free_listゲージ (GAUGE)
mysql.innodb.ibuf_mergedレート (RATE)
mysql.innodb.ibuf_merged_delete_marksレート (RATE)
mysql.innodb.ibuf_merged_deletesレート (RATE)
mysql.innodb.ibuf_merged_insertsレート (RATE)
mysql.innodb.ibuf_mergesレート (RATE)
mysql.innodb.ibuf_segment_sizeゲージ (GAUGE)
mysql.innodb.ibuf_sizeゲージ (GAUGE)
mysql.innodb.lock_structsレート (RATE)
mysql.innodb.locked_tablesゲージ (GAUGE)
mysql.innodb.locked_transactionsゲージ (GAUGE)
mysql.innodb.log_waitsレート (RATE)
mysql.innodb.log_write_requestsレート (RATE)
mysql.innodb.log_writesレート (RATE)
mysql.innodb.lsn_currentレート (RATE)
mysql.innodb.lsn_flushedレート (RATE)
mysql.innodb.lsn_last_checkpointレート (RATE)
mysql.innodb.mem_adaptive_hashゲージ (GAUGE)
mysql.innodb.mem_additional_poolゲージ (GAUGE)
mysql.innodb.mem_dictionaryゲージ (GAUGE)
mysql.innodb.mem_file_systemゲージ (GAUGE)
mysql.innodb.mem_lock_systemゲージ (GAUGE)
mysql.innodb.mem_page_hashゲージ (GAUGE)
mysql.innodb.mem_recovery_systemゲージ (GAUGE)
mysql.innodb.mem_thread_hashゲージ (GAUGE)
mysql.innodb.mem_totalゲージ (GAUGE)
mysql.innodb.os_file_fsyncsレート (RATE)
mysql.innodb.os_file_readsレート (RATE)
mysql.innodb.os_file_writesレート (RATE)
mysql.innodb.os_log_pending_fsyncsゲージ (GAUGE)
mysql.innodb.os_log_pending_writesゲージ (GAUGE)
mysql.innodb.os_log_writtenレート (RATE)
mysql.innodb.pages_createdレート (RATE)
mysql.innodb.pages_readレート (RATE)
mysql.innodb.pages_writtenレート (RATE)
mysql.innodb.pending_aio_log_iosゲージ (GAUGE)
mysql.innodb.pending_aio_sync_iosゲージ (GAUGE)
mysql.innodb.pending_buffer_pool_flushesゲージ (GAUGE)
mysql.innodb.pending_checkpoint_writesゲージ (GAUGE)
mysql.innodb.pending_ibuf_aio_readsゲージ (GAUGE)
mysql.innodb.pending_log_flushesゲージ (GAUGE)
mysql.innodb.pending_log_writesゲージ (GAUGE)
mysql.innodb.pending_normal_aio_readsゲージ (GAUGE)
mysql.innodb.pending_normal_aio_writesゲージ (GAUGE)
mysql.innodb.queries_insideゲージ (GAUGE)
mysql.innodb.queries_queuedゲージ (GAUGE)
mysql.innodb.read_viewsゲージ (GAUGE)
mysql.innodb.rows_deletedレート (RATE)
mysql.innodb.rows_insertedレート (RATE)
mysql.innodb.rows_readレート (RATE)
mysql.innodb.rows_updatedレート (RATE)
mysql.innodb.s_lock_os_waitsレート (RATE)
mysql.innodb.s_lock_spin_roundsレート (RATE)
mysql.innodb.s_lock_spin_waitsレート (RATE)
mysql.innodb.semaphore_wait_timeゲージ (GAUGE)
mysql.innodb.semaphore_waitsゲージ (GAUGE)
mysql.innodb.tables_in_useゲージ (GAUGE)
mysql.innodb.x_lock_os_waitsレート (RATE)
mysql.innodb.x_lock_spin_roundsレート (RATE)
mysql.innodb.x_lock_spin_waitsレート (RATE)

extra_performance_metrics は、次のメトリクスを追加します。

メトリクス名メトリクスタイプ
mysql.performance.query_run_time.avgゲージ (GAUGE)
mysql.performance.digest_95th_percentile.avg_usゲージ (GAUGE)

schema_size_metrics は、次のメトリクスを追加します。

メトリクス名メトリクスタイプ
mysql.info.schema.sizeゲージ (GAUGE)

イベント

MySQL チェックには、イベントは含まれません。

サービスのチェック

mysql.replication.slave_running:
監視対象の MySQL インスタンスに Agent が接続できない場合は、CRITICAL を返します。それ以外の場合は、OK を返します。詳細については、ここを参照してください。

mysql.can_connect:
Agent が MySQL に接続してメトリクスを収集できない場合は、CRITICAL を返します。それ以外の場合は、OK を返します。

トラブルシューティング

その他の参考資料

Datadog を使用した MySQL の監視については、一連のブログ記事を参照してください。