MySQLで実行された全てのクエリを把握する方法を挙げてみる。 いくつか方法はあるが、負荷の高い環境でクエリをとるにはうするのが良いだろうか?
パフォーマンスチューニングが主たる目的なので、Restoreからロールフォワードすることは考えない。 SELECT文が取れないので、SBR形式でbinary_logを書くことも除く。 version, 5.6, 5.7あたりを想定。
- general_log
- slow_query_log, long_query_time = 0
- events_statements_history テーブルを使う
- MySQLへのパッケットをキャプチャする
- 感想
general_log
標準的なクエリ取得方法。
general_log = 'ON'
に設定することで取得する。
標準的なやり方だが、全てのクエリがgeneral_log_fileに書き込まれるのでパフォーマンスの悪化が大きい。 ここに書き込まれる順番はmysqldがクエリを受け取った順番なので、実行順序とは異なる。
MySQL :: MySQL 5.7 Reference Manual :: 5.4.3 The General Query Log
再起動不要。
2018-06-23T17:27:52.827345Z 6 Query select * from t1
slow_query_log, long_query_time = 0
閾値を0にしてスロークエリ(すべてのクエリ)をロギングする。 general_logと同じようにファイルに書くのでパフォーマンスへの影響は大きい。 ファイル書き込みで遅くなる分と比べれば大した違いではないと思うが、閾値との比較が入る分微妙に遅くなるんじゃないかと思う。
Administrative statementsやインデックスを使わないクエリはデフォルトでは出力されないので注意が必用。
これらを取得するには、それぞれlog_slow_admin_statements, log_queries_not_using_indexesをONにする必用がある。
Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.
参照
2つのオプション含め再起動不要
# Time: 2018-06-23T17:27:52.827545Z # User@Host: tombo[tombo] @ [192.168.1.9] Id: 6 # Query_time: 0.000240 Lock_time: 0.000128 Rows_sent: 0 Rows_examined: 0 SET timestamp=1529774872; select * from t1;
events_statements_history テーブルを使う
performance_schemaのstatement event tableを使う。
MySQL :: MySQL 5.7 Reference Manual :: 24.11.6 Performance Schema Statement Event Tables
current, history, history_longと保持してくれている量に違いがあるので、欠損しない間隔でpollingして取得すればすべてのクエリが取れる。 DIGEST_STATEMENTである程度パラメータを抽象化したクエリが取れるほか、Instrumentsからの計測結果も取得できる。 currentを除くhistory系の保持件数performance_schema_events_stages_history(long)sizeの変更は再起動が必用。
mysql> select * from performance_schema.events_statements_history\G ...略 *************************** 15. row *************************** THREAD_ID: 31 EVENT_ID: 5 END_EVENT_ID: 5 EVENT_NAME: statement/sql/select SOURCE: socket_connection.cc:101 TIMER_START: 488005568226000 TIMER_END: 488005868363000 TIMER_WAIT: 300137000 LOCK_TIME: 128000000 SQL_TEXT: select * from t1 DIGEST: 23a2bd5d5f00f61920a837cbec0340c8 DIGEST_TEXT: SELECT * FROM `t1` CURRENT_SCHEMA: sample OBJECT_TYPE: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: NULL MYSQL_ERRNO: 0 RETURNED_SQLSTATE: NULL MESSAGE_TEXT: NULL ERRORS: 0 WARNINGS: 0 ROWS_AFFECTED: 0 ROWS_SENT: 0 ROWS_EXAMINED: 0 CREATED_TMP_DISK_TABLES: 0 CREATED_TMP_TABLES: 0 SELECT_FULL_JOIN: 0 SELECT_FULL_RANGE_JOIN: 0 SELECT_RANGE: 0 SELECT_RANGE_CHECK: 0 SELECT_SCAN: 1 SORT_MERGE_PASSES: 0 SORT_RANGE: 0 SORT_ROWS: 0 SORT_SCAN: 0 NO_INDEX_USED: 1 NO_GOOD_INDEX_USED: 0 NESTING_EVENT_ID: NULL NESTING_EVENT_TYPE: NULL NESTING_EVENT_LEVEL: 0 15 rows in set (0.00 sec)
MySQLへのパッケットをキャプチャする
tcpdumpやwiresharkでMySQLが使うportでフィルタしてパケットをキャプチャする。 デコードする必用があるのと、SSLで通信されているとdecryptしないといけない。
MySQLが動作している端末でキャプチャする方法とルーター等でポートミラーリングして他の端末でキャプチャする方法があり、後者のほうがMySQLのパフォーマンスには影響しにくいと思うが、ポートミラーリングによるネットワーク機器への負荷はどのくらい問題になるのかわからない。
感想
event_statements_historyかtcpdumpのどちらかでやるのがよさそうだけど、それぞれがどのくらいIO, Memory, CPUを圧迫するかは実験してみよう。 そもそもこういうのってコンプライアンス的に難しいのか?サービスによるのか?