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