InnoDB - High disk write I/O on ibdata1 file and ib_logfile0

Server Specification: VPS with following info

model name  : Intel(R) Xeon(R) CPU           E5649  @ 2.53GHz
MemTotal:      2058776 kB
MemFree:        244436 kB

We are running IP.Board from Invision Power Services, we are using
innodb_file_per_table and have reloaded the database to reduce ibdata1 size. However, we still got problem of high CPU and I/O usage lately despite of the reduced ibdata1 size.

From my inspection, I believe that it causes by high I/O usage on ibdata1. Below is the data I obtained using pt-ioprofile -cell sizes (in Percona ToolKit). Basically, it's the total I/O amount collected in the period of 30 seconds.

# pt-ioprofile -cell sizes
Fri Jul 20 10:22:23 ICT 2012
Tracing process ID 8581
     total      pread       read     pwrite      fsync       open      close   getdents      lseek      fcntl filename
   6995968          0          0    6995968          0          0          0          0          0          0 /db/mysql/ibdata1
   1019904          0          0    1019904          0          0          0          0          0          0 /db/mysql/ib_logfile0
    204800     204800          0          0          0          0          0          0          0          0 /db/mysql/admin_phpbb3forum/phpbb_posts.ibd
     49152      49152          0          0          0          0          0          0          0          0 /db/mysql/admin_ips/ips_reputation_cache.ibd
     32768      32768          0          0          0          0          0          0          0          0 /db/mysql/admin_ips/ips_reputation_totals.ibd
     29808          0          0          0          0          0          0      29808          0          0 /db/mysql/admin_ips/
... (other trivial I/O records truncated)

Running iotop and I see DISK WRITE: goes up and down around 2M/s and 200K/s

My question is, why we have high I/O write on ibdata1 and ib_logfileX while we have only about 5-10 small update per second into our sessions table

Here are a few things you could change

• innodb_log_buffer_size : You have 4M. The default is 8M. Try raising it to 8M.

• innodb_log_file_size : You have 128M. Given the following info you supplied

• you write on avg 93MB per hour to the innodb log files, based on 25% of the stat

• peak time would be about 372 MB an hour

• half is 186 MB round off to 192MB

• set innodb_log_file_size to 192M

• innodb_io_capacity seems a little high, make it 200 or 300 (depending on hardware)

• innodb_read_io_threads and innodb_write_io_threads should be maxed out at 64. Sometimes, less is better in a write-heavy environment. Use 8 or 16 in such cases.

To change innodb_log_file_size to 192M, add this to /etc/my.cnf

[mysqld]
innodb_log_buffer_size=8M
innodb_log_file_size=192M
innodb_open_files=300
innodb_read_io_threads=64
innodb_write_io_threads=64

then do the following

mysql -u... -p... -e"FLUSH TABLES;"
service mysql stop
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0000
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile0001
service mysql start

Test this during slow times and see if this improves things

UPDATE 2012-10-19 12:31 EDT

To put your situation into perspective think about what the purposes of ibdata1 and ib_logfile0/1 are. Rather than rewriting everything, please read my most recent post about ibdata1 and InnoDB Log Files : What exactly are iblog files in mysql

Once you have read my other post, now consider this: What would increase I/O on these files?

• Updating Secondary Indexes in ibdata1's Insert Buffer

• Cleaning up the Rollback Segments/Undo Space

IMHO I would increase the InnoDB Buffer Pool from 384M to 1G.

I would also recommend upgrading RAM to 8G

StackExchange Database Administrators Q#21209, answer score: 7