MySQL多线程复制故障(slave_pending_jobs_size_max)

最近,经常遇到mysql多线程复制故障的问题,报错有以下几种:

第一种:

Last_Error: Cannot schedule event Rows_query, relay-log name ./db-s18-relay-bin.000448, position 419156572 to Worker thread because its size 18483519 exceeds 16777216 of slave_pending_jobs_size_max.

第二种:

[Note] Multi-threaded slave: Coordinator has waited 701 times hitting slave_pending_jobs_size_max; current event size = 8167.

BUG地址:https://bugs.mysql.com/bug.php?id=68462

以上两种报错,初步判断问题可能在 slave_pending_jobs_size_max 的大小上,此值,官方默认是 16M,此值可以动态调整
关于此值的一些说明:

# 在多线程复制时,在队列中Pending的事件所占用的最大内存,默认为16M,如果内存富余,或者延迟较大时,可以适当调大;注意这个值要比主库的max_allowed_packet大

此值有如下几种情况:

     1.- 如果event大小已经超过了等待任务大小的上限(配置slave-pending-jobs-size-max ),就报event太大的错,然后返回;
     2.- 如果event大小+已经在等待的任务大小超过了slave-pending-jobs-size-max,就等待,至到等待队列变小;
     3.- 如果当前的worker的队列满的话,也等待。

下面是官方关于此参数的一些说明:(图片看不了,可点击查看大图)

QQ图片20160524124437

 

 

mysql 5.6 新特性中,自定义刷新日志时间:innodb_flush_log_at_timeout

      今天有一朋友发了一个参数过来(innodb_flush_log_at_timeout),着实没见过这个参数,从字面意思上理解和刷新日志有关,所以就查了一下,我这里说一下自己的观点:

      1.innodb_flush_log_at_timeout 这个参数的意思是刷新日志的时间,在mysql5.6版本中可以自定义,默认为1s。其与oracle有很大区别:
      在oracle中,有三种情况可以将日志缓冲区的数据写到在线日志文件中
      (1).日志缓冲区中的记录达到1M
      (2).每隔3秒
      (3).日志缓冲区已经用了三分之一
     2.INNODB REDO日志:InnoDB为了保证日志的刷写的高效,使用了内存的log buffer。
      由于InnoDB大部分情况下使用的是文件系统,(linux文件系统本身也是有buffer的)而不是直接使用物理块设备,这样的话就有两种丢失日志的可能性:日志保存在log_buffer中,机器挂了,对应的事务数据就丢失了;日志从log buffer刷到了linux文件系统的buffer,机器挂掉了,对应的事务数据就丢失了。
     3.InnoDB有一个参数用于设置这两个缓存的刷新: innodb_flush_log_at_trx_commit。而 innodb_flush_log_at_trx_commit  有三个值:0/1/2,默认是1。而innodb_flush_log_at_timeout 定义了每次日志刷新的时间,与  innodb_flush_log_at_trx_commit 配合使用,其具体流程,先看下图:
innodb_flush_log_at_time
innodb_flush_log_at_time
       innodb_flush_log_at_trx_commit=1,表示在每次事务提交的时候,都把log buffer刷到文件系统中(os buffer)去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。
       innodb_flush_log_at_trx_commit=0,表示每隔一秒把log buffer刷到文件系统中(os buffer)去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。也就是说一秒之前的日志都保存在日志缓冲区,也就是内存上,如果机器宕掉,可能丢失1秒的事务数据。
       innodb_flush_log_at_trx_commit=2,表示在每次事务提交的时候会把log buffer刷到文件系统中(os buffer)去,但是每隔一秒调用文件系统(os buffer)的“flush”操作将缓存刷新到磁盘上去。如果只是MySQL数据库挂掉了,由于文件系统没有问题,那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下,数据库的事务数据可能丢失1秒之类的事务数据。这样的好处,减少了事务数据丢失的概率,而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中,一般只是从log buffer的内存转移的文件系统的内存缓存中,对底层IO没有压力)。MySQL 5.6.6以后,这个“1秒”的刷新还可以用innodb_flush_log_at_timeout 来控制刷新间隔。
       结合上面几个参数的描述,我相信多数企业采用mysql innodb存储引擎都是为了充分保证数据的一致性,所以,innodb_flush_log_at_trx_commit这个参数一般都是 1,这样的话,innodb_flush_log_at_timeout 的设置对其就不起作用。innodb_flush_log_at_timeout 的设置只针对 innodb_flush_log_at_trx_commit为0/2 起作用,所以,此参数可暂时不做研究!

mysql 5.6的新特性一览

随着oralce发布mysql 5.6版本,其新特性也备受瞩目,下面整理一些个人感觉比较有用的特性:

部分内容来自网络,如有错误或遗漏,可留言补充,谢谢!

官方描述的新特性如下:

新增 在线 DDL /更改数据架构支持动态应用程序和开发人员灵活性

新增 复制全局事务标识可支持自我修复式集群

新增 复制无崩溃从机可提高可用性

新增 复制多线程从机可提高性能

新增 对 InnoDB 进行 NoSQL 访问,可快速完成键值操作以及快速提取数据来完成大数据部署

改进 在 Linux 上的性能提升多达 230%

改进 在当今多核、多 CPU 硬件上具备更高的扩展力

改进 InnoDB 性能改进,可更加高效地处理事务和只读负载

改进 更快速地执行查询,增强的诊断功能

改进 Performance Schema 可监视各个用户/应用程序的资源占用情况

改进 通过基于策略的密码管理和实施来确保安全性

复制功能 支持灵活的拓扑架构,可实现向外扩展和高可用性

分区 有助于提高性能和管理超大型数据库环境

ACID 事务 支持构建安全可靠的关键业务应用程序

存储过程 可提高开发人员效率

触发器 可在数据库层面实施复杂的业务规则

View 可确保敏感信息不受攻击

Information Schema 有助于方便地访问元数据

插入式存储引擎架构 可最大限度发挥灵活性

—————————————————————————————————————–

以下为网友结合官方总结的一些有用的新特性:[有遗漏,请留言补充]

1.权限认证,不用输入用户名和密码

2.用户密码有效期设置

3.Innodb全文检索

4.Innodb在线DDL功能增强,修改列名等不用复制数据

5.Innodb使用独享表空间时,可自定义表的数据文件存放的位置,繁忙的放SSD,支持单表在不同实例之间的转移

6.Innodb支持页大小的自定义, innodb_page_size

7.Innodb和Memcache接口的整合

8.Innodb统计信息收集更加精准,执行计划更加精准

9.Innodb Undo数据从系统表空间独立出来为单独的表空间,SSD

10.Innodb Redo日志文件大小调整为512G,以前最大为4G

11.Innodb减少内部争用,Flush操作从主线程独立出来为Flush线程,多Purge线程

12.Innodb死锁检测新方法,信息记录在Error Log中

13.Innodb Buffer Pool信息导出导入,Restart Database with Large Buffer Pool

14.Partition 支持分区和表Exchange

15.Partition 支持显示定义操作(Select、Delete、Insert、Replace等)的分区

16.Performance Schema功能增强

17.复制支持基于Transaction的复制Gtids,提高Master和Slave的一致性

18.复制Row复制只保存改变的列,大大节省Disk Space,Newwork resources和Memory usage

19.复制支持把Master 和Slave的相关信息记录在Table

20.复制支持延迟复制

21.复制执行多线程并行复制,降低Slave与Master的延迟

22.MRR Join操作时候使用范围扫描代替单点循环提高查询效率

23.ICP Index Condition Pushdown

24.Explain支持Delete、Insert、Replace、Update等DML操作

25.子查询优化

26.时间类型字段Time、Datetime、Timestamp支持的粒度由秒扩展到微秒