Dimitriさんのブログを読んでみよう

この記事は 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

グラフ例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

このブログ の このグラフ となります。

グラフ例2

sysbenchのTPC-C(最近のバージョンからTPC-C相当のベンチマークをsysbenchで実行できます)、10 x 100 warehouseのデータ、ユーザセッション数が 1,2,4, ... , 512, 1024、innodb_buffer_pool_sizeが128G、innodb_adaptive_hash_indexが無効と有効、innodb_doublewriteが無効と有効、トランザクション有効、といったケースでベンチマークを行ったグラフです。TPC-Cはwarehouseの数でデータ量が変わる類のベンチマークです。

グラフ下部にある通り、左から

1. innodb_adaptive_hash_index=0, innodb_doublewrite=0
2. innodb_adaptive_hash_index=1, innodb_doublewrite=1
3. innodb_adaptive_hash_index=0, innodb_doublewrite=1
4. 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

このブログ の このグラフ です。

グラフ例3

sysbenchのOLTP_RW、1000万行のテーブルが8個(10Mx8tab)、トランザクション有効(trx1)、バイナリログの出力無効/有効(binlog0/1)、sync_binlogが0/1/1000(sync0/1/1K)、でMySQL5.7に対するベンチマーク。 MySQL5.7だとバイナリログが無効の方がユーザセッション数が128あたりから遅くなるという不思議な結果になってる事が分かります。

OLTP_RWの中の特定の処理が遅いと見たのか、おそらく oltp_update_non_index.lua を使ったと思われる以下のグラフを続けて載せています。

グラフ例4

バイナリログの出力が無効な場合、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でどうなったか載せています。

グラフ例5

MySQL8.0におけるOLTP_RWでの結果。ユーザセッション数に関係無くバイナリログが無効な場合の方が有効な場合より良い結果となっている事が分かります。 続けて8.0におけるupdate NoKeyの場合は以下のような結果となっています。

グラフ例6

同様にユーザセッション数に関係無くバイナリログが無効な場合の方がよい結果となっています。

バイナリログの出力を無効にする事なんて昨今あるのか？、と感じる人が多いと思いますが、ボトルネックを探す場合(この場合は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さんとなります。