innodb status解读

作者: mysia | 来源:发表于2019-05-05 21:13 被阅读25次

1. 序

innodb存储引擎在show engine innodb status输出中，显示除了大量的内部信息，它输出就是一个单独的字符串，没有行和列，内容分为很多小段，每一段对应innodb存储引擎不同部分的信息，其中有一些信息对于innodb调优的时候，是非常有必要的。

输出内容中包含了一些平均值的统计信息，这些平均值是自上次输出结果生成以来的统计数，因此，如果正在检查这些值，那就要确保已经等待了至少30s的时间，使两次采样之间的积累足够长的统计时间并多次采样，检查计数器变化从而弄清其行为，并不是所有的输出都会在一个时间点上生成，因而也不是所有的显示出来的平均值会在同一时间间隔里重新再计算。而且，innodb有一个内部复位间隔，而它是不可预知的，各个版本也不一样。

2. 信息解读

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2019-05-05 17:10:04 7f17d12da700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 18183471 srv_active, 0 srv_shutdown, 7468587 srv_idle
srv_master_thread log flush and writes: 25652058

在执行show engine innodb status命令之后，在background thread之前的信息是innodb status相关状态信息，Per second averages calculated from the last 4 seconds意思是说接下来的信息，是对过去4秒时间内innodb状态的输出。

innodb存储引擎采用多线程模型，有多个不同的后台进程处理不同的任务。主要分为：

master thread；
IO thread；
purge thread；
page cleaner thread；

其中，master thread负责缓冲池中的数据异步刷新到磁盘，保证数据一致性。其内部由多个loop组成：

master loop；
background loop；
flush loop；
suspend loop；

master thread会根据运行状态在不同loop间进行切换。

srv_master_thread loops：Master线程的循环次数，master线程在每次loop过程中都会sleep，sleep的时间为1秒。而在每次loop的过程中会选择active、shutdown、idle中一种状态执行。Master线程在不停循环，所以其值是随时间递增的；
Srv_active：Master线程选择的active状态执行。Active数量增加与数据表、数据库更新操作有关，与查询无关，例如：插入数据、更新数据、修改表等；
Srv_shutdown：这个参数的值一直为0，因为srv_shutdown只有在mysql服务关闭的时候才会增加；
Srv_idle：这个参数是在master线程空闲的时候增加，即没有任何数据库改动操作时；
Log_flush_and_write：Master线程在后台会定期刷新日志，日志刷新是由参数innodb_flush_log_at_timeout参数控制前后刷新时间差

注:Background thread部分信息为统计信息，即mysql服务启动之后该部分值会一直递增，因为它显示的是自mysqld服务启动之后master线程所有的loop和log刷新操作。通过对比active和idle的值，可以获知系统整体负载情况。Active的值越大，证明服务越繁忙。

2.2 semphores

Semphores由两部分组成，event counters, 和可选项输出,即当前等待的事件(current waits)。

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 122756366
OS WAIT ARRAY INFO: signal count 430997400
Mutex spin waits 2700211012, rounds 7787068496, OS waits 58920191
RW-shared spins 152704065, rounds 1535459640, OS waits 27522434
RW-excl spins 224605789, rounds 2250797018, OS waits 28079422
Spin rounds per wait: 2.88 mutex, 10.06 RW-shared, 10.02 RW-excl

OS WAIT的信息中，reservation count表示Innodb产生了多少次OS WAIT；signal count表示，进行OS WAIT的线程，接收到多少次信号（singal）被唤醒。如果signal的数值很大，通常是几十万，上百万。就表明，可能是很多I/O的等待，或是Innodb争用(contention)问题。关于争用问题，可能与OS的进程调度有关，可尝试减少innodb_thread_concurrency参数。

解读下面的信息之前，有必要明白什么是OS Wait,什么是spin wait。

要明白这个，首先要明白Innodb如何处理互斥量(Mutexes)，以及什么是两步获得锁(two-step approach)。

首先进程会尝试获得一个锁，如果此锁被它人占用。它就会执行所谓的spin wait，即所谓循环的查询”锁被释放了吗？”。如果在循环过程中，一直未得到锁释放的信息，则其转入OS WAIT，即所谓线程进入挂起(suspended)状态。直到锁被释放后，通过信号(singal)唤醒线程。

Mutex spin waits是线程无法获取锁，而进入的spin wait；
rounds 是spin wait进行轮询检查Mutextes的次数；
OS waits 是线程放弃spin-wait进入挂起状态；

Spin wait的消耗远小于OS waits。Spin wait利用cpu的空闲时间，检查锁的状态，OS Wait会有所谓content switch，从CPU内核中换出当前执行线程以供其它线程使用。可以通过innodb_sync_spin_loops参数来平衡spin wait和os wait。

后续的RW-shared和RW-excl，与Mutex 同理；

2.3 LATEST DETECTED DEADLOCK

当服务器发生了死锁的情况时，这部分会显示出来。死锁通常的原因很复杂，但是这一部分只会显示最后两个发生死锁的事务，尽管可能也有其它事务也包含在死锁过程中。不过，尽管信息被删减了，通常你也能够通过这些信息找出死锁的原因。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-05-01 07:47:01 7f17c647b700
*** (1) TRANSACTION:
TRANSACTION 5095555036, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 7 lock struct(s), heap size 2936, 95 row lock(s)
MySQL thread id 2191773801, OS thread handle 0x7f17dbd65700, query id 11165345772 10.10.10.10 root Sending data
这里是个SQL
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3044 page no 3172 n bits 104 index `PRIMARY` of table `test`.`test` trx id 5095555036 lock mode S locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 5095555007, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 3657
mysql tables in use 3, locked 3
2275 lock struct(s), heap size 210472, 92740 row lock(s)
MySQL thread id 2191773784, OS thread handle 0x7f17c647b700, query id 11165345705 10.10.10.10 root Searching rows for update
这里是个SQL
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3044 page no 3172 n bits 104 index `PRIMARY` of table `test`.`test` trx id 5095555007 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3044 page no 3170 n bits 96 index `PRIMARY` of table `test`.`test` trx id 5095555007 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)

***(1) TRANSACTION表示第一个死锁事务；
*** (1) WAITING FOR THIS LOCK TO BE GRANTED表示等待的锁；
*** (2) TRANSACTION表示第二个事务的状态；
*** (2) HOLDS THE LOCK(S)表示事务2获得的锁；
*** (2) WAITING FOR THIS LOCK TO BE GRANTED表示事务2等待的锁；
*** WE ROLL BACK TRANSACTION (1)表示选择了事务1回滚，以避免无限期的死锁等待。

关于回滚事务，Innodb有一个内在的死锁检测机制，当死锁等待超过一定时间后，就会回滚其中一个事务。innodb_lock_wait_timeout可配置死锁的等待超时时间。

2.4 TRANSACTIONS

这一部分包含Innodb 事务（transactions）的统计信息，还有当前活动的事务列表，接下来，先介绍开始的统计部分。

------------
TRANSACTIONS
------------
Trx id counter 5151639920
Purge done for trx's n:o < 5151638034 undo n:o < 0 state: running but idle
History list length 568

---TRANSACTION 0显示的是当前的transaction id, 这个ID是一个系统变量随时每次新的transaction产生而增加。
Purge done这行显示的正在进行清空（purge）操作的transaction ID。可以通过查看与上一行ID的区别，明白没有被purge的事务落后的情况。
History list length 568记录了undo spaces内unpurged的事务的个数。

有人也许会计算4、5行ID之差，与第6行的这个数值比较，可能会发现完全不匹配。其实，事务的ID并不是按顺序purge的，所以有可能ID大于第5行当前purge的ID的事务，已经在前面被purge了。Purge的原则就是记录没有被其它事务继续使用了。

什么是purge操作？
要明白什么清空（purge）操作，得明白什么是事务的多版本控制，即MVCC(multi-version concurrency control)。

Innodb为了实现MVCC，需要在表空间内保存老版本的记录信息，这些信息存储于回滚段中（rollback segment）。所谓回滚段，在物理存储上是UNDO log的记录。

Purge到底做了些什么？其实它就相当于一个垃圾收集器。取个例子，当用户下一个命令，如 “DELETE FROM t WHERE c = 1;”, InnoDB 不会马上删除对应的记录，它会做如下三件事情:

它标记此记录为删除（通过删除标记位）；
存储原始的记录到UNDO log；
更新记录列DB_TRX_ID和DB_ROLL_PTR（这些列是Innodb在原记录列上增加的）。DB_TRX_ID记录了最后操作记录的事务ID。DB_ROLL_PTR也叫回滚指针(rollback pointer)，指向UNDO log 记录，此UNDO Log记录了原始记录的信息，这些信息可以用来重建原始记录（如发生了rollback的情况）。如果操作是插入，还会有一个DB_ROW_ID，这个指明了新记录的行号。

当事务提交后，那些标记了删除的记录，以及UNDOLog中的记录并不会马上清除，这些记录信息可以被其它事务所重用，或是共享。只有当没有任何事务共享这些记录的时候，这些记录才会被清除（purge）。这就是所谓purge操作。而为了提高数据库的操作效率，purge操作是由另外的线程异步完成。这就是什么存在unpurged的事务的原因。

接下来，是事务的列表，如下有一个例子：

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 5151638026, ACTIVE 3 sec
MySQL thread id 2261157212, OS thread handle 0x7f17d9e01700, query id 11572693489 10.10.10.10 root
Trx read view will not see trx with id >= 5151638027, sees < 5151637857

---TRANSACTION 5151638026表示事务ID；
ACTIVE 3 sec表示保持这个状态已经3秒了，其它可能的状态还包括“not started,” “active,” “prepared,” and “committedin memory”；
MySQL thread id 2261157212与processlist中的id是一致的；
query id 11572693489可以在general log中找到，后面则是来源ip和用户名；
最后一行显示了MVCC,多版本并发控制的信息，表明了哪些其它事务可以看到此记录，哪些不能。

2.5 FILE I/O

FILE I/O部分显示了I/O Helper thread的状态，包括一些统计信息。

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (read thread)
I/O thread 7 state: waiting for i/o request (read thread)
I/O thread 8 state: waiting for i/o request (read thread)
I/O thread 9 state: waiting for i/o request (read thread)
I/O thread 10 state: waiting for i/o request (read thread)
I/O thread 11 state: waiting for i/o request (read thread)
I/O thread 12 state: waiting for i/o request (read thread)
I/O thread 13 state: waiting for i/o request (read thread)
I/O thread 14 state: waiting for i/o request (read thread)
I/O thread 15 state: waiting for i/o request (read thread)
I/O thread 16 state: waiting for i/o request (read thread)
I/O thread 17 state: waiting for i/o request (read thread)
I/O thread 18 state: waiting for i/o request (write thread)
I/O thread 19 state: waiting for i/o request (write thread)
I/O thread 20 state: waiting for i/o request (write thread)
I/O thread 21 state: waiting for i/o request (write thread)
I/O thread 22 state: waiting for i/o request (write thread)
I/O thread 23 state: waiting for i/o request (write thread)
I/O thread 24 state: waiting for i/o request (write thread)
I/O thread 25 state: waiting for i/o request (write thread)
I/O thread 26 state: waiting for i/o request (write thread)
I/O thread 27 state: waiting for i/o request (write thread)
I/O thread 28 state: waiting for i/o request (write thread)
I/O thread 29 state: waiting for i/o request (write thread)
I/O thread 30 state: waiting for i/o request (write thread)
I/O thread 31 state: waiting for i/o request (write thread)
I/O thread 32 state: waiting for i/o request (write thread)
I/O thread 33 state: waiting for i/o request (write thread)
Pending normal aio reads: 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] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
538398418 OS file reads, 1310313349 OS file writes, 1024862715 OS fsyncs
0.75 reads/s, 16384 avg bytes/read, 439.89 writes/s, 126.72 fsyncs/s

I/O thread分别表示各个进程信息；
Pending normal aio表示IO thread的pending operations, pending的log和buffer pool thread的fsync()调用情况；
最后两行表示读写次数和速度；

2.6 INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 20823, seg size 20825, 23459418 merges
merged operations:
 insert 57124950, delete mark 145171292, delete 5096220
discarded operations:
 insert 0, delete mark 0, delete 0
49690.83 hash searches/s, 6845.29 non-hash searches/s

Ibuf表示insert buffer的大小；
free list len表示free list的长度；
seg size表示segment大小；
merges表示insert buffermerge的次数；
merged operations表示merge操作中，insert、标记delete、实际delete的次数；
discarded operations表示被放弃的操作详情；
最后一行表示每秒进行了多少次hash搜索，以及非hash搜索；

2.7 LOG

这里记录了tansaction log子系统的信息。

---
LOG
---
Log sequence number 4633347819916
Log flushed up to   4633347819803
Pages flushed up to 4633347691108
Last checkpoint at  4633347691108
Max checkpoint age    1302939833
Checkpoint age target 1262222964
Modified age          128808
Checkpoint age        128808
0 pending log writes, 0 pending chkp writes
996187604 log i/o's done, 57.00 log i/o's/second

Log sequence number表示当前LSN号；
Log flushed up to表示已经被刷新的磁盘的日志数量；
Pages flushed up to表示被刷新到磁盘的页数量；
Last checkpoint表示上一个检查点；
Max checkpoint age表示检查点最大的保持时间；
Checkpoint age target表示准备刷新的日志；
Modified age表示已修改，还没刷新的时间；
Checkpoint age表示距离上次checkpoint的时间；
最后两行显示pending log 的统计信息

计算方式如下：

Log sequence number   --> log_sys->lsn
Log flushed up to     --> log_sys->flushed_to_disk_lsn
Pages flushed up to   --> log_buf_pool_get_oldest_modification()
Last checkpoint at    --> log_sys->last_checkpoint_lsn
Max checkpoint age    --> log_sys->max_checkpoint_age
Checkpoint age target --> log_sys->max_checkpoint_age_async
Modified age          --> log_sys->lsn - log_buf_pool_get_oldest_modification()
Checkpoint age        --> log_sys->lsn - log_sys->last_checkpoint_lsn

2.8 BUFFER POOL AND MEMORY

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 26826768384; in additional pool allocated 0
Total memory allocated by read views 11624
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 823087072       (407994232 + 415092840)
    Page hash           3188312 (buffer pool 0 only)
    Dictionary cache    104735960       (102000368 + 2735592)
    File system         1062592         (812272 + 250320)
    Lock system         63768872        (63750152 + 18720)
    Recovery system     0       (0 + 0)
Dictionary memory allocated 2735592
Buffer pool size        1572856
Buffer pool size, bytes 25769672704
Free buffers            8192
Database pages          1539329
Old database pages      568064
Modified db pages       306
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 255818213, not young 3918019726
1206.20 youngs/s, 4.50 non-youngs/s
Pages read 561870753, created 26970631, written 681633130
0.75 reads/s, 0.25 creates/s, 707.07 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 6 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1539329, unzip_LRU len: 0
I/O sum[22816]:cur[0], unzip sum[0]:cur[0]

Total memory allocated表示分配给innodb的内存大小，以及additional pool使用的大小；
Internal hash tables表示hash table的一些信息；
Buffer pool size到Modified db pages，分别显示了Buffer pool大小，空闲的buffers, database pages, 脏页(dirty pages)。在此会看到buffer pool size比database pages要大，这是因为buffer pool还会存放lock index, hash index等一些其它的系统信息。
Pending开头的行显示了pending的reads 和writes；
Pages made young和not young表示LRU列表中page移动到队首的次数和未移动的次数，下面一行是每秒的速度；
Pages read表示innodb读写和创建页的次数，下面一行是每秒的速度；
Buffer pool hit表示缓冲池的命中率；
Pages read ahead预读页的速度，evicted without access预读无效的页的速度，Random read ahead随机读预读的速度；
LRU len表示LRU队列中page数量，unzip_LRU表示压缩页的数量;

2.9 ROW OPERATIONS

这一部分显示了row operation及其它的一些统计信息。

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
1 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
1 out of 1000 descriptors used
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 5151638027
Read view up limit trx id 5151637857
Read view low limit trx id 5151638027
Read view individually stored trx ids:
Read view trx id 5151637857
Read view trx id 5151638005
Read view trx id 5151638011
Read view trx id 5151638015
Read view trx id 5151638017
Read view trx id 5151638025
-----------------
Main thread process no. 4431, id 139749809452800, state: sleeping
Number of rows inserted 3063946866, updated 748472685, deleted 2500742143, read 1036828003469
2.75 inserts/s, 74.98 updates/s, 0.25 deletes/s, 237902.52 reads/s

0 queries inside InnoDB, 0 queries in queue表示多个线程在innodb，多少个线程在队列中；
1 read views open inside InnoDB表示有多少打开的innodb读视图，读视图是包含事务开始点的数据库内容的MVCC快照；
1 RW transactions active inside InnoDB表示内核的main thread的状态信息，其余可能的状态还会有：archiving log (if log archive is on)，doing background droptables，doing insert buffer merge，flushing buffer pool pages，flushing log，making checkpoint，purging，reserving kernel mutex，sleeping，suspending，waiting for buffer poolflush to end，waiting forser veractivity；
最后两行显示了增删改查的数量和速度；

innodb status解读

1. 序

2. 信息解读

2.2 semphores

2.3 LATEST DETECTED DEADLOCK

2.4 TRANSACTIONS

2.5 FILE I/O

2.6 INSERT BUFFER AND ADAPTIVE HASH INDEX

2.7 LOG

2.8 BUFFER POOL AND MEMORY

2.9 ROW OPERATIONS

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

DBA