LinodeでInnoDBチューニング(3)

概要

innodb_log_buffer_sizeをlog fileのサイズと勘違いしていた事が発覚したので修正したら、過去の記事とは違うベンチマークがでました。

前回までのベンチはinnodb_buffer_pool_sizeを大きくしていったものでしたが、それに合わせてログファイルを大きくしてみようとしました、が

実際に大きくしてたのはinnodb_log_buffer_sizeだったという。改めてベンチを取りなおしました。

360MB(560MBのVPS)でmysqldのメモリはtopで見たところ82%ほどまで使われるようになりましたが、420MB以上にしてもメモリ使用量は向上せずに、かえって速度低下を招いてしまったようです。

ひとまずinnodb_buffer_pool_sizeは360MBにして、他のパラメータをいじってみようかと思います。

ふと思ったのは、XenなのでHDDが物理的に1個とかで複数のユーザーが同じサーバー上でサービスを使用していて、それで正しく計測できてない、とかなにか事情があるのかなー。

ただ、そうするとこんなにベンチやっててI/Oを消費するユーザーがいると他の人困るからやっぱHDDは物理的に分けてると思うんですけど、どうなんだろう。xenに詳しい人教えて下さい。

来週会社で聞くとしますか。

my.cnf

[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 = 360M ## 300MB, 360MB, 420MBを試す
innodb_log_buffer_size = 8M
#innodb_log_file_size = 128M

#innodb_flush_method=O_DIRECT

#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

MySQL Benchmark Suite実行結

負荷の高いものだけ抜粋

sudo ./test-insert --user=root --password=XXXX

Insert into table with 16 keys and with a primary key with 16 parts

innodb_buffer_pool_size	Time for insert_key (100000)
300MB	273 wallclock secs ( 0.00 usr 6.75 sys + 0.00 cusr 0.00 csys = 6.75 CPU)
360MB	237 wallclock secs ( 0.00 usr 7.04 sys + 0.00 cusr 0.00 csys = 7.04 CPU)
420MB	348 wallclock secs ( 0.00 usr 7.03 sys + 0.00 cusr 0.00 csys = 7.03 CPU)
480MB	309 wallclock secs ( 0.00 usr 6.86 sys + 0.00 cusr 0.00 csys = 6.86 CPU)

Testing update of keys

innodb_buffer_pool_size	Time for update_of_primary_key_many_keys (256)
300MB	861 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU)
360MB	837 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU)
420MB	1006 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU)
480MB	1023 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)
300MB	155 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU)
360MB	158 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU)
420MB	178 wallclock secs ( 0.00 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.01 CPU)
480MB	210 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)
300MB	156 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU)
360MB	160 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU)
420MB	180 wallclock secs ( 0.00 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.01 CPU)
480MB	211 wallclock secs ( 0.00 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.02 CPU)