概要
前回、innodb_buffer_pool_sizeをちょっと大きくしたら性能がだいぶ変わったのでmysqldの使用メモリを全体の80%になるまで、少しずつあげてみたという記事です。
試したinnodb_buffer_pool_sizeの値
240MB, 300MB, 360MBを試しました。
mysqldのプロセスは順番に約56%、約68%、約82%まで大きくなりました。
メモリサイズを大きくすると単純に高速になると思ったのですが、300MBのほうが360MBよりも速かったという結果になりました。
どうしても納得できなかったので360MBでのベンチを2回とったところ、2回目のほうが高速でした、がそれでも300MBのほうが高速でした。
以下、比較表です。専用サーバーにしない場合は160MBぐらいがいい感じかもしれません。
MySQL Benchmark Suite実行結果
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) |
| 240M |
111 wallclock secs ( 0.78 usr 15.00 sys + 0.00 cusr 0.00 csys = 15.78 CPU) |
| 300M |
106 wallclock secs ( 0.78 usr 14.79 sys + 0.00 cusr 0.00 csys = 15.57 CPU) |
| 360M |
107 wallclock secs ( 0.75 usr 15.04 sys + 0.00 cusr 0.00 csys = 15.79 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) |
| 240M |
15 wallclock secs ( 3.45 usr 2.07 sys + 0.00 cusr 0.00 csys = 5.52 CPU) |
| 300M |
15 wallclock secs ( 3.56 usr 1.73 sys + 0.00 cusr 0.00 csys = 5.29 CPU) |
| 360M |
15 wallclock secs ( 3.55 usr 1.75 sys + 0.00 cusr 0.00 csys = 5.30 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) |
| 240M |
33 wallclock secs (12.09 usr 1.62 sys + 0.00 cusr 0.00 csys = 13.71 CPU) |
| 300M |
31 wallclock secs (11.72 usr 1.10 sys + 0.00 cusr 0.00 csys = 12.82 CPU) |
| 360M |
29 wallclock secs (11.47 usr 1.20 sys + 0.00 cusr 0.00 csys = 12.67 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) |
| 240M |
26 wallclock secs ( 5.41 usr 3.27 sys + 0.00 cusr 0.00 csys = 8.68 CPU) |
| 300M |
25 wallclock secs ( 5.22 usr 3.09 sys + 0.00 cusr 0.00 csys = 8.31 CPU) |
| 360M |
26 wallclock secs ( 5.15 usr 3.18 sys + 0.00 cusr 0.00 csys = 8.33 CPU) |
| 360M |
26 wallclock secs ( 5.15 usr 3.18 sys + 0.00 cusr 0.00 csys = 8.33 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) |
| 240M |
12 wallclock secs (10.70 usr 0.00 sys + 0.00 cusr 0.00 csys = 10.70 CPU) |
| 300M |
11 wallclock secs (10.32 usr 0.00 sys + 0.00 cusr 0.00 csys = 10.32 CPU) |
| 360M |
10 wallclock secs ( 9.94 usr 0.00 sys + 0.00 cusr 0.00 csys = 9.94 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) |
| 240M |
13 wallclock secs (11.22 usr 0.00 sys + 0.00 cusr 0.00 csys = 11.22 CPU) |
| 300M |
12 wallclock secs (10.40 usr 0.00 sys + 0.00 cusr 0.00 csys = 10.40 CPU) |
| 360M |
12 wallclock secs (10.20 usr 0.00 sys + 0.00 cusr 0.00 csys = 10.20 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) |
| 240M |
295 wallclock secs ( 0.00 usr 6.92 sys + 0.00 cusr 0.00 csys = 6.92 CPU) |
| 300M |
273 wallclock secs ( 0.00 usr 6.75 sys + 0.00 cusr 0.00 csys = 6.75 CPU) |
| 360M |
335 wallclock secs ( 0.00 usr 6.85 sys + 0.00 cusr 0.00 csys = 6.85 CPU) |
| 360M(2) |
300 wallclock secs ( 0.00 usr 6.73 sys + 0.00 cusr 0.00 csys = 6.73 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) |
| 240M |
923 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| 300M |
861 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| 360M |
1212 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| 360M(2) |
937 wallclock secs ( 0.00 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.03 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) |
| 240M |
245 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| 300M |
155 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| 360M |
278 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| 360M(2) |
166 wallclock secs ( 0.00 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.01 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) |
| 240M |
246 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| 300M |
156 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| 360M |
279 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU) |
| 360M(2) |
168 wallclock secs ( 0.00 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.01 CPU) |
