这里写图片描述
(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;
