show engine innodb status详解

分析的是安装在centos7下的mysql,版本为5.6.37。innodb存储引擎输出内容分为多段,每一段对应innodb存储引擎不同类信息,
可以分析目前mysql数据库运行状态,结合linux系统top、vmstat、iostat、free等命令,可用于高并发、高性能业务场景调优。
“#”后面内容对上面输出内容进行注释。

在linux系统shell下,输入mysql -u用户名 -p密码,进入数据库,敲下show engine innodb status。
mysql> show ENGINE INNODB status\G;

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2019-12-02 13:47:08 7f9b5deb6700 INNODB MONITOR OUTPUT   
#当前日期和时间。
=====================================
Per second averages calculated from the last 25 seconds  
#输出内容为统计平均值,25 seconds是计算出这一平均值的时间,即自上次输出以来的时间,或者是距上次内部复位的时长,
#两次采样之间的积累足够长并多次采样。
-----------------
BACKGROUND THREAD  
#nnoDB存储引擎的核心操作大部分都集中在Master Thread后台线程中。
-----------------
srv_master_thread loops: 9062857 srv_active, 0 srv_shutdown, 5005411 srv_idle
#srv_active为之前的每秒循环,srv_idle为每10秒的循环,srv_shutdown为停止的循环,通常为0,只在MySQL关闭时才会增
#加。上面可以看出主循环每秒进行了9062857次,每10秒进行了5005411次,每秒操作:每10秒操作的比例约为2:1。
srv_master_thread log flush and writes: 14068267
#负载低的情况下日志缓冲刷盘次数,14068267 ≈ 9062857+5005411。根据循环次数可大概判断当前数据库负载情况。如果每
#秒循环次数少,每10秒次数多,证明当前负载很低;如果每秒循环次数多,每10秒次数少,远大于10:1,证明当前负载很
#高,结合日志缓冲刷盘次数,说明目前负载不高。
----------
SEMAPHORES
事件计数器以及当前等待线程的列表,如果有性能上的瓶颈,可以使用这些信息来找出瓶颈
----------
OS WAIT ARRAY INFO: reservation count 90546997   
#os wait 的信息 ,reservation count 表示InnoDB产生了多少次OS WAIT,这行给出了关于操作系统等待数组的信息,它是
#一个插槽数组,innodb在数组里为信号量保留了一些插槽,操作系统用这些信号量给线程发送信号,使线程可以继续运行,
#以完成它们等着做的事情,这一行还显示出innodb使用了多少次操作系统的等待:保留统计(reservation count)显示了
#innodb分配插槽的频度,而信号计数(signal count)衡量的是线程通过数组得到信号的频度,操作系统的等待相对于空转
#等待(spin wait)要昂贵些。 
OS WAIT ARRAY INFO: signal count 1577174380   
#进行OS WAIT线程,接收到多少次信号(single)被唤醒,如果这个signal数值越大,可能是很多I/0等待或者是InnoDB争用问
#题(关于争用问题可能与OS调度有关,可尝试减少innodb_thread_concurrency)
Mutex spin waits 2055394099, rounds 4232876264, OS waits 57870094 
#spins:空转次数,通过innodb_sync_spin_loops控制,超过则转到OS waits;Mutex spin线程无法获取锁而进入Spin wait
# ,rounds是spin wait 进行轮询检查mutextes的次数,os wait 线程放弃spin-wait 进入挂起状态
RW-shared spins 247701292, rounds 1209346482, OS waits 15934440  
#RW-shared 共享锁
RW-excl spins 87500452, rounds 2820459524, OS waits 13902426   
#RW-excl 排他锁
Spin rounds per wait: 2.06 mutex, 4.88 RW-shared, 32.23 RW-excl 
#备注:要明白Innodb如何处理互斥量(Mutexes),以及什么是两步获得锁(two-step approach)。首先进程试图获得一个锁,
#如果此锁被它人占用。它就会执行所谓的spin wait,即所谓循环的查询”锁被释放了吗?”。如果在循环过程中,一直未得到
#锁释放的信息,则其转入OS WAIT,即所谓线程进入挂起(suspended)状态。直到锁被释放后,通过信号(singal)唤醒线程,
#Spin wait的消耗远小于OS waits。Spinwait利用cpu的空闲时间,检查锁的状态,OS Wait会有所谓content switch(即上
#下文切换),从CPU内核中换出当前执行线程以供其它线程使用。可以通过innodb_sync_spin_loops参数来平衡spin wait和
#os wait,不要担心空转等待,除非你在一秒里看到几十万个空转等待。可以考虑performance_schema库或者show engine 
#innodb mutex查看。
------------------------
LATEST FOREIGN KEY ERROR
#下面这一段外键错误的信息一般不会出现,除非你服务器上发生了外键错误,有时问题在于事务在插入,更新或删除一条记
#录时要寻找父表或子表,还有时候是当innodb尝试增加或删除一个外键或者修改一个已经存在的外键时,发现表之间类型不
#匹配,这部分输出对于调试与innodb不明确的外键错误发生的准确原因有帮助。
------------------------
2019-11-28 16:48:50 7f9b5cdf4700 Transaction:
TRANSACTION 2749083231, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 7786044, OS thread handle 0x7f9b5cdf4700, query id 2076526179 172.17.7.29 bip updating
DELETE FROM `qrtz_job_details`
Foreign key constraint fails for table `sdnx_bipdb`.`qrtz_triggers`:
,
  CONSTRAINT `qrtz_triggers_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) REFERENCES `qrtz_job_details` (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`)
Trying to delete or update in parent table, in index `PRIMARY` tuple:
DATA TUPLE: 12 fields;
 0: len 60; hex 6f72672e737072696e676672616d65776f726b2e7363686564756c696e672e71756172747a2e5363686564756c6572466163746f72794265616e2330; asc org.springframework.scheduling.quartz.SchedulerFactoryBean#0;;
 1: len 25; hex 61636365707442697a5472616465436f6c6c6563745461736b; asc acceptBizTradeCollectTask;;
 2: len 7; hex 44454641554c54; asc DEFAULT;;
 3: len 6; hex 0000a3dbae5f; asc      _;;
 4: len 7; hex 7d0000022c1750; asc }   , P;;
 5: SQL NULL;
 6: len 74; hex 636f6d2e76697665626573742e696e7465726e65742e62616e6b696e672e636f72652e667362702e71756172747a2e41636365707442697a5472616465436f6c6c65637451756172747a; asc com.vivebest.internet.banking.core.fsbp.quartz.AcceptBizTradeCollectQuartz;;
 7: len 1; hex 31; asc 1;;
 8: len 1; hex 31; asc 1;;
 9: len 1; hex 31; asc 1;;
 10: len 1; hex 31; asc 1;;
 11: len 273; hex aced0005737200156f72672e71756172747a2e4a6f62446174614d61709fb083e8bfa9b0cb020000787200266f72672e71756172747a2e7574696c732e537472696e674b65794469727479466c61674d61708208e8c3fbc55d280200015a0013616c6c6f77735472616e7369656e74446174617872001d6f72672e71756172747a2e7574696c732e4469727479466c61674d617013e62ead28760ace0200025a000564697274794c00036d617074000f4c6a6176612f7574696c2f4d61703b787000737200116a6176612e7574696c2e486173684d61700507dac1c31660d103000246000a6c6f6164466163746f724900097468726573686f6c6478703f40000000000010770800000010000000007800; asc     sr  org.quartz.JobDataMap           xr &org.quartz.utils.StringKeyDirtyFlagMap      ](   Z  allowsTransientDataxr  org.quartz.utils.DirtyFlagMap  . (v     Z  dirtyL  mapt  Ljava/util/Map;xp sr  java.util.HashMap      `    F  loadFactorI  thresholdxp?@      w         x ;;

But in child table `sdnx_bipdb`.`qrtz_triggers`, in index `SCHED_NAME`, there is a record:
PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 30; hex 6f72672e737072696e676672616d65776f726b2e7363686564756c696e67; asc org.springframework.scheduling; (total 60 bytes);
 1: len 25; hex 61636365707442697a5472616465436f6c6c6563745461736b; asc acceptBizTradeCollectTask;;
 2: len 7; hex 44454641554c54; asc DEFAULT;;
 3: len 28; hex 61636365707442697a5472616465436f6c6c65637454726967676572; asc acceptBizTradeCollectTrigger;;
 4: len 7; hex 44454641554c54; asc DEFAULT;;
#此处的错误为第一为,在删除qrtz_job_details的数据时,因为qrtz_triggers_ibfk_1对qrtz_job_details亦外键约束,
#删除时报错。
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-12-02 12:56:56 7f9b5d696700
#这里显示了最近一次发生死锁的日期和时间
*** (1) TRANSACTION:
TRANSACTION 2769703716, ACTIVE 0 sec starting index read
#这行表示事务2769703716,ACTIVE 0 sec表示事务处于活跃状态0s,starting index read表示正在使用索引读取数据行
mysql tables in use 7, locked 7
#这行表示事务2769703716正在使用7个表,且涉及锁的表有7个
LOCK WAIT 60 lock struct(s), heap size 13864, 23320 row lock(s)
#这行表示在等待60把锁,占用内存13864字节,涉及23320行记录,如果事务已经锁定了几行数据,这里将会有一行信息显
#示出锁定结构的数目(注意,这跟行锁是两回事)和堆大小,堆的大小指的是为了持有这些行锁而占用的内存大小,
#Innodb是用一种特殊的位图表来实现行锁的,从理论上讲,它可将每一个锁定的行表示为一个比特,经测试显示,每个锁
#通常不超过4比特
MySQL thread id 7966608, OS thread handle 0x7f9b5c697700, query id 2146854649 172.17.7.99 bip Sending data
#这行表示该事务的线程ID信息,操作系统句柄信息,连接来源、用户
update cs_recon_record_ext e INNER JOIN cs_recon_record t on e.RECON_SEQ_NO = t.SEQ_NO
                INNER JOIN cs_mer_stl_detail c  on t.ORDER_NO = c.ORDER_NO
                INNER JOIN cs_bank_pay_stl d on d.batch_no = c.BATCH_NO
                set ONE_IMPREST_REVERT_SEQ_NO = '20191202BSA0000001432',
                        ONE_IMPREST_REVERT_COLLECT_STATUS = 1
        where t.ORDER_DATE ='2019-12-01 00:00:00'
                and t.PAY_COMPANY = 'TENCENT'
                and t.BRANCH_NO = '237000045'
            and e.GATHER_STATUS = 1
                and e.ONE_IMPREST_REVERT_COLLECT_STATUS = 0
                and e.ONE_IMPREST_REVERT_STATUS = 0
        and t.SETTLE_STATUS = '1'
        and d.PAY_STL_STATUS IN ('2','4')
        and d.CUST_SET_DAY = '0'
                and t.CUST_SET_DAY= '0'
                and NOT EXISTS (select SEQ_NO from cs_branch_settlement_amt_advance b where b.SEQ_NO = e.ONE_IMPREST_REVERT_SEQ_NO)
#表示事务涉及的SQL
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
#这行信息表示第一个事务正在等待锁被授予
RECORD LOCKS space id 2609 page no 379932 n bits 96 index `PRIMARY` of table `sdnx_bipdb`.`cs_recon_record` trx id 2769703716 lock mode S locks rec but not gap waiting
#这行信息表示等待的锁是一个record lock,空间id是2609,页编号为379932,大概位置在页的96位处,锁发生在表
#`sdnx_bipdb`.`cs_recon_record` 的主键上,是一个S锁,但是不是gap lock。 waiting表示正在等待锁
Record lock, heap no 12 PHYSICAL RECORD: n_fields 59; compact format; info bits 0
#这部分剩下的内容只对调试才有用。
 0: len 21; hex 504d52435232303139313230313030303137333035; asc PMRCR2019120100017305;;
 1: len 6; hex 0000a504e91d; asc       ;;
 2: len 7; hex 450001eaa82138; asc E    !8;;
 3: len 6; hex 4e5830343836; asc NX0486;;
 4: len 12; hex 313031383036313330313133; asc 101806130113;;
 5: len 8; hex 3230313931323031; asc 20191201;;
 6: len 18; hex 4f4e45594152445041595f54454e43454e54; asc ONEYARDPAY_TENCENT;;
 7: len 1; hex 31; asc 1;;
 8: SQL NULL;
 9: len 3; hex 8fc782; asc    ;;
 10: len 2; hex 3030; asc 00;;
 11: len 25; hex 4e583034383656363530373039353033363435333139313638; asc NX0486V650709503645319168;;
 12: len 3; hex 434e59; asc CNY;;
 13: len 3; hex 434e59; asc CNY;;
 14: len 6; hex 800000012c00; asc     , ;;
 15: len 6; hex 800000000000; asc       ;;
 16: len 6; hex 800000000000; asc       ;;
 17: len 6; hex 800000000000; asc       ;;
 18: len 6; hex 800000000000; asc       ;;
 19: len 6; hex 800000000000; asc       ;;
 20: len 2; hex 3030; asc 00;;
 21: len 25; hex 4e583034383656363530373039353033363435333139313638; asc NX0486V650709503645319168;;
 22: len 6; hex 800000012b28; asc     +(;;
 23: len 6; hex 80000000003c; asc      <;;
 24: len 6; hex 800000012c00; asc     , ;;
 25: len 3; hex 8fc781; asc    ;;
 26: len 3; hex 8fc782; asc    ;;
 27: len 5; hex 99a4c40000; asc      ;;
 28: len 3; hex 8fc781; asc    ;;
 29: len 5; hex 99a4c2f024; asc     $;;
 30: len 1; hex 4d; asc M;;
 31: SQL NULL;
 32: len 1; hex 31; asc 1;;
 33: SQL NULL;
 34: len 1; hex 31; asc 1;;
 35: SQL NULL;
 36: len 3; hex 434e59; asc CNY;;
 37: len 6; hex 800000012c00; asc     , ;;
 38: len 6; hex 800000000000; asc       ;;
 39: len 6; hex 800000000000; asc       ;;
 40: len 6; hex 800000000000; asc       ;;
 41: len 21; hex 504d4d555332303139313230313030303134363132; asc PMMUS2019120100014612;;
 42: len 1; hex 4e; asc N;;
 43: len 21; hex 504d414e5432303139313132303030303030363137; asc PMANT2019112000000617;;
 44: len 20; hex 504d4a4e32303139313230323030323535333631; asc PMJN2019120200255361;;
 45: len 1; hex 31; asc 1;;
 46: len 5; hex 99a4c49e5f; asc     _;;
 47: len 3; hex 313031; asc 101;;
 48: len 3; hex 313031; asc 101;;
 49: SQL NULL;
 50: SQL NULL;
 51: SQL NULL;
 52: SQL NULL;
 53: len 1; hex 30; asc 0;;
 54: len 9; hex 323337303030303435; asc 237000045;;
 55: len 7; hex 54454e43454e54; asc TENCENT;;
 56: SQL NULL;
 57: SQL NULL;
 58: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2769692772, ACTIVE 43 sec fetching rows
#事务2处于活跃状态43s
mysql tables in use 5, locked 5
#正在使用5个表,涉及锁的表有5个
210287 lock struct(s), heap size 19314216, 10174859 row lock(s)
#这行表示在等待210287把锁,占用内存19314216字节,涉及10174859行记录,如果事务已经锁定了几行数据,这里将
#会有一行信息显示出锁定结构的数目(注意,这跟行锁是两回事)和堆大小,堆的大小指的是为了持有这些行锁而占
#用的内存大小,Innodb是用一种特殊的位图表来实现行锁的,从理论上讲,它可将每一个锁定的行表示为一个比特,
#经测试显示,每个锁通常不超过4比特
MySQL thread id 7965628, OS thread handle 0x7f9b5d696700, query id 2146822027 172.17.7.99 bip Sending data
#这行表示该事务的线程ID信息,操作系统句柄信息,连接来源、用户
update CS_RECON_RECORD t INNER JOIN cs_recon_record_ext e ON t.SEQ_No= e.RECON_SEQ_NO INNER JOIN
        CS_ACCEPT_BIZ_INFO k ON t.ACCEPT_BIZ_NO = k.ACCEPT_BIZ_NO and t.MER_CUST_NO = k.MER_CUST_NO
        INNER JOIN cs_cust_stl_info s on s.ACCEPT_BIZ_NO = k.ACCEPT_BIZ_NO and s.MER_CUST_NO = k.MER_CUST_NO
        LEFT JOIN cs_account_notice p ON t.AC_NOTICE_SEQ_NO = p.SEQ_NO
        SET AC_FLAG = '1',REAL_CLEAR_DATE = '2019-12-02',CLEAR_JOURNAL_NO = 'PMJN2019120200338993' 
        where 
        t.CRE_DTE >= '2019-11-25 13:10:00.037'
        AND t.CRE_DTE < '2019-12-02 13:10:00.037'
        AND AC_FLAG IN('0','2')
        AND PAY_TYPE = '1'
        AND e.GATHER_STATUS = 1
        AND p.CONFIRM_STATUS = '1'
        AND s.CUST_CLEAR_TYPE = '00'
*** (2) HOLDS THE LOCK(S):
#下面这部分是事务2的持有锁信息,持有事务1正在等待的锁。
RECORD LOCKS space id 2609 page no 379932 n bits 96 index `PRIMARY` of table `sdnx_bipdb`.`cs_recon_record` trx id 2769692772 lock_mode X locks rec but not gap
#这行信息表示等待的锁是一个record lock,空间id是2609,页编号为379932,大概位置在页的96位处,锁发生在表
#`sdnx_bipdb`.`cs_recon_record` 的主键上,是一个S锁,但是不是gap lock。 waiting表示正在等待锁
Record lock, heap no 2 PHYSICAL RECORD: n_fields 59; compact format; info bits 0
 0: len 21; hex 504d52435232303139313230313030303137323935; asc PMRCR2019120100017295;;
 1: len 6; hex 0000a504e91d; asc       ;;
 2: len 7; hex 450001eaa81738; asc E     8;;
 3: len 6; hex 4e5830303534; asc NX0054;;
 4: len 12; hex 313031393130323130333633; asc 101910210363;;
 5: len 8; hex 3230313931323031; asc 20191201;;
 6: len 18; hex 4f4e45594152445041595f54454e43454e54; asc ONEYARDPAY_TENCENT;;
 7: len 1; hex 31; asc 1;;
 8: SQL NULL;
 9: len 3; hex 8fc782; asc    ;;
 10: len 2; hex 3030; asc 00;;
 11: len 25; hex 4e583030353456363530373039323631363633343639353638; asc NX0054V650709261663469568;;
 12: len 3; hex 434e59; asc CNY;;
 13: len 3; hex 434e59; asc CNY;;
 14: len 6; hex 80000001fc00; asc       ;;
 15: len 6; hex 800000000000; asc       ;;
 16: len 6; hex 800000000000; asc       ;;
 17: len 6; hex 800000000000; asc       ;;
 18: len 6; hex 800000000000; asc       ;;
 19: len 6; hex 800000000000; asc       ;;
 20: len 2; hex 3030; asc 00;;
 21: len 25; hex 4e583030353456363530373039323631363633343639353638; asc NX0054V650709261663469568;;
 22: len 6; hex 80000001fa62; asc      b;;
 23: len 6; hex 800000000102; asc       ;;
 24: len 6; hex 80000001fc00; asc       ;;
 25: len 3; hex 8fc781; asc    ;;
 26: len 3; hex 8fc782; asc    ;;
 27: len 5; hex 99a4c40000; asc      ;;
 28: len 3; hex 8fc781; asc    ;;
 29: len 5; hex 99a4c2f005; asc      ;;
 30: len 1; hex 4d; asc M;;
 31: SQL NULL;
 32: len 1; hex 31; asc 1;;
 33: SQL NULL;
 34: len 1; hex 31; asc 1;;
 35: SQL NULL;
 36: len 3; hex 434e59; asc CNY;;
 37: len 6; hex 80000001fc00; asc       ;;
 38: len 6; hex 800000000000; asc       ;;
 39: len 6; hex 800000000000; asc       ;;
 40: len 6; hex 800000000000; asc       ;;
 41: len 21; hex 504d4d555332303139313230313030303134333831; asc PMMUS2019120100014381;;
 42: len 1; hex 4e; asc N;;
 43: len 21; hex 504d414e5432303139313132303030303030363137; asc PMANT2019112000000617;;
 44: len 20; hex 504d4a4e32303139313230323030323535333631; asc PMJN2019120200255361;;
 45: len 1; hex 31; asc 1;;
 46: len 5; hex 99a4c49e5f; asc     _;;
 47: len 3; hex 313031; asc 101;;
 48: len 3; hex 313031; asc 101;;
 49: SQL NULL;
 50: SQL NULL;
 51: SQL NULL;
 52: SQL NULL;
 53: len 1; hex 30; asc 0;;
 54: len 9; hex 323333303030303037; asc 233000007;;
 55: len 7; hex 54454e43454e54; asc TENCENT;;
 56: SQL NULL;
 57: SQL NULL;
 58: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2609 page no 378220 n bits 112 index `PRIMARY` of table `sdnx_bipdb`.`cs_recon_record` trx id 2769692772 lock_mode X locks rec but not gap waiting
Record lock, heap no 38 PHYSICAL RECORD: n_fields 59; compact format; info bits 0
 0: len 21; hex 504d52435232303139313230313030303032383633; asc PMRCR2019120100002863;;
 1: len 6; hex 0000a504e91d; asc       ;;
 2: len 7; hex 450001f05e383a; asc E   ^8:;;
 3: len 6; hex 4e5830343836; asc NX0486;;
 4: len 12; hex 313031393033323730303434; asc 101903270044;;
 5: len 8; hex 3230313931323031; asc 20191201;;
 6: len 17; hex 4f4e45594152445041595f414c49504159; asc ONEYARDPAY_ALIPAY;;
 7: len 1; hex 31; asc 1;;
 8: SQL NULL;
 9: len 3; hex 8fc782; asc    ;;
 10: len 2; hex 3030; asc 00;;
 11: len 25; hex 4e583034383656363530363230383239363837363131333932; asc NX0486V650620829687611392;;
 12: len 3; hex 434e59; asc CNY;;
 13: len 3; hex 434e59; asc CNY;;
 14: len 6; hex 80000007d000; asc       ;;
 15: len 6; hex 800000000000; asc       ;;
 16: len 6; hex 800000000000; asc       ;;
 17: len 6; hex 800000000000; asc       ;;
 18: len 6; hex 800000000000; asc       ;;
 19: len 6; hex 800000000000; asc       ;;
 20: len 2; hex 3030; asc 00;;
 21: len 25; hex 4e583034383656363530363230383239363837363131333932; asc NX0486V650620829687611392;;
 22: len 6; hex 80000007cc00; asc       ;;
 23: len 6; hex 800000000400; asc       ;;
 24: len 6; hex 80000007d000; asc       ;;
 25: len 3; hex 8fc781; asc    ;;
 26: len 3; hex 8fc782; asc    ;;
 27: len 5; hex 99a4c40000; asc      ;;
 28: len 3; hex 8fc781; asc    ;;
 29: len 5; hex 99a4c292a0; asc      ;;
 30: len 1; hex 4d; asc M;;
 31: SQL NULL;
 32: len 1; hex 31; asc 1;;
 33: SQL NULL;
 34: len 1; hex 31; asc 1;;
 35: SQL NULL;
 36: len 3; hex 434e59; asc CNY;;
 37: len 6; hex 80000007d000; asc       ;;
 38: len 6; hex 800000000000; asc       ;;
 39: len 6; hex 800000000000; asc       ;;
 40: len 6; hex 800000000000; asc       ;;
 41: len 21; hex 504d4d555332303139313230313030303032393431; asc PMMUS2019120100002941;;
 42: len 1; hex 4e; asc N;;
 43: len 21; hex 504d414e5432303139313132303030303030363138; asc PMANT2019112000000618;;
 44: len 20; hex 504d4a4e32303139313230323030323535333631; asc PMJN2019120200255361;;
 45: len 1; hex 31; asc 1;;
 46: len 5; hex 99a4c4a151; asc     Q;;
 47: len 3; hex 313031; asc 101;;
 48: len 3; hex 313031; asc 101;;
 49: SQL NULL;
 50: SQL NULL;
 51: SQL NULL;
 52: SQL NULL;
 53: len 1; hex 30; asc 0;;
 54: len 9; hex 323337303030303435; asc 237000045;;
 55: len 6; hex 414c49504159; asc ALIPAY;;
 56: SQL NULL;
 57: SQL NULL;
 58: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
#这里回滚事务1,死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象。正常死
#锁会自动释放,innodb有一个内在的死锁检测工具,当死锁超过一定时间后,会回滚其中一个事务,innodb_lock_wait
#_timeout可配置死锁等待超时时间。死锁在两情况下最容易产生:高并发同时操作同一条数据存在主键和辅助索引,
#加锁顺序相反,避免死锁方法即降低并发,操作数据时使加锁顺序相同。
------------
TRANSACTIONS
------------
Trx id counter 2770163002
#这行表示当前事务ID,这是一个系统变量,每创建一个新事务都会增加
Purge done for trx's n:o < 2770162997 undo n:o < 0 state: running but idle
#这是innodb清除旧MVCC行时所用的事务ID,将这个值和当前事务ID进行比较,就可以知道有多少老版本的数据未被
#清除。这个数字多大才可以安全的取值没有硬性和速成的规定,如果数据没做过任何更新,那么一个巨大的数字也
#不意味着有未清除的数据,因为实际上所有事务在数据库里查看的都是同一个版本的数据(此时只是事务ID在增加,
#而数据没有变更),另一方面,如果有很多行被更新,那每一行就会有一个或多个版本留在内存里,减少此类开销
#的最好办法就是确保事务已完成就立即提交,不要让它长时间地处于打开状态,因为一个打开的事务即使不做任何
#操作,也会影响到innodb清理旧版本的行数据。 undo n:o < 0这个是innodb清理进程正在使用的撤销日志编号,
#为0 0时说明清理进程处于空闲状态。
History list length 1853  
#历史记录的长度,即位于innodb数据文件的撤销空间里的页面的数目,如果事务执行了更新并提交,这个数字就会
#增加,而当清理进程移除旧版本数据时,它就会减少,清理进程也会更新Purge done for.....这行中的数值。
History list length 396
#记录了undo spaces内未清掉的事务个数,Purge的原则是记录没有被其它事务继续使用。
LIST OF TRANSACTIONS FOR EACH SESSION:
#每个session的事务状态
---TRANSACTION 0, not started
#每个事务的第一行以事务的ID和状态开始,not started表示这个事务已经提交并且没有再发起影响事务的语句,
#可能刚好空闲
MySQL thread id 7968566, OS thread handle 0x7f9b5deb6700, query id 2148384254 localhost root init
#然后每个事务的第二行是一些线程等信息,MySQL thread id <数字>部分和是hi用show full processlist;命
#令看到的id列相同。紧随其后的是一个内部查询id和一些连接信息,这些信息同样与show full processlist中的
#输出相同。这行显示次事务处于活跃状态已经188s,可能的所有状态有not started,active,prepared和committed
#in memory,一旦事务日志落盘了就会变成not started状态。在时间后面会显示出当前事务正在做什么(在这里为空
#没有显示出来),在源代码中有超过30个字符串常量可以显示在时间后面,如:fetching,preparing.
---TRANSACTION 2770152104, not started
MySQL thread id 7968569, OS thread handle 0x7f9b0b1c7700, query id 2148352067 172.17.7.99 bip
---TRANSACTION 2770135769, not started
MySQL thread id 7968552, OS thread handle 0x7f9b5c75a700, query id 2148274697 172.17.7.99 bip
---TRANSACTION 2770163000, not started
MySQL thread id 2, OS thread handle 0x7f9b64251700, query id 2095088115 Slave has read all relay log; waiting for the slave I/O thread to update it
#当前活跃的事务状态为ACTIVE,事务的详细信息,包括线程ID、执行时间、用户、SQL等。正在使用1个表,涉及
#锁的表0个。
--------
FILE I/O
#在InnoDB中大量使用了AIO(Async IO)来处理IO 请求,IO Thread主要是负责这些IO请求的回调处理,通过调用
#fsync()函数协调内存与磁盘之间的数据。
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)  
#insert buffer thread:负责插入缓冲合并,如:记录被从插入缓冲合并到表空间中
I/O thread 1 state: waiting for completed aio requests (log thread)
#log thread	负责异步刷新事务日志
I/O thread 2 state: waiting for completed aio requests (read thread)
......
I/O thread 33 state: waiting for completed aio requests (read thread)
#read thread:执行预读操作以尝试预先读取innodb预感需要的数据,数量为16
I/O thread 34 state: waiting for completed aio requests (write thread)
......
I/O thread 65 state: waiting for completed aio requests (write thread
#write thread:刷新脏页缓冲,16个write thread
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,
#读线程和写线程挂起操作的数目等,aio的意思是异步I/O
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
#insert buffer thread挂起的fsync()操作数目等
Pending flushes (fsync) log: 0; buffer pool: 0
#log thread挂起的fsync()操作数目等,当前被挂起的读和写操作数
25415664 OS file reads, 1131697975 OS file writes, 482342270 OS fsyncs
#这行显示了读,写和fsync()调用执行的数目,在你的机器环境负载下这些绝对值可能会有所不同,因此更重要
#的是监控它们过去一段时间内是如何改变的。
0.04 reads/s, 16384 avg bytes/read, 408.62 writes/s, 278.51 fsyncs/s
#这行显示了在头部显示的时间(指的是第1部分的时间)段内的每秒平均值。
#注:三行挂起读写线程、缓冲池线程、日志线程的统计信息的值是检测I/O受限的应用的一个好方法,如果这些
#I/O大部分有挂起操作,那么负载可能I/O受限。使用参数:innodb_read_io_threads和innodb_write_io_threads
#两个变量来配置读写线程的数量,默认各4个线程,已调整为32个线程。
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
#INSERT BUFFER即合并插入缓存,从innodb 1.0.x版本开始引入Change Buffer,是INSERT BUFFER升级版,即
#MySQL 5.1.x以上版本都支持,不仅包括INSERT BUFFER,还包括UPDATE BUFFER、DELETE BUFFER、PURGE BUFFER。
-------------------------------------
Ibuf: size 1, free list len 67188, seg size 67190, 4246428 merges
#这行显示了关于size(size 12代表了已经合并记录页的数量)、free list(代表了插入缓冲中空闲列表长度
#)和seg size大小(seg size 27572显示了当前insert buffer的长度,大小为#*16K=440M左右)的信息。
#4246428 merges代表合并插入的次数
merged operations:
#这个标签下的一行信息insert,delete mark,delete分别表示merge操作合并了多少个insert buffer,delete buffer,
#purge buffer
insert 5494185, delete mark 373193488, delete 1209235
#insert代表Insert Buffer;delete mark代表Delete Buffer;delete代表Purge Buffer;
discarded operations:
#这个标签下的一行信息表示当change buffer发生merge时表已经被删除了,就不需要再将记录合并到辅助索引
#中了
insert 0, delete mark 0, delete 
Hash table size 203997713, node heap has 237650 buffer(s
#这行显示了自使用哈希索引的状态,其中,Hash table size203997713表示AHI的大小,node heap has 237650 
#buffer(s)表示AHI的使用情况
46458.78 hash searches/s, 4908.24 non-hash searches/s
#这行显示了在头部第1部分提及的时间内Innodb每秒完成了多少哈希索引操作,46458.78 hash searches/s表示
#每秒使用AHI搜索的情况,4908.24 non-hash searches/s表示每秒没有使用AHI搜索的情况(因为哈希索引只能用
#于等值查询,而范围查询,模糊查询是不能使用哈希索引的。),通过hash searches: non-hash searches的比例
#大概可以了解使用哈希索引后的效率,哈希索引查找与非哈希索引查找的比例仅供参考,自适应哈希索引无法配
#置,但是可以通过innodb_adaptive_hash_index=ON|OFF参数来选择是否需要这个特性。
#innodb从1.0.x开始引入change buffer,可以视为insert buffer的升级,从这个版本开始,innodb可以对DML操
#作(insert,delete,update)都进行缓冲,他们分别是insert buffer,delete buffer,purge buffer,当然和之前
#insert buffer一样,change buffer适用对象仍然是非唯一索引的辅助索引,因为没有update buffer,所以对
#一条记录进行update的操作可以分为两个过程:A:将记录标记为删除B:真正将记录删除因此,delete buffer对
#应update 操作的第一个过程,即将记录标记为删除,purge buffer对应update的第二个过程,即将记录真正地删除
---
LOG
---
Log sequence number 2633765360682
#Log sequence number	最新产生的日志序列号
Log flushed up to   2633765360553
#Log flushed up to	已刷到磁盘的重做日志的日志号
Pages flushed up to 2633759231954
#Pages flushed up to	已刷到磁盘的页的日志号
Last checkpoint at  2633759194338
#Last checkpoint at	最后一次检查点位置,数据和日志一致的状态
#这行显示了上一次检查点的位置(一个检查点表示一个数据和日志文件都处于一致状态的时刻,并且能用于恢复
#数据),如果上一次检查点落后与上一行太多,并且差异接近于事务日志文件的大小,Innodb会触发“疯狂刷”,
#这对性能而言非常糟糕。
0 pending log writes, 0 pending chkp writes
#当前挂起的日志读写操作,可以将这行的值与第7部分FILE I/O对应的值做比较,以了解你的I/O有多少是由于日
#志系统引起的。
191052681 log i/o's done, 248.72 log i/o's/second
#这行显示了日志操作的统计和每秒日志I/O数,可以将这行的值与第7部分FILE I/O对应的值做比较,以了解你
#的I/O有多少是由于日志系统引起的。
#备注:InnoDB事务采用Write-Ahead log策略,即事务在提交时,先写重做日志,再修改页。Write-Ahead Log:
#如果一个页在写入磁盘时,必须先将内存中小于该页LSN的日志先写入到磁盘中。重做日志有LSN、每个页有LSN、
#Checkpoint也有LSN。LSN记录的是重做日志的总量,单位是字节。以下三种情况会将重做日志缓存刷到重做日志
#文件:Master Thread 每秒刷重做日志缓存到重做日志文件,innodb_flush_log_at_trx_commit=1时,每次事务
#提交刷重做日志缓存到重做日志文件,重做日志缓冲池剩余空间小于1/2时,刷重做日志缓存到重做日志文件
----------------------
BUFFER POOL AND MEMORY
#innodb_buffer_pool包含数据页、索引页、undo页、insert buffer、数据字典、自适应哈希索引、锁信息等。
#数据库缓冲池是通过LRU列表管理的。
----------------------
Total memory allocated 105495134208; in additional pool allocated 0
#这行显示了由innodb分配的总内存,以及其中多少是额外内存池分配,额外内存池仅分配了其中很小一部分内
#存,由内部内存分配器分配,现在的innodb版本一般使用操作系统的内存分配器,但老版本使用自己的,这是
#由于在那个时代有些操作系统并未提供一个非常好的内存分配实现。
Dictionary memory allocated 2563260
#为innodb数据字典分配的内存数(byte)
Buffer pool size   6291452
#从这行开始的下面4行显示缓冲池度量值,以页为单位,度量值有总的缓冲池大小,空闲页数,分配用来存储数
#据库页的页数,以及脏数据库页数。6291452*16K,共有96G的缓冲池
Free buffers       9330
#这行显示了缓冲池空闲页数,lru列表中的空闲页数量
Database pages     5709243
#lru列表中的非空闲页数量,显示了分配用来存储数据库页的页数,即,表示LRU列表中页的数量,包含young 
#sublist和old sublist
Old database pages 2107349
# 这行显示了LRU中的old sublist部分页的数量
Modified db pages  1995
#脏页的数量
Pending reads 0
#挂起读的数量
Pending writes: LRU 0, flush list 0, single page 0
#这行显示了挂起写的数量
#备注:这里挂起的读和写操作并不与FILE I/O部分的值匹配,因为Innodb可能合并许多的逻辑读写操作到一个
#物理I/O操作中,LRU代表最近使用到的被挂起数量,它是通过冲刷缓冲中不经常使用的页来释放空间以供给经
#常使用的页的一种方法,冲刷列表flush list存放着检查点处理需要冲刷的旧页被挂起的数量,单页single page
#被挂起的数量(single page写是独立的页面写,不会被合并)。
Pages made young 551630980, not young 1037946390
2.56 youngs/s, 0.04 non-youngs/s
#可以看到当前Buffer Pool Size共有6291452页,即6291452*16K。新读取到的页默认插入LRU列表的5/8的位置
#。此值由innodb_old_blocks_pct控制,即前5/8称为new list,后面3/8的称为old list。Pages made young 
#显示LRU列表old list移到new list的次数,not young显示仍在old list的次数。两个值受innodb_old_blocks_time
#影响,此值为微秒。如果old list中超过30微秒不再读取,则记录not young,反之记录为Pages made young。
Pages read 24168197, created 23016486, written 840342563
#这行显示了innodb被读取,创建,写入了多少页,读/写页的值是指的从磁盘读到缓冲池的数据,或者从缓冲池
#写到磁盘中的数据,创建页指的是innodb在缓冲池中分配但没有从数据文件中读取内容的页,因为它并不关心
#内容是什么(如,它们可能属于一个已经被删除的表)
0.04 reads/s, 3.64 creates/s, 150.11 writes/s
#这行显示了对应上面一行的每秒read,create,write的页数
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
#表示缓冲池命中率,如果低于95%需要具体排查。#这行显示了缓冲池的命中率,它用来衡量innodb在缓冲池中
#查找到所需页的比例,它度量自上次Innodb状态输出后到本次输出这段时间内的命中率,因此,如果服务器自那
#以后一直很安静,你将会看到No buffer pool page gets since the last printout。
#它对于度量缓存池的大小并没有用处。
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
#这行显示了页面预读,随机预读的每秒页数
LRU len: 5709243, unzip_LRU len: 1613670
#innodb1.0.x开始支持压缩页的功能,将原来16K的页压缩为1K,2K,4K,8K,而由于页的大小发生了变化,LRU
#列表也有了些改变,对于非16K的页,是通过unzip_LRU列表进行管理的,可以看到unzip_LRU len为0表示没有
#使用压缩页.
I/O sum[90104]:cur[64], unzip sum[0]:cur[0]
#对于压缩页的表,每个表的压缩比例可能不同,可能存在有的表页为8K,有的表页大小为2K的情况,unzip_LRUs
#怎样从缓存池中分配内存的呢?首先,在unzip_LRU列表中对不同压缩页大小的页进行分别管理,其次,通过伙
#伴算法进行内存的分配,例如:需要从缓存池中申请页为4K的大小,其过程如下:a:检查4K的unzip_LRU列表,
#检查是否有可用的空闲页,b:若有,则直接使用,c:若没有,检查8K的unzip_LRU列表,d:若能够得到空闲页,
#将页分成2个4K的页,存放到4K的unzip_LRU列表,e:若不能得到空闲页,从LRU列表中申请一个16K的页,将页分
#成1个8K,2个4K的页,分别存放到各自大小对应的unzip_LRU列表中。注:可能出现Free buffers和Database pages
#之和不等于Buffer pool size,因为缓冲池中的页肯会被分配给自适应哈希索引,lock信息,insert buffer等,
#而这部分页不需要LRU算法进行维护,因此不在LRU列表中。
----------------------
INDIVIDUAL BUFFER POOL INFO
#可通过innodb_buffer_pool_instances 来配置多个缓冲池实例,默认为1。可减少数据库内部资源竞争,增加并
#发处理。如分配多个缓冲池,每个缓冲池大小为 innodb_buffer_pool_size / innodb_buffer_pool_instances 。
----------------------
---BUFFER POOL 0
Buffer pool size   786432
Free buffers       1170
Database pages     709698
Old database pages 261958
Modified db pages  461
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 68365434, not young 125234848
0.04 youngs/s, 0.00 non-youngs/s
Pages read 2998547, created 2863564, written 160342629
0.00 reads/s, 0.04 creates/s, 30.04 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 709698, unzip_LRU len: 201707
I/O sum[11263]:cur[8], unzip sum[0]:cur[0]
......
---BUFFER POOL 7
Buffer pool size   786431
Free buffers       1171
Database pages     712437
Old database pages 262969
Modified db pages  253
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 69183588, not young 125109931
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2979962, created 2862344, written 100235837
0.00 reads/s, 0.24 creates/s, 14.48 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 712437, unzip_LRU len: 203260
I/O sum[11263]:cur[8], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
#这行显示了innodb内核内有多少个线程,队列中有多少个线程,队列中的查询是innodb为限制并发执行的线程
#数量而不运行进入内核的线程。查询在进入队列之前会休眠等待。
1 read views open inside InnoDB
#这行显示了有多少打开的innodb读视图,读视图是包含事务开始点的数据库内容的MVCC快照,你可以看看某特
#定事务在第6部分TRANSACTIONS是否有读视图
Main thread process no. 4111, id 140305385735936, state: sleeping
#这行显示了内核的主线程状态
Number of rows inserted 529148713, updated 266361482, deleted 324403280, read 2301598961937
#Number of rows inserted、updated、deleted、read,表示多少行被插入,更新和删除,读取及每秒信息,
#可用于监控。
87.88 inserts/s, 355.43 updates/s, 0.08 deletes/s, 323708.61 reads/s
#这行显示了对应上面一行的每秒平均值,如果想查看innodb有多少工作量在进行,那么这两行是很好的参考值
----------------------------
END OF INNODB MONITOR OUTPUT
============================
#要注意了,如果看不到这行输出,可能是有大量事务或者是有一个大的死锁截断了输出信息
1 row in set (0.01 sec)

ERROR: 
No query specified
发布了7 篇原创文章 · 获赞 0 · 访问量 100
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览