美文网首页
Innodb Status源码分析

Innodb Status源码分析

作者: perryn | 来源:发表于2017-02-17 17:59 被阅读152次
    函数调用关系 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;
    

    相关文章

      网友评论

          本文标题:Innodb Status源码分析

          本文链接:https://www.haomeiwen.com/subject/vmmlwttx.html