MariaDBのパフォーマンス検証-INSERT編
MariaDBのパフォーマンス検証-INSERT編
前回(MariaDBのパフォーマンス検証-SELECT編)に続きINSERTのベンチマークを取ります。
MariaDBの検証開始(INSERT編)
・1クライアント 1,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=1000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 0.583 seconds Minimum number of seconds to run all queries: 0.551 seconds Maximum number of seconds to run all queries: 0.625 seconds Number of clients running queries: 1 Average number of queries per client: 1000
・1クライアント 10,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=10000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 6.504 seconds Minimum number of seconds to run all queries: 6.319 seconds Maximum number of seconds to run all queries: 6.617 seconds Number of clients running queries: 1 Average number of queries per client: 10000
・1クライアント 100,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=100000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 65.329 seconds Minimum number of seconds to run all queries: 64.507 seconds Maximum number of seconds to run all queries: 66.246 seconds Number of clients running queries: 1 Average number of queries per client: 100000
・1クライアント 300,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=300000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 196.706 seconds Minimum number of seconds to run all queries: 192.764 seconds Maximum number of seconds to run all queries: 200.287 seconds Number of clients running queries: 1 Average number of queries per client: 300000
・1クライアント 500,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=500000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 335.026 seconds Minimum number of seconds to run all queries: 330.199 seconds Maximum number of seconds to run all queries: 338.260 seconds Number of clients running queries: 1 Average number of queries per client: 500000
・1クライアント 1,000,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=1 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=1000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 658.066 seconds Minimum number of seconds to run all queries: 647.098 seconds Maximum number of seconds to run all queries: 665.741 seconds Number of clients running queries: 1 Average number of queries per client: 1000000
・10クライアント 1,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=10000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 1.414 seconds Minimum number of seconds to run all queries: 1.205 seconds Maximum number of seconds to run all queries: 1.690 seconds Number of clients running queries: 10 Average number of queries per client: 1000
・10クライアント 10,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=100000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 10.206 seconds Minimum number of seconds to run all queries: 9.763 seconds Maximum number of seconds to run all queries: 10.958 seconds Number of clients running queries: 10 Average number of queries per client: 10000
・10クライアント 100,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=1000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 112.216 seconds Minimum number of seconds to run all queries: 104.011 seconds Maximum number of seconds to run all queries: 116.944 seconds Number of clients running queries: 10 Average number of queries per client: 100000
・10クライアント 300,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=3000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 343.846 seconds Minimum number of seconds to run all queries: 338.019 seconds Maximum number of seconds to run all queries: 347.677 seconds Number of clients running queries: 10 Average number of queries per client: 300000
・10クライアント 500,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=5000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 584.833 seconds Minimum number of seconds to run all queries: 552.751 seconds Maximum number of seconds to run all queries: 613.473 seconds Number of clients running queries: 10 Average number of queries per client: 500000
・10クライアント 1,000,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=10 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=10000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 1192.159 seconds Minimum number of seconds to run all queries: 1173.129 seconds Maximum number of seconds to run all queries: 1201.787 seconds Number of clients running queries: 10 Average number of queries per client: 1000000
・30クライアント 1,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=30000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 1.798 seconds Minimum number of seconds to run all queries: 1.719 seconds Maximum number of seconds to run all queries: 1.944 seconds Number of clients running queries: 30 Average number of queries per client: 1000
・30クライアント 10,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=300000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 13.991 seconds Minimum number of seconds to run all queries: 13.658 seconds Maximum number of seconds to run all queries: 14.430 seconds Number of clients running queries: 30 Average number of queries per client: 10000
・30クライアント 100,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=3000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 155.460 seconds Minimum number of seconds to run all queries: 149.888 seconds Maximum number of seconds to run all queries: 160.936 seconds Number of clients running queries: 30 Average number of queries per client: 100000
・30クライアント 300,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=9000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 481.657 seconds Minimum number of seconds to run all queries: 469.904 seconds Maximum number of seconds to run all queries: 488.896 seconds Number of clients running queries: 30 Average number of queries per client: 300000
・30クライアント 500,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=15000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 805.009 seconds Minimum number of seconds to run all queries: 787.829 seconds Maximum number of seconds to run all queries: 824.767 seconds Number of clients running queries: 30 Average number of queries per client: 500000
・30クライアント 1,000,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=30 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=30000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 1619.124 seconds Minimum number of seconds to run all queries: 1609.463 seconds Maximum number of seconds to run all queries: 1633.410 seconds Number of clients running queries: 30 Average number of queries per client: 1000000
・50クライアント 1,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=50000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 2.546 seconds Minimum number of seconds to run all queries: 2.344 seconds Maximum number of seconds to run all queries: 2.678 seconds Number of clients running queries: 50 Average number of queries per client: 1000
・50クライアント 10,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=500000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 19.273 seconds Minimum number of seconds to run all queries: 18.277 seconds Maximum number of seconds to run all queries: 20.362 seconds Number of clients running queries: 50 Average number of queries per client: 10000
・50クライアント 100,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=5000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 195.159 seconds Minimum number of seconds to run all queries: 187.217 seconds Maximum number of seconds to run all queries: 200.301 seconds Number of clients running queries: 50 Average number of queries per client: 100000
・50クライアント 300,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=15000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 583.948 seconds Minimum number of seconds to run all queries: 573.548 seconds Maximum number of seconds to run all queries: 593.750 seconds Number of clients running queries: 50 Average number of queries per client: 300000
・50クライアント 500,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=25000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 980.430 seconds Minimum number of seconds to run all queries: 954.919 seconds Maximum number of seconds to run all queries: 1015.408 seconds Number of clients running queries: 50 Average number of queries per client: 500000
・50クライアント 1,000,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=50 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=50000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 2008.044 seconds Minimum number of seconds to run all queries: 1979.944 seconds Maximum number of seconds to run all queries: 2037.356 seconds Number of clients running queries: 50 Average number of queries per client: 1000000
・100クライアント 1,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=100000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 3.642 seconds Minimum number of seconds to run all queries: 3.477 seconds Maximum number of seconds to run all queries: 3.954 seconds Number of clients running queries: 100 Average number of queries per client: 1000
・100クライアント 10,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=1000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 28.953 seconds Minimum number of seconds to run all queries: 28.631 seconds Maximum number of seconds to run all queries: 29.344 seconds Number of clients running queries: 100 Average number of queries per client: 10000
・100クライアント 100,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=10000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 306.172 seconds Minimum number of seconds to run all queries: 301.469 seconds Maximum number of seconds to run all queries: 310.961 seconds Number of clients running queries: 100 Average number of queries per client: 100000
・100クライアント 300,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=30000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 890.928 seconds Minimum number of seconds to run all queries: 881.189 seconds Maximum number of seconds to run all queries: 908.953 seconds Number of clients running queries: 100 Average number of queries per client: 300000
・100クライアント 500,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=50000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 1508.465 seconds Minimum number of seconds to run all queries: 1490.974 seconds Maximum number of seconds to run all queries: 1521.780 seconds Number of clients running queries: 100 Average number of queries per client: 500000
・10クライアント 1,000,000クエリーのINSERT
mysqlslap --no-defaults --concurrency=100 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=insert --number-of-queries=100000000 --iterations 3 --user root --password Enter password: Benchmark Running for engine innodb Average number of seconds to run all queries: 3040.921 seconds Minimum number of seconds to run all queries: 2947.363 seconds Maximum number of seconds to run all queries: 3111.252 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.583 | 6.504 | 65.329 | 196.706 | 335.026 | 658.066 |
| 10 | 1.414 | 10.206 | 112.216 | 343.846 | 584.833 | 1192.159 |
| 30 | 1.798 | 13.991 | 155.460 | 481.657 | 805.009 | 1619.124 |
| 50 | 2.546 | 19.273 | 195.159 | 583.948 | 980.430 | 2008.044 |
| 100 | 3.642 | 28.953 | 306.172 | 890.928 | 1508.465 | 3040.921 |
| クライアント数/クエリー | 1,000 | 10,000 | 100,000 | 300,000 | 500,000 | 1,000,000 |
|---|---|---|---|---|---|---|
| 1 | 0.5830 | 0.6504 | 0.65330 | 0.65569 | 0.67005 | 0.65807 |
| 10 | 1.41400 | 1.02060 | 1.12216 | 1.14615 | 1.16967 | 1.19216 |
| 30 | 1.79800 | 1.39910 | 1.55460 | 1.60552 | 1.61002 | 1.61912 |
| 50 | 2.54600 | 1.92730 | 1.95159 | 1.94649 | 1.96086 | 2.00804 |
| 100 | 3.642 | 2.8953 | 3.06172 | 2.96976 | 3.01693 | 3.040921 |
各クエリー数との所要時間を比較すると少ないクエリー数のほうがコストが高い。
10,000クエリーを超えると、ほぼ同じようなグラフになったが、10,000クエリーと100,000クエリー以上では
10クライアント以上の場合に約10％の性能差が出た。
update編に関してはまた今度。
tsunokawaのはてなダイアリー