MariaDBのパフォーマンス検証-MIXED編
MariaDBのパフォーマンス検証-MIXED編
MariaDBのパフォーマンス検証-SELECT編
MariaDBのパフォーマンス検証-INSERT編
MariaDBのパフォーマンス検証-UPDATE編
に続きMIXED編になります。
前回同様、かなりの計測時間を要するようなので、shellにてすべて計測するようにしました。
#!/bin/bash mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=1000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=10000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=100000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=300000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=500000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=1000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=10000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=100000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=1000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=3000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=5000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=10000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=30000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=300000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=3000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=9000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=15000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=30000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=50000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=500000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=5000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=15000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=25000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=50000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=100000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=1000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=10000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=30000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=50000000 --iterations 3 --user user >> /data/log/mixed.log mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=100000000 --iterations 3 --user user >> /data/log/mixed.log
この実行結果がこちら
cat /data/log/mixed.log Benchmark Running for engine innodb Average number of seconds to run all queries: 0.616 seconds Minimum number of seconds to run all queries: 0.598 seconds Maximum number of seconds to run all queries: 0.641 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: 6.807 seconds Minimum number of seconds to run all queries: 6.700 seconds Maximum number of seconds to run all queries: 6.894 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: 59.929 seconds Minimum number of seconds to run all queries: 56.763 seconds Maximum number of seconds to run all queries: 62.237 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: 187.449 seconds Minimum number of seconds to run all queries: 175.355 seconds Maximum number of seconds to run all queries: 195.355 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: 331.493 seconds Minimum number of seconds to run all queries: 321.547 seconds Maximum number of seconds to run all queries: 349.085 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: 658.997 seconds Minimum number of seconds to run all queries: 649.659 seconds Maximum number of seconds to run all queries: 669.723 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: 1.402 seconds Minimum number of seconds to run all queries: 1.130 seconds Maximum number of seconds to run all queries: 1.577 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: 11.581 seconds Minimum number of seconds to run all queries: 11.177 seconds Maximum number of seconds to run all queries: 12.293 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: 112.985 seconds Minimum number of seconds to run all queries: 111.865 seconds Maximum number of seconds to run all queries: 113.878 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: 353.238 seconds Minimum number of seconds to run all queries: 339.854 seconds Maximum number of seconds to run all queries: 376.578 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: 580.314 seconds Minimum number of seconds to run all queries: 569.166 seconds Maximum number of seconds to run all queries: 599.907 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: 1167.205 seconds Minimum number of seconds to run all queries: 1156.061 seconds Maximum number of seconds to run all queries: 1178.405 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: 2.164 seconds Minimum number of seconds to run all queries: 1.696 seconds Maximum number of seconds to run all queries: 2.741 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: 16.258 seconds Minimum number of seconds to run all queries: 16.061 seconds Maximum number of seconds to run all queries: 16.479 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: 159.104 seconds Minimum number of seconds to run all queries: 156.987 seconds Maximum number of seconds to run all queries: 161.578 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: 485.546 seconds Minimum number of seconds to run all queries: 475.464 seconds Maximum number of seconds to run all queries: 503.472 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: 806.649 seconds Minimum number of seconds to run all queries: 796.700 seconds Maximum number of seconds to run all queries: 825.260 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: 1619.508 seconds Minimum number of seconds to run all queries: 1601.335 seconds Maximum number of seconds to run all queries: 1637.469 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: 2.353 seconds Minimum number of seconds to run all queries: 2.200 seconds Maximum number of seconds to run all queries: 2.602 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: 19.891 seconds Minimum number of seconds to run all queries: 18.279 seconds Maximum number of seconds to run all queries: 20.896 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: 206.430 seconds Minimum number of seconds to run all queries: 200.102 seconds Maximum number of seconds to run all queries: 215.855 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: 588.127 seconds Minimum number of seconds to run all queries: 586.018 seconds Maximum number of seconds to run all queries: 589.195 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: 998.991 seconds Minimum number of seconds to run all queries: 982.054 seconds Maximum number of seconds to run all queries: 1008.038 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: 1986.052 seconds Minimum number of seconds to run all queries: 1975.946 seconds Maximum number of seconds to run all queries: 1991.246 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: 3.725 seconds Minimum number of seconds to run all queries: 3.652 seconds Maximum number of seconds to run all queries: 3.802 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: 30.355 seconds Minimum number of seconds to run all queries: 29.357 seconds Maximum number of seconds to run all queries: 30.868 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: 299.008 seconds Minimum number of seconds to run all queries: 298.025 seconds Maximum number of seconds to run all queries: 300.501 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: 900.876 seconds Minimum number of seconds to run all queries: 887.674 seconds Maximum number of seconds to run all queries: 912.563 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: 1488.621 seconds Minimum number of seconds to run all queries: 1473.729 seconds Maximum number of seconds to run all queries: 1501.386 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: 2993.594 seconds Minimum number of seconds to run all queries: 2974.164 seconds Maximum number of seconds to run all queries: 3030.517 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 | 0.616 | 6.807 | 59.929 | 187.449 | 331.493 | 658.997 |
| 10 | 1.402 | 11.581 | 112.985 | 353.238 | 580.314 | 1167.205 |
| 30 | 2.164 | 16.258 | 159.104 | 485.546 | 806.649 | 1619.508 |
| 50 | 2.353 | 19.891 | 206.43 | 588.127 | 998.991 | 1986.052 |
| 100 | 3.725 | 30.355 | 299.008 | 900.876 | 1488.621 | 2993.594 |
| クライアント数/クエリー | 1,000 | 10,000 | 100,000 | 300,000 | 500,000 | 1,000,000 |
|---|---|---|---|---|---|---|
| 1 | 0.616 | 0.6807 | 0.59929 | 0.62483 | 0.662986 | 0.658997 |
| 10 | 1.402 | 1.1581 | 1.12985 | 1.17746 | 1.160628 | 1.167205 |
| 30 | 2.164 | 1.6258 | 1.59104 | 1.618486667 | 1.613298 | 1.619508 |
| 50 | 2.353 | 1.9891 | 2.0643 | 1.960423333 | 1.997982 | 1.986052 |
| 100 | 3.725 | 3.0355 | 2.99008 | 3.00292 | 2.977242 | 2.993594 |
MIXEDは挿入とテーブルスキャンを半々行うのだが、実際の運用ではそうはいかないので運用の際の試算にはあくまで理論値として考えていただきたいです。
1クエリーあたりの所要時間(ms)を見ると、1,000クエリー以外はほぼ同じ線形となりました。
1,000クエリーの場合のみ10クライアント以上になると24%以上もの実行速度に差が見られます。
通常考えられるのは、クエリー数に比例して1クエリーあたりの所要時間は多くなるのが一般的なはずなのですが、MariaDBの場合は10,000クエリーを超えるとたとえクライアント数が多くなったとしても、ほぼ変化がないことが伺えました。
MariaDB自体のオプティマイザ/エクゼキュータが従来のMySQLと比べて、大幅に改善されている為だと思われます。
大量のトラフィックを抱えているサービスの運用を考えている方はMariaDBを選定の対象に入れてもいいかもしれません。
MariaDB＋SSDの検証についてはまた今度。
tsunokawaのはてなダイアリー
計測結果の1クエリーあたりの所要時間・・・
クエリーが多いほど所要時間が小さいのって良いの？
普通は、クエリーが多いほど時間かかりそうだけど。
>クエリーが多いほど所要時間が小さいのって良いの？
msの世界なので、体感はあまりわかりませんが、結果的に多くのトラフィックやユーザを抱える場合には特に問題は起きないかと思われますので、気にするほどのことではないかもしれません。
>普通は、クエリーが多いほど時間かかりそうだけど。
ぼうずさんがおっしゃる通り、通常はクエリー数が多いほど処理時間は大きくなるはずですが、1000クエリーの場合でinsert,update,mixedにおいてはそうなりませんでした。
計測環境がHDDなので、HDD特有のシーク遅延によるものかと考えましたが、現在SSDの検証も進めており、同様な結果になりそうです。
MariaDBのバージョンが10.0系なので、beta版の10.1系では違った計測になるかもしれません。
https://mariadb.org/