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

この記事は MySQL Casual Advent Calendar 2018 - Qiita 24日目の記事です。

昨日はhmatsu47さんによるMySQL 8.0 が DMR/RC だった頃に試した機能について振り返ってまとめてみる - Qiitaでした。

この記事について

MySQLの各種ベンチマークについて書かれている OracleDimitriさんのブログ があります。 メジャーバージョンアップのタイミングや性能改善が入った場合、様々なパターンのベンチマークを行い公開しています。

但し、グラフが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

f:id:hiroi10:20181219190422p:plain
グラフ例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

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

f:id:hiroi10:20181219210642p:plain
グラフ例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

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

f:id:hiroi10:20181223204432p:plain
グラフ例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 を使ったと思われる以下のグラフを続けて載せています。

f:id:hiroi10:20181223205001p:plain
グラフ例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でどうなったか載せています。

f:id:hiroi10:20181223205634p:plain
グラフ例5
MySQL8.0におけるOLTP_RWでの結果。ユーザセッション数に関係無くバイナリログが無効な場合の方が有効な場合より良い結果となっている事が分かります。 続けて8.0におけるupdate NoKeyの場合は以下のような結果となっています。
f:id:hiroi10:20181223205858p:plain
グラフ例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さんとなります。

RyzenマシンにCentOS7を入れた話

Ryzen 7 1800XでCentOS7が一応動かせたのでメモがてら書いておきます。

  • H/W環境
    • CPU Ryzen 7 1800X
    • MEM Crucial W4U2400CM-8G(DDR4-2400 8GB x 2) CLはMBのAUTO。確か17-17-17とか。
    • MB ASRock FATALITY X370 GAMING K4
    • SSD Crucial CT275MX300SSD1
    • HDD Western Digital WD30EZRZ-RT/T
    • HLDS GH24NSD1.AXJU1LB (DVD/CD-R drive)
    • GPU ELSA GD1050-2GERS
  • OS
    • CentOS 7.3(インストール後にELRepoから4.10 Kernelにアップデート。理由は後述)

UEFI環境にLinuxを入れるのにあまり慣れていないのと、ASRockのマザーボードの使用経験が少なく(大体ASUSだった)のでそもそもCentOS7のインストールイメージを焼いたCD-Rから起動するのに若干手間取ったりしました。CSM(Compatibility Supported Module)とかFastBootとか意識する必要のある場面にこれまで遭遇していなかったため。。。

CentOS7のNetinstallからOSのインストールを行いました。Ryzenが出たての頃に海外サイトでLinuxRyzen環境で動かしてる情報を漁ってた頃はインストールでこけるように見えたのですが、インストール自体は何事もなく完了しました。

しかし、インストール直後のKernelのオプションで起動しようとすると以下のフォーラムのメッセージで起動処理が止まる状態となりました。
conflicting fb hw usage nouveaufb vs EFI VGA - removing generic driver

回答にある通り、 nomodeset を付けることでとりあえず起動する、ログイン可能な状態まで進めたのでELRepoをリポジトリに追加し、更新を行なっています。
そもそもの情報として以下サイトを見ていました。
STOP CONSTANT CENTOS 7.3 CRASHES WITH AMD RYZEN USING KERNEL 4.10

なお、上記を解消後にネットワーク設定を行い、さっさとKernel 4.10に上げましたが、これを書くために 3.10.0-514.10.2.el7.x86_64 で起動したらコンソールでは操作が出来ませんでしたが、リモートからsshで繋ぐ事が可能な状態でした。インストール直後の3.10.0-514.el7.x86_64とは挙動が違いましたので、このバージョン間での更新で多少良くなったのかもしれません。
3.10.0-514.10.2.el7.x86_64 であっても nomodeset を付ければコンソール操作は行える状態となりました。

初期状態の起動オプションは以下のようになっていました(UUIDは長いので{UUID}に省略)。

linuxefi /boot/vmlinuz-3.10.0-514.el7.x86_64 root=UUID={UUID} ro crashkernel=auto rhgb quiet LANG=en_US.UTF-8

ですので、起動時のgrubの画面でeで編集画面に入り、

linuxefi /boot/vmlinuz-3.10.0-514.el7.x86_64 root=UUID={UUID} ro crashkernel=auto rhgb quiet LANG=en_US.UTF-8 nomodeset

と編集して起動しています。rhgb quietがあると「conflicting fb〜」な画面が見れないので安定して操作が行えるようになるまではrhgb quietは消した方が良いかと思います。

Ryzenはメモリとか、まぁ相性やら性能やら癖があるようですので、同じ環境でもCentOS7が動くかは分かりませんが、上記のH/W環境ではとりあえず動かす事が出来ています。

MySQL 5.7.17(5.7.18出ちゃったけど。。。)にてsysbench 0.5のoltp.luaを使用し、point-selectのみのベンチマークを取った結果は1回のみの実行ですが以下のようになりました。cpupowerをperformanceにしてますが、UEFIにてCool'n'Quietは無効にしていません。この辺も差が出るのが今後試したいところ。

LD_PRELOAD=/usr/lib64/libjemalloc.so.1 \
      sysbench \
        --test=/path/to/lua/oltp.lua \
        --rand-init=on \
        --db-driver=mysql \
        --rand-type=uniform \
        --oltp-table-size=3000000 --oltp-tables-count=8 \
        --oltp_point_selects=1000 --oltp_sum_ranges=0 --oltp_simple_ranges=0 \
        --oltp_order_ranges=0 --oltp_distinct_ranges=0 \
        --oltp-read-only=on \
        --mysql-socket=/var/lib/mysql/mysql.sock \
        --mysql-db=sbtest5 \
        --mysql-user=sbtest --mysql-password=sbtest-pw \
        --max-time=90 --max-requests=0 \
        --num-threads=$thread \
        run 
スレッド数 qps
8 138244
16 205629
32 204006
64 201601
128 206409
256 206206
512 206378
1024 206656
2048 206306
4096 208140

搭載メモリの都合上、データ量に差があるのですが(今回は大体5.6GB)、Xeon E5-2630 x 2(2.3GHz, 2P12C24T)の環境よりはスコアが出ています。また、最近はNUMA環境(2CPU)でばかりベンチを取っていて、その場合は2048スレッドあたりから性能低下する傾向があったのですが、1CPUということもあるのか論理CPUコア数の16と同じスレッド数からは性能が安定するという違いが見られました。

なお、Ryzen環境でCentOS動いたー、というところでとりあえずベンチマークを流したのでモニタリングは一切していません。モニタリング設定をして、後日改めてベンチマーク取りたいと考えていますが、どうなるかは分かりません。

その他のMB(多分主に)でも同じようにしてCentOSが動くかは分かりませんが、Ryzen環境でLinuxを試したい場合はKernel4.10以上が良いように海外サイト見てると見受けられるのでそれを目安に導入を試してみると良いんじゃないかと思います。

なお、gistにcpupowerがデフォの状態(周波数が2.2GHzになってる)ですがcpuinfoとかの結果を載せてありますので興味のある方はご参照ください(Ryzen 7 1800X cpuinfo)

MySQL5.6と5.7のちょっとした違いとinnodb_thread_concurrencyの影響

この記事はMySQL Casual Advent Calendar 2016の22日目です。

innodb_thread_concurrencyを最近のデフォルトである0と論理CPUコア数の2倍の48に設定した場合に観測出来た小ネタです。ベンチマークのtpsを載せていますが、1回しか取得してないので、割と誤差があると考えられるため、目安程度に見てください。

  • 環境
    • CentOS 6.6(2.6.32-504.12.2.el6.x86_64)
    • Xeon E5-2643 v2 3.5GHz x 2(2P12C24T)
    • Memory 64GB (8GB x 8, DDR3 1866MHz)
    • HDD SAS 300GB x 2 10k rpm(RAID1 BBU付き)
    • FileSystem ext4
    • ベンチマークのデータ量はinnodb_buffer_pool_sizeに収まる量
      • メモリで殴るような環境を想定
  • ベンチマークツール
    • sysbench 0.5 (oltp.lua)
    • 実行スレッド数(8, 16, 32, 64, ... , 2048, 4096)
  • ベンチマーク実行コマンド(以下をスレッド数1から512まで8個同時実行)
    • 1ケース実行毎に150秒間のSleepを入れています。
      LD_PRELOAD=/usr/lib64/libjemalloc.so.1 \
      /usr/local/sysbench-0.5/bin/sysbench \
        --test=/usr/local/sysbench-0.5/lua/oltp.lua \
        --rand-init=on --db-driver=mysql --oltp-read-only=off \
        --rand-type=uniform \
        --oltp-tables-count=18 --oltp-table-size=3000000 \
        --mysql-socket=/var/lib/mysql/mysql.sock \
        --mysql-db=sbtest5 \
        --mysql-user=sbtest --mysql-password=sbtest-pw \
        --max-time=300 --max-requests=0 \
        --num-threads=$thread \
        run 

上記条件でベンチマークを行なった際のグラフは以下のようになります(モニタリング間隔15秒)。
左から5.7のinnodb_thread_concurrency 48 / 0, 5.6のinnodb_thread_concurrency 48 / 0の結果です。


5.7側のDMLがジグサグしていて安定感がありません。また、(自分の観測範囲で)あまり使われてない以下を設定しています。

innodb_max_purge_lag_delay=1000000
innodb_max_purge_lag=100000

こちらの設定をざっくり書くと、SHOW ENGINE INNODB STATUS\G を実行した際に確認できるHistory list lengthをinnodb_max_purge_lagの設定値(100000)に収まるようにしようとします。

その結果、innodb_thread_concurrencyが48の場合は収まるような結果となっていますが、innodb_thread_concurrencyが0の場合は収まらずに伸び続けています。History list lengthは多くても5000以下程度に収まるのが望ましい、と考えています。一定以上の大きさになると割と性能が下がる傾向にあるためです。よって、innodb_max_purge_lagの制御が行われる状態はあまり良くありません。
ただ、今回の結果を見ると全部メモリに載る状態だと性能への影響が軽微なのかもしれません。LinkBenchでメモリに載り切らない場合では10000超えたあたりで結構性能低下した事があるのでその他の要因と関連がありそうです。

またcheckpoint ageが5.6は150秒間のスリープの間に減少していますが、5.7ではinnodb_io_capacityの設定値に準じて書き出しを行うため減少しきらない状態となっています(5.6はinnodb_io_capacityの制御が割と雑なため)。
 ※完全に同じ条件じゃない、と言われると実際そうです

とはいえディスクI/Oがあまり強くない環境で大量の接続・更新が行われる場合、innodb_thread_concurrencyを48にすることでHistory list lengthの伸びを抑制出来るためベンチマーク後のibdata1のサイズは結構な差が出ます。

以下ベンチマーク後のibdata1のファイルサイズです。

5.7 ccr 48 5.7 ccr0 5.6 ccr48 5.6 ccr0
268MB 780MB 204MB 460MB

5.7の方がibdata1のサイズが大きいのは、単純に5.6よりtpsが高く、処理量が多いためです。
以下sysbenchのtpsです。

スレッド数 5.7 ccr48 5.7 ccr0 5.6 ccr48 5.6 ccr0
8 2133.73 2176.16 2085.15 2130.29
16 3454.79 3454.86 3378.4 3433.78
32 3860.71 3920.18 3637.29 3665.24
64 4077.97 4300.74 3659.85 3731.46
128 4529.18 4623.69 3669.78 3755.15
256 4669.99 4714.38 3648.23 3655.07
512 4416.23 4466.67 3614.36 3729.47
1024 4683.41 4634.53 3520.22 3585.23
2048 4460.89 4590.36 3407.69 3499.41
4096 4253.65 4435.6 3049.28 3343.77

ということでジグザグなグラフの割に5.7の方がトータルで見ると性能が出ていることになります。しかし、あるタイミングを切り取れば5.6より性能が出ていない状態を表しています。

では5.7で安定させるためには何をしたら良いでしょうか。性能が下がるタイミングではcheckpont ageが張り付いていて、ibdファイルへのフラッシュが動作していると考えられます。例えばib_logfile系をtmpfsに置くとか、I/O強いにすれば良いのはそれはそうなんですが、そう出来る環境とは限りません。今回のケースは5.6の設定をほぼそのまま5.7で使用している事が悪いので、パラメータ調整を行います。
流用するようなケースはバージョンアップが多いかと思いますので、そのタイミングで調整が行える可能性が高いかと思います。

という事でフラッシュをもっとゆっくりさせれば良いのでシンプルにib_logfileを増やします。

innodb_log_files_in_group = 12

これでib_logfile0からib_logfile11までの合計12GBになります。この状態で5.7のみベンチマークを再取得したグラフとtpsは以下のようになります。取得順を間違えてて、今回は左がinnodb_thread_concurrency 0, 右がinnodb_thread_concurrency 48となります。




スレッド数 5.7 ccr48 5.7 ccr0
8 2262.15 2289.72
16 4214.03 4231.89
32 5476.94 5492.78
64 5757.04 5772.37
128 5805.44 5817.16
256 5732.35 5751.25
512 5810.34 5830.87
1024 5930.8 5744.61
2048 5785.43 5656.04
4096 5523.51 5536.83

安定した結果、tpsは更に上昇しcheckpoint ageが張り付くこともありません。とはいえinnodb_thread_concurrencyが0の場合、相変わらずHistory list lengthは伸びます。この辺りまで来るとH/Wの変更が無理だとDurability下げるしかないのかなー、と思います(doublewrite止めるとか)。

適切にib_logfile(REDO)増やしたりinnodb_thread_concurrency, innodb_max_purge_lagを設定する事で延命出来る可能性があります。メモリ大量に積んでて「バッファにデータもINDEXも載るから処理は余裕だぜー」と油断してると5.7の環境でib_logfileが少ない場合、気付かないタイミングでスローダウンしてる可能性があるのでご注意ください。特にモニタリング間隔が5分とかだと埋もれる可能性が高いです。

なお、単純にundo領域が肥大化するのをどうにかしたいだけであれば innodb_undo_directory, innodb_undo_tablespaces, innodb_undo_log_truncate とか設定しておく方が特に5.7においては良いと思います。

という感じです。5.6と5.7の違い、innodb_thread_concurrencyを0以外にした方が良いケースについて知っておいて頂ければ幸いです。

明日は@yoku0825さんによる「COUNTを速くする(?)SQL1本ノック その2」です。

MySQL5.7でInnoDBのTransparent Page Compressionを試してみる Part2

これはMySQL Casual Advent Calendar 2015の21日目のエントリです。
Part1に続いての内容となります。

今回の計測パターン

Part1ではデータ・INDEXが全てinnodb_buffer_pool_sizeに収まる量でしたが、今回は収まらない量、実サイズで141GBのデータで計測。
LinkBenchの実行オプションは前回と同じです

# ./bin/linkbench -c config/MyConfig.properties -D maxtime=3600 -D requests=10000000 -D requesters=64 -r

パラメータについて

最後にまとめますが、結構変更しました。無圧縮であれば変える事なく安定した性能を出せたのですが、やはりreadの展開負荷が増えるとパラメータ変更しないとどうにもならない感じでした。まだ改善出来そうな気がするけど、そこまでやると環境特化が過ぎるのでほどほどという感じです。

データ量について

zlib, lz4, none(無圧縮)で以下のようになっています。FBWorkload.propertiesでmaxid1 = 140000001として作成しています。

compression innodb_page_size du -sch du -sch --apparent-size
lz4 16k 77GB 141GB
zlib 16k 70GB 141GB
none 16k 141GB 141GB

ベンチマーク前後の圧縮状況

zlibの場合

ベンチマーク実行前。

mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        58.8689 |            1994.0352 |       4848.0000 |
| linkdb/linktable#P#p1  |        58.9474 |            2036.2070 |       4960.0000 |
| linkdb/linktable#P#p2  |        58.7449 |            1884.5313 |       4568.0000 |
| linkdb/linktable#P#p3  |        58.2566 |            5511.7969 |      13204.0000 |
| linkdb/linktable#P#p4  |        59.0217 |            1786.6523 |       4360.0000 |
| linkdb/linktable#P#p5  |        58.8554 |            2473.6133 |       6012.0000 |
| linkdb/linktable#P#p6  |        58.8582 |            1936.9570 |       4708.0000 |
| linkdb/linktable#P#p7  |        59.0226 |            2715.9844 |       6628.0000 |
| linkdb/linktable#P#p8  |        59.1500 |            1581.7109 |       3872.0000 |
| linkdb/linktable#P#p9  |        58.4090 |            3137.6250 |       7544.0000 |
| linkdb/linktable#P#p10 |        58.8885 |            1825.3516 |       4440.0000 |
| linkdb/linktable#P#p11 |        58.4348 |            3747.5156 |       9016.0000 |
| linkdb/linktable#P#p12 |        59.1133 |            1574.9570 |       3852.0000 |
| linkdb/linktable#P#p13 |        59.0273 |            2268.2500 |       5536.0000 |
| linkdb/linktable#P#p14 |        58.7831 |            1937.1953 |       4700.0000 |
| linkdb/linktable#P#p15 |        58.6023 |            3702.6133 |       8944.0000 |
| linkdb/counttable      |        61.7001 |            3021.0938 |       7888.0000 |
| linkdb/nodetable       |        29.1925 |           27674.3906 |      39084.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>
[root@e5 ~]# du -sch /var/lib/mysql/zlib/linkdb/
70G     /var/lib/mysql/zlib/linkdb/
70G     total
[root@e5 ~]# du --apparent-size -sch /var/lib/mysql/zlib/linkdb/
141G    /var/lib/mysql/zlib/linkdb/
141G    total
[root@e5 ~]#

ベンチマーク実行後

mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        26.9303 |            4059.7500 |       5556.0000 |
| linkdb/linktable#P#p1  |        25.9182 |            4237.4766 |       5720.0000 |
| linkdb/linktable#P#p2  |        27.0742 |            3821.3125 |       5240.0000 |
| linkdb/linktable#P#p3  |        25.4127 |           11453.6250 |      15356.0000 |
| linkdb/linktable#P#p4  |        25.6982 |            3744.8125 |       5040.0000 |
| linkdb/linktable#P#p5  |        24.8719 |            5255.9609 |       6996.0000 |
| linkdb/linktable#P#p6  |        26.1643 |            4010.7578 |       5432.0000 |
| linkdb/linktable#P#p7  |        25.5466 |            5709.0898 |       7668.0000 |
| linkdb/linktable#P#p8  |        25.1685 |            3364.4258 |       4496.0000 |
| linkdb/linktable#P#p9  |        26.3750 |            6402.4297 |       8696.0000 |
| linkdb/linktable#P#p10 |        26.0155 |            3788.0039 |       5120.0000 |
| linkdb/linktable#P#p11 |        25.4671 |            7808.0664 |      10476.0000 |
| linkdb/linktable#P#p12 |        25.0689 |            3350.9180 |       4472.0000 |
| linkdb/linktable#P#p13 |        26.1782 |            4709.8281 |       6380.0000 |
| linkdb/linktable#P#p14 |        27.2247 |            3912.3984 |       5376.0000 |
| linkdb/linktable#P#p15 |        25.8104 |            7689.0078 |      10364.0000 |
| linkdb/counttable      |        55.0439 |            3842.8516 |       8548.0000 |
| linkdb/nodetable       |        25.5240 |           32727.7305 |      43944.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>
[root@e5 ~]# du -sch /var/lib/mysql/zlib/linkdb/
118G    /var/lib/mysql/zlib/linkdb/
118G    total
[root@e5 ~]# du --apparent-size -sch /var/lib/mysql/zlib/linkdb/
162G    /var/lib/mysql/zlib/linkdb/
162G    total
[root@e5 ~]#
lz4の場合

ベンチマーク実行前

mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        51.9957 |            2327.2500 |       4848.0000 |
| linkdb/linktable#P#p1  |        51.8411 |            2388.6836 |       4960.0000 |
| linkdb/linktable#P#p2  |        51.9117 |            2196.6719 |       4568.0000 |
| linkdb/linktable#P#p3  |        51.0858 |            6456.6758 |      13200.0000 |
| linkdb/linktable#P#p4  |        51.9142 |            2096.5391 |       4360.0000 |
| linkdb/linktable#P#p5  |        51.7082 |            2903.3008 |       6012.0000 |
| linkdb/linktable#P#p6  |        51.9340 |            2262.9453 |       4708.0000 |
| linkdb/linktable#P#p7  |        51.8711 |            3189.9805 |       6628.0000 |
| linkdb/linktable#P#p8  |        52.0150 |            1859.8984 |       3876.0000 |
| linkdb/linktable#P#p9  |        51.5774 |            3653.0039 |       7544.0000 |
| linkdb/linktable#P#p10 |        51.8585 |            2137.4844 |       4440.0000 |
| linkdb/linktable#P#p11 |        51.0880 |            4407.9492 |       9012.0000 |
| linkdb/linktable#P#p12 |        51.9364 |            1851.4102 |       3852.0000 |
| linkdb/linktable#P#p13 |        51.9473 |            2658.2734 |       5532.0000 |
| linkdb/linktable#P#p14 |        51.8707 |            2264.0000 |       4704.0000 |
| linkdb/linktable#P#p15 |        51.6060 |            4332.2305 |       8952.0000 |
| linkdb/counttable      |        59.2718 |            3212.6406 |       7888.0000 |
| linkdb/nodetable       |        28.4740 |           27983.8320 |      39124.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>
[root@e5 ~]# du  -sch /var/lib/mysql/lz4/linkdb/
77G     /var/lib/mysql/lz4/linkdb/
77G     total
[root@e5 ~]# du --apparent-size -sch /var/lib/mysql/lz4/linkdb/
141G    /var/lib/mysql/lz4/linkdb/
141G    total
[root@e5 ~]#

ベンチマーク実行後

mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        24.2162 |            4228.7383 |       5580.0000 |
| linkdb/linktable#P#p1  |        23.3699 |            4401.6328 |       5744.0000 |
| linkdb/linktable#P#p2  |        24.3990 |            3976.6133 |       5260.0000 |
| linkdb/linktable#P#p3  |        22.8851 |           11903.4609 |      15436.0000 |
| linkdb/linktable#P#p4  |        23.0805 |            3898.2813 |       5068.0000 |
| linkdb/linktable#P#p5  |        22.3430 |            5460.8438 |       7032.0000 |
| linkdb/linktable#P#p6  |        23.6796 |            4164.0391 |       5456.0000 |
| linkdb/linktable#P#p7  |        22.9749 |            5937.0977 |       7708.0000 |
| linkdb/linktable#P#p8  |        22.6060 |            3498.2070 |       4520.0000 |
| linkdb/linktable#P#p9  |        23.7852 |            6658.1250 |       8736.0000 |
| linkdb/linktable#P#p10 |        23.4115 |            3936.6484 |       5140.0000 |
| linkdb/linktable#P#p11 |        22.8490 |            8119.3672 |      10524.0000 |
| linkdb/linktable#P#p12 |        22.5232 |            3480.2578 |       4492.0000 |
| linkdb/linktable#P#p13 |        23.5010 |            4902.0586 |       6408.0000 |
| linkdb/linktable#P#p14 |        24.5686 |            4076.3125 |       5404.0000 |
| linkdb/linktable#P#p15 |        23.2421 |            7995.0977 |      10416.0000 |
| linkdb/counttable      |        51.3861 |            4196.3516 |       8632.0000 |
| linkdb/nodetable       |        24.9594 |           33062.8867 |      44060.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>
[root@e5 ~]# du  -sch /var/lib/mysql/lz4/linkdb/
121G    /var/lib/mysql/lz4/linkdb/
121G    total
[root@e5 ~]# du --apparent-size -sch /var/lib/mysql/lz4/linkdb/
162G    /var/lib/mysql/lz4/linkdb/
162G    total
[root@e5 ~]#
none(無圧縮)の場合

ベンチマーク実行前。MySQLのinformation_schemaに対するクエリは無圧縮のためエラーになりますが一応載せておきます。

mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`information_schema`.`INNODB_SYS_TABLESPACES`.`FILE_SIZE` - `information_schema`.`INNODB_SYS_TABLESPACES`.`ALLOCATED_SIZE`)'
mysql>
[root@e5 ~]# du -sch /var/lib/mysql/none/linkdb/
141G    /var/lib/mysql/none/linkdb/
141G    total
[root@e5 ~]# du --apparent-size -sch /var/lib/mysql/none/linkdb/
141G    /var/lib/mysql/none/linkdb/
141G    total
[root@e5 ~]#

ベンチマーク

[root@e5 ~]# du -sch /var/lib/mysql/none/linkdb/
163G    /var/lib/mysql/none/linkdb/
163G    total
[root@e5 ~]# du --apparent-size -sch /var/lib/mysql/none/linkdb/
163G    /var/lib/mysql/none/linkdb/
163G    total
[root@e5 ~]#

ベンチマーク結果

compress スコア
lz4 9625
zlib 7966
none 11041

予想通りといえばそれまでの結果で none > lz4 > zlib の順に速い結果となりました。

考察

zlib, lz4, noneのベンチマーク中のグラフを見ていきます。グラフの順番もzlib, lz4, noneになります。

noneに比べるとzlib、lz4はグラフがかなりジグザグであまり安定しておらず、ベンチマーク実行から5分程度で性能が落ちていることが分かります。そのタイミングはInnoDBバッファプールのFree Buffersが枯渇したタイミングとほぼ一致します。

noneのケースでFree buffersが多少残っているのはinnodb_lru_scan_depthにより空きがきちんと確保されているためです。zlib, lz4のどちらも上手く空き領域が確保出来ていません。そのため、パラメータを変更したと先に記載したとおり、innodb_io_capacity=80000, innodb_io_capacity_max=120000としてzlib, lz4のベンチマークを実行しています。
他にinnodb_lru_scan_depthの設定値どおり空きが確保出来ていないため、innodb_page_cleanersを論理CPUコア数と同じ24にするためにinnodb_buffer_pool_instancesを24に変更、その結果innodb_buffer_pool_size = 42Gとしています。チャンクサイズのせいか40Gとしても42Gに調整されたためです。

History list lengthもzlib, lz4は跳ねる事があり、zlibは特に顕著でした。

最初傾向が異なる事を前提に、innodb_purge_threadsをデフォルトの4のままベンチマークを流しましたがHistory list lengthが増加の一途だったためzlib, lz4についてはinnodb_purge_threadsを8にして測定しています。
それでも以下のようにpurge_invokedが全然増えない時間帯があるため、今回の測定ケースでzlibを使う場合はinnodb_purge_threads, innodb_io_capacityをもっと増やしても良いかもしれません。

無圧縮のpurge_invokedの安定感を見ると安心します。

interrupt, context switchについては zlib > lz4 > none の順で高くなっており、そのあたりも性能に影響を及ぼしていると考えられます。

原因が分かっていませんがzlibの場合、fsyncの数が他と比較して結構高くなっていました。こちらもzlibが遅い原因の一つになっていそうです。zlib, lz4、共にwriteが多いのは確かですが、それにしてもlz4以上にzlibはfsyncが多いのが謎です。

お試しで測定した際、zlib, lz4双方の特徴としてwait/synch/mutex/innodb/buf_dblwr_mutexの待ち時間が長い傾向にありました。innodb_autoextend_incrementを8に下げて測定した所、待ち時間が多少減少し、性能も改善する傾向にありましたので今回の測定ではinnodb_autoextend_increment=8で測定しています。noneではinnodb_autoextend_increment=32としています。減らすことでwait/io/file/innodb/innodb_data_fileは増加する傾向でしたが、今回の測定環境ではそれを補って改善する結果となりました。
このあたりは使用するioデバイスの性能で変わる可能性が大いに考えられるため適宜調整すると良いでしょう。

やや見辛いと思いますのでwait/synch/mutex/innodb/buf_dblwr_mutexのWaits, Timeのみ取り出したのグラフを載せておきます。

その他のグラフを適当に載せておきます。

buffer_flush_n_to_flush_requestedがzlib, lz4で大きいのはinnodb_io_capacityの設定がnoneよりも大きい設定にしているためです。




まとめ

個人的にはTransparent Page Compressionを使うならlz4を採用したい所です。圧縮率はzlibの方が良いようですので少しでもディスク使用量を減らしたい場合はワークロードと相談しつつ決める事になるかと思います。今回はLinkBenchで使用される3テーブル全てを圧縮して計測しましたが、全てのテーブルを圧縮する事はそんなに無いと思いますのでテーブルのデータ構造による圧縮率を考慮しつつ使用すると良いかと思います。
ただ、圧縮を使った場合はパラメータ調整がかなり面倒な感じなのでサービスで使うテーブルで使用する場合は十分に検証を行う必要がありそうです。溜め込む形のテーブルでバッチ処理のみでの使用であれば割と大雑把な設定でも動くんじゃないかと思いますし、圧縮のせいで遅いとなったらさっさとALTER発行してnoneに変えてしまえば良いでしょう。

もうちょっとパラメータいじったPart3やるかは不明です。

最後にパラメータ載せておきます。

none(無圧縮)のパラメータ
skip-name-resolve
skip-external-locking
max_allowed_packet = 16M
query_cache_type = 0
query_cache_size = 0
thread_cache_size = 1024
table_open_cache = 4096
table_open_cache_instances = 24

performance_schema = ON
performance_schema_instrument='%sync%=on'

innodb_monitor_enable = 'all'

back_log = 1024

sync_binlog = 1
log-bin=mysql-bin
master_info_repository = TABLE
relay_log_info_repository = TABLE

innodb_strict_mode
innodb_file_format = Barracuda
innodb_buffer_pool_instances = 24
innodb_buffer_pool_size = 42G
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
innodb_log_files_in_group = 16
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_purge_threads = 4
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_flush_neighbors = 0
innodb_io_capacity = 12000
innodb_io_capacity_max = 15000
innodb_lru_scan_depth = 4000
innodb_open_files = 3000

innodb_print_all_deadlocks = 1

innodb_change_buffer_max_size = 10

innodb_checksums = 1
innodb_checksum_algorithm = crc32

innodb_adaptive_flushing = 1
innodb_adaptive_flushing_lwm = 10
innodb_adaptive_hash_index = 0
innodb_read_ahead_threshold = 0
innodb_sync_array_size = 24
innodb_autoextend_increment = 32

innodb_max_purge_lag_delay=1000000
innodb_max_purge_lag=100000

innodb_page_size=16k

log_timestamps = SYSTEM
innodb_page_cleaners = 24
innodb_numa_interleave = ON
zlib, lz4の時のパラメータ
skip-name-resolve
skip-external-locking
max_allowed_packet = 16M
query_cache_type = 0
query_cache_size = 0
thread_cache_size = 1024
table_open_cache = 4096
table_open_cache_instances = 24

performance_schema = ON
performance_schema_instrument='%sync%=on'

innodb_monitor_enable = 'all'

back_log = 1024

sync_binlog = 1
log-bin=mysql-bin
master_info_repository = TABLE
relay_log_info_repository = TABLE

innodb_strict_mode
innodb_file_format = Barracuda
innodb_buffer_pool_instances = 24
innodb_buffer_pool_size = 42G
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
innodb_log_files_in_group = 16
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_purge_threads = 8
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_flush_neighbors = 0
innodb_io_capacity = 80000
innodb_io_capacity_max = 120000
innodb_lru_scan_depth = 3000
innodb_open_files = 3000

innodb_print_all_deadlocks = 1

innodb_change_buffer_max_size = 10

innodb_checksums = 1
innodb_checksum_algorithm = crc32

innodb_adaptive_flushing = 1
innodb_adaptive_flushing_lwm = 10
innodb_adaptive_hash_index = 0
innodb_read_ahead_threshold = 0
innodb_sync_array_size = 24
innodb_autoextend_increment = 8

innodb_max_purge_lag_delay=1000000
innodb_max_purge_lag=100000

innodb_page_size=16k

log_timestamps = SYSTEM
innodb_page_cleaners = 24
innodb_numa_interleave = ON

MySQL関連のパラメータ(主にInnoDB)について

このエントリはMySQL Casual Advent Calendar 2015の10日目のエントリです。

先日のMySQL Casual Talks Vol8で@karupaneruraさんがパラメータの振り返りのような発表をされていたので、昨今あまり書かれなくなったMySQLに絡む設定パラメータについて書きます。それなりのメモリ(32GBとか)やSSDとか使ってる事を前提にしたような内容となります。
依存して変更した方が良いパラメータもあるので内容が前後に飛びますがご容赦下さい。またソースコードをがっつり読んだわけではなく、ベンチマーク中の挙動から推測している箇所が多分にあります。MyISAMのテーブルがサービス用データベースに同居する事を考慮していません。
結構突貫で書いているので後から微妙に修正する可能性があります。

InnoDBのパラメータ

  • innodb_buffer_pool_size
    • 挙げるまでも無く最近のバージョンのMySQLを使うなら必ず設定すべきパラメータ。データとINDEXが格納されるため、DB専用サーバとして使う場合は70%程度割り当てる事が推奨されます。後述する innodb_log_file_size やバイナリログはファイルシステムキャッシュにある程度載っていた方が良いのでギリギリまで攻める必要はないかと思います。
    • innodb_page_sizeをデフォルト(16k)より小さくした場合、例えば4kにした場合は16kの時と比較し多くのメモリを消費するため、innodb_page_sizeを16kで運用していたサーバを4kに変更した場合は設定値によりますがGB単位で下げないとスワップ使い始めるため注意が必要です。
  • innodb_log_file_size
    • redoログを書くファイルのサイズ。所謂ib_logfileのファイルサイズを指定します。redoログの合計サイズはinnodb_log_file_size x innodb_log_files_in_group となるため数GBとする場合はinnodb_log_file_sizeを512MBとか1GBに設定し、innodb_log_files_in_groupを調整するのが良いと思います。1ファイルを大きくしすぎるとファイルシステムキャッシュから溢れやすくなるためです。更新が多い環境、かつ5.6以上を利用している場合は合計4GB以上に設定するのも有りです。5.5では合計の最大値が4GBのため注意して下さい。
    • わざと小さいサイズにする事もありますが、適切にその他のパラメータやファイルシステムを選択出来るのであれば更新ヘビーな環境では大きい方がほとんどの場合、性能向上・安定する傾向があります。
    • 一般にib_logfileの合計サイズが大きくなるとcrash recoveryの時間が長くなります。
  • innodb_buffer_pool_instances
    • innodb_buffer_pool_sizeをいくつのインスタンスに分けるか指定します。デフォルトは8です。なおinnodb_buffer_pool_sizeが1G以下(未満?)の場合は1が設定されます)40GBとか大きい値をinnodb_buffer_pool_sizeに設定している場合は20とか設定することで同時実行性能が向上します。
  • innodb_flush_method
    • こちらも言わずもがな、なパラメータ。SANストレージや仮想環境の場合はデフォルトを使う事が多いようですが、BBU付きRAIDを使っていたりSSDの場合はO_DIRECTを推奨します。ファイルシステムがXFSの場合は並列書込みが可能(たぶん同一ファイルへの)となるようですのでext3ext4よりも性能が上がるケースが多いです。
  • innodb_purge_threads
    • あまり意識している人はいない気がするパラメータ。5.6ではデフォルト1、5.7ではデフォルト4となっています。更新が多く、show engine innodb status\Gを見た場合に History list length [数字] の数字が肥大化していっている場合は増やす事が推奨されます。但しMySQLの再起動が必要です。History list lengthが増えているということはundo領域(デフォルトではibdata1と同居)が肥大化する要因となるため気を配ったほうが良いです。またHistory list lengthが大きくなっていくと基本的には性能が劣化する傾向があります。
    • 何か時間の超絶かかるSELECTとかが実行されている場合もHistory list lengthが増える事があります。その場合は増やしてもたいした効果はないと考えられます(だってpurge出来ないし)
    • なお、過去の記憶ですが、無駄に増やしすぎるとコンテキストスイッチが増加して少ない設定の時よりも性能が下がるので程ほどに設定して下さい。5.7なら殆どのケースでデフォルトで十分だと思います。
  • innodb_io_capacity, innodb_io_capacity_max(max側は5.6から追加)
    • I/O アクティビティー (バッファープールからのページのフラッシュや挿入バッファーからのデータのマージなど) に上限を設定します(めんどいのでマニュアルより)。
    • 増やした方が良いケースとして知っているパターンを記載します。show engine innodb status\Gを実行し、INDIVIDUAL BUFFER POOL INFO以下の各バッファプールインスタンスのFree buffersが0で張り付いている場合は増やす事が推奨されます。少ないとバッファからのフラッシュ待ちが発生し性能劣化します。
    • これはioリクエスト数ではなく、innodb_page_sizeを1としているように見受けられます。ですのでinnodb_page_sizeが16kと4kでは異なる設定にする事が推奨されます。1000設定していてinnodb_page_sizeが16kなら 16k x 1000だけ書こうとするはずです。
    • このパラメータの挙動は5.5、5.6、5.7で全部微妙に違うはずなのでそれぞれの環境で調整する必要があります。5.7で改善され必要な状況では綺麗にpage/secで書込みを行うように見られました。5.7より前は設定値以上で書込みを行うケースが見受けられました。
    • なお、増やし過ぎても性能が下がるので様子を見ながら設定変更するのが良いです。
  • innodb_lru_scan_depth
    • ざっくり書くとInnoDBバッファプールを使い切るぐらいのデータ・INDEXが存在する環境において各バッファプールインスタンスのFree buffersをどこまで空けておくかを指定します。デフォルト1024です。よってinnodb_buffer_pool_instancesがデフォルトの8の場合は1024 x 8(page)だけMySQLは空きを確保しようとします。
    • innodb_lru_scan_depthの設定値を下回るとMySQLは空きを確保するために積極的にフラッシュしようとするようなので、innodb_io_capacityが少ないと待ち時間が長くなり性能劣化するようでした。
    • 一般にinnodb_io_capacityを増やしたらinnodb_lru_scan_depthも増やせ、と言われているようです。但し、先にも記載したとおりバッファプールインスタンス毎になるためinnodb_buffer_pool_instancesの設定値も意識して調整する必要があります。
  • innodb_checksum_algorithm
    • 最近のCPUならCRC32の計算を速く行ってくれるのでcrc32を設定することを推奨します。書込み性能向上しますが、体感できるほどのワークロードは滅多にないと思います。が気持ち的にcrc32に設定するのが良いと思います。
    • strict_crc32は使ったことが無いですが、マニュアルを読む限りサービス開始時からこのパラメータを設定出来る場合はstrict_crc32を使うのが良さそうです。
  • innodb_change_buffer_max_size
    • innodb_change_bufferingが有効であれば使われます。デフォルト25ですが木下さんのスライドによるとSSDのようにio性能高い場合は少なくすることが推奨のようです。セカンダリインデックスの更新が多くないなら下げて良いかなー、という感じです。私はベンチマーク取る時は多少下げて10にしています。
  • innodb_numa_interleave
    • 5.6.27, 5.7.9から利用可能になったパラメータ。NUMA環境においてInnoDBバッファプールをnumactl --interleave=allのようにして扱うようになります。Swap Insanity対策にも有効にしておくと良いと思います。
  • innodb_flush_neighbors, innodb_read_ahead_threshold
    • SSDPCI-E SSDを使う場合は0にして余計なioを減らしたほうがほとんどのケースで性能向上が期待出来ます。HDD環境で0にすると涙目になるぐらい性能が劣化する事があるので注意して下さい。
  • innodb_read_io_threads, innodb_write_io_threads
    • 変更して厳密に測定した事は無いのですが、SSDを使う場合は12とか設定した方が速くなる事が多いようです。
  • innodb_adaptive_hash_index
    • KVSのようなハッシュインデックスをバッファ内に持つかの指定。デフォルトONで有効です。データ・INDEXが全てInnoDBバッファプールに収まる範囲では有効な方が速くなるケースがありますが、収まらない場合は当然ヒットしないことがあるのでオーバーヘッドが発生し性能が劣化する事があります。一応5.7.8からinnodb_adaptive_hash_index_parts(デフォルト8)というパラメータが増えていますが、バッファに収まらない環境ではやはりOFFの方が性能が出る傾向にあります。
    • 早いケースでも劇的に、という程ではないのでOFFで良いんじゃないかと最近は思っています。
    • 内部のmutex競合の問題もあるようです。中の人も問題認識はされているようですので5.8では改善されるかもしれません。

その他のパラメータ

  • thread_cache_size
    • 都度アプリケーションからMySQLへ接続/切断を行うような環境では128とかもっと大きい値を設定しておいた方が接続が速くなります。MySQLは接続ごとにスレッドを生成しますが、それをキャッシュしておくことによりスレッド生成の待ち分、短縮されるためとなります。
    • show global statusのThreads_createdが増え続ける傾向にある場合には適当に増やして様子を見るのが良いです。このパラメータはset globalで動的変更可能です。
  • back_log
    • 増やしておくと何らかのタイミングでアクセスが急増し、MySQLへの接続が一気に発生した場合の取りこぼしが減ります。MySQLのスレッド生成が追い付かない、またはTCP接続の段階でエラーとなってぶった切られた場合はLost Connectionがエラーとしてアプリケーションのログに記録される事があります。
    • この値を増やす場合は以降に記載するnet.core.somaxconnやnet.core.netdev_max_backlogも合わせて増やす事が推奨されます。

OS側色々

  • vm.swappiness
    • CentOS6系(たしか6.3あたりのKernelから)ならvm.swappiness=1(sysctl.confに書いてOS再起動しても永続化)にしておく事でスワップを使う可能性を抑制出来ます。CentOS5系なら0です。ただこの設定をするのは怖い、という人もいるようです。自分の経験ではinnodb_buffer_pool_sizeとかを適切に設定していれば問題になった事はありませんが、もちろん絶対問題無いという保障はしません。
  • net.core.somaxconn、net.core.netdev_max_backlogも多数の接続/切断が行われる環境では増やす事が推奨されます。
  • ファイルシステム
    • 更新が多い環境であればxfsの方が速い傾向にあります。ext4だと同一ファイルへの複数プロセス(スレッド)からの書込みが行えないためだと考えられます。
    • ext4, xfsではIOバリアがデフォルト有効のため、性能を求めるのであればマウントオプションにbarrier=0(またはnobarrier)と書いて無効にする事もあります。凄く速いSSDだと有効でも無効でも、MySQLから使う分には差が全然出ない事もありました。

まとめ

色々と書いてみましたがどうでしょうか。一気に書き上げたので変な記載もあるかもしれませんが参考になれば幸いです。
個人的にはその他パラメータだとinnodb_adaptive_flushing_lwmを動かした場合の挙動をもうちょっとみたいと思っています。

他にもこの辺設定しろよ、とかあればMySQL CasualのSlackなんかでご連絡いただけると喜びます。

MySQL5.7でInnoDBのTransparent Page Compressionを試してみる Part1

このエントリはMySQL Casual Advent Calendar 2015の6日目のエントリです。
Part1としているのは12/21にPart2を公開予定のためです。

InnoDBのTransparent Page Compression

MySQL 5.7からInnoDBにTransparent Page Compressionという圧縮機能が追加されました。InnoDBの圧縮機能はMySQL 5.1のInnoDB Pluginから使用可能でしたが、これまでのInnoDB圧縮と違い、Transparent Page Compressionは使用環境の制限があります。具体的にはFileSystemがhole punchingをサポートしている必要があります。
MySQL Server Blogからの抜粋ですが以下のようにKernelのバージョン制限があります。
InnoDB Transparent Page Compression

よく使われているであろうCentOSでは7系であれば標準のKernelで利用可能です。
2016/01/06追記: 5.7.8-rc以降、CentOS6の2.6.32-504.12.2.el6.x86_64では使えることを確認しました。なおLinux genericだとダメなもよう。RHEL/Oracle Linux用のrpmだと使用可のようです。
利用可能な場合はMySQL起動時に以下のようなログが出力されます。

[Note] InnoDB: PUNCH HOLE support available

サポートされている圧縮形式はzlibとlz4の2つです。

注意点

特徴として*.ibdファイルはsparseファイルになるのでコールドバックアップを行っている場合は注意が必要です。
またxfsでのsparseファイルの挙動がやや怪しいので現時点ではext4か可能ならnvmfsでの使用が推奨になるかと思います。
InnoDB deadlock, thread stuck on kernel calls from transparent page compression

利用方法

サポートされているKernel環境であれば使用する際はこれまでのInnoDBの圧縮機能とほとんど変わらず、CREATE TABLE文に記載する形になります。

CREATE TABLE `t1` (
 id int,
 primary key (id)
) ENGINE=InnoDB COMPRESSION="zlib";

これまでのInnoDB圧縮より構文はシンプルかもしれません。
COMPRESSIONで指定可能な項目は zlib, lz4, noneの3つです。これまでのInnoDB圧縮との大きな違いは途中で圧縮を無効にしたい、有効にしたい、といった場合は以下のようにALTER文を実行しますがメタデータの変更しか発生しないためデータ量に関わらず即座に応答が返ってきます。

mysql> ALTER TABLE counttable COMPRESSION="none";
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> ALTER TABLE counttable COMPRESSION="lz4";
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

対象ページへの次の書き込みから圧縮・解凍されたデータが書き込まれる形となるので多少好みが分かれるかもしれません。
強制的に全ページを変更後の内容(zlib, lz4, none)に書き換えたい場合はOPTIMIZE TABLEを使う必要があります。

圧縮率

今回はLinkBenchのデータ(maxid1 = 20000001)のデータで確認しました。innodb_page_sizeは4k, 8k, 16k, 32k, 64kと全て試してみました(16kがデフォルト)。なお先にも記載した通りsparseファイルとなるためサイズの確認にはduコマンドに--apparent-sizeオプションの有無で確認しています。innodb_page_size 16kの場合の詳細な値は後述します。

compression innodb_page_size du -sch du -sch --apparent-size
lz4 4k 21GB 26GB
lz4 8k 17GB 25GB
lz4 16k 13GB 24GB
lz4 32k 11GB 25GB
lz4 64k 9.8GB 26GB
zlib 4k 21GB 26GB
zlib 8k 13GB 25GB
zlib 16k 11GB 24GB
zlib 32k 8.7GB 25GB
zlib 64k 8.0GB 26GB

各テーブルの圧縮率をMySQLから確認する場合は以下のようになります。

innodb_page_size=16kでzlibの場合
mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        62.5583 |             353.4492 |        944.0000 |
| linkdb/linktable#P#p1  |        63.5606 |             230.2969 |        632.0000 |
| linkdb/linktable#P#p2  |        61.3289 |             580.0664 |       1500.0000 |
| linkdb/linktable#P#p3  |        62.3612 |             414.0273 |       1100.0000 |
| linkdb/linktable#P#p4  |        61.7483 |             368.7461 |        964.0000 |
| linkdb/linktable#P#p5  |        62.1545 |             534.3789 |       1412.0000 |
| linkdb/linktable#P#p6  |        62.0091 |             784.1328 |       2064.0000 |
| linkdb/linktable#P#p7  |        63.0929 |             329.2109 |        892.0000 |
| linkdb/linktable#P#p8  |        62.6877 |             343.2734 |        920.0000 |
| linkdb/linktable#P#p9  |        63.6906 |             219.3086 |        604.0000 |
| linkdb/linktable#P#p10 |        61.4523 |             488.7852 |       1268.0000 |
| linkdb/linktable#P#p11 |        63.9216 |             202.0391 |        560.0000 |
| linkdb/linktable#P#p12 |        62.2676 |             250.5430 |        664.0000 |
| linkdb/linktable#P#p13 |        62.1705 |             550.7969 |       1456.0000 |
| linkdb/linktable#P#p14 |        61.9548 |             590.4609 |       1552.0000 |
| linkdb/linktable#P#p15 |        63.2757 |             308.4844 |        840.0000 |
| linkdb/counttable      |        61.5928 |             434.7695 |       1132.0000 |
| linkdb/nodetable       |        29.3900 |            3951.3359 |       5596.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql> 

innodb_page_size=16kでlz4の場合
mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        55.4050 |             420.9766 |        944.0000 |
| linkdb/linktable#P#p1  |        56.7123 |             273.5781 |        632.0000 |
| linkdb/linktable#P#p2  |        53.2847 |             702.5977 |       1504.0000 |
| linkdb/linktable#P#p3  |        54.7643 |             499.4023 |       1104.0000 |
| linkdb/linktable#P#p4  |        53.4013 |             447.3477 |        960.0000 |
| linkdb/linktable#P#p5  |        53.9754 |             649.8672 |       1412.0000 |
| linkdb/linktable#P#p6  |        54.0529 |             948.3477 |       2064.0000 |
| linkdb/linktable#P#p7  |        55.7346 |             394.8477 |        892.0000 |
| linkdb/linktable#P#p8  |        55.5176 |             409.2383 |        920.0000 |
| linkdb/linktable#P#p9  |        56.7004 |             263.2617 |        608.0000 |
| linkdb/linktable#P#p10 |        53.5631 |             588.8203 |       1268.0000 |
| linkdb/linktable#P#p11 |        56.8499 |             241.6406 |        560.0000 |
| linkdb/linktable#P#p12 |        54.0886 |             304.8516 |        664.0000 |
| linkdb/linktable#P#p13 |        54.4187 |             661.8398 |       1452.0000 |
| linkdb/linktable#P#p14 |        54.2518 |             710.0117 |       1552.0000 |
| linkdb/linktable#P#p15 |        55.7080 |             370.2813 |        836.0000 |
| linkdb/counttable      |        59.3272 |             458.7891 |       1128.0000 |
| linkdb/nodetable       |        28.2260 |            3999.2461 |       5572.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>

圧縮率だけ見るとzlibの方が良さそうです。なおALTER文でCOMPRESSION=noneに全てのテーブルを変更したあとにLinkBenchを1時間実行した場合、以下のようにcompressed_pctが当然のことながら減少しました。

innodb_page_size=16kでzlib->noneのベンチマーク実行後
mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from inf
ormation_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        20.6476 |             831.6133 |       1048.0000 |
| linkdb/linktable#P#p1  |        22.5837 |             529.5273 |        684.0000 |
| linkdb/linktable#P#p2  |        17.8877 |            1468.1680 |       1788.0000 |
| linkdb/linktable#P#p3  |        18.6294 |            1025.2695 |       1260.0000 |
| linkdb/linktable#P#p4  |        17.1622 |             947.6641 |       1144.0000 |
| linkdb/linktable#P#p5  |        17.2568 |            1366.9180 |       1652.0000 |
| linkdb/linktable#P#p6  |        18.7089 |            1937.9805 |       2384.0000 |
| linkdb/linktable#P#p7  |        20.2312 |             781.7344 |        980.0000 |
| linkdb/linktable#P#p8  |        20.9004 |             806.8164 |       1020.0000 |
| linkdb/linktable#P#p9  |        21.3016 |             513.1133 |        652.0000 |
| linkdb/linktable#P#p10 |        18.2654 |            1219.4805 |       1492.0000 |
| linkdb/linktable#P#p11 |        20.0674 |             489.1875 |        612.0000 |
| linkdb/linktable#P#p12 |        15.6215 |             664.9023 |        788.0000 |
| linkdb/linktable#P#p13 |        19.8355 |            1333.9375 |       1664.0000 |
| linkdb/linktable#P#p14 |        20.3784 |            1410.8945 |       1772.0000 |
| linkdb/linktable#P#p15 |        19.2842 |             749.0430 |        928.0000 |
| linkdb/counttable      |         0.1590 |            2168.5469 |       2172.0000 |
| linkdb/nodetable       |         0.2112 |            7771.5547 |       7788.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.01 sec)

mysql>

innodb_page_size=16kでlz4->noneのベンチマーク実行後
mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        19.1485 |             847.3242 |       1048.0000 |
| linkdb/linktable#P#p1  |        21.0874 |             536.6055 |        680.0000 |
| linkdb/linktable#P#p2  |        16.2208 |            1494.6211 |       1784.0000 |
| linkdb/linktable#P#p3  |        17.1224 |            1044.2578 |       1260.0000 |
| linkdb/linktable#P#p4  |        14.9215 |             969.8945 |       1140.0000 |
| linkdb/linktable#P#p5  |        15.5323 |            1395.4063 |       1652.0000 |
| linkdb/linktable#P#p6  |        17.0397 |            1977.7734 |       2384.0000 |
| linkdb/linktable#P#p7  |        18.5834 |             797.8828 |        980.0000 |
| linkdb/linktable#P#p8  |        19.3214 |             822.9219 |       1020.0000 |
| linkdb/linktable#P#p9  |        20.0094 |             524.7383 |        656.0000 |
| linkdb/linktable#P#p10 |        16.7346 |            1242.3203 |       1492.0000 |
| linkdb/linktable#P#p11 |        18.5290 |             495.3438 |        608.0000 |
| linkdb/linktable#P#p12 |        14.0730 |             673.6680 |        784.0000 |
| linkdb/linktable#P#p13 |        18.2792 |            1353.2969 |       1656.0000 |
| linkdb/linktable#P#p14 |        18.2571 |            1451.7539 |       1776.0000 |
| linkdb/linktable#P#p15 |        17.4944 |             765.6523 |        928.0000 |
| linkdb/counttable      |         0.1593 |            2168.5391 |       2172.0000 |
| linkdb/nodetable       |         0.2220 |            7750.7578 |       7768.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>

LinkBenchの実行

今回はinnodb_page_size=16kでzlib, lz4, noneで行いました。しかし、先に記載した通り、今回のデータ量ではinnodb_buffer_pool_sizeにデータ・INDEXが全て入ってしまう状態であったため明確な差が出ませんでした。これはwriteの時のみ圧縮すれば良い状態になってしまったためと考えられます。
DBの圧縮ではreadの展開の方が頻度が多く、writeのように1回だけ行えば良い訳では無いからです。そのためPart2ではバッファに載り切らない状態での測定を予定しています。

  • ベンチマーク環境
    • CentOS 7.1
    • 3.10.0-229.14.1.el7.x86_64
    • Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz x 2(HT有効)
    • DDR3 1333 MHz ECC 8GB x 8
    • Intel SSD 910 Series 400GB(200GB x 2, RAID0(md)) ext4を使用
    • NIC / Intel Corporation I350 Gigabit Network Connection(SupermicroのMB(X9DAE)のオンボード)
  • 実行コマンド
    • ./bin/linkbench -c config/MyConfig.properties -D maxtime=3600 -D requests=10000000 -D requesters=64 -r

innodb_page_cleanerを24に設定し、その他の設定は先日のはてダと同一です。

以下ベンチマーク結果です。

compress スコア
lz4 35316
zlib 33371
none 34673

なんかlz4が一番高くなりました。。。圧縮でwrite量が減ったのかな?、という謎。CPU使用率は各コアidleが30%ぐらいあったのでlz4のように高速性が売りなものだとこんな結果になるのかもしれません。

またlz4, zlibでデータを作成し、ALTERでCOMPRESSION=noneとした場合は以下のようになりました。

compress スコア
lz4->none 33848
zlib->none 34576

なんか不思議な感じです。

以下ベンチマーク中のグラフです。順番はzlib, lz4, zlib->none, lz4->none, noneの計5パターンです。








まとめ

使用についてはこれまでのInnoDBの圧縮よりは気軽に使える感じですが、Kernelが比較的新しいバージョンでないと使えないのが難点かと思います。
データ量がinnodb_buffer_pool_sizeに全て入るうちは明確な差が出ませんでした。Part2では100GB以上のデータを用意して傾向がどう変わるか見ていく予定です。

MySQL Casual Talks Vol.8 でMySQL 5.7とMariaDB 10.1の性能比較について発表しました

はじめに

遅くなりましたが11/20(金)に開催されたMySQL Casual Talks Vol.8で以下スライドで発表しました。その内容の補足とかを載せておきます。

最近MariaDB 10.1がGAになり、MySQL 5.7がGAになり、MariaDBってどうなん?、とか聞かれる事が増えたためとりあえずベンチマークで性能比較してみよう、が発端でした。MariaDBの10系はMySQL 5.5をベースにしているということで正直あまり期待していなかったのですが、起動時のログを見るとInnoDBはPerconaのXtraDB5.6ベースだったのでPercona5.6と同じ傾向かなぁという想像で実施しましたが概ね同じ傾向となりました。

まず、私はMariaDB10系はまともに触ったことがなかったので変だなと思った所を色々書いていきます。

MariaDBの変だと思った所
  1. innodb_page_size=4096にするとやな感じのログが出る(mysql_install_dbの際)
  2. 一通りデータ・INDEXがバッファに載った後も謎のreadが発生し続ける

まずは1.の変なログです。

InnoDB: Warning: innodb_page_size has been changed from default value 16384 to 4096d. (###EXPERIMENTAL### operation)
2015-11-30 01:58:57 2ab81a63f2a0 InnoDB: innodb-page-size has been changed from the default value 16384 to 4096
(snip)
2015-11-30  1:59:03 46976268028224 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
InnoDB: Note: File system for file ./mysql/innodb_table_stats.ibd has file block size 4096 not supported for page_size 4096
InnoDB: Note: Using file block size 2048 for file ./mysql/innodb_table_stats.ibd
InnoDB: Note: File system for file ./mysql/innodb_index_stats.ibd has file block size 4096 not supported for page_size 4096
InnoDB: Note: Using file block size 2048 for file ./mysql/innodb_index_stats.ibd
InnoDB: Note: File system for file ./mysql/gtid_slave_pos.ibd has file block size 4096 not supported for page_size 4096
InnoDB: Note: Using file block size 2048 for file ./mysql/gtid_slave_pos.ibd
(snip)

一応これでも動くようですが凄く気持ち悪いです。少なくもとMySQL5.6, 5.7では出力されません。
ですので、今回発表の全てのベンチマークではデフォルトのinnodb_page_size=16384(16k)で実施しています。


続いて2.について。ディスクI/O状況のグラフです。左側がMySQL5.7、右側がMariaDB10.1となります。

MySQL5.7ではほぼほぼreadが発生しない状態になるのですが、MariaDBについては絶えず8〜9MB/secの読み取り(bio)が発生しています。pt-ioprofile等で見ましたが読み取り対象のファイルが分かりませんでした。dstat使うと間違いなくMariaDBのプロセスが読み取りを行っているのですが対象ファイルが完全に謎。知っている方がいましたら教えて下さい。

あと、MySQLとの互換性を頻繁に主張しているMariaDBですが、パラメータで見るとMySQL5.6にあったパラメータでもMariaDB10.1には存在しなかったりします。例えば以下のもの。

master_info_repository
relay_log_info_repository

InnoDBはXtraDB5.6系なのでInnoDB関連パラメータは割りと同じのあるかむしろ多いかなのですがInnoDB以外の所は5.5系ベースのためこういった存在しないパラメータが出てくるのだと思います。SQLベースだったら互換性保たれてるんですかね?

sysbenchの補足

今回select.luaとoltp.luaを使っていますがやっていることはほぼほぼ一緒です。以下luaファイルの中身の抜粋です。

select.luaの抜粋
function event(thread_id)
   local table_name
   table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
   rs = db_query("SELECT pad FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
end

oltp.luaの抜粋
   for i=1, oltp_point_selects do
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
   end

oltp.luaを見ると他にもクエリ発行する処理が書かれていますが今回実行したオプションでは上記のクエリのみの実行となります。Dimitriさんのブログ、MariaDBのブログではoltp.luaで実行してるようでしたので比較のために両方取った形になります。
oltp.luaは見たとおりforでぐるぐる回すので速くなったようです。

LinkBench実行についての補足

LinkBench実行前にmysqldプロセスを停止し、ファイルシステムキャッシュを開放しています。

echo 3 > /proc/sys/vm/drop_caches

ib_buffer_poolファイルも削除し、mysqld起動時のバッファの暖気は行わないようしています。

その他ベンチマーク時にはまった点

はまった点というかCentOSでは7系から採用されたsystemdに慣れていないせいではあるのですが、systemd経由で起動した場合、MySQL 5.7もMariaDB 10.1もmysqld_safeを経由しないため、これまでmysqld_safeで設定されていた項目を全てsystemdの起動設定ファイルに記載する必要があります。

今回行ったのは以下2点です。

  1. jemallocの使用
  2. numactlの使用

jemallocについて。mysqld_safeを経由する場合は [mysqld_safe]セクションにmalloc-lib=/lib64/libjemalloc.so.1のように書くことで指定出来たのですが、これを/etc/systemd/system/mysqld.service.d/service.conf等に書く必要があります。
またopen_files_limitパラメータについてもsystemdの起動設定ファイルにLimitNOFILEが記載されていて、LimitNOFILE以下に制限されてしまうので大きな値をopen_files_limitで指定している場合は変更する必要があります。以下記載例です。

[Service]
LimitNOFILE=65535
Environment="LD_PRELOAD=/lib64/libjemalloc.so.1"

systemdを使う場合の設定については本家のマニュアル(2.5.10 Managing MySQL Server with systemd)を参照して下さい。

numactlについて。NUMA環境ではSwap Insanity予防のためnumactl --interleave=allで起動する事を個人的に推奨しており、おそらくその他の方も行っていると思います。
MySQL 5.6、5.7の最新版ではinnodb_numa_interleaveというパラメータが増えているのでこれを有効にすればnumactl --interleave=allで起動したのとほぼ同様の効果が得られるため本家のMySQLではnumactlはあまり必要では無くなっています(InnoDBバッファプールのみinterleave=allになる)。

mysqld_safeを経由していた場合、Perconaでは[mysqld_safe]セクションにnuma-interleave=1としておくとnumactl --interleave=allで起動したのと同様の事を内部的に行ってくれるのですが、先にも記載した通りsystemd経由の場合はmysqld_safeは使われません。
そのためMariaDBでは/usr/lib/systemd/system/mariadb.serviceを以下のように書き換えて起動していました。変更箇所のみ抜粋したのが以下です。

#ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS
ExecStart=/usr/bin/numactl --interleave=all /usr/sbin/mysqld $MYSQLD_OPTS
公開されているベンチマーク結果について

OracleやPercona、MariaDBが公開しているベンチマーク結果がありますが、細かいパラメータやサーバ環境が載っていないものについては話半分というかそういう事もあるんだね、という程度でしか見ていません。実際に同じようなベンチマークを行い、同じ傾向になれば少なくとも自分が取得したH/W環境において、公開されているベンチマークの傾向については信用しても良いな、と判断しています。
MariaDBのブログの公式としてのベンチマーク結果(と捉えて問題無いと思いますが)は、正直あまり信用していません。何故かというと環境に関する公開情報がほとんどのケースで少なすぎるからです。

ぶっちゃけ人の取ったベンチマークなんて鵜呑みにするもんじゃない、と思っています(私の公開しているのもその程度の見方でOKです。信用出来ると思った場合は信用して下さい)。基本疑って掛かって下さい(特にベンダーが対抗して出したようなやつ)。そもそもベンチマークツールで取得した結果・傾向がそのままそれぞれのサービス環境で同じ傾向になる事なんてマレだと思います。

それでもMySQL5.7のreadwrite時の性能改善については特筆すべきものがあると感じています。performance_schemaからの傾向と実際のベンチマークからもそう感じています。ちょっと上手く取れてなかったようですが、以下のようになっています。今回も左(すごい狭い)がMySQL5.7、右がMariaDB10.1です。


取る際にしくったのかMySQLが狭くなってますが上のグラフがrwlockの待ち時間、下が待ちに入った回数です。とにかくrwlockの待ち時間減少が著しいです。MySQL5.7がsxlockと名前が変わっているのは5.7からS-lock、X-lockに加えてSX-lockが増えた結果、変更されたためのようです。
※この辺は前回のMyNA会のdimstatについてのスライドを見て頂いた方が良いかもしれません。但しMySQL 5.6と5.7の比較です。

その他としては秒間接続数なんかは環境問わずそれだけで速くなる改善かと思います。
MySQL Performance: Improved Connect/sec Rate in MySQL 5.7
MySQL 5.6, 5.7の比較でsysbenchのソースコードをいじってクエリ発行しないようにした比較ですがこちらも以前のMyNA会で計測しているので参考までに載せておきます。

その他ベンチマーク中にぶつぶつTwitterに書いていた内容を@yoku0825さんがまとめてくれてますので参考までに見ていただければと思います。
MySQLベンチマークおじさんがMySQL 5.7とMariaDB 10.1の比較をしていると聞いて

最後に

MariaDBのブログとDimitriさんのブログ、Perconaのブログで5.7との比較したベンチマークが載っていて、Dimitriさんのベンチマークの大半がCPUコア数が増えた際のスケーラビリティについて言及されています(物理72コアでもスケールする)。
そんなブログを掲載したらMariaDBはスライドに載せたコモディティハードウェア(4コア 64GBメモリ!?)でMariaDBの方がreadonlyだと速いと載せてきました。実際MariaDBの方が4コアだと速いようでしたがwhere id = Nのような主キーオンリーで速くても大半のサービスでは嬉しくないわけで。2倍とか3倍差があれば考慮しても良いかなと思いますが10%前後の差です。
※極論、主キー探査しかしないならKVSとか使えば良いわけで(データ量にもよるけど)。RDBの必要無いし。

最近はPCI-E SSD(iodriveとか)を使っている環境ではSlaveサーバはあくまでフェールオーバー用や集計用で、サービスの全てのクエリをMasterで行うケースも結構増えてきてるように感じています。その場合、当然readonlyな状況は滅多に発生しないためreadwriteで速い方を推奨されるかと思います。

Perconaは5.7でMySQL5.7の改善点がほぼ取り込まれると考えられるのでPerconaはスケーラビリティについても追随してくるだろうし、更に改善してくる可能性もあると思います。しかしMariaDBは10.1をMySQL 5.7がGAになる前に出したため、XtraDB5.7(?)を使用すると思われる次のバージョンまではそれなりのreadwriteが発生する環境でMySQL5.7に勝つことは無いと思います。
(GTIDのフォーマットも本家MySQLと違うしどうするんでしょうね?)

最後に主要な設定を載せておきます。

max_connections = 4096
query_cache_type = 0
query_cache_size = 0
performance_schema = ON
performance_schema_instrument='%sync%=on'

innodb_monitor_enable = 'all'

back_log = 1024

sync_binlog = 1
log-bin=mysql-bin
master_info_repository = TABLE
relay_log_info_repository = TABLE

innodb_strict_mode
innodb_file_format = Barracuda
innodb_buffer_pool_instances = 20
innodb_buffer_pool_size = 40G
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
innodb_log_files_in_group = 16
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_purge_threads = 4
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_flush_neighbors = 0 / 1
innodb_io_capacity = 5000 / 12000 / 18000
innodb_io_capacity_max = 8000 / 15000 / 23000
innodb_lru_scan_depth = 4000
innodb_open_files = 3000

innodb_print_all_deadlocks = 1

innodb_change_buffer_max_size = 10

innodb_checksums = 1
innodb_checksum_algorithm = crc32

innodb_adaptive_flushing = 1
innodb_adaptive_flushing_lwm = 10
innodb_adaptive_hash_index = 0 / 1
innodb_read_ahead_threshold = 0 / 56
innodb_sync_array_size = 24 / 32
innodb_autoextend_increment = 32
metadata_locks_hash_instances = 48

innodb_max_purge_lag_delay=1000000
innodb_max_purge_lag=100000

innodb_page_size=16k

loose-log_timestamps = SYSTEM
loose-innodb_buffer_pool_dump_pct = 100
loose-innodb_page_cleaners = 8
loose-innodb_numa_interleave = ON

skip-name-resolve