自增表死锁问题分析及处理

今天,研发提交了一个死锁信息,涉及到自增表的死锁,测试,压测产生的死锁信息,并发500,信息如下:
1234

从上面死锁信息来看,lock mode AUTO-INC waiting,应该是表的自增列的问题,初步了解,这个死锁和 innodb_autoinc_lock_mode 的值有一定的关系,但也不因全归咎于mysql的问题。

从5.6的用户手册中查找到AUTO-INC的相关信息:

InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned in a predictable and repeatable order for a given sequence of INSERT statements

InnoDB在为自增列产生值的时候,使用一种叫做AUTO_INC的表级锁来做控制。这种锁是作用于语句的而不是事务(即语句执行完了锁就会被释放)。使用这种锁是为了确保自增列的值的可预见性和可重复性。可预见性是说当一条insert语句作用于多行时,这些行的自增列基于第一行来说是可预见的;可重复执行是指基于语句的复制在slave重放时自增列的值与master的一致。

innodb_autoinc_lock_mode:

默认值:1,可取值为:0,1,2

在 mysql5.1.22之前,没有这个选项,默认都是0,在并发数大于208以上可能出现很多死锁

下面解释一下innodb_autoinc_lock_mode 几种默认值的含义:

0:traditonal (每次都会产生表锁)

1:consecutive (默认,可预判行数时使用新方式,不可时使用表锁,对于simple insert会获得批量的锁,保证连续插入)

Simple inserts:

直接通过分析语句,获得要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住。(INSERT, INSERT … VALUES(),VALUES())

Bulk inserts:

因为不能确定插入的数量,因此使用和以前的模式相同的表级锁定。(INSERT … SELECT, REPLACE … SELECT, LOAD DATA)

其中 insert ….. select ….. ,是特殊的select加X锁的情况,原因是为保证数据的一致性(M-S环境)

Mixed-mode inserts:

直接分析语句,获得最坏情况下需要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住。

(INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’);INSERT … ON DUPLICATE KEY UPDATE)

2:interleaved (不会锁表,来一个处理一个,并发最高)

这种模式是来一个分配一个,而不会锁表,只会锁住分配id的过程,和innodb_autoinc_lock_mode = 1的区别在于,不会预分配多个,这种方式并发性最高。

从上面来看,一般 innodb_autoinc_lock_mode = 1 默认值,基本上满足需要。

虽说 innodb_autoinc_lock_mode = 2 不安全,但是在 binlog_format=ROW,transaction-isolation=READ-COMMITTED , innodb_autoinc_lock_mode = 2 是非常安全的。

至于,针对高并发的表,主键列的设置建议如下:

1.采用DB的自增属性,此时,需要调整DB参数,尽可能提高并发:
binlog_format=ROW,transaction-isolation=READ-COMMITTED , innodb_autoinc_lock_mode = 2

2.采用程序生成全局自增ID,利用redis、memcache集合生成 ,这也是推荐的方式

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主从一致性校验修复工具[详解]:pt-table-checksum 和 pt-table-sync

在实际DB维护过程中,我们可能会遇到因为数据不一致而引起的主从中断,此时,我们应该如何处理呢!

percona提供了一个很好的工具:percona-toolkit中的 pt-table-checksum 和 pt-table-sync

percona-toolkit的安装这里就不做演示了,各位自行安装

一.检查主从数据的一致性情况:

pt-table-checksum [OPTIONS] [DSN]。

pt-table-checksum:在主上通过执行校验的查询对复制的一致性进行检查,对比主从的校验值,从而产生结果。

DSN指向的是主的地址,该工具的退出状态不为零,如果发现有任何差别,或者如果出现任何警告或错误,不指定任何参数,会直接对本地的所有数据库的表进行检查。

如pt-table-checksum u=root,p=123456

注意事项如下:

(1).测试需要一个既能登录主库,也能登录从库,而且还能同步数据库的账号;

(2).只能指定一个host,必须为主库的IP;

(3).在检查时会向表加S锁;

(4).运行之前需要从库的同步IO和SQL进程是YES状态。

mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO ‘checksums’@’x.x.x.x’

IDENTIFIED BY ‘xxxx’;

(5).如果在配置主从时,忽略复制mysql库时,需要在 主上 和 从上 都执行上面的授权语句

[root@mysql-master1 ~]# pt-table-checksum h=’172.16.10.53′,u=’checksums’,p=’checksums’,P=3306 -d test_0109 –nocheck-replication-filters –replicate=test_checksum.checksums –nocheck-binlog-format –nocheck-plan –recursion-method=hosts

Waiting to check replicas for differences: 0% 00:00 remain

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

01-12T14:39:51 0 0 0 1 0 0.024 test_0109.broker

01-12T14:39:51 0 0 4 1 0 0.010 test_0109.check_conistent

01-12T14:39:51 0 0 0 1 0 0.012 test_0109.partner_alert_rule

01-12T14:39:51 0 1 0 1 0 0.266 test_0109.proc_test

01-12T14:39:51 0 1 529 1 0 0.021 test_0109.project

参数说明:

TS :完成检查的时间。

ERRORS :检查时候发生错误和警告的数量。

DIFFS :0表示一致,1表示不一致。当指定–no-replicate-check时,会一直为0,当指定–replicate-check-only会显示不同的信息。

ROWS :表的行数。

CHUNKS :被划分到表中的块的数目。

SKIPPED :由于错误或警告或过大,则跳过块的数目。

TIME :执行的时间。

TABLE :被检查的表名。

参数意义:

–nocheck-replication-filters :不检查复制过滤器,建议启用。后面可以用–databases来指定需要检查的数据库。

–no-check-binlog-format : 不检查复制的binlog模式,要是binlog模式是ROW,则会报错。

–replicate-check-only :只显示不同步的信息。

–replicate= :把checksum的信息写入到指定表中,建议直接写到被检查的数据库当中。

–databases= :指定需要被检查的数据库,多个则用逗号隔开。

–tables= :指定需要被检查的表,多个用逗号隔开

–recursion-method=: 主机信息

h=172.16.10.53 :Master的地址

u=checksums :用户名

p=checksums :密码

P=3306 :端口

通过DIFFS是1可以看出主从的表数据不一致。通过查看从库上的 test_checksum.checksums 表可以看到主从库的检验信息。
mysql> select * from test_checksum.checksums;
+———–+——————–+——-+————+————-+—————-+—————-+———-+———-+————+————+———————+
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
+———–+——————–+——-+————+————-+—————-+—————-+———-+———-+————+————+———————+
| test_0109 | broker | 1 | 0.005901 | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 2015-01-12 14:39:51 |
| test_0109 | check_conistent | 1 | 0.000484 | NULL | NULL | NULL | 709a8dc | 4 | 709a8dc | 4 | 2015-01-12 14:39:51 |
| test_0109 | partner_alert_rule | 1 | 0.000825 | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 2015-01-12 14:39:51 |
| test_0109 | proc_test | 1 | 0.000439 | NULL | NULL | NULL | 652c8c22 | 99 | 0 | 0 | 2015-01-12 14:39:51 |
| test_0109 | project | 1 | 0.006852 | NULL | NULL | NULL | 0 | 0 | 862da9de | 529 | 2015-01-12 14:39:51 |
+———–+——————–+——-+————+————-+—————-+—————-+———-+———-+————+————+———————+

通过上面的 this_crc <> master_crc 更能清楚的看出他们的不一致了,通过chunk知道是这个张表的哪个块上的记录出现不一致。要是主的binlog模式是Row 则会报错:

Replica db2 has binlog_format ROW which could cause pt-table-checksum to break replication.

Please read “Replicas using row-based replication” in the LIMITATIONS section of the tool’s documentation.

If you understand the risks, specify –no-check-binlog-format to disable this check.

指定–replicate-check-only参数会在前一次pt-table-checksum检验的数据之上比较(不会再执行计算),显示出数据不一致的SLAVE主机名

二.修复主从数据不一致的情况:

通过上面的工具,我们检测出主从之间数据不一致的情况,那此时我们应该如何处理呢?

percona提供了pt-table-sync 用于修复主从数据的一致性

pt-table-sync [OPTIONS] DSN [DSN]。

pt-table-sync: 高效的同步MySQL表之间的数据,他可以做单向和双向同步的表数据。他可以同步单个表,也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。

继续上面的复制环境,主和从的表数据不一致,需要修复

pt-table-sync –print –replicate=test_checksum.checksums h=172.16.10.53,u=checksums,p=checksums,P=3306 h=172.16.10.55,u=checksums,p=checksums,P=3306

// 先MASTER的IP,再SLAVE的IP

// 以上是打印出同步语句,如果不一致数据较多,不需要打印出这些语句;

参数意义参考:

–replicate= :指定通过pt-table-checksum得到的表,这2个工具差不多都会一直用。

–databases= : 指定执行同步的数据库,多个用逗号隔开。

–tables= :指定执行同步的表,多个用逗号隔开。

–sync-to-master :指定一个DSN,即从的IP,他会通过show processlist或show slave status 去自动的找主。

h=127.0.0.1 :服务器地址,命令里有2个ip,第一次出现的是M的地址,第2次是Slave的地址。

u=root :帐号。

p=123456 :密码。

–print :打印,但不执行命令。

–execute :执行命令。
执行数据修复:

pt-table-sync –execute –replicate=test_checksum.checksums h=172.16.10.53,u=checksums,p=checksums,P=3306 h=172.16.10.55,u=checksums,p=checksums,P=3306

此时,再检测主从数据一致性,发现主从数据已经一致了:

[root@mysql-master1 ~]# pt-table-checksum h=’172.16.10.53′,u=’checksums’,p=’checksums’,P=3306 -d test_0109 –nocheck-replication-filters –replicate=test_checksum.checksums –nocheck-binlog-format –nocheck-plan –recursion-method=hosts

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

01-12T14:54:16 0 0 0 1 0 0.018 test_0109.broker

01-12T14:54:16 0 0 4 1 0 0.008 test_0109.check_conistent

01-12T14:54:16 0 0 0 1 0 0.008 test_0109.partner_alert_rule

01-12T14:54:16 0 0 0 1 0 0.016 test_0109.proc_test

01-12T14:54:16 0 0 529 1 0 0.273 test_0109.project

附:
以下错误处理方式:

(1).要是表中没有唯一索引或则主键则会报错:
Can’t make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.

(2).要是从库有的数据,而主库没有,那这个数据怎么处理?
会给出删除SLAVE多余数据,和修复SLAVE缺失数据的SQL语句。

(3).该工具执行检查表动作,检查连接的帐号需要有很高的权限,在一般权限上需要加SELECT, PROCESS, SUPER, REPLICATION SLAVE等权限。
pt-table-checksm 配合pt-table-sync使用,在执行pt-table-sync数据同步之前,一定要执行pt-table-checksm命令检查。

(4).手动执行pt-table-checksum时会出现Diffs cannot be detected because no slaves were found. Please read the –recursion-method documentation for information.

从字面意思上看是,主库找不到从数据库。只需要在从库配置文件/具体目录/my.cnf中添加

report_host=slave_ip

report_port=slave_port

即可。

也可以在pt-table-checksum –recursion-method=hosts

默认是通过show processlist 找到host的值或show slave hosts 找到host的值。

mysql 5.6在gtid复制模式下复制错误,如何跳过??

这里就不粘贴复制的报错状态信息,简要说一下当前的环境:

系统为:red hat linux 6 (2.6.32)

mysql版本为:5.6.10

采用的一机多实例的部署方式

前言:

gtid 复制模式下,不要使用 replicate_wild_do_table 此参数,使用此类参数,会复制异常(即状态信息正常,但从机没有复制过来)

下面进入正题:

在我们遇到复制错误时,如果复制没有特别的错误时,我们一般采用 set global sql_slave_skip_counter=1 来跳过错误,但是,在mysql 5.6 的gtid模式下,此方法是没有作用的。我们采取如下方式:

1.查看从机的复制状态信息:(主要是以下几个值)

Retrieved_Gtid_Set: D68DBC47-3AAE-11E2-BC2F-842B2B699BDA:141-151

Executed_Gtid_Set: D68DBC47-3AAE-11E2-BC2F-842B2B699BDA:1-140

Retrieved_Gtid_Set项:记录了relay日志从Master获取了binlog日志的位置

Executed_Gtid_Set项:记录本机执行的binlog日志位置(如果是从机,包括Master的binlog日志位置和slave本身的binlog日志位置)

2.在从机上执行如下操作,以下所有操作,如果没有特别说明,均在从机上执行:

> reset master;

> reset slave all;

> set global grid_purged=’D68DBC47-3AAE-11E2-BC2F-842B2B699BDA:1-141′;

> change master to master_host=’****’,master_user=’****’,master_password=’******’,master_port=3306,master_auto_position=1;

> start slave;

> show slave status\G;

此时再查看复制状态信息,重复以上操作 set global grid_purged=’D68DBC47-3AAE-11E2-BC2F-842B2B699BDA:1-141′; 此值依次往后推,直到复制正常为止

其它相关信息,可以参考下面博客链接:

http://imysql.cn/2014/07/31/mysql-faq-exception-replication-with-gtid.shtml