はじめに
MySQLのバイナリログについて学習したことを簡単にまとめます。
検証に使用した環境
DB：MySQL8.0
スロークエリーログとは
スロークエリーログには、実行に多くの時間を要したSQLステートメントがロギングされます。
ロギングの判断基準は、long_query_timeシステム変数で指定した秒数を超えた場合となります。
※デフォルトでは、スロークエリーログは無効になっています
このログを使用することで、実行に長い時間を要する最適化候補となるクエリーを見つけることができます。
ただし、デフォルトの状態では、ALTER文などの管理ステートメントや、参照にインデックスを使用しない（インデックスが効かない）クエリーは対象外となります。
また、mysqldumpslowコマンドを使用するとスロークエリーログファイルの内容を要約することもできます。
ロギングされるタイミング
ステートメントを実行し、全てのロックが解放した後にロギングされます。
そのため、実行順とは異なる順番で書き込まれることがあります。
ロギングの制御
スロークエリーログへの出力有無はシステム変数の設定値をもとに制御します。
判断順序
MySQLは以下の手順に従い、スロークエリーログへの出力有無を判断します。
1.クエリーは管理ステートメントかどうか
管理ステートメントである場合、log_slow_admin_statementsが有効であること。
2.クエリーの実行にlong_query_timeで指定した秒数かかっているか
また、インデックスが効いてないクエリーの場合、log_queries_not_using_indexesが有効であること
3.クエリーはmin_examined_row_limitで指定した行数を検査しているか
4.log_throttle_queries_not_using_indexesにより、1分単位のログ出力の制限がされていないこと
システム変数
slow_query_log
スロークエリーログの有効・無効を指定します。
1または引数指定なし → 有効
0 → 無効
slow_query_log=1 # 有効の場合
slow_query_log_file
ログファイル名を指定します。
指定しない場合、host_name-slow.logとなります。
絶対パスで別のディレクトリを指定しないかぎり、データディレクトリ内にファイルを作成します。
log_output
ログの出力先を指定します。
log_outputには、以下の単語をカンマ区切りで指定します。
TABLE → テーブルに出力
FILE → ファイルに出力
NONE → テーブル、ファイルに記録しない
※NONEがある場合、他よりも優先されます。
起動時にlog_outputを指定していない場合、デフォルトのロギング先はFILEになります。
long_query_time
スロークエリーログに書き込むステートメントの実行時間の閾値を指定します。
設定出来る精度はマイクロ秒までで、最小値は0秒、デフォルト値は10秒となっています。
log_slow_admin_statements
スロークエリーログへのロギング対象にする管理ステートメントを指定します。
含められる管理ステートメントは以下の通り、
ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE、および REPAIR TABLE
※MySQL5.6.11より古い場合、システム変数名はlog-slow-admin-statementsです
log_queries_not_using_indexes
ONにすることで、インデックスが効かないクエリーもログに出力されるようになります。
ただし、インデックスを効かないクエリーもログに出力した場合、出力量が増大し、改善すべきクエリーを検出しにくくなる可能性があります。
log_throttle_queries_not_using_indexes
1分あたりにスロークエリーログへ書き込み出来るクエリー数を設定します。
デフォルト値は0で「制限なし」です。
※log_queries_not_using_indexesが有効であること
min_examined_row_limit
行数を指定します。
指定された行数より少ない行を検査するクエリーはスロークエリーログに書き込まれなくなります。
デフォルトは0です。
ロギングされる内容
※以下、公式Docより抜粋
・Query_time: duration
ステートメントの実行時間 (秒)。
・Lock_time: duration
ロックを取得する時間 (秒)。
・Rows_sent: N
クライアントに送信された行数。
・Rows_examined:
サーバーレイヤーによって検査された行数 (ストレージエンジン内部の処理はカウントされません)。
追加フィールド
ファイル出力のみ、log_slow_extraシステム変数を有効にすると出力される項目が追加されます。
※log_slow_extraはMySQL8.0.14から使用可能
※追加出力される項目は以下、公式Docより抜粋
・Thread_id: ID
ステートメントスレッド識別子。
・Errno: error_number
ステートメントのエラー番号。
エラーが発生しなかった場合は0。
・Killed: N
ステートメントが終了した場合、理由を示すエラー番号。
ステートメントが正常に終了した場合は0。
・Bytes_received: N
ステートメントのBytes_received値。
・Bytes_sent: N
ステートメントのBytes_sent値。
・Read_first: N
ステートメントのHandler_read_first値。
・Read_last: N
ステートメントのHandler_read_last値。
・Read_key: N
ステートメントのHandler_read_key値。
・Read_next: N
ステートメントのHandler_read_next値。
・Read_prev: N
ステートメントのHandler_read_prev値。
・Read_rnd: N
ステートメントのHandler_read_rnd値。
・Read_rnd_next: N
ステートメントのHandler_read_rnd_next値。
・Sort_merge_passes: N
ステートメントのSort_merge_passes値。
・Sort_range_count: N
ステートメントのSort_range値。
・Sort_rows: N
ステートメントのSort_rows値。
・Sort_scan_count: N
ステートメントのSort_scan値。
・Created_tmp_disk_tables: N
ステートメントのCreated_tmp_disk_tables値。
・Created_tmp_tables: N
ステートメントのCreated_tmp_tables値。
・Start: timestamp
ステートメントの実行開始時間。
・End: timestamp
ステートメントの実行終了時間。
参考文献