Mysql – Changing to InnoDB increases iostat

innodbMySQLoptimization

I have changed my MyISAM tables to InnoDB and from that day, iostat is increasing continuously. My findings on the server say that mysqld is the one who is increasing iostat.

But how can it increase continuously?

write is heavy. And server has 8GB.am pasting my.cnf 

innodb_data_file_path = ibdata1:10M;ibdata2:20M:autoextend
innodb_autoextend_increment=5
innodb_file_per_table
innodb_log_group_home_dir = /var/lib/log/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 2M
#innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
#innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_log_file_size = 40M
innodb_log_files_in_group = 2

innodb_thread_concurrency=16
innodb_thread_sleep_delay=1000

innodb_max_dirty_pages_pct = 80

query_cache_size=512M
query_cache_limit= 1M

max_heap_table_size = 32M
tmp_table_size = 64M

read_buffer_size = 512K

innodb_log_buffer_size=8M
thread_cache_size = 70

table_cache=2048

This iostat started increasing when I changed engine of my tables from MyISAM to InnoDB.

Please help now.

Best Answer

Disclaimer: This is still very broad and could be anything, so this answer is only some potential reasons for increased IO activity

My first inclination to your question But how can it increase continuously? when you mentioned that the load is 'write-heavy' is that you have autocommit enabled. If you are doing a lot of inserts, this might be your issue (emphasis mine):

When importing data into InnoDB, turn off autocommit mode, because it performs a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET autocommit and COMMIT statements:

SET autocommit=0;
... SQL import statements ...
COMMIT;

[src]

If that doesn't help, another major source of IO in a write-heavy environment are your indexes. Make sure you are not over-indexing (have unused indexes).

Also, InnoDB will write the primary key data to the index file for each secondary index as a way to identify them. So if you have a lot of natural or composite primary keys in tables that have secondary indexes, that could be a lot of extra data that is being written.

Related Question