MySQL 詳解¶
著者：劉鋭
データベースはビジネスの要であり、アプリケーションアーキテクチャとアプリケーションパフォーマンスを左右します。アプリケーションサービスのパフォーマンスはサービス自体の水平スケーリングで解決できますが、データベースのパフォーマンスがアプリケーションの最終的な命運を決定します。MySQLはデータベースの王様として、ほぼあらゆる業界で使用されています。ビジネスの増加に伴い、不適切なSQLの使用や大量のスロークエリがアプリケーションを低下させる可能性があるため、その監視が非常に重要です。
MySQL 統合¶
MySQL モニタリング¶
主に4つの観点からMySQL関連のメトリクス情報を全体的に確認します。
- 概要
- アクティブユーザー情報
- InnoDB
- ロック情報
概要¶
概要セクションでは、主に接続数、QPS、TPS、異常接続数、毎秒のインデックスなし結合（join）クエリ回数、スキーマサイズ分布、スロークエリ、ロック待機時間などの観点からMySQLの概要分析を行います。
アクティブユーザー情報¶
MySQLのコネクション（connection）に注目したことはありますか？まずはエラーを見てみましょう。
MySQLの接続は、長い接続（長连接）と短い接続（短连接）の両方を許可していることを知っています。接続を確立するプロセス自体に大きなオーバーヘッドがあるため、一般的には長い接続（長连接）が採用されます。しかし、長い接続（長连接）を使用すると、メモリ使用量が増加する可能性があります。これは、MySQLがクエリ実行中に接続オブジェクトを管理するために一時的にメモリを使用し、これらの接続オブジェクトリソースは接続が切断されたときにのみ解放されるためです。長い接続（長连接）が多数蓄積されると、メモリ使用量が増大し、システムによって強制KILLされ、MySQLサービスの異常再起動が発生する可能性があります。
このような長い接続（長连接）の状況に対しては、定期的に切断する必要があります。接続が占有するメモリサイズを判断することで、永続的な長い接続（長连接）であるかどうかを推測できます。また、大規模な操作を実行するたびに mysql_reset_connection を実行して、接続リソースを再初期化することもできます。
MySQLの接続は、通常、1つのユーザーリクエストに対して1つの接続が割り当てられます。リクエスト操作が長時間実行されない場合、接続が滞留し、データベースの接続数を急速に消費します。つまり、データベース内に長時間実行が完了しないSQLがある場合、そのSQLは接続を占有し続け、解放しません。その間、アプリケーションからのリクエストは絶えずデータベースに流入し続け、データベースの接続数が急速に使い果たされます。
クラウドネイティブ、マイクロサービスの文脈では、データベースのコネクション（Connection）に対する要求がますます高まっており、MySQLのコネクション（Connection）はアプリケーションのボトルネックになりやすくなっています。Too many connections は、MySQLが動作するマシンのCPUを100%にし、同時にアプリケーションがそれ以上のコネクション（Connection）を取得できなくなり、ビジネスを中断させる原因となります。これをリアルタイムで監視することで、データベースのボトルネックを迅速に見つけることができます。さらに、各ユーザーのコネクション（Connection）に関する詳細、例えば現在のユーザーの接続数や累積接続数なども見つけることができます。
また、現在のコネクション（Connection）に基づいてMySQLに最適化作業を実行することもできます。
- 最大接続数を増加
- マスター・スレーブバックアップによる読み取り/書き込み分離
- ビジネス分割による複数データベースインスタンスの導入
- キャッシュの追加・削減によるクエリの削減
- など
InnoDB¶
mysql.conf の innodb=true パラメータを設定することで、InnoDB メトリクスの収集を有効にします。
ロック情報¶
MySQL スロークエリ¶
本番業務システムにとって、スロークエリは障害やリスクでもあります。障害が発生すると、システムが利用不能になり、本番業務に影響を及ぼす可能性があります。大量のスロークエリが存在し、SQLの実行が遅ければ遅いほど、消費するCPUリソースやIOリソースも大きくなります。したがって、この種の障害を解決・回避するには、スロークエリ自体に注目することが鍵となります。
現在、スロークエリを最適化する方法は主に2つあります。
slow log スロークエリログを有効にし、スロークエリログを収集し、人間がスロークエリSQLに対して explain を実行します。
Guance を利用して、MySQL で dbm を有効にし、データベースのパフォーマンスメトリクスを収集します。同時に、実行時間の長いSQL文を自動的に選択し、その実行計画を取得し、実際の実行プロセスにおけるさまざまなパフォーマンスメトリクスを収集します。
MySQL slow log¶
広義のスロークエリ¶
私たちがよく目にするのは狭義のスロークエリ、つまりクエリ時間が既定の時間を超えたものです。例えば、デフォルトでは10秒以上経過しても結果が返ってこないクエリ文はスロークエリとマークされます。これ以外にも、スロークエリを引き起こす可能性のある状況があり、これらもスロークエリとしてマークできます。
- 返されるレコードセットが大きい。
- インデックスを使用しないクエリを頻繁に使用する。
スロークエリログの有効化¶
以下の設定は、MySQL 5.7 でスロークエリログを有効にする方法です。
#### slow log スロークエリログ ####
slow_query_log = 1 ## スロークエリログを有効化
slow_query_log_file = /var/log/mysql/slow.log ## スロークエリログファイル名
long_query_time = 2 ## SQL文が2秒を超えたら記録
# min_examined_row_limit = 100 ## SQL実行中にexamined_rowで取得したデータが100行を超えた場合のみ記録
#log-queries-not-using-indexes ## インデックスを使用しないSQLをスロークエリに記録
log_throttle_queries_not_using_indexes = 5 ## インデックスを使用しないSQLの記録を1分間に5回に制限（1つのSQL文が記録され続けるのを防ぐため）
log-slow-admin-statements = table ## 管理操作（alter、analyze table コマンドなど）を記録
log_output = file ## スロークエリログの形式（FILE|TABLE|NONE、デフォルトはファイル形式、TABLEはテーブル形式、テーブル形式は推奨しない）
log_timestamps = 'system' ## スローログの記録時間形式（システム時間を採用）
ここでは上位100件のスロークエリ文を記録しています。さらに多くのスロークエリを確認するには、ログエクスプローラーで詳細なログ情報を確認できます。
MySQL dbm¶
データベースパフォーマンスメトリクスは、主にMySQLの組み込みデータベース performance_schema から収集されます。このデータベースは、実行時にサーバー内部の実行状況を取得する方法を提供します。このデータベースを介して、DataKitは過去のクエリ文の各種メトリクス統計、クエリ文の実行計画、およびその他の関連パフォーマンスメトリクスを収集できます。収集されたパフォーマンスメトリクスデータはログとして保存され、source はそれぞれ mysql_dbm_metric、mysql_dbm_sample、mysql_dbm_activity です。
dbm を有効にすることで、データベースパフォーマンスメトリクスデータを直接収集できます。コレクターの設定については、MySQL を参照してください。
[[inputs.mysql]]
# データベースパフォーマンスメトリクス収集を有効化
dbm = true
...
# モニタリングメトリクス設定
[inputs.mysql.dbm_metric]
enabled = true
# モニタリングサンプリング設定
[inputs.mysql.dbm_sample]
enabled = true
# 待機イベント収集
[inputs.mysql.dbm_activity]
enabled = true
...
mysql_dbm_metric ビュー¶
dbm を有効にして収集されたデータベースパフォーマンスメトリクスは、ビュー上で現在のデータのパフォーマンスを直感的に分析できます：スロークエリ最大所要時間、スロー挿入最大所要時間、スロークエリSQL実行回数、単一SQL最大実行回数（実行頻度）、最長ロック時間など。
ビュー【SQL 所要時間 TOP 20】では、クエリ時間の降順でソートし、上位20件のスロークエリSQLを取得します。パラメータを調整することで、必要なTOP Nを表示することもできます。
mysql_dbm_activity ビュー¶
mysql_dbm_activity ビューを構築することで、現在実行中のSQL数、イベントタイプ分布（現在のイベントがCPUイベントかUser sleepイベントかなど）、イベント状態分布（例：Sending data、Creating sort indexなど）、イベントコマンドタイプ（Command Type）分布（例：現在の状態がQueryかsleepかなど）、およびイベントリストを監視できます。
イベントタイプ分布¶
Processing SQL のイベントタイプを指します。
- CPU
- User sleep
イベント状態分布¶
現在の Processing SQL の状態タイプ分布状況です。主な状態タイプは以下のとおりです。
- init ：初期実行
- Sending data： データ送信中
- Creating sort index ： ソートインデックス作成中
- freeing items：現在の項目を解放中
- converting HEAP to MyISAM：ヒープをMyISAMに変換中
- query end：クエリ完了
- Opening tables：テーブルを開いています
- statistics：統計
イベントコマンドタイプ（Command Type）分布¶
現在の Processing SQL のコマンドタイプ（Command Type）分布状況です。主なタイプは以下のとおりです。
- query : クエリ。query は
イベント状態と合わせて分析する必要があります。 - sleep：スリープ。まだスケジュールされていません。
- daemon：デーモン（daemon）方式で実行中。
イベントリスト¶
イベント Top 100。現在の100件のイベントレコードを表示します。これには、イベントID（processlist_id）、processlist_user（現在のイベントが属するユーザー）、DB Host（イベントホスト）、SQL（実行イベント文）、process Host（イベント発行ホスト）、イベントタイプ、イベント状態、イベント実行時間などが含まれます。
スキーマごとのプロセス（process）イベント推移の確認¶
対応するスキーマのイベント推移を確認することで、現在のスキーマの負荷状況を分析します。
ビューテンプレート¶
[MySQL モニタリングビュー]
[MySQL Activity]
[MySQL dbm Metric]
[MySQL スロークエリ]