MySQL 8.0.36でPGOを使ってビルド、性能比較してみる

この記事について

以下の内容をまとめています。

  • MySQL 8.0.36をPGO(Profile Guided Optimization)を使ってビルドするために必要となるプロファイルを取得出来るようにソースからビルドする方法
  • プロファイルの取得方法
  • プロファイルを利用してビルドする
  • PGOを利用しない場合との簡単な性能比較

なお、PGOを使ってビルドしたMySQLで問題が起きても保証しませんし出来ません。そこまで使い込めていないのでPGOを使わない場合と比較してどうなのかは全く未知数なためです。何か問題が起きても自己責任で対応してください。

また、プロファイルを取るのにsysbenchを使うという、ベンチマーク(sysbench)のためのプロファイルを取得してる形になるので、あらゆるケースでこういった性能向上をもたらすとは限りません。先に書いておくとpgoを使った結果20〜40%速くなりました。

PGOを使ったビルドに必要なこと

  1. プロファイルが取得出来るようにMySQLをビルドする
  2. 出来上がったプロファイル取得可能なmysqldを起動する
  3. ベンチマークmtrなど、負荷をかけたり任意のクエリを投げたりする(プロファイルが取得可能なmysqldはかなり遅いです)
  4. mysqldを停止する(プロファイルが出力される)
  5. 出力されたプロファイルを利用してMySQLをビルドする

単純に2回ビルドが必要であり、また負荷をかけたりしてプロファイルを取得する必要があるため割と手間がかかります。

MySQL 8.0.36をプロファイルが取得出来るようにビルドする

ということでビルドしてきます。
当初はclangでやろうとしてましたが、MySQLではプロファイルが正しく取得出来ないようでしたのでgccを利用しています。

  • OS Rocky Linux 8(4.18.0-513.5.1.el8_9.x86_64)
  • CPU Intel Xeon E5-2643 v3 3.4GHz x 2(2P12C24T)
  • MEM 128GB(16GB x 8, DDR3 2133MHz)

必要なパッケージのインストール

sudo dnf install --enablerepo=powertools cmake ncurses-devel \
  numactl-devel libaio-devel \
  systemd-devel libtirpc-devel rpcgen cyrus-sasl-devel openldap-devel \
  gcc-toolset-12-gcc gcc-toolset-12-gcc-c++ gcc-toolset-12-binutils \
  gcc-toolset-12-annobin-annocheck gcc-toolset-12-annobin-plugin-gcc

環境によって不足しているパッケージがあるかもしれないので、その場合は適宜インストールしてください。

ビルドとインストール

sudo install -d -o $(whoami) -g $(group) /usr/local/src/mysql
cd /usr/local/src/mysql
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-boost-8.0.36.tar.gz
tar zxf mysql-boost-8.0.36.tar.gz
mkdir build && cd build
cmake -DFPROFILE_GENERATE=ON \
  -DFPROFILE_DIR=/usr/local/src/mysql/profile-dir \
  -DWITH_SYSTEMD=ON \
  -DWITH_BOOST=../mysql-8.0.36/boost ../mysql-8.0.36
make -j 16 (CPUコア数に適宜変更を推奨)
sudo make install

-DFPROFILE_DIRで指定したディレクトリにプロファイルが出力されるようになります。そのためビルドするユーザ及びMySQLの実行ユーザが書き込める必要があります。
一般的にMySQLの実行はmysqlユーザで行うことが多いと思いますが、プロファイルの取得時のみ、ビルドしたユーザで実行した方がディレクトリのオーナー・パーミッションを気にしなくて良いので楽です。

また、プロファイル取得時はsystemd経由での起動もお勧めしません。rootユーザで一部のプロファイルが出力され、その後MySQLの実行ユーザで同じファイルを書き込もうとしてエラーになることがあるためです。

プロファイルが書き込めない場合、MySQLのエラーログに出力されます。datadirの初期化(mysqld --initializeや--initialize-insecure)の場合は標準出力(標準エラー出力?)されるのですぐに気付くと思います。

MySQLのインストール先はデフォルトでは/usr/local/mysql になります。変更する場合はcmakeのオプションで-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-8.0.36のように指定してください。

プロファイルを取得する

my.cnfの用意

先に記載した通り、プロファイルを取得する際はビルドで利用した一般ユーザでMySQLを起動します。そのためmy.cnfの[mysqld]セクションにてuser = [ビルドユーザ]を指定してください。
その他の設定は現在利用中のmy.cnfと同じで大丈夫なはずです。私は特に問題ありませんでしたが、異常がある場合はエラーログを確認するなど、適宜対応してください。

MySQLの起動

プロファイルを取得する場合はsystemd経由で起動しない方が良いため、以下のようにdatadirの初期化を行い、起動します。
--user, --datadirは適宜変更してください。またdatadirはビルドユーザが書き込めるようにオーナーやパーミッションを設定してください。

/usr/local/mysql/bin/mysqld --no-defaults --user={ビルドユーザ} \
  --initialize-insecure --datadir=/var/lib/mysql --log-error-verbosity=3
/usr/local/mysql/bin/mysqld &

検証用ユーザ、データのロード

プロファイルを取得するには実際に処理を行わせる必要があります。私はsysbenchでデータのロード、point select、non index update、index update、oltpなどを実行しました。
以前sysbenchの使い方についてまとめているのでもし同じように行う場合は参考にしてください。
hiroi10.hatenablog.com
最初の方に書きましたが、プロファイルが取得出来る状態のmysqldは非常に遅いです。ベンチマークを流す場合は遅いことを認識した上で実行しないと驚くかもしれません。
一通り処理が流し終わったらMySQLを停止します。systemd経由で起動していないためmysqlコマンドで接続して停止します。

mysql -uroot -e 'shutdown;'

停止後にMySQLのエラーログを確認してください。パーミッションなどが理由でプロファイルの書き込みが出来なかった場合は何らかの出力があるためです。
停止後にビルド時に-DFPROFILE_DIRで指定したディレクトリに以下のようにファイルが出力されているはずです。かなり大量のファイルになるのでご注意ください。

以下は出力ファイルの例ですが環境によって変わるはずです。

#usr#local#src#mysql#build#vio#CMakeFiles#vio_objlib.dir#viosocket.cc.gcda
#usr#local#src#mysql#build#vio#CMakeFiles#vio_objlib.dir#viossl.cc.gcda
#usr#local#src#mysql#build#vio#CMakeFiles#vio_objlib.dir#viosslfactories.cc.gcda

以上でプロファイルを利用してビルドする準備は完了です。

プロファイルを利用してビルドする

プロファイルを指定して改めてビルドします。別なディレクトリでビルドします。

cd /usr/local/src/mysql
mkdir build-pgo && cd build-pgo
cmake -DFPROFILE_USE=1 \
  -DFPROFILE_DIR=/usr/local/src/mysql/profile-dir \
  -DWITH_SYSTEMD=ON \
  -DWITH_BOOST=../mysql-8.0.36/boost \
  -DCMAKE_C_FLAGS='-Wno-missing-profile' \
  -DCMAKE_CXX_FLAGS='-Wno-missing-profile' \
  ../mysql-8.0.36
make -j 16 (CPUコア数に適宜変更を推奨)
sudo make install
sudo cp -i ./scripts/mysqld.service /usr/lib/systemd/system/mysqld.service
sudo systemctl daemon-reload

この場合、上書きインストールになるのでインストール先を別なディレクトリにする場合は-DCMAKE_INSTALL_PREFIXオプションを利用して別なディレクトリにしてください。-Wno-missing-profileを指定し、プロファイルが存在しないコードをビルドする際にwarningを出力しないようにしています。

PGOを利用したMySQLはsystemd経由で起動しても問題ないため、mysqld.service をコピーしています。
またmy.cnfの[mysqld]セクションにプロファイル取得用に記載していた user = {ビルドユーザ} はお使いのmy.cnfの元の状態に戻しておいてください。

PGOを利用しない場合との簡単な性能比較

sysbenchを利用してプロファイルを取得したのでsysbench速くなることを期待して実行しました。
データ量はテーブル数8、レコード数10000000でinnodb_buffer_pool_sizeに全て収まるデータ量となっています。
個人的な目的として8.0系でのI/O周りの改善ではなく、比較的単純な処理の性能低下がどうにかならんのか?、と色々試していたためこういったベンチマーク内容で測定しています。

なお、トランザクションを有効(begin; {DML}; commit;}にしているのでoltp以外は1トランザクションあたり3クエリです。
念のため書いておきますがtransactions per secはhigher is better, Latencyはlower is betterです。

simple select

スレッド数毎のtransactions per secは以下のようになりました。

thds 1 2 4 8 16 32 64 128 256 512 1024
normal 9536.00 18556.91 35355.40 60466.10 90848.28 122095.61 123047.98 124865.77 121841.57 111793.50 98101.31
with pgo 14791.91 26751.15 52541.50 86702.95 118730.86 164271.05 163456.22 165232.27 162657.31 145873.85 130588.62

Latency(ms)の95th percentileは以下のようになっていました。

thds 1 2 4 8 16 32 64 128 256 512 1024
normal 0.11 0.11 0.13 0.16 0.20 0.41 0.63 1.21 2.35 5.09 12.98
with pgo 0.07 0.10 0.09 0.11 0.16 0.29 0.47 0.92 1.76 4.03 11.45

point selectのsysbenchの実行方法は以下で、--threadsオプションの値をforとかで回しながら実行しています。

sysbench \
    /usr/share/sysbench/oltp_read_only.lua \
    --rand-seed=$RANDOM --rand-type=uniform \
    --db-driver=mysql --db-ps-mode=disable \
    --skip_trx=false \
    --report-interval=1 \
    --tables=8 --table-size=10000000 --mysql-db=sbtest \
    --mysql-user=sbtest --mysql-password={password} \
    --mysql-socket=/tmp/mysql.sock --point_selects=1 --range_selects=0 \
    --time=60 --threads={num threads} \
    run

non index updates

スレッド数毎のtransactions per secは以下のようになりました。

thds 1 2 4 8 16 32 64 128 256 512 1024
normal 4160.98 7918.04 13577.11 21584.82 32614.90 40147.40 46502.45 50112.75 50614.77 47699.98 44828.06
with pgo 5320.93 9937.79 15924.34 25338.64 36847.67 46371.56 53528.19 59114.57 59972.15 55801.85 51117.43

Latency(ms)の95th percentileは以下のようになっていました。

thds 1 2 4 8 16 32 64 128 256 512 1024
normal 0.26 0.28 0.34 0.46 0.64 1.10 2.07 4.33 9.56 21.11 43.39
with pgo 0.20 0.23 0.31 0.40 0.58 0.97 1.79 3.62 7.98 17.63 38.94

non index updateのsysbenchの実行方法は以下で、--threadsオプションの値をforとかで回しながら実行しています。

sysbench \
    /usr/share/sysbench/oltp_read_write.lua \
    --rand-seed=$RANDOM --rand-type=uniform \
    --db-driver=mysql --db-ps-mode=disable \
    --skip_trx=false --report-interval=1 --tables=8 \
    --table-size=10000000 --mysql-db=sbtest \
    --mysql-user=sbtest --mysql-password={password} \
    --mysql-socket=/tmp/mysql.sock --point_selects=0 \
    --range_selects=0 --index_updates=0 \
    --non_index_updates=1 --delete_inserts=0 \
    --time=60  --threads={num threads} \
    run

index updates

スレッド数毎のtransactions per secは以下のようになりました。

thds 1 2 4 8 16 32 64 128 256 512 1024
normal 3232.56 6909.83 12529.92 19856.93 29298.31 33710.39 37260.95 39511.08 41085.68 39516.78 38225.84
with pgo 4906.85 9215.24 15445.60 23579.23 35040.26 41631.39 47172.44 50674.84 52242.76 49325.77 46954.87

Latency(ms)の95th percentileは以下のようになっていました。

thds 1 2 4 8 16 32 64 128 256 512 1024
normal 0.48 0.40 0.39 0.52 0.77 1.76 3.30 6.32 12.98 27.66 56.84
with pgo 0.23 0.25 0.32 0.42 0.61 1.39 2.43 4.65 9.73 21.50 44.98

index updateのsysbenchの実行方法は以下で、--threadsオプションの値をforとかで回しながら実行しています。

sysbench \
    /usr/share/sysbench/oltp_read_write.lua \
    --rand-seed=$RANDOM --rand-type=uniform \
    --db-driver=mysql --db-ps-mode=disable \
    --skip_trx=false --report-interval=1 --tables=8 \
    --table-size=10000000 --mysql-db=sbtest \
    --mysql-user=sbtest --mysql-password={password}  \
    --mysql-socket=/tmp/mysql.sock --point_selects=0 \
    --range_selects=0 --index_updates=0 \
    --non_index_updates=1 --delete_inserts=0 \
    --time=60  --threads={num threads}  \
    run

oltp

スレッド数毎のtransactions per secは以下のようになりました。

thds 1 2 4 8 16 32 64 128 256 512 1024
normal 905.87 1746.75 3409.06 6045.36 9379.77 10583.15 11097.59 10488.04 10298.81 10366.01 10333.03
with pgo 1218.36 2373.08 4512.32 7770.68 11946.22 13711.97 14352.37 13604.27 13322.18 13294.31 13046.24

Latency(ms)の95th percentileは以下のようになっていました。

thds 1 2 4 8 16 32 64 128 256 512 1024
normal 1.25 1.30 1.34 1.64 2.18 4.65 9.39 23.10 54.83 112.67 219.36
with pgo 0.94 0.97 1.03 1.27 1.79 3.55 7.17 17.32 41.85 87.56 173.58

oltpのsysbenchの実行方法は以下で、--threadsオプションの値をforとかで回しながら実行しています。

sysbench \
    /usr/share/sysbench/oltp_read_write.lua \
    --rand-seed=$RANDOM --rand-type=uniform \
    --db-driver=mysql --db-ps-mode=disable \
    --skip_trx=false --report-interval=1 --tables=8 \
    --table-size=10000000 --mysql-db=sbtest \
    --mysql-user=sbtest --mysql-password={password} \
    --mysql-socket=/tmp/mysql.sock --point_selects=1 \
    --range_selects=1 --range_size=10 --index_updates=1 \
    --non_index_updates=1 --delete_inserts=1 \
    --time=60  --threads={num threads}  \
    run

結果

初めの方に書いた通り、同じ8.0.36でpgoを利用することで20〜40%速くなる結果となりました。8.0系ではパラメータチューニングをどう頑張っても5.7系に追いつけるイメージが無かったんですが、pgoを利用することで5.7と比較してもトントンか速く出来そうだなー、となりました。

PGOを使ってみようと思った理由

最初に英語のOracleのブログでEnterprise Editionの方が速くなった、というのが出てきて、よくあるCommunity版とEnterprise版の比較ではthread poolを使って実行スレッド数が増えても性能がー、といったのが基本です。
ただ、このブログの結果に関してはthread poolじゃないだろ、という結果で「何これ?」となりました。英語圏も含めて色々と情報を探してたらPGOかも?みたいなやり取りが出てきてPGOについて調べ始めたという流れです。
PGOを利用する場合に使われるgccのオプションを調べてMySQLソースコード(CMakeList.txtとか)を探し、それっぽいのがあったため試した感じです。

最近だとPerconaのブログでMySQL 8.2.0 Community vs. Enterprise; Is There a Winner?というのが上がっていたりでCommunity版とEnterprise版の比較が出てきてたりします。
Oracleのブログでは8.0.35での比較でしたが、Perconaでは8.2.0での比較なので、最近のバージョンではCommunity版とEnterprise版でコードは同じでも最適化の仕方が違うのかもしれません。私は8.0系でEnterprise版を利用したことがないので確認出来ていません。

my.cnf

今回使ったmy.cnfです。終わってからinnodb_flush_log_at_trx_commit=0にしてたは…、と気付いたけどI/Oは先に書いた通りそこまで気にしてないというかsimple selectみたいなのはせめて5.7系ぐらい速くなってよ、とやってたので今回はそのままにします。時間ができたらinnodb_flush_log_at_trx_commit=1でもやってみるかもしれません。
また5.7と8.0を行ったり来たりしているため8.0系だとDeplicatedになったパラメータをそのまま利用していたりします。また、自分が見ている環境が基本的にutf8mb3でutf8mb4ではないためcharacter-set-server = utf8としています。

[mysqld]
datadir=/var/lib/mysql
socket = /tmp/mysql.sock

skip-name-resolve

max_connections = 4096
character-set-server = utf8
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 16M
net_buffer_length = 8K
thread_cache_size = 100
table_open_cache = 20480
table_open_cache_instances = 16

max_heap_table_size = 128M
tmp_table_size = 128M

performance_schema = ON
loose-performance_schema_show_processlist = ON
loose-performance_schema_instrument='%sync%=on'

loose-innodb_monitor_enable = 'all'

back_log = 1024

sync_binlog = 1
log-bin=mysql-bin
log_slave_updates
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery
binlog_format=row
binlog_rows_query_log_events
server-id       = 1
expire_logs_days = 7

gtid_mode = ON
enforce_gtid_consistency = ON

loose-innodb_file_format = Barracuda
innodb_buffer_pool_instances = 12
innodb_buffer_pool_size = 80G
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
innodb_log_files_in_group = 4
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 = 8
innodb_write_io_threads = 8
innodb_flush_neighbors = 0
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_lru_scan_depth = 4000
innodb_open_files = 3000

innodb_flush_log_at_trx_commit = 0

innodb_print_all_deadlocks = 1

innodb_change_buffer_max_size = 5

innodb_checksum_algorithm = crc32

innodb_adaptive_flushing = 1
innodb_adaptive_flushing_lwm = 10
innodb_read_ahead_threshold = 0

innodb_max_purge_lag_delay=1000000
innodb_max_purge_lag=100000

innodb_page_size=4096

log_timestamps = SYSTEM
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = OFF
innodb_buffer_pool_dump_pct = 100
innodb_page_cleaners = 24
innodb_numa_interleave = ON

sysbench 1.0を使ってみよう

この記事はMySQL Advent Calendar 2019 - Qiitaの18日目です。

この記事について

sysbench 1.0の使い方や、実際にどういうクエリが実行されるかを知ってもらえれば良いなと考えて書いています。

sysbenchについて

sysbenchは現在バージョン1.0でgithubで公開されています。

github.com

データベースに対するベンチマークとしてはMySQL向けの印象が強いですが、現在はPostgreSQLに対してもベンチマーク可能となっているようです(自分はMySQLに対してしか利用した事がありません)。

$ sysbench --help | grep pgsql
  pgsql - PostgreSQL driver
pgsql options:
  --pgsql-host=STRING     PostgreSQL server host [localhost]
  --pgsql-port=N          PostgreSQL server port [5432]
  --pgsql-user=STRING     PostgreSQL user [sbtest]
  --pgsql-password=STRING PostgreSQL password []
  --pgsql-db=STRING       PostgreSQL database name [sbtest]
$

主要なOSへのインストール方法はgithubに記載されていますので割愛します。

sysbenchの使い方

MySQLに対しての利用方法は概ね以下のようになります。

  1. ベンチマーク用データベースとMySQLユーザの作成
  2. ベンチマーク用のデータのロード
  3. ベンチマークの実行

順番に必要な作業を記載します。

ベンチマーク用データベースとMySQLユーザの作成

mysql> CREATE DATABASE sbtest;
mysql> CREATE USER sbtest@localhost IDENTIFIED BY 'sbtest-password';
mysql> CREATE USER sbtest@'10.0.0.0/255.255.255.0' IDENTIFIED BY 'sbtest-password';
mysql> GRANT ALL ON sbtest.* to sbtest@localhost;
mysql> GRANT ALL ON sbtest.* to sbtest@'10.0.0.0/255.255.255.0';
 ※上記は10.なリモートから実行する場合を例として書いていますが、環境に応じて変更して下さい。

MySQL 8.0系で利用する場合はMySQLユーザの作成では

mysql> CREATE USER sbtest@localhost IDENTIFIED WITH mysql_native_password by 'sbtest-password';

のようにmysql_native_passwordを指定してください。MySQL 8.0系でデフォルトとなったcaching_sha2_passwordにはこのブログを書いている時点では対応していません。

ベンチマーク用のデータのロード

以降はCentOS7の環境での実行を想定しています。その他の環境ではluaファイルのPATHが異なるかもしれません。

データのロード時に考慮する内容は主に利用するテーブル数と1テーブル辺りのレコード数です。以下の例を参考に主要なオプションについて記載していきます。

$ sysbench \
  /usr/share/sysbench/oltp_common.lua \
  --db-driver=mysql \
  --tables=16 \
  --table-size=2500000 \
  --threads=4 \
  --mysql_storage_engine=innodb \
  --mysql-db=sbtest \
  --mysql-user=sbtest \
  --mysql-password=sbtest-password \
  --mysql-socket=/tmp/mysql.sock \
  prepare

なお、0.5系までの癖で /usr/share/sysbench/oltp_common.lua と書いていますが(自分はsysbench0.5系はソースビルドばかりでパスが不定だったため)、1.0系では、少なくともyumでsysbenchをインストールした場合、luaファイル(シナリオ名)をフルパスで書く必要はなく、以下のように書いても動作します。

$ sysbench \
  --db-driver=mysql \
  --tables=16 \
  --table-size=2500000 \
  --threads=4 \
  --mysql_storage_engine=innodb \
  --mysql-db=sbtest \
  --mysql-user=sbtest \
  --mysql-password=sbtest-password \
  --mysql-socket=/tmp/mysql.sock \
  oltp_common \
  prepare

strace -f -ff -e trace=openを付けてsysbenchを実行すると以下のようにファイルを探している事が分かります(出力をgrep luaしたものです)。

open("./oltp_common.lua", O_RDONLY)     = -1 ENOENT (No such file or directory)
open("./oltp_common/init.lua", O_RDONLY) = -1 ENOENT (No such file or directory)
open("./src/lua/oltp_common.lua", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/home/hiroi10/.luarocks/share/lua/5.1/oltp_common.lua", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/home/hiroi10/.luarocks/share/lua/5.1/oltp_common/init.lua", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/home/hiroi10/.luarocks/share/lua/oltp_common.lua", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/home/hiroi10/.luarocks/share/lua/oltp_common/init.lua", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/usr/local/share/lua/5.1/oltp_common.lua", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/usr/share/lua/5.1/oltp_common.lua", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/usr/share/sysbench/oltp_common.lua", O_RDONLY) = 3
open("/usr/share/sysbench/oltp_common.lua", O_RDONLY) = 3
  • --tables 利用するテーブル数です。上記の例では16テーブル作成します(sbtest1からsbtest16テーブルが作成される)
  • --table-size 1テーブル辺りのレコード数です。250万レコードをロードするように指定しています。
  • --threads ロード処理のスレッド数です。4を指定しているので4テーブルに対して並列にロードを行います。サーバスペックに合わせて調整する事でロード時間を短縮できます。基本的にはtablesで指定した値の約数を指定します。
  • --mysql_storage_engine ベンチマークで利用するテーブルのストレージエンジンの指定です。InnoDB以外を利用する場合は変更してください。
  • --mysql-socket MySQL接続に利用するソケットファイルの指定です。外部のサーバへベンチマークを行う場合は --mysql-host を利用して下さい。

なお、上記のコマンドで作成されるテーブル定義は以下のようになります。

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2500001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

charsetはmy.cnfの設定により変わります(この環境ではcharacter-set-server = utf8)。

以降はluaファイルまでのフルパスで指定しています。

ベンチマークの実行

データのロードと同様、luaファイルを指定します。このブログ執筆時点の1.0.19-1.el7では以下のluaが用意されています。

$ rpm -ql sysbench | grep lua
/usr/share/sysbench/bulk_insert.lua
/usr/share/sysbench/oltp_common.lua
/usr/share/sysbench/oltp_delete.lua
/usr/share/sysbench/oltp_insert.lua
/usr/share/sysbench/oltp_point_select.lua
/usr/share/sysbench/oltp_read_only.lua
/usr/share/sysbench/oltp_read_write.lua
/usr/share/sysbench/oltp_update_index.lua
/usr/share/sysbench/oltp_update_non_index.lua
/usr/share/sysbench/oltp_write_only.lua
/usr/share/sysbench/select_random_points.lua
/usr/share/sysbench/select_random_ranges.lua
/usr/share/sysbench/tests/include/inspect.lua
/usr/share/sysbench/tests/include/oltp_legacy/bulk_insert.lua
/usr/share/sysbench/tests/include/oltp_legacy/common.lua
/usr/share/sysbench/tests/include/oltp_legacy/delete.lua
/usr/share/sysbench/tests/include/oltp_legacy/insert.lua
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua
/usr/share/sysbench/tests/include/oltp_legacy/oltp_simple.lua
/usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua
/usr/share/sysbench/tests/include/oltp_legacy/select.lua
/usr/share/sysbench/tests/include/oltp_legacy/select_random_points.lua
/usr/share/sysbench/tests/include/oltp_legacy/select_random_ranges.lua
/usr/share/sysbench/tests/include/oltp_legacy/update_index.lua
/usr/share/sysbench/tests/include/oltp_legacy/update_non_index.lua

基本的には/usr/share/sysbench/以下のファイルのみの利用で良いと思いますが、sysbench 0.5に慣れている人は/usr/share/sysbench/tests/include/oltp_legacy/以下の方が最初は良いかもしれません。とはいえ0.5系と1.0系ではオプションが結構変わっているため上記のオプションのまま利用しようとするとエラーになるはずです。

全てに対して書いてもあまり意味が無い(理由は後述)ため、

/usr/share/sysbench/oltp_read_only.lua
/usr/share/sysbench/oltp_read_write.lua

について記載していきます。

oltp_read_only.lua

名前から想像出来る通りread onlyなOLTPベンチマークを実行します。実行例としては以下のようになります。

$ sysbench \
  /usr/share/sysbench/oltp_read_only.lua \
  --db-driver=mysql \
  --db-ps-mode=disable \
  --rand-type=uniform \
  --tables=1 \
  --table-size=2500000 \
  --mysql-db=sbtest \
  --mysql-user=sbtest \
  --mysql-password=sbtest-password \
  --mysql-socket=/tmp/mysql.sock \
  --events=1 \
  --threads=1 \
  run

--rand-type=uniformとしていますが、sysbench 1.0では uniform,gaussian,special,pareto の4種類が指定可能になっています。
また--rand-seedでseedを与えることが可能です。

この例では1スレッド(--threads=1)で1トランザクション(--events=1)の実行となり、実行されるクエリは以下のようになります。

BEGIN
SELECT c FROM sbtest1 WHERE id=654
SELECT c FROM sbtest1 WHERE id=28
SELECT c FROM sbtest1 WHERE id=264
SELECT c FROM sbtest1 WHERE id=355
SELECT c FROM sbtest1 WHERE id=363
SELECT c FROM sbtest1 WHERE id=259
SELECT c FROM sbtest1 WHERE id=635
SELECT c FROM sbtest1 WHERE id=845
SELECT c FROM sbtest1 WHERE id=255
SELECT c FROM sbtest1 WHERE id=124
SELECT c FROM sbtest1 WHERE id BETWEEN 586 AND 685
SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN 571 AND 670
SELECT c FROM sbtest1 WHERE id BETWEEN 646 AND 745 ORDER BY c
SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 934 AND 1033 ORDER BY c
COMMIT

上記のクエリの実行数やレンジサイズを指定するオプションが存在しています。全てのオプションを省略せずに記載すると以下のようになります。

$ sysbench \
  /usr/share/sysbench/oltp_read_only.lua \
  --db-driver=mysql \
  --db-ps-mode=disable \
  --rand-type=uniform \
  --tables=16 \
  --table-size=2500000 \
  --mysql-db=sbtest \
  --mysql-user=sbtest \
  --mysql-password=sbtest-password \
  --mysql-socket=/tmp/mysql.sock \
  --point_selects=10 \
  --range_selects=1 \
  --range_size=100 \
  --distinct_ranges=1 \
  --simple_ranges=1 \
  --sum_ranges=1 \
  --order_ranges=1 \
  --events=1 \
  --threads=1 \
  run

各オプションのデフォルト値は以下のようにhelpを参照する事で確認可能です。

$ sysbench /usr/share/sysbench/oltp_read_only.lua help
sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)

oltp_read_only.lua options:
  --auto_inc[=on|off]           Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]
  --create_secondary[=on|off]   Create a secondary index in addition to the PRIMARY KEY [on]
  --delete_inserts=N            Number of DELETE/INSERT combinations per transaction [1]
  --distinct_ranges=N           Number of SELECT DISTINCT queries per transaction [1]
  --index_updates=N             Number of UPDATE index queries per transaction [1]
  --mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb]
  --non_index_updates=N         Number of UPDATE non-index queries per transaction [1]
  --order_ranges=N              Number of SELECT ORDER BY queries per transaction [1]
  --pgsql_variant=STRING        Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is 'redshift'. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0
  --point_selects=N             Number of point SELECT queries per transaction [10]
  --range_selects[=on|off]      Enable/disable all range SELECT queries [on]
  --range_size=N                Range size for range SELECT queries [100]
  --secondary[=on|off]          Use a secondary index in place of the PRIMARY KEY [off]
  --simple_ranges=N             Number of simple range SELECT queries per transaction [1]
  --skip_trx[=on|off]           Don't start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
  --sum_ranges=N                Number of SELECT SUM() queries per transaction [1]
  --table_size=N                Number of rows per table [10000]
  --tables=N                    Number of tables [1]
$

リモートからsysbenchによる負荷をかける場合、デフォルトの--range_size=100のままだと最近のDB向けの比較的高性能なサーバでは1Gbpsの環境ではトラフィックが埋まる事が多いため、その場合は10など小さな値を設定した方が良いかもしれません。

また、先ほどあまり意味が無いと書いたのはこれらのオプションを変更する事でその他のシナリオは概ね再現出来るためです。

例えばoltp_point_select.luaですがoltp_read_onlyを利用した場合は以下のようになります。

$ sysbench \
  /usr/share/sysbench/oltp_read_only.lua \
  --db-driver=mysql \
  --db-ps-mode=disable \
  --rand-type=uniform \
  --tables=16 \
  --table-size=2500000 \
  --mysql-db=sbtest \
  --mysql-user=sbtest \
  --mysql-password=sbtest-password \
  --mysql-socket=/tmp/mysql.sock \
  --point_selects=10 \
  --range_selects=0 \
  --events=1 \
  --threads=1 \
  run

概ね再現出来ると書いたのはoltp_point_select.luaを利用した場合はBEGIN, COMMITは行われず以下のようにSELECTのみの実行となる点です。

SELECT c FROM sbtest1 WHERE id=118
SELECT c FROM sbtest1 WHERE id=14
SELECT c FROM sbtest1 WHERE id=19
SELECT c FROM sbtest1 WHERE id=165
SELECT c FROM sbtest1 WHERE id=522
SELECT c FROM sbtest1 WHERE id=228
SELECT c FROM sbtest1 WHERE id=530
SELECT c FROM sbtest1 WHERE id=631
SELECT c FROM sbtest1 WHERE id=91
SELECT c FROM sbtest1 WHERE id=274

oltp_point_selectの場合の実行例
$ sysbench \
  --db-driver=mysql \
  --db-ps-mode=disable \
  --rand-type=uniform \
  --tables=1 \
  --table-size=2500000 \
  --mysql-db=sbtest \
  --mysql-user=sbtest \
  --mysql-password=sbtest-password \
  --mysql-socket=/tmp/mysql.sock \
  --events=10 \
  --threads=1 \
  oltp_point_select \
  run

これまで--eventsを利用していましたが、よく使われるのは--timeの方になると思います。以下の例は

BEGIN;
SELECT ... FROM sbtest? WHERE id=?;
COMMIT;

を16スレッド(--threads=16)で60秒(--time=60)実行します。

sysbench \
  /usr/share/sysbench/oltp_read_only.lua \
  --rand-type=uniform \
  --db-driver=mysql \
  --db-ps-mode=disable \
  --skip_trx=false \
  --report-interval=1 \
  --tables=16 \
  --table-size=2500000 \
  --mysql-db=sbtest \
  --mysql-user=sbtest \
  --mysql-password=sbtest-password \
  --mysql-socket=/tmp/mysql.sock \
  --point_selects=1 \
  --range_selects=0 \
  --time=60 \
  --threads=16 \
  run

また、--report-interval=1を付けているため、1秒ごとの処理状況が標準出力されます。以下は出力からの抜粋です。

[ 1s ] thds: 1 tps: 5052.86 qps: 5052.86 (r/w/o: 5052.86/0.00/0.00) lat (ms,95%): 0.50 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1 tps: 5060.06 qps: 5060.06 (r/w/o: 5060.06/0.00/0.00) lat (ms,95%): 0.50 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1 tps: 5126.98 qps: 5126.98 (r/w/o: 5126.98/0.00/0.00) lat (ms,95%): 0.43 err/s: 0.00 reconn/s: 0.00

ベンチマークを実行する場合、何かしらのツールでリソースの使用状況をモニタリングしていると思います。しかし、--report-intervalによる出力をチラ見しながら、不安定な動きをしていないか見る事は良いことだと思います。

参照系では大きくブレることは少ないですが、更新系が混ざってくると、パラメータ設定次第であるタイミングから性能が劣化、または不安定になったりするためです。最近のモニタリングツールでも大体が1分間隔でしかメトリクス収集は行わないため、秒単位で性能状況を見ることはベンチマークにおいては重要です。

自分の知る限りではPrometheusを利用すれば30秒間隔とかでも取れますが、1秒間隔は流石に現実的ではないと思います。

oltp_read_write.lua

oltp_read_onlyからoltp_read_writeに変わって処理内容もガラッと変わると想像されるかもしれませんが、oltp_read_onlyに更新クエリが追加されるのみとなります。以下の内容でsysbenchを実行し、実行されるクエリをgeneral_logで見てみます。

sysbench \
  /usr/share/sysbench/oltp_read_write.lua \
  --db-driver=mysql \
  --db-ps-mode=disable \
  --rand-type=uniform \
  --tables=1 \
  --report-interval=1 \
  --table-size=2500000 \
  --mysql-db=sbtest \
  --mysql-user=sbtest \
  --mysql-password=sbtest-password \
  --mysql-socket=/tmp/mysql.sock \
  --events=1 \
  --threads=1 \
  run

以下が実行されるクエリです。

BEGIN
SELECT c FROM sbtest1 WHERE id=553
SELECT c FROM sbtest1 WHERE id=988
SELECT c FROM sbtest1 WHERE id=796
SELECT c FROM sbtest1 WHERE id=725
SELECT c FROM sbtest1 WHERE id=737
SELECT c FROM sbtest1 WHERE id=427
SELECT c FROM sbtest1 WHERE id=152
SELECT c FROM sbtest1 WHERE id=701
SELECT c FROM sbtest1 WHERE id=692
SELECT c FROM sbtest1 WHERE id=339
SELECT c FROM sbtest1 WHERE id BETWEEN 293 AND 392
SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN 750 AND 849
SELECT c FROM sbtest1 WHERE id BETWEEN 531 AND 630 ORDER BY c
SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 839 AND 938 ORDER BY c  <-- ここまでoltp_read_onlyと同じ
UPDATE sbtest1 SET k=k+1 WHERE id=565
UPDATE sbtest1 SET c='80365142812-61664067844-27898419451-95821410096-49949035961-25881054423-53211266130-31533669052-07284756755-75182702516' WHERE id=603
DELETE FROM sbtest1 WHERE id=193
INSERT INTO sbtest1 (id, k, c, pad) VALUES (193, 865, '79677907636-77908888445-35876144595-79841914748-92463881441-22980761485-12268580981-91813881393-43479361017-11941355729', '21824524478-57110388043-83645081525-58882100167-39073201445')
COMMIT

sysbenchを利用したベンチマーク結果は色々と公開されていますが、oltpのread only, read writeとだけ書いてあり、オプションの指定がなければこのようなクエリが1トランザクションとして実行されています。

MySQLのバージョン間の差をみたり、パラメータ変更による影響を見たり、というのには参考にして良いと思いますが、「sysbenchで(公開されているH/Wスペックにおいて)あれだけtpsが出てるから自分のサービスでも大丈夫だろう」というのは実に危険だというのがよく分かるかと思います。

昨今のサービスでこんなクエリを1トランザクションとして実行する環境ありますか? 自分が最近はゲーム系のサービスばかりを運用しているからというのを考慮しても(過去も含め)見たことがありません。

それぞれのクエリを個別に実行する

個人的にはoltp_read_only, oltp_read_writeをデフォルトのオプションでスレッド数、実行時間を調整して傾向を見ることは良いと思いますが、collationの違いやバージョン間でクエリの特性が変わっていないかを確認するために個別に実行することも大事だと考えています。

ということで、個別に実行する場合の実行例を以下に書いていきます。なお、先日のdb tech showcase Tokyo 2019で発表した内容と被りますが、あちらはログインしないと資料がダウンロード出来ないので改めて書きます。 https://www.mysql.com/jp/why-mysql/presentations/mysql-ndb-cluster-performance-trends-201909-jp/

実行コマンド(simple select)
sysbench \
  /usr/share/sysbench/oltp_read_only.lua \
  --rand-seed=$RANDOM --rand-type=uniform \
  --db-driver=mysql --db-ps-mode=disable \
  --skip_trx=false --report-interval=1 \
  --tables=16 --table-size=2500000 --mysql-db=sbtest \
  --mysql-user=sbtest --mysql-password=sbtest-password \
  --mysql-host=[Server] --point_selects=1 --range_selects=0 \
  --time=60 --threads=[threads] \
  run 

実行されるクエリ
BEGIN;
SELECT c FROM sbtest{1..16} WHERE id=?;
COMMIT;
実行コマンド(simple range)
sysbench \
  /usr/share/sysbench/oltp_read_only.lua \
  --rand-seed=$RANDOM --rand-type=uniform \
  --db-driver=mysql --db-ps-mode=disable \
  --skip_trx=false --report-interval=1 --tables=16 \
  --table-size=2500000 --mysql-db=sbtest \
  --mysql-user=sbtest --mysql-password=sbtest-password \
  --mysql-host=[Server]  --point_selects=0 \
  --range_selects=1 --range_size=100 --distinct_ranges=0 \
  --simple_ranges=1 --sum_ranges=0 --order_ranges=0 \
  --time=60  --threads=[threads] \
  run

実行されるクエリ
BEGIN;
SELECT c FROM sbtest{1..16} WHERE id BETWEEN ? AND ?+99;
COMMIT;
実行コマンド(distinct range)
sysbench \
  /usr/share/sysbench/oltp_read_only.lua \
  --rand-seed=$RANDOM --rand-type=uniform \
  --db-driver=mysql --db-ps-mode=disable \
  --skip_trx=false --report-interval=1 --tables=16 \
  --table-size=2500000 --mysql-db=sbtest \
  --mysql-user=sbtest --mysql-password=sbtest-password \
  --mysql-host=[Server]  --point_selects=0 \
  --range_selects=1 --range_size=100 --distinct_ranges=1 \
  --simple_ranges=0 --sum_ranges=0 --order_ranges=0 \
  --time=60  --threads=[threads] \
  run

実行されるクエリ
BEGIN;
SELECT DISTINCT c FROM sbtest{1..16} WHERE id BETWEEN ? AND ?+99 ORDER BY c;
COMMIT;
実行コマンド(order range)
sysbench \
  /usr/share/sysbench/oltp_read_only.lua \
  --rand-seed=$RANDOM --rand-type=uniform \
  --db-driver=mysql --db-ps-mode=disable \
  --skip_trx=false --report-interval=1 --tables=16 \
  --table-size=2500000 --mysql-db=sbtest \
  --mysql-user=sbtest --mysql-password=sbtest-password \
  --mysql-host=[Server]  --point_selects=0 \
  --range_selects=1 --range_size=100 --distinct_ranges=0 \
  --simple_ranges=0 --sum_ranges=0 --order_ranges=1 \
  --time=60  --threads=[threads] \
  run

実行されるクエリ
BEGIN;
SELECT c FROM sbtest{1..16} WHERE id BETWEEN ? AND ?+99 ORDER BY c;
COMMIT;
実行コマンド(sum range)
sysbench \
  /usr/share/sysbench/oltp_read_only.lua \
  --rand-seed=$RANDOM --rand-type=uniform \
  --db-driver=mysql --db-ps-mode=disable \
  --skip_trx=false --report-interval=1 --tables=16 \
  --table-size=2500000 --mysql-db=sbtest \
  --mysql-user=sbtest --mysql-password=sbtest-password \
  --mysql-host=[Server]  --point_selects=0 \
  --range_selects=1 --range_size=100 --distinct_ranges=0 \
  --simple_ranges=0 --sum_ranges=1 --order_ranges=0 \
  --time=60  --threads=[threads] \
  run

実行されるコマンド
BEGIN;
SELECT SUM(k) FROM sbtest{1..16} WHERE id BETWEEN ? AND ?+99;
COMMIT;
実行コマンド(index update)
sysbench \
  /usr/share/sysbench/oltp_read_write.lua \
  --rand-seed=$RANDOM --rand-type=uniform \
  --db-driver=mysql --db-ps-mode=disable \
  --skip_trx=false --report-interval=1 --tables=16 \
  --table-size=2500000 --mysql-db=sbtest \
  --mysql-user=sbtest --mysql-password=sbtest-password \
  --mysql-host=[Server]  --point_selects=0 \
  --range_selects=0 --index_updates=1 \
  --non_index_updates=0 --delete_inserts=0 \
  --time=60  --threads=[threads] \
  run

実行されるコマンド
BEGIN;
UPDATE sbtest{1..16} SET k=k+1 WHERE id=?;
COMMIT;
実行コマンド(non index update)
sysbench \
  /usr/share/sysbench/oltp_read_write.lua \
  --rand-seed=$RANDOM --rand-type=uniform \
  --db-driver=mysql --db-ps-mode=disable \
  --skip_trx=false --report-interval=1 --tables=16 \
  --table-size=2500000 --mysql-db=sbtest \
  --mysql-user=sbtest --mysql-password=sbtest-password \
  --mysql-host=[Server]  --point_selects=0 \
  --range_selects=0 --index_updates=0 \
  --non_index_updates=1 --delete_inserts=0 \
  --time=60  --threads=[threads] \
  run

実行されるコマンド
BEGIN;
UPDATE sbtest{1..16} SET c='80072134092-60652554881-69674038014-57275979530-64276708839-93357887853-70407071827-11777439603-12309722760-55583118621' WHERE id=?;
COMMIT;
 ※c=の右辺は実際には毎回ランダム
実行コマンド(delete inserts)
sysbench \
  /usr/share/sysbench/oltp_read_write.lua \
  --rand-seed=$RANDOM --rand-type=uniform \
  --db-driver=mysql --db-ps-mode=disable \
  --skip_trx=false --report-interval=1 --tables=16 \
  --table-size=2500000 --mysql-db=sbtest \
  --mysql-user=sbtest --mysql-password=sbtest-password \
  --mysql-host=[Server]  --point_selects=0 \
  --range_selects=0 --index_updates=0 \
  --non_index_updates=0 --delete_inserts=1 \
  --time=60  --threads=[threads] \
  run

実行されるコマンド
BEGIN;
DELETE FROM sbtest{1..16} WHERE id=?;
INSERT INTO sbtest{1..16} (id, k, c, pad) VALUES (?, 1973805, '16539270078-92733418255-11895901715-49934491663-02991122091-88500890759-95303402801-54193953438-18514372798-94752958410', ‘70787577509-57898806153-06324535718-95357965790-02996016537');
COMMIT;
 ※c, padにinsert する値は毎回ランダム。deleteで指定したidの値とinsertで指定されるidは同一になるため、行が歯抜けになることはありません。

上記で個々のクエリの差を見たり、比較したい内容に応じてオプションを変更して1トランザクションで実行されるクエリを調整すると良いと思います。

今回紹介していないselect_random_pointsやselect_random_rangesのシナリオも環境によっては有用かもしれませんので是非確認してみて下さい。この2つのシナリオはoltp_read_only, oltp_read_writeでは実行出来ないはずです。

是非sysbenchで色々なクエリを実行してみて下さい。

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なんかでご連絡いただけると喜びます。