Skip to main content
MariaDB

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の検証についてはまた今度。






2 注釈 “MariaDBのパフォーマンス検証-MIXED編

  1. 計測結果の1クエリーあたりの所要時間・・・
    クエリーが多いほど所要時間が小さいのって良いの?
    普通は、クエリーが多いほど時間かかりそうだけど。

    1. >クエリーが多いほど所要時間が小さいのって良いの?
      msの世界なので、体感はあまりわかりませんが、結果的に多くのトラフィックやユーザを抱える場合には特に問題は起きないかと思われますので、気にするほどのことではないかもしれません。

      >普通は、クエリーが多いほど時間かかりそうだけど。
      ぼうずさんがおっしゃる通り、通常はクエリー数が多いほど処理時間は大きくなるはずですが、1000クエリーの場合でinsert,update,mixedにおいてはそうなりませんでした。
      計測環境がHDDなので、HDD特有のシーク遅延によるものかと考えましたが、現在SSDの検証も進めており、同様な結果になりそうです。
      MariaDBのバージョンが10.0系なので、beta版の10.1系では違った計測になるかもしれません。
      https://mariadb.org/

コメントを閉じる

Translate »