理解show engine innodb status之BUFFER POOL AND MEMORY

mysql版本：Ver 5.6.34-debug for Linux on x86_64 (Source distribution)

显示结果

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 139984896; in additional pool allocated 0
Dictionary memory allocated 56355
Buffer pool size   8191
Free buffers       8033
Database pages     158
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 158, 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: 158, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

入口函数

函数srv_printf_innodb_monitor是show engine  innodb status的总入口，部分详细信息还会调用其他函数。


fputs("----------------------\n"
              "BUFFER POOL AND MEMORY\n"
              "----------------------\n", file);
        fprintf(file,
                "Total memory allocated " ULINTPF
                "; in additional pool allocated " ULINTPF "\n",
                ut_total_allocated_memory,
                mem_pool_get_reserved(mem_comm_pool));
        fprintf(file, "Dictionary memory allocated " ULINTPF "\n",
                dict_sys->size);


        buf_print_io(file);

Total memory allocated 

取的是全局变量ut_total_allocated_memory的值，表示的是当前系统的总内存。

/** The total amount of memory currently allocated from the operating
system with os_mem_alloc_large() or malloc().  Does not count malloc()
if srv_use_sys_malloc is set.  Protected by ut_list_mutex. */
extern ulint            ut_total_allocated_memory;

in additional pool allocated

取的是函数mem_pool_get_reserved(mem_comm_pool)的返回结果，看代码其实就是取全局变量mem_com_pool的成员变量reserved的数值，而mem_comm_pool的数据结构是mem_pool_t，reserved表示的是已经使用的内存。

不过看mem_comm_pool变量在赋值的时候有个条件，即如果系统参数innodb_use_sys_malloc为ON的话，mem_comm_pool基本为0，因为我的db上的该参数为默认值ON，所以该值一直都是0，也就是如果采用操作系统自带的分配内存的方式，这里就不要额外再占用内存。看官方文档innodb_use_sys_malloc在将来可能被废弃，一般采用默认值就好，所以in additional pool allocated基本可以忽略。
if (UNIV_LIKELY(srv_use_sys_malloc)) {
                /* When innodb_use_sys_malloc is set, the
                mem_comm_pool won't be used for any allocations.  We
                create a dummy mem_comm_pool, because some statistics
                and debugging code relies on it being initialized. */
                size = 1;
        }

        mem_comm_pool = mem_pool_create(size);

Dictionary memory allocated

取的是全局变量dict_sys的成员变量size的值，数据类型为dict_sys_t，表示的数据词典占用的data+index的总字节数

Buffer pool size  Free buffers等相关

后面的显示通过调用buf_print_io(file)进行显示，看代码如果缓冲池实例只有一个，那么只需要调用buf_print_io_instance(pool_info_total, file);如果缓冲池实例有多个，那么调用  buf_print_io_instance(pool_info_total, file);显示全局的缓存池使用后，再循环调用buf_print_io_instance(&pool_info[i], file);显示每一个innodb_buffer_pool_instance的缓冲池使用情况。我们主要分析总缓冲池使用情况。
buf_print_io_instance(pool_info_total, file);


        /* If there are more than one buffer pool, print each individual pool
        info */
        if (srv_buf_pool_instances > 1) {
                fputs("----------------------\n"
                "INDIVIDUAL BUFFER POOL INFO\n"
                "----------------------\n", file);


                for (i = 0; i < srv_buf_pool_instances; i++) {
                        fprintf(file, "---BUFFER POOL %lu\n", i);
                        buf_print_io_instance(&pool_info[i], file);
                }
        }

从代码可查到buffer pool这块的信息主要通过调用函数buf_print_io_instance显示，而主要的显示信息通过直接查一个全局变量pool_info的值获取

fprintf(file,
                "Buffer pool size   %lu\n"
                "Free buffers       %lu\n"
                "Database pages     %lu\n"
                "Old database pages %lu\n"
                "Modified db pages  %lu\n"
                "Pending reads %lu\n"
                "Pending writes: LRU %lu, flush list %lu, single page %lu\n",
                pool_info->pool_size,
                pool_info->free_list_len,
                pool_info->lru_len,
                pool_info->old_lru_len,
                pool_info->flush_list_len,
                pool_info->n_pend_reads,
                pool_info->n_pending_flush_lru,
                pool_info->n_pending_flush_list,
                pool_info->n_pending_flush_single_page);



查看pool_size的数据结构buf_pool_info_t得知：
pool_size表示当前缓冲池总大小，也就是Buffer pool size；
free_list_len:缓存池中free list的总大小，也就是Free buffers；
lru_len：缓冲池总lru列表的总大小，也就是已经使用的缓冲池大小，即Database pages；
old_lru_len：缓冲池中LRU_old_len列表的总大小，也就是当前缓存池中可以被淘汰的大小，即Old database pages ；innodb的LRU引入了一个midpoint的概念，即新读取到的页，会放在LRU列表的midpoint位置，midpoint位置到LRU列表最后的部分成为old lru,即准备被淘汰的lru列表，一般稳定情况下midpoint/lru约等于3/8,由参数innodb_old_blocks_pct控制。这样设计是为了避免某些大表扫描把真正的热数据一次性踢出缓冲池。（如果lru链表总长度小于512，则采用常规的lru算法）
flush_list_len：缓冲池总flush list列表的总大小，也就是当前缓冲池脏页的总大小，即Modified db pages；

一般情况下Free buffers+Database pages<=Buffer pool size,小于是因为缓冲池中有些使用的页并不一定在lru列表，比如自适应哈希索引，锁信息等。比如下面这个情况：
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 200, seg size 202, 193372 merges
merged operations:
 insert 122487, delete mark 72187, delete 1241
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 7470467, node heap has 13213 buffer(s)
3171.42 hash searches/s, 14588.61 non-hash searches/s
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 3863347200; in additional pool allocated 0
Dictionary memory allocated 1586657
Buffer pool size   230392
Free buffers       7992
Database pages     209187
Old database pages 77063
Modified db pages  48194


node heap has 13213 buffer(s)表示自适应哈希索引等占用了13213个页，而Buffer pool size - Free buffers - Database pages  =13213；
这里的Old database pages/Database pages 约等于八分之三；

n_pend_reads：/*!< buf_pool->n_pend_reads, pages pending read */挂起的读操作数量，即当前缓存池未完成的数量

n_pending_flush_lru：/*!< Pages pending flush in LRU */当前缓冲池为完成的需要刷新lru的数量
n_pending_flush_list：/*!< Pages pending flush in FLUSH LIST */当前缓冲池需要刷新脏页的数量
n_pending_flush_single_page：/*!< Pages pending to be flushed as part of single page flushes issued by various user threads */

Pages made young等相关

"Pages made young %lu, not young %lu\n"
                "%.2f youngs/s, %.2f non-youngs/s\n"
                "Pages read %lu, created %lu, written %lu\n"
                "%.2f reads/s, %.2f creates/s, %.2f writes/s\n",
                pool_info->n_pages_made_young,
                pool_info->n_pages_not_made_young,
                pool_info->page_made_young_rate,
                pool_info->page_not_made_young_rate,
                pool_info->n_pages_read,
                pool_info->n_pages_created,
                pool_info->n_pages_written,
                pool_info->pages_read_rate,
                pool_info->pages_created_rate,
                pool_info->pages_written_rate);

n_pages_made_young：/*!< number of pages made young */,移动到新区域的有多少页，新区域即（lru-old_lru）

n_pages_not_made_young：/*!< number of pages not made young */没有移到到新区域的有多少页
page_made_young_rate：/*!< page made young rate in pages per second */每秒移到到新区域有多少页
page_not_made_young_rate:/*!< page not made young rate in pages per second */每秒没有移动到新区域有多少页
n_pages_read:/*!< buf_pool->n_pages_read */db启动以后缓存池读取的总页数，也就是global状态值Innodb_pages_read
n_pages_created:/*!< buf_pool->n_pages_created */db启动以后缓冲池创建的总页数，也就是global状态值Innodb_pages_created
n_pages_written:/*!< buf_pool->n_pages_written */db启动以后缓冲池更改的总页数，也就是global状态值Innodb_pages_written
pages_read_rate:/*!< num of pages read per second *平均每秒读取页数
pages_created_rate:/*!< num of pages create per second */平均每秒创建页数
pages_written_rate:/*!< num of  pages written per second */平均每秒更改页数

Buffer pool hit rate等相关

if (pool_info->n_page_get_delta) {
                fprintf(file,
                        "Buffer pool hit rate %lu / 1000,"
                        " young-making rate %lu / 1000 not %lu / 1000\n",
                        (ulong) (1000 - (1000 * pool_info->page_read_delta
                                         / pool_info->n_page_get_delta)),
                        (ulong) (1000 * pool_info->young_making_delta
                                 / pool_info->n_page_get_delta),
                        (ulong) (1000 * pool_info->not_young_making_delta
                                 / pool_info->n_page_get_delta));
        } else {
                fputs("No buffer pool page gets since the last printout\n",
                      file);
        }

从上面这段代码看出，如果n_page_get_delta不为0，即自从上次执行show engine innodb status之后没有任何页更改，则直接打印No buffer pool page gets since the last printout。如果有更改，则打印如下的相关信息

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

page_read_delta：/*!< num of pages read since last printout */，应该是上次打印之后未命中缓冲池的读取次数
n_page_get_delta：/*!< num of buffer pool page gets since last printout */，应该是上次打印之后总共读取的次数
young_making_delta： /*!< num of pages made young since last printout */，上次打印后直到现在新产生的页数
not_young_making_delta：/*!< num of pages not make young since last printout */，上次打印后直到现在未改变的页数

Pages read ahead等相关

/* Statistics about read ahead algorithm */
        fprintf(file, "Pages read ahead %.2f/s,"
                " evicted without access %.2f/s,"
                " Random read ahead %.2f/s\n",


                pool_info->pages_readahead_rate,
                pool_info->pages_evicted_rate,
                pool_info->pages_readahead_rnd_rate);



pages_readahead_rate：/*!< readahead rate in pages per second */预读的速率，对应global status的Innodb_buffer_pool_read_ahead
pages_evicted_rate：/*!< rate of readahead page evicted without access, in pages per second */表示通过预读加载进来的页，随后又立即被驱逐出缓冲池的页数的产生速率，可参考global status的Innodb_buffer_pool_read_ahead_evicted 

pages_readahead_rnd_rate：/*!< random readahead rate in pages per second */随机预读的速率

LRU内部信息相关

        /* Print some values to help us with visualizing what is

        happening with LRU eviction. */
        fprintf(file,
                "LRU len: %lu, unzip_LRU len: %lu\n"
                "I/O sum[%lu]:cur[%lu], unzip sum[%lu]:cur[%lu]\n",
                pool_info->lru_len, pool_info->unzip_lru_len,
                pool_info->io_sum, pool_info->io_cur,

                pool_info->unzip_sum, pool_info->unzip_cur);

lru_len：/*!< Length of buf_pool->LRU */LRU列表长度
unzip_lru_len：/*!< length of buf_pool->unzip_LRU list */
io_sum： /*!< buf_LRU_stat_sum.io */
io_cur：/*!< buf_LRU_stat_cur.io, num of IO for current interval */
unzip_sum：/*!< buf_LRU_stat_sum.unzip */
unzip_cur：/*!< buf_LRU_stat_cur.unzip, num pages decompressed in current interval */