MariaDBのパフォーマンス検証-SELECT編
MariaDBのパフォーマンス検証-SELECT編
前回MariaDBを入れたのですが今後やりたい検証は、
・SSDとHDDでどれくらいのパフォーマンス向上が見られるか
・MySQLとMariaDBでどの程度のパフォーマンスの差があるのか
今回はその足がかりとして、MariaDBのベンチマークをとります。
まずはマシンスペックの公開
- CPU:Intel(R) Core(TM) i7-2600K CPU @ 3.40GHz(VM上なので4コア割り当て)
- Memory : 8GB
- HDD:ST3000DM001(3TB)
server.confなどのMariaDBの設定はインストール時のデフォルトのままにあえてしてあります。
MariaDBの検証開始(SELECT編)
[検索対象 10万レコード]
・1クライアント 1,000回の検索
mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=1000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 0.061 seconds Minimum number of seconds to run all queries: 0.061 seconds Maximum number of seconds to run all queries: 0.061 seconds Number of clients running queries: 1 Average number of queries per client: 1000
・1クライアント 10,000回の検索
mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=10000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 0.641 seconds Minimum number of seconds to run all queries: 0.641 seconds Maximum number of seconds to run all queries: 0.641 seconds Number of clients running queries: 1 Average number of queries per client: 10000
・1クライアント 100,000回の検索
mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=100000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 6.127 seconds Minimum number of seconds to run all queries: 6.127 seconds Maximum number of seconds to run all queries: 6.127 seconds Number of clients running queries: 1 Average number of queries per client: 100000
・1クライアント 300,000回の検索
mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=300000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 18.432 seconds Minimum number of seconds to run all queries: 18.432 seconds Maximum number of seconds to run all queries: 18.432 seconds Number of clients running queries: 1 Average number of queries per client: 300000
・1クライアント 500,000回の検索
mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=500000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 30.204 seconds Minimum number of seconds to run all queries: 30.204 seconds Maximum number of seconds to run all queries: 30.204 seconds Number of clients running queries: 1 Average number of queries per client: 500000
・1クライアント 1,000,000回の検索
mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=1000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 60.417 seconds Minimum number of seconds to run all queries: 60.417 seconds Maximum number of seconds to run all queries: 60.417 seconds Number of clients running queries: 1 Average number of queries per client: 1000000
・10クライアント 1,000回の検索
mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=10000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 0.213 seconds Minimum number of seconds to run all queries: 0.213 seconds Maximum number of seconds to run all queries: 0.213 seconds Number of clients running queries: 10 Average number of queries per client: 1000
・10クライアント 10,000回の検索
mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=100000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 1.740 seconds Minimum number of seconds to run all queries: 1.740 seconds Maximum number of seconds to run all queries: 1.740 seconds Number of clients running queries: 10 Average number of queries per client: 10000
・10クライアント 100,000回の検索
mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=1000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 16.464 seconds Minimum number of seconds to run all queries: 16.464 seconds Maximum number of seconds to run all queries: 16.464 seconds Number of clients running queries: 10 Average number of queries per client: 100000
・10クライアント 300,000回の検索
mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=3000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 48.420 seconds Minimum number of seconds to run all queries: 48.420 seconds Maximum number of seconds to run all queries: 48.420 seconds Number of clients running queries: 10 Average number of queries per client: 300000
・10クライアント 500,000回の検索
mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=5000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 80.726 seconds Minimum number of seconds to run all queries: 80.726 seconds Maximum number of seconds to run all queries: 80.726 seconds Number of clients running queries: 10 Average number of queries per client: 500000
・10クライアント 1,000,000回の検索
mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=10000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 165.407 seconds Minimum number of seconds to run all queries: 165.407 seconds Maximum number of seconds to run all queries: 165.407 seconds Number of clients running queries: 10 Average number of queries per client: 1000000
・30クライアント 1,000回の検索
mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=30000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 0.581 seconds Minimum number of seconds to run all queries: 0.581 seconds Maximum number of seconds to run all queries: 0.581 seconds Number of clients running queries: 30 Average number of queries per client: 1000
・30クライアント 10,000回の検索
mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=300000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 5.020 seconds Minimum number of seconds to run all queries: 5.020 seconds Maximum number of seconds to run all queries: 5.020 seconds Number of clients running queries: 30 Average number of queries per client: 10000
・30クライアント 100,000回の検索
mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=3000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 50.573 seconds Minimum number of seconds to run all queries: 50.573 seconds Maximum number of seconds to run all queries: 50.573 seconds Number of clients running queries: 30 Average number of queries per client: 100000
・30クライアント 300,000回の検索
mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=9000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 147.458 seconds Minimum number of seconds to run all queries: 147.458 seconds Maximum number of seconds to run all queries: 147.458 seconds Number of clients running queries: 30 Average number of queries per client: 300000
・30クライアント 500,000回の検索
mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=15000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 252.477 seconds Minimum number of seconds to run all queries: 252.477 seconds Maximum number of seconds to run all queries: 252.477 seconds Number of clients running queries: 30 Average number of queries per client: 500000
・30クライアント 1,000,000回の検索
mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=30000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 507.953 seconds Minimum number of seconds to run all queries: 507.953 seconds Maximum number of seconds to run all queries: 507.953 seconds Number of clients running queries: 30 Average number of queries per client: 1000000
・50クライアント 1,000回の検索
mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=50000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 0.923 seconds Minimum number of seconds to run all queries: 0.923 seconds Maximum number of seconds to run all queries: 0.923 seconds Number of clients running queries: 50 Average number of queries per client: 1000
・50クライアント 10,000回の検索
mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=500000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 9.287 seconds Minimum number of seconds to run all queries: 9.287 seconds Maximum number of seconds to run all queries: 9.287 seconds Number of clients running queries: 50 Average number of queries per client: 10000
・50クライアント 100,000回の検索
mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=5000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 83.410 seconds Minimum number of seconds to run all queries: 83.410 seconds Maximum number of seconds to run all queries: 83.410 seconds Number of clients running queries: 50 Average number of queries per client: 100000
・50クライアント 300,000回の検索
mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=15000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 251.291 seconds Minimum number of seconds to run all queries: 251.291 seconds Maximum number of seconds to run all queries: 251.291 seconds Number of clients running queries: 50 Average number of queries per client: 300000
・50クライアント 500,000回の検索
mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=25000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 424.029 seconds Minimum number of seconds to run all queries: 424.029 seconds Maximum number of seconds to run all queries: 424.029 seconds Number of clients running queries: 50 Average number of queries per client: 500000
・50クライアント 1,000,000回の検索
mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=50000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 851.824 seconds Minimum number of seconds to run all queries: 851.824 seconds Maximum number of seconds to run all queries: 851.824 seconds Number of clients running queries: 50 Average number of queries per client: 1000000
・100クライアント 1,000回の検索
mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=100000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 1.715 seconds Minimum number of seconds to run all queries: 1.715 seconds Maximum number of seconds to run all queries: 1.715 seconds Number of clients running queries: 100 Average number of queries per client: 1000
・100クライアント 10,000回の検索
mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=1000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 16.973 seconds Minimum number of seconds to run all queries: 16.973 seconds Maximum number of seconds to run all queries: 16.973 seconds Number of clients running queries: 100 Average number of queries per client: 10000
・100クライアント 100,000回の検索
mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=10000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 168.628 seconds Minimum number of seconds to run all queries: 168.628 seconds Maximum number of seconds to run all queries: 168.628 seconds Number of clients running queries: 100 Average number of queries per client: 100000
・100クライアント 300,000回の検索
mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=30000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 500.999 seconds Minimum number of seconds to run all queries: 500.999 seconds Maximum number of seconds to run all queries: 500.999 seconds Number of clients running queries: 100 Average number of queries per client: 300000
・100クライアント 500,000回の検索
mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=50000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 831.242 seconds Minimum number of seconds to run all queries: 831.242 seconds Maximum number of seconds to run all queries: 831.242 seconds Number of clients running queries: 100 Average number of queries per client: 500000
・100クライアント 1,000,000回の検索
mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=100000 --number-of-queries=100000000 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 1733.055 seconds Minimum number of seconds to run all queries: 1733.055 seconds Maximum number of seconds to run all queries: 1733.055 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.061 | 0.641 | 6.127 | 18.432 | 30.204 | 60.417 |
| 10 | 0.213 | 1.740 | 16.464 | 48.420 | 80.726 | 165.407 |
| 30 | 0.581 | 5.020 | 50.573 | 147.458 | 252.477 | 507.953 |
| 50 | 0.923 | 9.287 | 83.410 | 251.291 | 424.029 | 851.824 |
| 100 | 1.715 | 16.973 | 168.628 | 500.999 | 831.242 | 1733.055 |
| クライアント数 | 1,000回 | 10,000回 | 100,000回 | 300,000回 | 500,000回 | 1,000,000回 |
|---|---|---|---|---|---|---|
| 1 | 0.061 | 0.0641 | 0.06127 | 0.06144 | 0.060408 | 0.060417 |
| 10 | 0.213 | 0.1740 | 0.16464 | 0.16140 | 0.161452 | 0.165407 |
| 30 | 0.581 | 0.5020 | 0.50573 | 0.49153 | 0.504954 | 0.507953 |
| 50 | 0.923 | 0.9287 | 0.83410 | 0.83764 | 0.848118 | 0.851824 |
| 100 | 1.715 | 1.6973 | 1.68628 | 1.67000 | 1.662484 | 1.733055 |
クエリー数の多さにかかわらず、1クエリーあたりの処理時間はほぼ同じですね。
update編に関してはまた今度。
tsunokawaのはてなダイアリー