Mysql high IO usage

MySQL & MariaDB Other MySQL® Questions
1

Hi,

We have not small database (~450Gb, ~30000 tables) and few slave servers for it:

  • slave2 (scsi hdd with raid 10)
  • slave3 (sas hdd wthout raid)
    but after we doing “optimize” at master server replication at slave3 starts to lag:
  1. load on the disk becomes 100%
  2. the number of inserts falls
  3. lag continues to grow

It seems to be a hardware problem, but if we will get a new copy of Mysql datadir from slave2 (which have no lag) to slave3 - we will get an actual copy of database (when it will catch up). On graphs in cacti we can see that hardware can handle ~100 inserts per second, but when it’s 100% IO - there is only ~30 inserts per second.

What we alredy did:

  1. Move relay logs to another hdd
  2. disable journal at ext4 and tune it a little (mount opts - rw,noatime,nodiratime,data=writeback)

but no improvement…

What else to tune?

2

is this problem appears during optimize replicates to slave and optimize is in progress or optimize completed on both master/slave and slave catches up master??
In first case you can run optimize on master ( do not replicate session commands “set sql_log_bin=0” ) and then on slave, in this case your slave will not lag during optimize.

3

Hi,

I would suggest to read this blog post. [url]http://www.mysqlperformanceblog.com/2013/01/03/is-there-a-room-for-more-mysql-io-optimization/[/url]