缓解MySQL写入压力和主从延迟的尝试

最近单位需要用MySQL存放大量的日志数据，写入压力很大，并且有很大的主从延迟。

最近单位需要用MySQL存放大量的日志数据，写入压力很大，并且有很大的主从延迟。

具体环境如下
MySQL 5.6.14
服务器(单CPU,6核心,12线程 32G内存)
服务器硬盘(共33T,Raid5)

第一个尝试,分散IO
一般我们使用/dbdata挂载点存放数据文件
/data挂载点存放日志文件(redo log file,binlog,relay log等)
这样的好处是将随机IO和顺序IO分开,不形成争用.

缺点是/data挂载点的IO使用率一般较低.

当然,这种情况在一般用途的数据库并无大碍.
但是在Insert密集的数据库,数据文件所在的物理设备使用率会一直保持在100%.而日志文件所在的物理设备使用率一般也就是5%左右,甚至更低.

这时,也许可以考虑将一部分表的数据文件移动到日志文件所在的物理设备,以平衡IO资源使用.

MySQL的datadir在/dbdata挂载点,
police_im_user_mac数据库通过软链接,实际指向/data的挂载点.
这样,会将一部分的随机IO分散到/data挂载点,减轻/dbdata挂载点的压力,并且减小了CPU的等待

第二个尝试,一个库一个表
因为MySQL 5.6的多线程复制是基于数据库的.
而我们每个表的写入压力都很大,所以修改了结构,每个数据库只有一个表.然后启动多线程复制(实际上就是多个SQL线程),并且设置传输压缩

slave_compressed_protocol=on （Master,Slave都需要配置）
slave_parallel_workers=10

第三个尝试,修改参数
innodb_flush_log_at_trx_commit = 0
innodb_support_xa=0
sync_binlog=0
增加innodb_buffer_pool_size
将innodb_max_dirty_pages_pct设置的更大
将redolog file size设置的更大

经过这些修改,写入压力和主从延迟有了很大的缓解，，但是启动slave_parallel_workers,一旦出现SQL线程异常导致的复制中断,恢复之后,可能会出现1062号错误

这是因为多SQL线程的线程同步机制可能有问题,一旦复制中断,现象上看,等同于Slave异常关机.

当然,处理方法也是一样的

本文永久更新链接地址：