LINE株式会社は、2023年10月1日にLINEヤフー株式会社になりました。LINEヤフー株式会社の新しいブログはこちらです。 LINEヤフー Tech Blog

Blog


MySQLのperformance-schema-instruments利用によるパフォーマンス影響を調べてみた

この記事は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をみるとわかる。