Innodb Status源码分析

函数调用关系 1.1 基本介绍 这里写图片描述

(innodb status核心函数调用关系)

InnoDB中执行show engine innodb status的相关函数的调用。InnoDB status目前包括了2个部分:

innodb_show_status函数最终调用的是srv_printf_innodb_monitor函数; srv_printf_innodb_monitor函数的调用关系如上图所示。
innodb_mutex_show_status展示了show engine innodb mutex的相关最新信息。

1.2 innodb status信息

  mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2016-11-21 12:04:28 7fffcee8f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 39 seconds
-----------------
BACKGROUND THREAD
-----------------  
/*
Background thread是代表了innodb核心thread的运行情况
*/
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 94 srv_idle
srv_master_thread log flush and writes: 94
----------
SEMAPHORES //信号量相关的信息
----------
----------
MUTEX INFO //互斥量的相关函数
----------
Locked mutex: addr 0x1800b40 thread 140736664762112 file /data/opt/mysql-5632/storage/innobase/handler/ha_innodb.cc line 12658
Locked mutex: addr 0x1800ac0 thread 140736664762112 file /data/opt/mysql-5632/storage/innobase/srv/srv0srv.cc line 1129
Total number of mutexes 8358
-------------
RW-LATCH INFO
-------------
Total number of rw-locks 16427 //读写锁的数量
OS WAIT ARRAY INFO: reservation count 4
--Thread 140736658593536 has waited at srv0srv.cc line 1087 for 28.00 seconds the semaphore:
Mutex at 0x1800ac0 created file srv0srv.cc line 924, lock var 1
Last time reserved in file /data/opt/mysql-5632/storage/innobase/srv/srv0srv.cc line 1129, waiters flag 1
OS WAIT ARRAY INFO: signal count 3
Mutex spin waits 4, rounds 63, OS waits 2
RW-shared spins 2, rounds 60, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 15.75 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS //事务的相关情况
------------
Trx id counter 9474
Purge done for trx's n:o < 8974 undo n:o < 0 state: running but idle
History list length 2
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 2, OS thread handle 0x7fffcee8f700, query id 3 localhost root init
show engine innodb status
--------
FILE I/O //IO线程个数
--------
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 (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 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
308 OS file reads, 5 OS file writes, 5 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
//插入缓冲以及自适应哈希索引的操作次数
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, used cells 0, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG  //日志的SCN刷新情况
---
Log sequence number 1633736
Log flushed up to   1633736
Pages flushed up to 1633736
Last checkpoint at  1633736
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY //InnoDB BP的大小、Page缓冲情况
----------------------
Total memory allocated 139984896; in additional pool allocated 0
Dictionary memory allocated 52162
Buffer pool size   8191
Free buffers       8034
Database pages     157
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 157, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
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: 157, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 4197, id 140736632858368, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1.3 gdb调试日志

这里写图片描述

执行show innodb status时候函数栈，执行这个操作，有2个部分的工作，一个是在mysql server层面进行另外一个部分是在innodb存储引擎的操作; 1.http request:mysql client 发起的请求 2.sql parser:解析sql语句，每个THD带着解析后的SQL解析树，去在整个生命周期中执行相关的操作。 3.privileage control:权限控制，依据解析树种的信息判断是否有该用户、是否有查询的表和列，是否具备访问这些表的权限等动作。 4. query cache:结果SQL的hash计算，发现在query cache中命中，直接从cache中返回结果给cleint. 5.join:如果有多个表的join，选择最优的表连接顺序。 6.optimize:THD带着的解析树,收集sql中的表的统计信息，使用贪婪算法查找执行的最优路径，最终找到mysql 认为最优的执行路径。 7.executing:THD拿着之前生成的物理执行计划，调用Mysql Innodb接口查找相关的数据给client.

2.函数说明

2.1 srv_print_master_thread_info函数

    获取innodb中srv_master_thread(主线程)的运行状态信息,主线程有如下4个状态，分别获取主线程在每个状态下执行相关操作的动作次数。

/**
如果innodb内核有任务执行,则srv_main_active_loops++,在代码层面如果innodb运行过程中主线程调用了srv_master_do_active_tasks函数,则srv_main_active_loops++。该函数主要做后台异步的drop table操作、
Insert buf的merge操作、同步flush relog buffer log到磁盘、do checkpoint操作。
*/
static ulint        srv_main_active_loops       = 0;
/** 
master thread被挂起时候,调用srv_master_do_idle_tasks函数进行和srv_master_do_shutdown_tasks函数类似的操作。
*/
static ulint        srv_main_idle_loops     = 0;
/** 
Innodb内核任务执行完毕后,则srv_main_shutdown_loops++，在代码层面,Innodb调用了srv_master_do_shutdown_tasks函数,该函数是循环执行，直到没有任务可以执行后在退出,这个函数一般做表删除的后台操作、relog flush操作、insert buf的merge操作或者做checkpoint操作。                  */
static ulint        srv_main_shutdown_loops     = 0;

/** 
flush relog buffer 日志 到磁盘，有这样的操作srv_log_writes_and_flush++,之前提到的线程的flush relog操作都是调用 srv_sync_log_buffer_in_background 这个函数。该函数的flush log的操作策略受制于innodb_flush_logs_at_trx_commit 参数影响。*/
static ulint        srv_log_writes_and_flush    = 0;