この記事について
以下の内容をまとめています。
- 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
