金沙国际官网_金沙国际平台登录

因为这个金沙国际官网_金沙国际平台登录网站与很多的大型澳门赌场都有合作,金沙国际官网_金沙国际平台登录尽职尽责,高效执行,保持好奇心,不断学习,追求卓越,点击进入金沙国际官网_金沙国际平台登录马上体验吧,所以现在也正式地开始了营业。

您的位置:金沙国际官网 > 数据库 > InnoDB锁冲突案例演示,存储过程和触发器的应用

InnoDB锁冲突案例演示,存储过程和触发器的应用

发布时间:2019-11-05 03:41编辑:数据库浏览(173)

           几天前收到某个业务项目,MySQL数据库逻辑备份mysqldump备份失败的邮件,本是在休假,但本着工作认真负责,7*24小时不间断运维的高尚职业情操,开始了DBA的排错之路(一开始数据库的备份都是成功的,巧的是我休假就出问题,怀疑是数据量又有增长)

     

    实验案例一:验证索引的作用

           首先我们了解下mysqldump备份,数据流向的一个过程:MySQL Server端从数据文件中检索出数据,然后分批将数据返回给mysqldump客户端,然后mysqldump再把数据写入到NFS上。一般情况下存储不是SSD或者是普通磁盘,那么向NFS上写入数据比Server端检索完数据发送给mysqldump客户端要慢得多,这就有可能mysqldump无法及时接收MySQL Server端发送过来的数据,导致Server端检索出来的数据在内存中积压等待发送。当超过等待的时间net_write_timeout(默认60s)时就连接断开,同时抛出错误。

    金沙国际官网,Preface

    1、首先创建一个数据量大的表,名称为“学生表”,分别有三列,学号,姓名和班级,如下图所示,学号为自动编号,班级为默认值“一班”。

     1、定位问题

     

    金沙国际官网 1

           登录到机器上,先查看了备份文件的逻辑,再查看备份的日志和备份文件大小,确认备份失败并定位到是备份命令mysqldump行执行一半失败(根据备份文件较之前的几天减少了一半且脚本运行日志来断定)。凌晨的备份无效,因而手动触发脚本执行备份,发现了报错如下:

        I've demontstrated several InnoDB locking cases in my previous blog.I'm gonna do the rest tests about InnoDB locks.

    2、向表中插入大量数据,数据越多,验证索引的效果越好。

    [root@mysql_query hk_sa]# bash /opt/shells/mysqldump.sh    
    Warning: Using a password on the command line interface can be insecure.
    mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `rrd_api_log` at row: 2821866
    

     

    使用语句完成:While 1>0  Insert into 学生表(姓名)  values(‘于美丽’)

    2、排查问题

    Procedure

    上面语句是一个死循环,除非强制结束,如果1大于0就会一直向表中插入姓名

          查看备份失败的表的行数为4982704,查看手动备份失败处的行信息是2017-02-05 04:03:18写入,之前都没有出现过这个备份失败的问题。于是开始怀疑是不是最近数据增长太大或者表的字段太宽的问题(其他数据库的表更大,有的甚至达到400G也没有出现过这个问题,表数据量太大的可能性不大,但单行备份失败,怀疑大字段的问题)

     

    如下图所示:

          查看表结构如下:

    Test table information and the relevant variables.

    金沙国际官网 2

    [root@localhost] | 08:42:21 | [heika0516] > desc rrd_api_log;
    +---------------+-------------+------+-----+---------+----------------+
    | Field         | Type        | Null | Key | Default | Extra          |
    +---------------+-------------+------+-----+---------+----------------+
    | id            | bigint(20)  | NO   | PRI | NULL    | auto_increment |
    | api_command   | varchar(30) | NO   |     | NULL    |                |
    | request_info  | text        | NO   |     | NULL    |                |
    | response_info | text        | NO   |     | NULL    |                |
    | create_time   | datetime    | NO   |     | NULL    |                |
    +---------------+-------------+------+-----+---------+----------------+
    5 rows in set (0.01 sec) 
    
     1 zlm@192.168.56.100:3306 [zlm]>show create table t1G
     2 *************************** 1. row ***************************
     3        Table: t1
     4 Create Table: CREATE TABLE `t1` (
     5   `c1` int(10) unsigned NOT NULL DEFAULT '0',
     6   `c2` int(10) unsigned NOT NULL DEFAULT '0',
     7   `c3` int(10) unsigned NOT NULL DEFAULT '0',
     8   `c4` int(10) unsigned NOT NULL DEFAULT '0',
     9   PRIMARY KEY (`c1`),
    10   KEY `c2` (`c2`)
    11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    12 1 row in set (0.00 sec)
    13 
    14 zlm@192.168.56.100:3306 [zlm]>select * from t1;
    15 +----+----+----+----+
    16 | c1 | c2 | c3 | c4 |
    17 +----+----+----+----+
    18 |  0 |  0 |  0 |  0 |
    19 |  1 |  1 |  1 |  0 |
    20 |  3 |  3 |  3 |  0 |
    21 |  4 |  2 |  2 |  0 |
    22 |  6 |  2 |  5 |  0 |
    23 |  8 |  6 |  6 |  0 |
    24 | 10 |  4 |  4 |  0 |
    25 +----+----+----+----+
    26 8 rows in set (0.01 sec)
    27 
    28 zlm@192.168.56.100:3306 [zlm]>select @@transaction_isolation;
    29 +-------------------------+
    30 | @@transaction_isolation |
    31 +-------------------------+
    32 | REPEATABLE-READ         |
    33 +-------------------------+
    34 1 row in set (0.00 sec)
    35 
    36 zlm@192.168.56.100:3306 [(none)]>show variables like 'innodb_status_output_locks';
    37 +----------------------------+-------+
    38 | Variable_name              | Value |
    39 +----------------------------+-------+
    40 | innodb_status_output_locks | ON    |
    41 +----------------------------+-------+
    42 1 row in set (0.00 sec)
    

    等待5分钟左右,打开表的属性,查看表的行数,当前为1032363,如下图所示:

         方向定了剩下的就是验证自己的猜想了,于是我开始查找资料,果然被我百度到了一些有价值的东西,包括MySQL官方的一些说法:

     

    金沙国际官网 3

         然而新的问题又出来了,很多前辈都讲增大net_write_timeout的值,Server端会消耗更多的内存甚至导致swap的使用影响性能,但又不确定是不是参数调整所致,存在潜在的风险。不过这种说法我并不同意,因为我执行的过程中发现,MEM的free反而变多了,你没有听错真的变多了。

    Test 1: session1 update while session2 insert.

    3、 使用语句查询第900000行的数据,Select * from 学生表 Where 学号=900000

    #mysqldump备份执行前
    [root@mysql_query hk_sa]# free -m
                 total       used       free     shared    buffers     cached
    Mem:         16080      13305       2775          0        121       3729
    -/+ buffers/cache:       9454       6626
    Swap:         8191        349       7842
    
    #增大了net_write_timeout的值
    [root@localhost] | 08:51:53 | [(none)] > set @@global.net_write_timeout=500;
    Query OK, 0 rows affected (0.01 sec)
    
    #bash完脚本发现备份OK的
    [root@mysql_query hk_sa]# ls -lh /opt/app/mysql/data/heika0516/rrd_api_log.ibd 
    -rw-r--r-- 1 mysql mysql 4.1G Aug  7 22:03 /opt/app/mysql/data/heika0516/rrd_api_log.ibd
    
    #mysqldump备份执行后
    [root@mysql_query hk_sa]# free -m
                 total       used       free     shared    buffers     cached
    Mem:         16080      12434       3646          0         93       2890
    -/+ buffers/cache:       9450       6630
    Swap:         8191        349       7842
    
     1 //Session1:
     2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c4=20 where c2>=4;select * from t1 where c2>=4;
     3 Query OK, 0 rows affected (0.00 sec)
     4 
     5 Query OK, 2 rows affected (0.00 sec)
     6 Rows matched: 2  Changed: 2  Warnings: 0
     7 
     8 +----+----+----+----+
     9 | c1 | c2 | c3 | c4 |
    10 +----+----+----+----+
    11 | 10 |  4 |  4 | 20 |
    12 |  8 |  6 |  6 | 20 |
    13 +----+----+----+----+
    14 2 rows in set (0.00 sec)
    15 
    16 //Lock information of session1.
    17 TABLE LOCK table `zlm`.`t1` trx id 2997544 lock mode IX
    18 RECORD LOCKS space id 178 page no 4 n bits 80 index c2 of table `zlm`.`t1` trx id 2997544 lock_mode X
    19 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
    20  0: len 8; hex 73757072656d756d; asc supremum;;
    21 
    22 Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
    23  0: len 4; hex 00000006; asc     ;;
    24  1: len 4; hex 00000008; asc     ;;
    25 
    26 Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
    27  0: len 4; hex 00000004; asc     ;;
    28  1: len 4; hex 0000000a; asc     ;;
    29 
    30 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997544 lock_mode X locks rec but not gap
    31 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
    32  0: len 4; hex 00000008; asc     ;;
    33  1: len 6; hex 0000002dbd28; asc    - (;;
    34  2: len 7; hex 3a0000012727bb; asc :   '' ;;
    35  3: len 4; hex 00000006; asc     ;;
    36  4: len 4; hex 00000006; asc     ;;
    37  5: len 4; hex 00000014; asc     ;;
    38 
    39 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
    40  0: len 4; hex 0000000a; asc     ;;
    41  1: len 6; hex 0000002dbd28; asc    - (;;
    42  2: len 7; hex 3a000001272799; asc :   '' ;;
    43  3: len 4; hex 00000004; asc     ;;
    44  4: len 4; hex 00000004; asc     ;;
    45  5: len 4; hex 00000014; asc     ;;
    46 
    47 //Session2:
    48 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,5,10,10;
    49 Query OK, 0 rows affected (0.00 sec)
    50 
    51 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    52 
    53 //Session2 was block because of the gap lock(c2>=4 hold a supremum lock) which was holed by session 1.The value c2=5 which session2 want to insert is conficted with the range lock.
    

    金沙国际官网 4

      到此为止,mysqldump备份失败确实是解决,但是前辈们反映的问题是消耗更多的内存,到我这反而释放了更多的内存,这样修改参数终究是会存在安全隐患,且这个参数会影响所有的会话连接。那就先不管了,过个好假期再搞。不过我始终有个疑问,我这维护的400G的大表,且部分表比这更大,也有大字段就没出现过这个问题,怎么会突然出现呢,因此我怀疑不是表数据过多的问题,还是大字段的问题。

     

    4、打开“sql server profiler ”工具进行跟踪,如下图所示:

           休假回来后,立马开始test排错的工作,先分析确认好切入点,我就把net_write_timeout的值改为默认60,但这样备份肯定会失败的,于是想到了max_allowed_packet参数,但全局调整这个参数,对网络发包和各个会话也有影响,继续对mysqldump这个备份进行调研,居然让我发现了一个牛逼的可接参数max_allowed_packet,在mysqldump后面加了这个选项,值大小大家可以根据表的大小进行设置,我这里给的500M,至此问题彻底解决了,也没有改变参数的全局值影响会话。

    Test 2: session1 update while session2 insert.

    打开“sql server profiler ”工具查看跟踪的信息,发现查询时间很长,cpu工作了265毫秒,reads:读了8649次,writes:写了10次,duration:总计花费2336毫秒完成查询。

     1 //Session1:
     2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c4=20 where c2>=4;select * from t1 where c2>=4;
     3 Query OK, 0 rows affected (0.00 sec)
     4 
     5 Query OK, 2 rows affected (0.00 sec)
     6 Rows matched: 2  Changed: 2  Warnings: 0
     7 
     8 +----+----+----+----+
     9 | c1 | c2 | c3 | c4 |
    10 +----+----+----+----+
    11 | 10 |  4 |  4 | 20 |
    12 |  8 |  6 |  6 | 20 |
    13 +----+----+----+----+
    14 2 rows in set (0.00 sec)
    15 
    16 //Session2:
    17 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,2,10,10;
    18 Query OK, 0 rows affected (0.00 sec)
    19 
    20 Query OK, 1 row affected (0.00 sec)
    21 Records: 1  Duplicates: 0  Warnings: 0
    22 
    23 //This time the transaction in session2 was committed immediately.The value c2=2 didn't conflict with the range lock in session1.
    

    金沙国际官网 5

     

    为了下面分析文件更准确,多执行几次Select* from 学生表 Where 学号=900000

    本文由金沙国际官网发布于数据库,转载请注明出处:InnoDB锁冲突案例演示,存储过程和触发器的应用

    关键词:

上一篇:到底是什么意思,批量拼脚本神器

下一篇:没有了