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

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

具体环境如下tb4linux系统宝典

MySQL 5.6.14tb4linux系统宝典

服务器(单CPU,6核心,12线程 32G内存)tb4linux系统宝典

服务器硬盘(共33T,Raid5)tb4linux系统宝典

第一个尝试,分散IOtb4linux系统宝典

一般我们使用/dbdata挂载点存放数据文件tb4linux系统宝典

/data挂载点存放日志文件(redo log file,binlog,relay log等)tb4linux系统宝典

这样的好处是将随机IO和顺序IO分开,不形成争用.tb4linux系统宝典

tb4linux系统宝典

缺点是/data挂载点的IO使用率一般较低.tb4linux系统宝典

tb4linux系统宝典

当然,这种情况在一般用途的数据库并无大碍.tb4linux系统宝典

但是在Insert密集的数据库,数据文件所在的物理设备使用率会一直保持在100%.而日志文件所在的物理设备使用率一般也就是5%左右,甚至更低.tb4linux系统宝典

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

tb4linux系统宝典

MySQL的datadir在/dbdata挂载点,tb4linux系统宝典

police_im_user_mac数据库通过软链接,实际指向/data的挂载点.tb4linux系统宝典

这样,会将一部分的随机IO分散到/data挂载点,减轻/dbdata挂载点的压力,并且减小了CPU的等待tb4linux系统宝典

tb4linux系统宝典

第二个尝试,一个库一个表tb4linux系统宝典

因为MySQL 5.6的多线程复制是基于数据库的.tb4linux系统宝典

而我们每个表的写入压力都很大,所以修改了结构,每个数据库只有一个表.然后启动多线程复制(实际上就是多个SQL线程),并且设置传输压缩tb4linux系统宝典

slave_compressed_protocol=on (Master,Slave都需要配置)tb4linux系统宝典

slave_parallel_workers=10tb4linux系统宝典

第三个尝试,修改参数tb4linux系统宝典

innodb_flush_log_at_trx_commit = 0tb4linux系统宝典

innodb_support_xa=0tb4linux系统宝典

sync_binlog=0tb4linux系统宝典

增加innodb_buffer_pool_sizetb4linux系统宝典

将innodb_max_dirty_pages_pct设置的更大tb4linux系统宝典

将redolog file size设置的更大tb4linux系统宝典

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

tb4linux系统宝典

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

当然,处理方法也是一样的tb4linux系统宝典