概要
前回(今朝)の記事で使用したmy.cnfでMySQL Benchmark Suiteのinsertに関するベンチマークをとってみました。
今回はinnodb_buffer_pool_sizeとinnodb_log_buffer_sizeを変更してみたらどれだけ数値が変わるのかを見てみる試みです。
my.cnf
前回の記事で紹介したmy.cnfです。何度も言いますが下記サイトを参考にしています。
http://d.hatena.ne.jp/sh2/20100415
[mysqld]
character_set_server = utf8
collation_server = utf8_general_ci
#transaction_isolation = READ-COMMITTED
ignore-builtin-innodb
plugin-load = innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 4M
innodb_log_buffer_size = 1M
#innodb_log_file_size = 128M
#server_id = 1
#log_bin = mysql-bin
#binlog_format = MIXED
#binlog_cache_size = 128K
#sync_binlog = 1
slow_query_log = 1
long_query_time = 0.1
query_cache_type = 1
query_cache_size = 1M
key_buffer_size = 512K
sort_buffer_size = 512K
read_buffer_size = 128K
max_allowed_packet = 16M
max_connections = 64
thread_cache_size = 8
table_open_cache = 4096
[client]
default_character_set = utf8
port = 3306
socket = /var/lib/mysql/mysql.sock
これを次のようにパラメータを変更してみます。
innodb_buffer_pool_size = 4M
innodb_log_buffer_size = 1M
innodb_buffer_pool_size = 80M
innodb_log_buffer_size = 20M
innodb_buffer_pool_size = 160M
innodb_log_buffer_size = 40M
test-insert
Generating random keys
| innodb_buffer_pool_size |
Time for insert (300000) |
| 4M |
126 wallclock secs ( 0.70 usr 15.02 sys + 0.00 cusr 0.00 csys = 15.72 CPU) |
| 80M |
113 wallclock secs ( 6.50 usr 9.75 sys + 0.00 cusr 0.00 csys = 16.25 CPU) |
| 160M |
115 wallclock secs ( 0.69 usr 15.01 sys + 0.00 cusr 0.00 csys = 15.70 CPU) |
Testing insert of duplicates
| innodb_buffer_pool_size |
Time for insert_duplicates (100000) |
| 4M |
20 wallclock secs ( 3.68 usr 1.95 sys + 0.00 cusr 0.00 csys = 5.63 CPU) |
| 80M |
16 wallclock secs ( 2.30 usr 3.38 sys + 0.00 cusr 0.00 csys = 5.68 CPU) |
| 160M |
16 wallclock secs ( 3.22 usr 2.12 sys + 0.00 cusr 0.00 csys = 5.34 CPU) |
Test of prepared+execute/once prepared many execute selects
| innodb_buffer_pool_size |
Time for prepared_select (100000) |
| 4M |
35 wallclock secs (11.74 usr 1.79 sys + 0.00 cusr 0.00 csys = 13.53 CPU) |
| 80M |
32 wallclock secs ( 9.81 usr 3.57 sys + 0.00 cusr 0.00 csys = 13.38 CPU) |
| 160M |
31 wallclock secs (11.23 usr 1.74 sys + 0.00 cusr 0.00 csys = 12.97 CPU) |
| innodb_buffer_pool_size |
Time for once_prepared_select (100000) |
| 4M |
31 wallclock secs ( 5.56 usr 3.25 sys + 0.00 cusr 0.00 csys = 8.81 CPU) |
| 80M |
26 wallclock secs ( 5.00 usr 3.49 sys + 0.00 cusr 0.00 csys = 8.49 CPU) |
| 160M |
26 wallclock secs ( 5.36 usr 3.25 sys + 0.00 cusr 0.00 csys = 8.61 CPU) |
Retrieving data from the table
| innodb_buffer_pool_size |
Time for select_big (10:3000000) |
| 4M |
12 wallclock secs (10.97 usr 0.00 sys + 0.00 cusr 0.00 csys = 10.97 CPU) |
| 80M |
12 wallclock secs (10.18 usr 0.25 sys + 0.00 cusr 0.00 csys = 10.43 CPU) |
| 160M |
11 wallclock secs (10.69 usr 0.00 sys + 0.00 cusr 0.00 csys = 10.69 CPU) |
| innodb_buffer_pool_size |
Time for order_by_big_key (10:3000000) |
| 4M |
13 wallclock secs (10.84 usr 0.00 sys + 0.00 cusr 0.00 csys = 10.84 CPU) |
| 80M |
12 wallclock secs (10.14 usr 0.28 sys + 0.00 cusr 0.00 csys = 10.42 CPU) |
| 160M |
12 wallclock secs (10.48 usr 0.00 sys + 0.00 cusr 0.00 csys = 10.48 CPU) |
Insert into table with 16 keys and with a primary key with 16 parts
| innodb_buffer_pool_size |
Time for insert_key (100000) |
| 4M |
1410 wallclock secs ( 0.00 usr 7.80 sys + 0.00 cusr 0.00 csys = 7.80 CPU) |
| 80M |
517 wallclock secs ( 0.00 usr 7.24 sys + 0.00 cusr 0.00 csys = 7.24 CPU) |
| 160M |
357 wallclock secs ( 0.00 usr 6.81 sys + 0.00 cusr 0.00 csys = 6.81 CPU) |
Testing update of keys
| innodb_buffer_pool_size |
Time for update_of_primary_key_many_keys (256) |
| 4M |
6541 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| 80M |
2339 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| 160M |
1179 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
Deleting everything from table
| innodb_buffer_pool_size |
Time for delete_big_many_keys (128) |
| 4M |
- |
| 80M |
1262 wallclock secs ( 0.00 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.01 CPU) |
| 160M |
360 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| innodb_buffer_pool_size |
Time for delete_all_many_keys (1) |
| 4M |
4410 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| 80M |
1262 wallclock secs ( 0.00 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.01 CPU) |
| 160M |
361 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
まとめ
あまりにも項目が多いので途中から大きな差がついているところだけ抜き出しました。
専用DBサーバなどの場合、innodb_buffer_pool_sizeはメモリの80%を割り当てるべきだという話を聞いていましたがupdateで大変な差がついています。
ただDeleteでも大きく差がついていた事が意外でしたがこれはインデックスに対しても変更が必要になるので負荷が高いのか、innodb_buffer_pool_sizeはそれを改善する事ができるとかでしょうかね。
なんだか面白くなってきたのでしばらくパラメータをいじって遊んでみます。
