MySQL 9.7.0-erではPGO(Profile Guided Optimization)が有効になっているらしいので、それまでのバイナリよりもちょっと性能が良さそう。あと、これはビルドの中で有効にするので手でビルドしたものよりもちょっと性能が良いはず。
というわけで雑ベンチ。
$ cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="8.10"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="8.10"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Oracle Linux Server 8.10"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:8:10:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://github.com/oracle/oracle-linux"
ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8"
ORACLE_BUGZILLA_PRODUCT_VERSION=8.10
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=8.10
$ nproc
4
$ free -h
total used free shared buff/cache available
Mem: 15Gi 2.2Gi 376Mi 682Mi 12Gi 12Gi
Swap: 4.0Gi 318Mi 3.7Gi
MySQL 9.6.0のビルド済みバイナリ。 innodb_dedicated_server に設定は任せてしまうことにした。
$ wget https://dev.mysql.com/get/Downloads/MySQL-9.6/mysql-9.6.0-linux-glibc2.28-x86_64.tar.xz
$ tar xf mysql-9.6.0-linux-glibc2.28-x86_64.tar.xz
$ cd mysql-9.6.0-linux-glibc2.28-x86_64/
$ bin/mysqld --no-defaults --initialize-insecure --lower_case_table_names=1
$ bin/mysqld --no-defaults --lower_case_table_names=1 --innodb-dedicated-server=1 --log-error-verbosity=3 --innodb-monitor-enable=all --daemonize
ベンチマーククライアント側は雑にサーバー側の倍のスレッドになるように。100万行10テーブルでサーバ側のサイズは2.4GB。
$ nproc
8
$ free -h
total used free shared buff/cache available
Mem: 3.4Gi 674Mi 798Mi 2.0Mi 2.2Gi 2.7Gi
Swap: 3.4Gi 137Mi 3.2Gi
$ sudo dnf install --enablerepo="*" -y sysbench
$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=60 --report-interval=1 prepare
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 1000000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 1000000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 1000000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 1000000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 1000000 records into 'sbtest6'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 1000000 records into 'sbtest7'
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 1000000 records into 'sbtest8'
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 1000000 records into 'sbtest9'
Creating a secondary index on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 1000000 records into 'sbtest10'
Creating a secondary index on 'sbtest10'...
1スレッド9.6
$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 1
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 1 tps: 127.89 qps: 2572.72 (r/w/o: 1804.40/511.55/256.77) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1 tps: 133.06 qps: 2664.15 (r/w/o: 1862.81/535.23/266.12) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1 tps: 123.00 qps: 2458.00 (r/w/o: 1722.00/490.00/246.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1 tps: 128.00 qps: 2562.96 (r/w/o: 1791.97/514.99/256.00) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 1 tps: 137.00 qps: 2736.04 (r/w/o: 1918.03/544.01/274.00) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1 tps: 142.00 qps: 2840.00 (r/w/o: 1988.00/568.00/284.00) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 1 tps: 144.00 qps: 2880.03 (r/w/o: 2016.02/576.01/288.00) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 1 tps: 150.00 qps: 2990.96 (r/w/o: 2090.97/599.99/300.00) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 1 tps: 150.00 qps: 3013.02 (r/w/o: 2109.01/604.00/300.00) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 1 tps: 153.00 qps: 3058.97 (r/w/o: 2141.98/610.99/306.00) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
..
[ 298s ] thds: 1 tps: 199.00 qps: 3976.96 (r/w/o: 2782.97/795.99/398.00) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 1 tps: 197.00 qps: 3933.99 (r/w/o: 2751.99/788.00/394.00) lat (ms,95%): 5.77 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 1 tps: 199.00 qps: 3991.97 (r/w/o: 2797.98/795.99/398.00) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 784952
write: 224272
other: 112136
total: 1121360
transactions: 56068 (186.89 per sec.)
queries: 1121360 (3737.81 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0038s
total number of events: 56068
Latency (ms):
min: 4.42
avg: 5.35
max: 36.29
95th percentile: 6.79
sum: 299950.61
Threads fairness:
events (avg/stddev): 56068.0000/0.00
execution time (avg/stddev): 299.9506/0.00
起動しているmysqldを公式ビルドの9.6から9.7に取り換え。
$ pkill mysqld
$ bin/mysqld --no-defaults --initialize-insecure --lower_case_table_names=1
$ bin/mysqld --no-defaults --lower_case_table_names=1 --innodb-dedicated-server=1 --log-error-verbosity=3 --innodb-monitor-enable=all --daemonize
$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=60 --report-interval=1 prepare
$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 1
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 1 tps: 136.87 qps: 2752.47 (r/w/o: 1930.22/547.50/274.75) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1 tps: 129.06 qps: 2581.26 (r/w/o: 1806.88/516.25/258.13) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1 tps: 126.00 qps: 2523.99 (r/w/o: 1764.00/508.00/252.00) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1 tps: 139.00 qps: 2775.96 (r/w/o: 1945.97/551.99/278.00) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 1 tps: 148.00 qps: 2964.02 (r/w/o: 2072.01/596.00/296.00) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1 tps: 154.00 qps: 3076.03 (r/w/o: 2156.02/612.01/308.00) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 1 tps: 159.00 qps: 3180.02 (r/w/o: 2226.01/636.00/318.00) lat (ms,95%): 7.84 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 1 tps: 156.00 qps: 3123.00 (r/w/o: 2184.00/627.00/312.00) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 1 tps: 161.00 qps: 3220.98 (r/w/o: 2253.98/645.00/322.00) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 1 tps: 163.00 qps: 3256.00 (r/w/o: 2282.00/648.00/326.00) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
..
[ 295s ] thds: 1 tps: 214.00 qps: 4280.01 (r/w/o: 2996.01/856.00/428.00) lat (ms,95%): 5.09 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 1 tps: 212.99 qps: 4259.88 (r/w/o: 2981.92/851.98/425.99) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 1 tps: 213.01 qps: 4260.10 (r/w/o: 2982.07/852.02/426.01) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 1 tps: 212.00 qps: 4239.96 (r/w/o: 2967.97/847.99/424.00) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 1 tps: 215.00 qps: 4300.02 (r/w/o: 3010.01/860.00/430.00) lat (ms,95%): 5.18 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 1 tps: 210.00 qps: 4200.05 (r/w/o: 2940.04/840.01/420.01) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 848134
write: 242324
other: 121162
total: 1211620
transactions: 60581 (201.93 per sec.)
queries: 1211620 (4038.70 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0021s
total number of events: 60581
Latency (ms):
min: 3.99
avg: 4.95
max: 41.05
95th percentile: 6.55
sum: 299950.65
Threads fairness:
events (avg/stddev): 60581.0000/0.00
execution time (avg/stddev): 299.9506/0.00
おおちょっと良さそう。抜粋。
| 9.6.0 | 9.7.0-er | |
|---|---|---|
| min | 4.42 | 3.99 |
| avg | 5.355 | 4.95 |
| max | 36.29 | 41.05 |
| 95%ile | 6.79 | 6.55 |
| QPS | 3737.81 | 4038.70 |
95%ileで3%くらいレイテンシーが減ってる。sysbenchでプロファイルして当ててる可能性はあるのでsysbenchでのスコアが余計に良く出る可能性はあるので、あとで tpcc-mysql でも当ててみたい（が、1行引いて云々…なワークロードだったら全体的にレイテンシ下がるかな？）
スレッド数8(MySQL側のスレッド数を超えさせる）でもやってみる。daemon起動し直すのが面倒な関係で9.7.0-erが先。
$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 cleanup
$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 prepare
$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 --threads=8 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 8
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 8 tps: 557.63 qps: 11257.47 (r/w/o: 7892.72/2241.50/1123.25) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 8 tps: 728.16 qps: 14585.28 (r/w/o: 10213.30/2915.66/1456.33) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 8 tps: 816.00 qps: 16307.02 (r/w/o: 11412.01/3263.00/1632.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 8 tps: 817.99 qps: 16397.83 (r/w/o: 11470.88/3290.97/1635.98) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 8 tps: 717.99 qps: 14305.89 (r/w/o: 10021.92/2847.98/1435.99) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 8 tps: 869.01 qps: 17422.16 (r/w/o: 12192.11/3492.03/1738.02) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 8 tps: 914.97 qps: 18271.37 (r/w/o: 12790.56/3650.87/1829.94) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 8 tps: 917.03 qps: 18337.55 (r/w/o: 12835.38/3668.11/1834.05) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 8 tps: 925.00 qps: 18490.97 (r/w/o: 12939.98/3700.99/1850.00) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 8 tps: 921.00 qps: 18443.03 (r/w/o: 12915.02/3686.01/1842.00) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
..
[ 295s ] thds: 8 tps: 1018.00 qps: 20369.91 (r/w/o: 14260.94/4071.98/2036.99) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 8 tps: 1004.99 qps: 20091.88 (r/w/o: 14064.91/4016.98/2009.99) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 8 tps: 987.99 qps: 19727.73 (r/w/o: 13806.81/3946.95/1973.97) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 8 tps: 1001.00 qps: 20052.10 (r/w/o: 14036.07/4012.02/2004.01) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 8 tps: 1010.01 qps: 20216.15 (r/w/o: 14156.10/4040.03/2020.01) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 8 tps: 1006.00 qps: 20080.93 (r/w/o: 14049.95/4018.99/2011.99) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 4088658
write: 1168188
other: 584094
total: 5840940
transactions: 292047 (973.46 per sec.)
queries: 5840940 (19469.20 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0089s
total number of events: 292047
Latency (ms):
min: 4.66
avg: 8.22
max: 117.24
95th percentile: 12.08
sum: 2399733.89
Threads fairness:
events (avg/stddev): 36505.8750/71.89
execution time (avg/stddev): 299.9667/0.00
同じく9.6.0にバイナリを取り換えて。
$ pkill mysqld
$ cd ../mysql-9.6.0-linux-glibc2.28-x86_64
$ bin/mysqld --no-defaults --lower_case_table_names=1 --innodb-dedicated-server=1 --log-error-verbosity=3 --innodb-monitor-enable=all --daemonize
$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 --threads=8 cleanup
$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 --threads=8 prepare
$ sysbench --mysql-host=10.0.0.177 --mysql-user=sbtest oltp_read_write --table_size=1000000 --tables=10 --time=300 --report-interval=1 --threads=8 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 8
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 8 tps: 534.59 qps: 10793.69 (r/w/o: 7567.17/2149.34/1077.17) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 8 tps: 609.20 qps: 12222.01 (r/w/o: 8555.81/2447.80/1218.40) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 8 tps: 700.99 qps: 13986.84 (r/w/o: 9786.89/2797.97/1401.98) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 8 tps: 724.98 qps: 14506.62 (r/w/o: 10163.73/2892.92/1449.96) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 8 tps: 677.02 qps: 13543.40 (r/w/o: 9476.28/2713.08/1354.04) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 8 tps: 755.98 qps: 15115.67 (r/w/o: 10584.77/3018.93/1511.97) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 8 tps: 791.99 qps: 15830.89 (r/w/o: 11075.92/3170.98/1583.99) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 8 tps: 802.01 qps: 16039.30 (r/w/o: 11231.21/3204.06/1604.03) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 8 tps: 809.01 qps: 16229.17 (r/w/o: 11351.12/3260.04/1618.02) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 8 tps: 821.01 qps: 16401.14 (r/w/o: 11487.10/3272.03/1642.01) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00
..
[ 295s ] thds: 8 tps: 883.02 qps: 17675.43 (r/w/o: 12373.30/3534.09/1768.04) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 8 tps: 884.00 qps: 17699.00 (r/w/o: 12389.00/3541.00/1769.00) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 8 tps: 873.99 qps: 17499.79 (r/w/o: 12258.85/3492.96/1747.98) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 8 tps: 883.99 qps: 17660.87 (r/w/o: 12351.91/3540.97/1767.99) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 8 tps: 894.99 qps: 17912.73 (r/w/o: 12538.81/3584.95/1788.97) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 8 tps: 794.01 qps: 15863.25 (r/w/o: 11110.18/3164.05/1589.03) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 3589908
write: 1025688
other: 512844
total: 5128440
transactions: 256422 (854.72 per sec.)
queries: 5128440 (17094.31 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0082s
total number of events: 256422
Latency (ms):
min: 5.09
avg: 9.36
max: 132.09
95th percentile: 13.95
sum: 2399787.50
Threads fairness:
events (avg/stddev): 32052.7500/66.58
execution time (avg/stddev): 299.9734/0.00
スレッド数が上がっても同傾向だった。
| 9.6.0 | 9.7.0-er | |
|---|---|---|
| min | 5.09 | 4.66 |
| avg | 9.36 | 8.22 |
| max | 132.09 | 117.24 |
| 95%ile | 13.95 | 12.08 |
| QPS | 17094.31 | 19469.20 |
パッと見良さそう。
他には
- 今はバッファプールに全てデータが収まっているはずなので、収まりきらないサイズでの比較（これはI/Oが支配的になるのでどっちでも大して変わらなくなるんじゃないかなあ）
- sysbench oltp*以外のベンチマークスイート（1行引いて1行更新系のやつだったら同じ傾向を示すような気がする、OLAP系はもともと完走できる気がしないしどうしようかな…）
- もうちょっと真面目にチューニングして比較
- ビルドオプション揃えてPGOが効いてないだけのソースビルドとPGOが効いてるビルド済みバイナリの比較
あたりが思いつくので誰か試してください。
0 件のコメント :
コメントを投稿