Mysql – High IO when switched to InnoDB

innodbmyisamMySQL

We have query that worked well in MyISAM. When we switched to InnoDB, the writes I/O raised up to 50 times.

The query is type of INSERT ... ON DUPLICATE KEY UPDATE

I don't know if the problem is specific to ON DUPLICATE. Maybe we should try to update and if no rows were changed – do an insert. I guess MyISAM is doing exactly the same thing.

We tried to change various parameters for InnoDB Storage Engine according to recommendations on this site, but nothing helped.

Here is our configuration:

Variable_name   Value
innodb_adaptive_flushing    ON
innodb_adaptive_hash_index  ON
innodb_additional_mem_pool_size  8388608
innodb_autoextend_increment 8
innodb_autoinc_lock_mode    1
innodb_buffer_pool_instances    1
innodb_buffer_pool_size 8589934592
innodb_change_buffering all
innodb_checksums    ON
innodb_commit_concurrency   0
innodb_concurrency_tickets  500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir     
innodb_doublewrite  ON
innodb_fast_shutdown     1
innodb_file_format  Antelope
innodb_file_format_check    ON
innodb_file_format_max  Antelope
innodb_file_per_table   ON
innodb_flush_log_at_trx_commit  0
innodb_flush_method  
innodb_force_load_corrupted OFF
innodb_force_recovery   0
innodb_io_capacity  200
innodb_large_prefix OFF
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog   OFF
innodb_log_buffer_size  8388608
innodb_log_file_size    187695104
innodb_log_files_in_group   2
innodb_log_group_home_dir   .\
innodb_max_dirty_pages_pct  75
innodb_max_purge_lag    0
innodb_mirrored_log_groups  1
innodb_old_blocks_pct   37
innodb_old_blocks_time  0
innodb_open_files   300
innodb_purge_batch_size 20
innodb_purge_threads     0
innodb_random_read_ahead    OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads  64
innodb_replication_delay    0
innodb_rollback_on_timeout  OFF
innodb_rollback_segments    128
innodb_spin_wait_delay  6
innodb_stats_method nulls_equal
innodb_stats_on_metadata    OFF
innodb_stats_sample_pages   8
innodb_strict_mode  OFF
innodb_support_xa    ON
innodb_sync_spin_loops  30
innodb_table_locks  ON
innodb_thread_concurrency   8
innodb_thread_sleep_delay   10000
innodb_use_native_aio   ON
innodb_use_sys_malloc   ON
innodb_version  1.1.8
innodb_write_io_threads 64

Best Answer

OBSERVATION #1

You have innodb_thread_concurrency set to 8. In MySQL 5.5, it is 0 by default. Setting innodb_thread_concurrency to 0 allows InnoDB to decide the best number of concurrency tickets to handle at the same time. I discussed way back on May 26, 2011 : About single threaded versus multithreaded databases performance

OBSERVATION #2

You have innodb_log_file_size set to 179M (187695104). If you have already set this value based on Proper tuning for 30GB InnoDB table on server with 48GB RAM, then 179M is set for your environment. If you did not change this and there are lots of data to change with the INSERT ... ON DUPLICATE KEY UPDATE, then you should consider raising the log file size to 1G. Please follow the steps from my post How to safely change MySQL innodb variable 'innodb_log_file_size'?

OBSERVATION #3

Since MySQL 5.5's InnoDB Storage Engine can access multiple CPUs and multiple core, you need to set innodb_buffer_pool_instances to a number higher than 1. I just discussed this a week ago in another post : Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?. Essentially, you need to find out how many physical cores there are (numactl --hardware), and set innodb_buffer_pool_instances to that number.

Give it a Try !!!

Related Question