DBA

innodb status解读

2019-05-05  本文已影响25人  mysia

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负责缓冲池中的数据异步刷新到磁盘，保证数据一致性。其内部由多个loop组成：

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

注: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)唤醒线程。

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)

关于回滚事务，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

有人也许会计算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 不会马上删除对应的记录，它会做如下三件事情:

  1. 它标记此记录为删除（通过删除标记位）；
  2. 存储原始的记录到UNDO log；
  3. 更新记录列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

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

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

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   --> 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]

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
上一篇下一篇

猜你喜欢

热点阅读