MariaDBのパフォーマンス検証-UPDATE編
MariaDBのパフォーマンス検証-UPDATE編
前回(MariaDBのパフォーマンス検証-INSERT編)に続きUPDATE編になります。
mysqlslapに変更はないですが、ベンチマークの測定手法を変更しました。
#!/bin/bash mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=1000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=10000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=100000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=300000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=500000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=1000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=10000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=100000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=1000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=3000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=5000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=10000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=30000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=300000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=3000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=9000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=15000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=30000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=50000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=500000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=5000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=15000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=25000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=50000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=100000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=1000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=10000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=30000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=50000000 --iterations 3 --user user >> /data/log/log.log mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --number-of-queries=100000000 --iterations 3 --user user >> /data/log/log.log
その結果がこちら
cat /data/log/log.log Benchmark Running for engine innodb Average number of seconds to run all queries: 1.007 seconds Minimum number of seconds to run all queries: 0.864 seconds Maximum number of seconds to run all queries: 1.234 seconds Number of clients running queries: 1 Average number of queries per client: 1000 Benchmark Running for engine innodb Average number of seconds to run all queries: 10.381 seconds Minimum number of seconds to run all queries: 9.783 seconds Maximum number of seconds to run all queries: 11.248 seconds Number of clients running queries: 1 Average number of queries per client: 10000 Benchmark Running for engine innodb Average number of seconds to run all queries: 96.485 seconds Minimum number of seconds to run all queries: 94.000 seconds Maximum number of seconds to run all queries: 99.490 seconds Number of clients running queries: 1 Average number of queries per client: 100000 Benchmark Running for engine innodb Average number of seconds to run all queries: 293.174 seconds Minimum number of seconds to run all queries: 253.817 seconds Maximum number of seconds to run all queries: 324.325 seconds Number of clients running queries: 1 Average number of queries per client: 300000 Benchmark Running for engine innodb Average number of seconds to run all queries: 577.571 seconds Minimum number of seconds to run all queries: 573.075 seconds Maximum number of seconds to run all queries: 581.371 seconds Number of clients running queries: 1 Average number of queries per client: 500000 Benchmark Running for engine innodb Average number of seconds to run all queries: 1164.927 seconds Minimum number of seconds to run all queries: 1150.228 seconds Maximum number of seconds to run all queries: 1173.792 seconds Number of clients running queries: 1 Average number of queries per client: 1000000 Benchmark Running for engine innodb Average number of seconds to run all queries: 2.362 seconds Minimum number of seconds to run all queries: 2.173 seconds Maximum number of seconds to run all queries: 2.496 seconds Number of clients running queries: 10 Average number of queries per client: 1000 Benchmark Running for engine innodb Average number of seconds to run all queries: 21.458 seconds Minimum number of seconds to run all queries: 20.669 seconds Maximum number of seconds to run all queries: 22.162 seconds Number of clients running queries: 10 Average number of queries per client: 10000 Benchmark Running for engine innodb Average number of seconds to run all queries: 226.310 seconds Minimum number of seconds to run all queries: 213.815 seconds Maximum number of seconds to run all queries: 246.493 seconds Number of clients running queries: 10 Average number of queries per client: 100000 Benchmark Running for engine innodb Average number of seconds to run all queries: 655.892 seconds Minimum number of seconds to run all queries: 641.320 seconds Maximum number of seconds to run all queries: 675.910 seconds Number of clients running queries: 10 Average number of queries per client: 300000 Benchmark Running for engine innodb Average number of seconds to run all queries: 1098.739 seconds Minimum number of seconds to run all queries: 1085.015 seconds Maximum number of seconds to run all queries: 1113.446 seconds Number of clients running queries: 10 Average number of queries per client: 500000 Benchmark Running for engine innodb Average number of seconds to run all queries: 2196.359 seconds Minimum number of seconds to run all queries: 2193.769 seconds Maximum number of seconds to run all queries: 2200.687 seconds Number of clients running queries: 10 Average number of queries per client: 1000000 Benchmark Running for engine innodb Average number of seconds to run all queries: 3.071 seconds Minimum number of seconds to run all queries: 2.786 seconds Maximum number of seconds to run all queries: 3.622 seconds Number of clients running queries: 30 Average number of queries per client: 1000 Benchmark Running for engine innodb Average number of seconds to run all queries: 32.148 seconds Minimum number of seconds to run all queries: 28.872 seconds Maximum number of seconds to run all queries: 36.240 seconds Number of clients running queries: 30 Average number of queries per client: 10000 Benchmark Running for engine innodb Average number of seconds to run all queries: 285.165 seconds Minimum number of seconds to run all queries: 277.731 seconds Maximum number of seconds to run all queries: 299.301 seconds Number of clients running queries: 30 Average number of queries per client: 100000 Benchmark Running for engine innodb Average number of seconds to run all queries: 848.665 seconds Minimum number of seconds to run all queries: 827.814 seconds Maximum number of seconds to run all queries: 886.266 seconds Number of clients running queries: 30 Average number of queries per client: 300000 Benchmark Running for engine innodb Average number of seconds to run all queries: 1421.268 seconds Minimum number of seconds to run all queries: 1399.533 seconds Maximum number of seconds to run all queries: 1432.320 seconds Number of clients running queries: 30 Average number of queries per client: 500000 Benchmark Running for engine innodb Average number of seconds to run all queries: 2844.003 seconds Minimum number of seconds to run all queries: 2820.621 seconds Maximum number of seconds to run all queries: 2860.186 seconds Number of clients running queries: 30 Average number of queries per client: 1000000 Benchmark Running for engine innodb Average number of seconds to run all queries: 3.788 seconds Minimum number of seconds to run all queries: 3.635 seconds Maximum number of seconds to run all queries: 3.929 seconds Number of clients running queries: 50 Average number of queries per client: 1000 Benchmark Running for engine innodb Average number of seconds to run all queries: 34.003 seconds Minimum number of seconds to run all queries: 32.136 seconds Maximum number of seconds to run all queries: 35.629 seconds Number of clients running queries: 50 Average number of queries per client: 10000 Benchmark Running for engine innodb Average number of seconds to run all queries: 330.275 seconds Minimum number of seconds to run all queries: 328.781 seconds Maximum number of seconds to run all queries: 332.447 seconds Number of clients running queries: 50 Average number of queries per client: 100000 Benchmark Running for engine innodb Average number of seconds to run all queries: 994.923 seconds Minimum number of seconds to run all queries: 983.108 seconds Maximum number of seconds to run all queries: 1015.619 seconds Number of clients running queries: 50 Average number of queries per client: 300000 Benchmark Running for engine innodb Average number of seconds to run all queries: 1674.608 seconds Minimum number of seconds to run all queries: 1671.734 seconds Maximum number of seconds to run all queries: 1676.100 seconds Number of clients running queries: 50 Average number of queries per client: 500000 Benchmark Running for engine innodb Average number of seconds to run all queries: 3321.232 seconds Minimum number of seconds to run all queries: 3311.626 seconds Maximum number of seconds to run all queries: 3337.490 seconds Number of clients running queries: 50 Average number of queries per client: 1000000 Benchmark Running for engine innodb Average number of seconds to run all queries: 5.943 seconds Minimum number of seconds to run all queries: 5.571 seconds Maximum number of seconds to run all queries: 6.469 seconds Number of clients running queries: 100 Average number of queries per client: 1000 Benchmark Running for engine innodb Average number of seconds to run all queries: 49.199 seconds Minimum number of seconds to run all queries: 48.467 seconds Maximum number of seconds to run all queries: 49.644 seconds Number of clients running queries: 100 Average number of queries per client: 10000 Benchmark Running for engine innodb Average number of seconds to run all queries: 480.660 seconds Minimum number of seconds to run all queries: 474.839 seconds Maximum number of seconds to run all queries: 491.531 seconds Number of clients running queries: 100 Average number of queries per client: 100000 Benchmark Running for engine innodb Average number of seconds to run all queries: 1419.254 seconds Minimum number of seconds to run all queries: 1407.660 seconds Maximum number of seconds to run all queries: 1439.166 seconds Number of clients running queries: 100 Average number of queries per client: 300000 Benchmark Running for engine innodb Average number of seconds to run all queries: 2377.316 seconds Minimum number of seconds to run all queries: 2370.664 seconds Maximum number of seconds to run all queries: 2381.409 seconds Number of clients running queries: 100 Average number of queries per client: 500000 Benchmark Running for engine innodb Average number of seconds to run all queries: 4724.157 seconds Minimum number of seconds to run all queries: 4713.680 seconds Maximum number of seconds to run all queries: 4741.827 seconds Number of clients running queries: 100 Average number of queries per client: 1000000
[計測結果]
| クライアント数/クエリー | 1,000 | 10,000 | 100,000 | 300,000 | 500,000 | 1,000,000 |
|---|---|---|---|---|---|---|
| 1 | 1.007 | 10.381 | 96.485 | 293.174 | 577.571 | 1164.927 |
| 10 | 2.362 | 21.458 | 226.31 | 655.892 | 1098.739 | 2196.359 |
| 30 | 3.071 | 32.148 | 274.788 | 848.665 | 1421.268 | 2844.003 |
| 50 | 3.788 | 34.003 | 330.275 | 994.923 | 1674.6 | 3321.232 |
| 100 | 5.943 | 49.199 | 480.66 | 1419.254 | 2377.316 | 4724.157 |
| クライアント数/クエリー | 1,000 | 10,000 | 100,000 | 300,000 | 500,000 | 1,000,000 |
|---|---|---|---|---|---|---|
| 1 | 1.00700 | 1.03810 | 0.96485 | 0.97725 | 1.15514 | 1.16493 |
| 10 | 2.36200 | 2.14580 | 2.26310 | 2.18631 | 2.19748 | 2.19636 |
| 30 | 3.07100 | 3.21480 | 2.74788 | 2.82888 | 2.84254 | 2.84400 |
| 50 | 3.78800 | 3.40030 | 3.30275 | 3.31641 | 3.34920 | 3.32123 |
| 100 | 5.90169 | 4.73933 | 4.98171 | 4.84000 | 4.11606 | 4.05532 |
1クエリーあたりの所要時間(ms)を見ると、1,000クエリーと10,000クエリーとの差ではクライアント数(平行処理数)10～30までほぼ同じような線形だが、
50～100クライアントでは顕著に差が出た。クエリー数が多いほど処理時間が少なくなる傾向がある。
100クライアントで見ると約24％ほど処理時間が向上している。
また、100,000クエリー以上を見ると50クライアントまではほぼ同じ線形ではあるが、100クライアントでは500,000クエリーから大きく差が出た。
300,000クエリーと1,000,000クエリーとでは約19%も多いクエリー数のほうが処理時間が早い。
負荷が高くなればなるほど、性能が良くなる傾向だといえる。
select,insert,updateをあわせたMIX編に関してはまた今度。
tsunokawaのはてなダイアリー