Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
It's difficult to say with what you have provided. To start with, can you provide some insight in your environment. Is it read or write heavy? How much memory does the server have? What are your memory configuration variables in my.cnf set to? and so on. –  Derek Downey May 22 '12 at 13:22
    
Firstly thanks for your response. I have shared required details in above area. Please suggest now!!!! –  sam May 23 '12 at 5:32
    
Sorry, at least one more bit of info needed. Which version of MySQL? –  Derek Downey May 23 '12 at 13:33
    
Hi, please don't say sorry. You are helping me:). version is mysql5.5 –  sam May 25 '12 at 5:04
    
I am facing same issues ,can you tell me why this things happend on server. when i run iotop it show mysql is busy in disk writing operations but when i see processlist of mysql it will blank .now suggest what to do , I/O increase rapidally ? –  user9061 May 29 '12 at 7:40
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.