この記事は MySQL Casual Advent Calendar 2018 - Qiita 24日目の記事です。
昨日はhmatsu47さんによるMySQL 8.0 が DMR／RC だった頃に試した機能について振り返ってまとめてみる - Qiitaでした。
この記事について
MySQLの各種ベンチマークについて書かれている Oracle の Dimitriさんのブログ があります。 メジャーバージョンアップのタイミングや性能改善が入った場合、様々なパターンのベンチマークを行い公開しています。
但し、グラフがDimitriさん自作のdimstatを利用したものとなっていて、見方・略語が分かりにくくて辛い、という話を前から割と聞いていたので今回ある程度まとめようと思います。
なお、Dimitriさんが行うベンチマークはあくまでベンチマークツールによる負荷をかけた際のバージョン間の改善やMySQLのフォーク(Percona Server, MariaDB)との比較がメインとなっています。また、とにかくボトルネックとなっている内部処理を探すようなケースもあります。そのため、改善の恩恵を実サービスで受けられるかは実際に各々の環境で試してみる必要があります。
これは良い悪いではなく、ベンチマークをどういうポジション・観点で行っているか、という話なので中の人が行うならこうなるよね、と個人的には思っています。
略語について
MySQLのよく変更されるパラメータをご存知の方は「これかな？」と分かる事が多いのですが、それでも前後の文章を読まないと本当にそうなのか分からない事があります。MySQLのパラメータの略語についてはそのままパラメータ名を、英語のスラング的な略語はどういった略語なのかを書いていきます。
一般的な英語の略語
- PITA: pain in the ass: イライラする、うんざりする
- BTW: by the way: ところで
MySQL関連、パラメータの略語
| 略語 | パラメータや説明 |
|---|---|
| 48cores-HT 2S Skylake server | 2ソケット(合計48コア HyperThreading有効) の Intel SkylakeのCPUを載せたサーバ |
| OL7.4 | Oracle Linux 7.4 |
| BP | innodb_buffer_pool |
| dblwr | innodb_doublewrite |
| trx_commit | innodb_flush_log_at_trx_commit |
| checksum | innodb_checksum_algorithm |
| PFS | performance_schema |
| AHI | Adaptive Hash Index(innodb_adaptive_hash_index) |
| io capacity | innodb_io_capacity |
| io max | innodb_io_capacity_max |
| lru depth | innodb_lru_scan_depth |
| BP instances | innodb_buffer_pool_instances |
| cleaner threads | innodb_page_cleaners |
checksumについてはバイナリログでも利用されていますが、Dimitriさんのブログで登場した記憶は無いので基本的にInnoDBのchecksumと考えて良いかと思います。
グラフの表題に出てくるMySQL関連、パラメータの略語
| 略語 | パラメータや説明 |
|---|---|
| trx1 | トランザクション有効(主にsysbenchのグラフで登場) |
| pool128G | innodb_buffer_pool_size = 128G |
| ahi0 | innodb_adaptive_hash_index = OFF、ahi1ならON(デフォルトはON) |
| 10Mx8tab | 10 millionのテーブルが8個(10 million x 8 table)。主にsysbenchを利用した場合に出てきます。 |
| uniform-ps-trx | sysbenchのオプションをまとめて略したもの。--rand-type=uniform --db-ps-mode=auto。trxはトランザクションを使用(使わない場合は--skip-trxオプションを利用)。sysbench 1.0.15時点では--rand-typeで指定可能な値は uniform,gaussian,special,pareto の4つでデフォルトはspecial。 |
| p_sel1 | sysbenchのpoint_select。主キーに対する一意検索(ex: WHERE句でid=[ランダムな値]のみ指定)。 |
| binlog0/1 | binlog0はskip_log_bin=1 でバイナリログの出力を無効。1はバイナリログの出力有効。 |
おまけ: sysbench 1.0で利用するluaファイル
sysbenchはバージョン 0.5 からluaを利用するようになっています。1.0.15では以下のluaファイルが用意されています。
bulk_insert.lua oltp_common.lua oltp_delete.lua oltp_insert.lua oltp_point_select.lua oltp_read_only.lua oltp_read_write.lua oltp_update_index.lua oltp_update_non_index.lua oltp_write_only.lua select_random_points.lua select_random_ranges.lua
OLTP_RWと略されているのは基本的にoltp_read_write.luaを利用していると考えて良いです。oltp_common.luaを参照すると頭の方でざっくりオプションを確認する事が出来ます。
グラフを見る
概ねここまでの内容で最近のグラフは(おそらく)理解可能になっていると思います。 以下いくつかの例を参考に解説します。
グラフ例1
sysbenchのOLTP_RW(oltp_read_write.lua)、innodb_buffer_pool_size=32G、トランザクション有効、innodb_doublewrite=0、バイナリログの出力無効なケースでMariaDB 10.3.5、MySQL 5.7、Percona Server 5.7、MySQL 8.0.13での比較となります。
表題で1..1024usrとあるのでユーザセッション数が1,2,4,8,..,512,1024のケースと勘違いしそうですが、グラフ下部にある通り32, 64, 128の3パターンでsysbenchを実行したグラフとなります。また、Commit/secとあるのでsysbenchのOLTP_RWを実行した際に秒間コミット数をグラフ化したものとなります。
dimstatでグラフ化した場合、ベンチマーク間でいくら間隔が開いてもグラフとしては隙間が出来ない特徴があります(もちろん取り方によります)。
グラフ例2
このブログ の このグラフ となります。
グラフ下部にある通り、左から
- innodb_adaptive_hash_index=0, innodb_doublewrite=0
- innodb_adaptive_hash_index=1, innodb_doublewrite=1
- innodb_adaptive_hash_index=0, innodb_doublewrite=1
- innodb_adaptive_hash_index=0, innodb_doublewrite=1, innodb_checksum_algorithm=crc32(chksum1より、ブログの説明読まないと分からない), innodb_io_capacity=2000, innodb_io_capacity_max=4000, innodb_buffer_pool_instances=8(bp8より、説明が見当たらないので多分)
bp8がちょっと謎です(innodb_buffer_pool_sizeが128Gなのでデフォルトでinnodb_buffer_pool_instancesは8のため)。
このグラフから一番分かりやすい差は2と3でAHIが無効な方が性能が出たという事でしょう。なお、AHIが有効な方が速いケースもあるため、プロダクション環境で変更する場合はサーバ・MySQLをきちんとモニタリングされている環境で性能がどう変わるか確認する事をオススメします。
グラフ例3
このブログ の このグラフ です。
OLTP_RWの中の特定の処理が遅いと見たのか、おそらく oltp_update_non_index.lua を使ったと思われる以下のグラフを続けて載せています。
バイナリログの出力が無効な場合、INDEXを含まないカラムの更新に対する処理はユーザセッションが64を超えると半分程度に低下していることが分かります。 Dimitriさんはブログに
as you can see, enabling Binlog is helping a lot MySQL 5.7 to not loose performance on high load.. and this is all because it helps to "hide" the REDO log contention we have in 5.7
と書き、続けて8.0でどうなったか載せています。
バイナリログの出力を無効にする事なんて昨今あるのか？、と感じる人が多いと思いますが、ボトルネックを探す場合(この場合はMySQLそのものの)、性能に係る物は無効にし、1つずつ有効にしていった際の性能差を見ていく必要があるのでこういう例も試しているのだと思います。 と、ここまで書いてAmazon Auroraってバイナリログを基本的に無効にすることを推奨、というかデフォルト無効だったと記憶しているんですが同様の問題は起きないようになっているのかな？とか思いました(まぁ起きないんじゃないですかね、きっと、たぶん)。
まとめ
自分も慣れるまではかなり文章とグラフを行ったり来たり、場合によっては古いブログを読んだりしていましたが慣れると割と引っかからずに読めるようになります。時間のある方はdimstat自体を自分の環境に入れて動かしてみるとより分かりやすいと思います。
dimstatを試してみたい方はこちらから辿って行ってマニュアルとか読むと良いでしょう。とはいえバンドルされているのがApache1系だったりとかで面食らうかもしれませんが、そのあたりは触る必要は普通無いので気にしたら負けです。。。
MySQLの新しいバージョンが出るタイミングやフォークのプロダクトで何か改良があった場合、またはTwitterでDimitriさんがFacebookやPerconaの人とやり取りをした後に「それもやってみるは」的な感じでブログが書かれる事がありますので月1回ぐらい覗いて見ると良いかと思います。
なお、最近はストレージがIntel Optaneだったり合計40コア以上のCPUだったりとWeb系ではなかなか使わないようなスペックでのベンチマークが多いので、ご自身が面倒を見る環境のDBサーバが合計20コア以下の場合は別途ベンチマークを取る事をお勧めします。NUMA環境かどうかそうですし、コア数で傾向が変わることも結構あるためです。 またPersistent connectionが利用出来る環境かどうかも高負荷な場合は結構な性能差が出たりします。
明日はいよいよ最終日(25日目)。kk2170さんとなります。
