この記事はLINE Advent Calendar 2018の10日目の記事です。
こんにちは、LINEのデータベース室に所属しています大塚(tom__bo)です。 データベース室での業務としては主にMySQLを中心としたDBのオペレーション, 各種DBの管理ツール・検証ツールの開発を行っています。
この記事ではMySQLのパフォーマンス情報を取得する際に利用するperformance_schemaデータベースとinformation_schemaデータベースのinnodb_metricsテーブルを全て有効にすることでどの程度MySQL全体のパフォーマンスが悪化するかを実験した内容の一部を紹介します。 突然これらについて書いても戸惑う方も多いと思うので、最初にこれらの簡単な説明をします。また、performance_schema, information_schema.innodb_metricsについては奥野さんの著書, 詳解MySQL5.7[1]を参考に勉強しました。 そのため、詳細についてはそちらを一読されることをおすすめします。
パフォーマンス調査に利用するデータベース、テーブル
MySQLのパフォーマンス情報を取得するには、performance_schemaの計器(instruments)を利用する方法とinformation_schemaのinnodb_metricsテーブル(またはこれを含むパフォーマンス関連のテーブルをまとめたsys.metricsビュー)を利用する方法が一般的だと思います。 これらから取得できるパフォーマンス情報によってMySQL内部や、特にInnoDBストレージエンジンでどういった処理が何回呼ばれ、それらの処理にどれくらい時間がかかっているのかを把握することが可能です。 一方で、パフォーマンス情報を取得するためのオーバーヘッドが当然あります。 今回は、これらのパフォーマンス情報を取得するのにどれくらいのオーバーヘッドがかかるのかを簡単に実験したので、その結果を紹介しようと思います。 まず、performance_schemaとinnodb_metricsの利用方法を簡単に説明し、次にそれらのパフォーマンス影響に関する実験結果を紹介します。 また、実験にあたって、MySQLのバージョンは5.7.22-communityを利用しました。
performance_schema
概要
MySQLのパフォーマンス情報を取ろうとしたときに最初に見るのがこのperformance_schemaデータベースかと思います。 performance_schemaにはMySQLのソースコードの各所に埋め込まれたinstrumentsから情報を収集することで、ある処理が呼ばれた回数やその処理にかかった時間などの情報が蓄積されています。 ユーザがよく使うものはデフォルトで有効化されているので、特に設定をしなくても情報を見ることができますが、それ以外のものは必要に応じて設定を有効化する必要があります。 performance_schemaは情報を取得するinstrumentsとそこで取れた情報を格納するconsumersを設定することで利用できるようになっており、多くのinstrumentsは対応するconsumersに情報を格納するという仕組みになっています。
具体的にはperformance_schemaデータベースにある、setup_instruments, setup_objects, setup_consumers, threads, setup_actors, setup_timersテーブルで対応するレコードを設定(有効化や取得方法の指定)します。
デフォルトではobjects(データベース), threads, actors(クライアントユーザ)は計測が有効になっているので、これらをフィルタする必要がなければ、setup_instruments, setup_consumersで収集する対象のinstruments, consumerを有効にすればデフォルトの設定以外のinstrumentsで計測を行えます。[2]
取得できる情報
instrumentsの側面からパフォーマンス情報を取得できる項目を見てみます。 これらはperformance_schema.setup_instrumentsテーブルで確認することができます。
5.7.22では以下のように1032個のinstrumentsがあり、さらに/
で区切られて分類されています。1つめの/
までの大分類を見ると以下のような結果となりました。
select substring_index(name, '/', 1) as category, count(*) from setup_instruments group by substring_index(name, '/', 1);
+-------------+----------+
| category | count(*) |
+-------------+----------+
| idle | 1 |
| memory | 379 |
| stage | 132 |
| statement | 193 |
| transaction | 1 |
| wait | 326 |
+-------------+----------+
6 rows in set (0.00 sec)
詳解MySQL5.7[1]のP188, リスト5.2と比較するとidle, transactionを除くカテゴリで計器の数が増えていることがわかります。 これが8.0.13ではさらにerrorというカテゴリが増え、全部で1217個に増えていました。
利用方法
setup_instrumentsテーブルではENABLED, TIMEDカラム、setup_consumersテーブルではENABLEDカラムの値で機能を有効化する構造になっているので、これらを有効化します。 有効化する場合は、UPDATE文でこれらの値を'YES'に、無効化する場合は'NO'に更新します。 setup_insturmentsのTIMEDカラムの値を'YES'にすることで有効にしたinstrumentsのタイミング情報を取得することができます。 またmemoryカテゴリのinstrumentsはこれを計測する仕組みがないためTIMEDカラムをYESにUPDATEしてもクエリは成功しますが、このカテゴリのレコードの値は'NO'のままになり、有効化できません。
以下にそれぞれのテーブルのサンプルを示します。
select * from setup_instruments limit 1;
+-------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO | NO |
+-------------------------------------------+---------+-------+
select * from setup_consumers limit 1;
+-----------------------+---------+
| NAME | ENABLED |
+-----------------------+---------+
| events_stages_current | NO |
+-----------------------+---------+
INFORMATION_SCHEMA.INNODB_METRICS
概要
INNODB_METRICSテーブルにはinnodb_monitorから取得したInnoDBストレージエンジンの情報が蓄積されます。 performance_schemaから取得できる情報と重なる部分もありますが、performance_schemaや'SHOW ENGINE INNODB STATUS'では表示されない情報も取得することができます。
INNODB_METRICSテーブルにはカウンターの名前である'NAME', カウンターのカテゴリを示す'SUBSYSTEM', その他カウンターの状態, 計測値の統計情報などが入っています。 それぞれの項目の説明は'COMMENT'カラムを見ることでわかります。
以下にinnodb_metricsテーブルのサンプルを示します。
select * from innodb_metrics limit 1\G
*************************** 1. row ***************************
NAME: metadata_table_handles_opened
SUBSYSTEM: metadata
COUNT: 0
MAX_COUNT: NULL
MIN_COUNT: NULL
AVG_COUNT: NULL
COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: NULL
TIME_DISABLED: NULL
TIME_ELAPSED: NULL
TIME_RESET: NULL
STATUS: disabled
TYPE: counter
COMMENT: Number of table handles opened
取得できる情報
MySQL 5.7.22では235項目があり、大きく17のsubsystemに分かれていました。 それぞれのsubsystemで上記のようなカウンタごとの状態, 計測値, 計測値の統計情報を取得することができます。
sybsystemごとのカウンターの数は以下の表のようになりました。 この表には'module name'というカラムがありますが、これはsubsystemに含まれるカウンタを有効化する際に指定するsubsystemと同じ分類です。 経緯や理由はわかりませんが、subsystem単位でカウンタを有効化するときにはsubsystemに対応するmoduleを指定して有効化する必要があります。
module name | subsystem name | count |
---|---|---|
module_metadata | metadata | 3 |
module_lock | lock | 16 |
module_buffer | buffer | 75 |
module_buf_page | buffer_page_io | 32 |
module_os | os | 9 |
module_trx | transaction | 12 |
module_purge | purge | 7 |
module_compress | compression | 4 |
module_file | file_system | 1 |
module_index | index | 6 |
module_adaptive_hash | adaptive_hash_index | 8 |
module_ibuf_system | change_buffer | 8 |
module_srv | server | 25 |
module_ddl | ddl | 6 |
module_dml | dml | 4 |
module_log | recovery | 15 |
module_icp | icp | 4 |
概要のドキュメント: https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-metrics-table.html
各カラムの情報: https://dev.mysql.com/doc/refman/5.7/en/innodb-metrics-table.html
利用方法
メトリクスを有効化,無効化,リセットするには以下のコマンドを実行します。 これらはカウンターごと, モジュール(subsystem)ごと, 文字列パターン, もしくは全選択のいずれかの方法でカウンターを指定して設定できます。
SET GLOBAL innodb_monitor_enable = [counter-name|module_name|pattern|all];
SET GLOBAL innodb_monitor_disable = [counter-name|module_name|pattern|all];
SET GLOBAL innodb_monitor_reset = [counter-name|module_name|pattern|all];
例えば, indexサブシステムを有効にするには以下のクエリを実行します。
SET GLOBAL innodb_monitor_enable = module_index;
(余談) SYS.METRICS VIEW
バージョン5.7から追加されたsysスキーマにinnodb_metricsを含む、以下の3箇所からとった情報をまとめて簡単に取得できるようにしてくれているVIEWがあります。
- performance_schema.global_statusテーブルの情報
- INFORMATION_SCHEMA.INNODB_METRICSテーブルの情報
- performance_schemaからとったメモリ関連の情報
https://dev.mysql.com/doc/refman/5.7/en/sys-metrics.html
このテーブルをカテゴリ(typeカラム)ごとに見ると以下のような情報が取れることがわかります。
select type, count(*) from sys.metrics group by type;
+--------------------------------------+----------+
| type | count(*) |
+--------------------------------------+----------+
| Global Status | 221 |
| InnoDB Metrics - adaptive_hash_index | 8 |
| InnoDB Metrics - buffer | 57 |
| InnoDB Metrics - buffer_page_io | 32 |
| InnoDB Metrics - change_buffer | 8 |
| InnoDB Metrics - compression | 4 |
| InnoDB Metrics - ddl | 6 |
| InnoDB Metrics - dml | 4 |
| InnoDB Metrics - icp | 4 |
| InnoDB Metrics - index | 6 |
| InnoDB Metrics - lock | 12 |
| InnoDB Metrics - metadata | 3 |
| InnoDB Metrics - os | 5 |
| InnoDB Metrics - purge | 7 |
| InnoDB Metrics - recovery | 12 |
| InnoDB Metrics - server | 22 |
| InnoDB Metrics - transaction | 12 |
| Performance Schema | 2 |
| System Time | 2 |
+--------------------------------------+----------+
19 rows in set (0.01 sec)
typeの'InnoDB Metrics - ...'の分類をみるとINNODB_METRICSと数がだいたい同じ(やや少ない)です。 ざっくりした情報を見たい場合は便利なのですが、取得できる情報はVariable_name, Variable_valueに集約されていて元のテーブルでわかる詳細な情報が省略されてしまっています。 せっかく取った情報を完全に把握できないのは好ましくないので、ここではこのviewの元になっているINFORMATION_SCHEMA.INNODB_METRICSテーブルをみていきます。
実験
環境
- MySQL: 5.7.22 (community)
- buffer_pool_size 10GB
設定ごとのperformance比較をしたいだけなので、詳細な設定やハードウェア情報は省略します。
内容
今回は簡単に性能差を比較して見るために、sysbenchのoltp_read_writeのシナリオで以下のようなオプションでベンチマークを行い、処理できたトランザクション数から各設定でのパフォーマンスを比較します。
table-sizeを1000000, table数を10, clientのスレッド数を10で10分間ベンチマークを実行します。 sysbenchのprepareでデータを用意した段階でのデータベースのサイズは約2.4GBでした。
以下のコマンドでoltp_read_witeシナリオのベンチマークを行いました。
sysbench /usr/share/sysbench/oltp_read_write.lua \
--db-driver=mysql \
--table-size=1000000 \
--tables=10 \
--mysql-host=xxx \
--mysql-port=3306 \
--mysql-user=sysbench \
--mysql-password=sysbench \
--mysql-db=sysbench \
--time=600 \
--threads=10 \
run
また、実験ごとにmysqldを再起動し、buffer_poolを読まないように以下の設定をmy.cnfに書きました。
innodb_buffer_pool_dump_at_shutdown = OFF
innodb_buffer_pool_load_at_startup = OFF
実験結果
上記の設定でperformance_schemaのinstruments/consumersの全項目, 同じくinstruments/consumersのTIMEDを含めた全項目, innodb_metricsの全項目を有効・無効化する組み合わせで実験を行い、デフォルトの設定とのTPS(1秒あたりのトランザクション処理数)を比較した結果が以下です。
SETUP_INSTRUMENTS ALL 'ENABLED' COLUMN | SETUP_INSTRUMENTS ALL 'TIMED' COLUMN | ALL INNODB_METRICS | TPS | 全てDEFAULTとのTPS比 | AVERAGE LATENCY | COMMENT |
---|---|---|---|---|---|---|
NO | NO | DISABLED | 3506.25 | 1.031 | 2.28 | performance_schema, innodb_metricsの設定両方を全て無効化した状態 |
NO | NO | DEFAULT | 3496.40 | 1.029 | 2.29 | performance_schemaのinstrumentsを全て無効化した状態 |
DEFAULT | DEFAULT | DISABLED | 3391.85 | 0.998 | 2.36 | innodb_metricsのを全て無効化した状態 |
DEFAULT | DEFAULT | DEFAULT | 3399.32 | 1.00 | 2.35 | デフォルト設定 |
DEFAULT | DEFAULT | ENABLED | 3389.69 | 0.997 | 2.36 | innodb_metricsのを全て有効化した状態 |
YES | DEFAULT | DEFAULT | 2906.15 | 0.855 | 2.75 | performance_schemaのinstrumentsを全て有効化した状態 |
YES | YES | DEFAULT | 2837.44 | 0.835 | 2.82 | performance_schemaのinstruments, TIMEDを全て有効化した状態 |
YES | DEFAULT | ENABLED | 2908.34 | 0.856 | 2.75 | performance_schema, innodb_metricsの設定両方を全て有効化した状態 |
YES | YES | ENABLED | 2825.31 | 0.831 | 2.83 | performance_schema(TIMEDも有効), innodb_metricsの設定両方を全て有効化した状態 |
"TPS", "全てDEFAULTとのTPS比"を見ることで、それぞれの設定による効果を確認してみます。 運用上はデフォルトの設定からinstrumentsを無効化していくことはまずないと思いますが、無効化することで高速化できていることがわかります。 innodb_metricsの設定を一括でON/OFFしてもTPSは誤差程度にしか変わりませんでした。 一方で、performance_schemaのinstrumentsは全てを一括でON/OFFした極端な場合ですが、TPSでみると15%程度の性能劣化が起こりました。 今回はsysbenchのoltp_read_witeという非常に単純なシナリオでDBのデータがbuffer_poolに乗り切るなど、簡易的な環境で実験しましたが、performance_schemaのinstrumentsは全てを一括でONにするのは避けて、必要な項目ごとに設定を行ったほうが良さそうです。 一方でinnodb_metricsの設定は有効にしても大きな影響はなさそうです。どちらもonlineで変更できるので、本番環境では様子を見ながら実行すれば良いと思います。
まとめ
perfromance_schemaとinformation_schema.innodb_metricsテーブルでパフォーマンス情報を収集する仕組みと設定方法を説明し、これらの設定を一括で有効化・無効化した際のパフォーマンスをsysbenchのoltp_read_witeシナリオによる簡単なベンチマークで検証しました。
全ての設定を一括で有効にした場合15%程度のパフォーマンス低下が発生することがわかりました。 この実験はこれまで説明したように非常に単純なシナリオ(クエリ)で、負荷もそれほど大きくないものでしたので、プロダクション環境では適宜様子を見ながら設定する必要がありますが、一つの目安になれば幸いです。
それでは、明日は大原さんによる「GitHub Pull Requestの自動closeの挙動まとめ」です。お楽しみに!
[1] 奥野幹也 著, "詳解MySQL5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド", 翔泳社, 2016年 (この記事の内容の詳細については5章を読むことをおすすめします)
[2] objectsは厳密には全てではなくmysql, performance_schema, information_schemaを除く全てのデータベースがデフォルトで有効になっている。これはsetup_objectsをみるとわかる。