民生银行项目经理徐春阳 - MySQL核心参数含义的源码解析

2020-02-27 286浏览

1.MySQL核心参数含义源码解析徐春阳
2.
3.1. 前言 MySQL运维常见的困惑？响应时间莫名奇妙地变长？ Cpu利用率飙高了，原因？主从中断？ SQL执行结果有错？不能准确预计容量数据库慢得有点莫名其妙了？ O R 数据库本身的问题？系统环境/网络环境有问题？怀疑数据库参数不合适了？修改? 最后-‐-‐在问题真正解决前，跟同事持不同的意见？
4.2. 数据库核心参数总览事务安全/提交有关 innodb_flush_log_at_trx_commit sync_binlog binlog_group_commit_sync_delay binlog_group_commit_sync_no_delay_count innodb_commit_concurrency ...... 各种类型cache有关缓冲池有关 thread_cache_size table_open_cache table_open_cache_instances table_definition_cache binlog_cache_size query_cache_size ...... 其他重要参数 innodb_thread_concurrency innodb_concurrency_tickets max_connections max_user_connections max_allowed_packet slave_max_allowed_packet back_log wait_timeout long_query_time ...... innodb_buffer_pool_size innodb_buffer_pool_instances innodb_max_dirty_pages_pct innodb_max_dirty_pages_pct_lwm innodb_io_capacity_max innodb_io_capacity innodb_lru_scan_depth innodb_adaptive_flushing innodb_adaptive_flushing_lwm innodb_old_blocks_time innodb_old_blocks_pct 。。。。。。注：对于某些参数，如果视角不同，归类也可能不一样。限于时间，后面仅讲解缓存池的机制以及与其相关参数。
5.3. 缓存池工作机制概述功能是什么？应考虑的因素如何实现？访问页面时，如缓存命中，直接访问内存，无需访问磁盘。页面的修改在内存中进行，用户无需等待page的更新写进磁盘。缓存池大小有限，如何提高缓存命中率？如何缓存最热的页面？页面的修改虽然在内存中先进行，但需要考虑何时将被修改的页面刷入磁盘，使页面的更新“落地”。采用LRU算法进行页面替换 LRU链表分成old/young两部分被更新的页面被加入flush列表，后台刷新线程及时刷新
6.3.1 缓存池中的列表 free 空闲内存页（块）列表，需要装载（缓存）磁盘上的数据页的时候，从该列表取内存块。 Buffer bool instance 1 or N 个 Buffer bool instance LRU 缓存了磁盘上的数据页的内存块列表，包含三类： 1.未修改的页面，可以从该列表中摘除，然后挂到free 列表中。 2.已修改还未刷新到磁盘的页面。 3.已修改且已经刷新到磁盘的页面（实际上可并为第1类） flush 在内存中被修改但还没有刷新到磁盘的数据页列表，就是所谓的“脏”页列表，内存中的数据跟对应的磁盘上的数据不一致。属于该列表的页面同样存在于LRU列表中，但反之未必。
7.3.2 访问数据页的流程访问数据页时在缓存池中没有命中，则需要先找到空闲块来装载数据页没有空余的块，然后从LRU中查找未被修改的页/ 或已被刷新的页，找到后放入free列表中，然后从free列表中重新取 Buffer pool 未命中 FFrreeee lliisstt LRU list 选择该空闲页存放 free null R D 脏页 R 未修改/已刷新页 free 查找方向尾部 D DRD LRU list D DD 尾部 D 在LRU列表中找不到可以替换的页，则从LRU列表的队尾开始选择单页刷新，刷新完成后加入free列表. 单页刷盘
8.3.3 访问数据页的源码之—寻找空闲的block函数从free list 取block,找到就返回 block 从LRU列表中查找可以替换的 block然后放入free 列表，如果返回true,然后跳转到函数loop 位置，重新从free列表中找空闲的block。第一次扫描时仅scan 100个页( 不考虑压缩页），如果需要进行第二次扫描，则扫描整个 lru列表。从LRU列表中刷新一个页面(” 脏”页)，然后加入free列表，跳转到loop位置，进行新的循环。？？？如果需要用户线程来刷新“脏页”来产生空闲页，系统的性能将如何？Free or 可以替换的页大量存在太重要了。
9.3.4 缓存池的有关参数缓冲池有关参数 innodb_buffer_pool_size innodb_buffer_pool_instances innodb_flushing_avg_loops innodb_max_dirty_pages_pct innodb_max_dirty_pages_pct_lwm innodb_io_capacity_max innodb_io_capacity innodb_lru_scan_depth innodb_adaptive_flushing innodb_adaptive_flushing_lwm innodb_old_blocks_time innodb_old_blocks_pct 。。。。。。每个参数的含义？
10.4 缓存池源码解析
11.4.1 buf_flush_page_cleaner_coordinator 函数(1)—解析简称协调函数，该函数为后台页面刷新协调线程的入口函数，该线程所有工作都是这个函数来完成的：它负责对innodb 缓存池刷新线程（即 page cleaner线程）的调度，同时自己也会执行刷新函数。刷新协调线程期望每1秒钟进行一次的“脏页”刷新（如有必要）。同时，在调度刷新动作之前，会对每个buffer pool instance 生成需要刷新多少页的建议。生成建议的函数为page_cleaner_flush_pages_recommendation。如上所述，期望1秒钟进行一次刷新，如果两次刷新的间隔超过 4000ms,error l og 会出现相关信息.如下图:
12.4.1 buf_flush_page_cleaner_coordinator 函数(2)—相关代码循环超时调用刷新建议函数
13.4.1 buf_flush_page_cleaner_coordinator 函数(e)—主体流程对每个缓冲池实例生成需要刷新多少脏页的建议通过设置事件的方式，向刷新线程发出刷新请求协调线程作为刷新调度总负责人的角色，会保证每一个buffuer pool instance 都开始进行了刷新。如果某个buffer还没有开始刷新，则由协调函数自己进行刷新，直到所有的buffer p ool i nstance都已开始/ 进行了刷新，才退出这个while循环。等待所有的buffer p ool i nstance完成刷新。这也是相邻两次刷新的启动时间的间隔可能超过1秒，甚至几秒的原因，如间隔时间超过4秒，则错误日志有 “page_cleaner:1000msintended loop took 4055ms” 类似记录。
14.4.2 page_cleaner_flush_pages_recommendation 函数概述(1) 计算脏页刷新与LSN增长的平均速度，但并非每次函数调用都计算，而是由参数 innodb_flushing_avg_loops 决定，默认为30，即函数被调用30次时或经过30秒之后再计算这两项的平均速度。分别用变量avg_page_rate与lsn_avg_rate保存。计算规则为：新的平均值=（原平均值+最近这段时间的平均速度）/2。通过缓冲池中的脏页百分比计算innodb_io_capacity的百分比，由函数af_get_pct_for_dirty计算，结果用变量pct_for_dirty保存。通过lsn的age来计算innodb_io_capacity百分比，由函数 af_get_pct_for_lsn计算，结果用变量pct_for_lsn保存。注:这两个函数后面将介绍 pct_total = ut_max(pct_for_dirty, pct_for_lsn); 两值比较后取大的值赋给pct_total 根据lsn(重做日志）产生的平均速度即lsn_avg_rate，以及脏页的oldest_modification（最老的日志号lsn）这两个因素来第一次计算每个buffer p ool i nstance需要刷新多少页面，将结果存入page_cleaner-‐ >slots[i].n_pages_requested，同时，将所有buffer p ool i nstance需要刷新的总数计入变量sum_pages_for_lsn，将 sum_pages_for_lsn 与srv_max_io_capacity * 2比较，取小值赋给变量pages_for_lsn。 n_pages = (PCT_IO(pct_total) + avg_page_rate + pages_for_lsn) / 3; 注：PCT_IO(pct_total)= pct_total * innodb_io_capacity/100 由此得出：Innodb_io_capacity参数的设置将直接影响对缓存池进行刷新的数量。
15.4.2 page_cleaner_flush_pages_recommendation 函数概述(e) if (n_pages > srv_max_io_capacity) { n_pages = srv_max_io_capacity; } 再次为每个buffer p ool instance计算最终建议的刷新页面的数量：代码如下解析：如果前面根据活跃重做日志量来计算得出的pct_for_lsn>30,则每个缓存池需要刷新的页面等于page_cleaner-‐ >slots[i].n_pages_requested * n_pages / sum_pages_for_lsn + 1；即根据前面介绍的根据redo的产生速度以及Page的age分布所计算出来的需要刷新页的数量乘以n_pages再除以sum_pages_for_lsn再加1. 这两个值的来源请查看上页ppt。否则，则认为redo l og f ile 还有足够的空间，不考虑脏页的age在缓存池中分布可能不均的情况，则建议每个缓存池实例的刷新页面的数量一致，即等于n_pages / s rv_buf_pool_instances，即总的页面数除以buffer pool实例的个数。 *lsn_limit = LSN_MAX; /*将函数入参指针所指向的内存地址赋值。*/ return(n_pages); /*返回建议刷新页面的总数*/
16.4.2.1 计算刷新页面的平均速度以及redo日志产生的平均速度 srv_flushing_avg_loops，即参数 innodb_flushing_avg_loops，默认30，下面都以默认值做说明，即30次刷新循环之后做一次平均速度的统计与更新。 sum_pages为最近30次刷新循环所刷新的页面的总和。 prev_lsn为上一次统计平均值时的当时的lsn号。Cur_lsn 减去 prev_lsn表示的最近30次刷新循环期间的redo日志增长量。根据这两个值再除以30次循环所历时时间等于这段时间的平均速度。跟原来的值再平均，就是当前的最新的平均值。分别用 avg_page_rate 与lsn_avg_rate 表示. 计算出来供后续使用。
17.4.2.2 根据脏页的百分比以及lsn的age来计算io_capacity的百分比 LSN为oldest_lsn之前的页面都已经刷新到磁盘。 lsn的age表示涉及到的脏页还没有刷入磁盘的重做日志的大小。取pct_for_dirty与 pct_for_lsn中的大值最为pct_total 根据lsn的age，来计算 io_capacity的百分比，age越大，返回值越大。结果 pct_for_lsn后面会再次被利用。根据buffer p ool中的脏页百分比来计算io_capacity的百分比。脏页的百分比越大，百分比越大。返回的值越大。
18.4.2.2.1 根据脏页百分比来计算io_capacity百分比—af_get_pct_for_dirty 获取“脏”页百分比没有“脏”页，直接返回0 其他情况，返回0 如果参数 innodb_max_dirty_ pages_pct_lwm为 0(默认值），且 dirty_pct大于参数 innodb_max_dirty_ pages_pct，则返回 100 否则，如果dirty_pct 大于参数 innodb_max_dirty_pag es_pct_lwm ，当 dirty_pct越接近 innodb_max_dirty_pag e_pct，返回值越接近 100。
19.4.2.2.2 根据lsn的age来计算io_capacity的百分比-‐-‐af_get_pct_for_lsn 如果没有开启 innodb_adaptive_flushing 参数（默认on)，且lsn的age没有达到 max_async_age,则返回0。 max_async_age是根据log file大小计算出来的。也就是说，当 age超过max_async_age，则必须考虑redo logfile的可用空间可能不足这个因素来做刷新建议。剩下情况:根据lsn的age 跟max_async_age的比值来计算io_capacity的百分比。返回值符给 pct_for_lsn
20.4.2.3 试算每个buffer pool instance需要刷新的页面。注：下一页面将分别对注释1-‐5进行讲解注1 注2 注3 注4 注5
21.4.2.3.1 试算每个buffer pool instance需要刷新的页面—注释。 lsn_avg_rate表示redo log 增长的平均速度，该值在函数前面部分已计算得出。 buf_flush_lsn_scan_factor是源码中的固定值，等于3。oldest_lsn的含义是 old_modification小于该oldest_lsn的页面都已经刷新到磁盘。根据公式计算得出 target_lsn. 对buffer p ool instances进行递归计算， instances数量由参数innodb_buffer_pool_instances 决定。计算每个buffer p ool i nstance需要刷新多少页的临时，计算规则：从脏页列表的最后一个页开始查找，将所有oldest_modification小于target_lsn的页面进行计数，临时结果存入pages_for_lsn。将所有的buffer p ool i nstance的pages_for_lsn的值相加后符给sum_pages_for_lsn，但该值在后续还需要除以buf_flush_lsn_scan_factor。因为target_lsn在前面被放大，因此所得的sum_page_for_lsn的值也被放大，在后续后缩小还原。得出当前的buffer p ool i nstance 需要刷新的页面的数量。结果存入page_cleaner-‐ >slots[i].n_pages_requested。公式为pages_for_lsn / buf_flush_lsn_scan_factor + 1; ，原因之前的target_lsn被放大，所以得出的临时结果pages_for_lsn也被放大 buf_flush_lsn_scan_factor倍，现在缩小还原。这次计算得到的page_cleaner-‐ >slots[i].n_pages_requested值为第一次计算结果，这个结果后续还会被更新。注1 注2 注3 注4 注5
22.4.2.4 每个buffer pool instance生成最终的刷新建议。 “脏”页的age , 可以理解为脏页的第一次被修改时间。越早被修改,age越大。如果一个缓冲池中 age大的脏页多，在考虑age的分布情况下，则被建议刷新的页面就多。 n_pages代表预计需要刷新页面的总和，取值由脏页的百分比，redo生成的平均速度，当前脏页刷新平均速度，当前lsn的age,以及脏页的age分布情况，以及参数io_capacity与 max_innodb_capacity这些因素共同决定。如果pct_for_lsn小于30，则认为redo有足够空间，而不需要考虑“脏”页的age分布情况，则建议每个 instance刷新同样的数量。否则，将使用前面根据“脏页”的oldest_modification（也就是age的分布），计算得出每个instance的刷新数量，再综合其它因素，又进行一次计算。每个instance刷新的数量将不同。
23.4.3 请求刷新—pc_request函数 for ( ulint i = 0; i < page_cleaner-‐>n_slots; i++) { page_cleaner_slot_t* slot = &page_cleaner-‐>slots[i]; ………………………………………………………………………….. ………………………………………………………………………….. ………………………………………………………………………….. /* slot-‐>n_pages_requested was already set by page_cleaner_flush_pages_recommendation() */ slot-‐>state = PAGE_CLEANER_STATE_REQUESTED; } os_event_set(page_cleaner-‐>is_requested); 将所有buffer pool instances 的刷新状态设置为 PAGE_CLEANE R_STATE_REQ UESTED，即申请刷新。通过设置事件，唤醒/触发page cleaner 线程，然后调用 pc_flush_slot函数来进行buffer p ool 的刷新。
24.4.4 执行刷新—pc_flush_slot函数 for ( i = 0; i < page_cleaner-‐>n_slots; i++) { slot = &page_cleaner-‐>slots[i]; if (slot-‐>state == PAGE_CLEANER_STATE_REQUESTED) { break; } } slot-‐>state = PAGE_CLEANER_STATE_FLUSHING; /* Flush pages from end of LRU if required */ slot-‐>n_flushed_lru = buf_flush_LRU_list(buf_pool); slot-‐>succeeded_list = buf_flush_do_batch( buf_pool, BUF_FLUSH_LIST, slot-‐>n_pages_requested, page_cleaner-‐>lsn_limit, &slot-‐>n_flushed_list); 寻找一个标志为申请刷新的缓存池实例，然后选为刷新对象，将状态修改为flushing.。然后执行后面的刷新。执行LRU列表的刷新。但除 buffer_pool外没有其他参数，基于lru 列表会刷新多少页？执行“脏页”列表的批量刷新。入参 slot-‐ >n_pages_requested为前面的刷新建议函数为该buffer pool instance生成的建议刷新的页面数。
25.4.4.1 buf_flush_LRU_list函数 scan_depth第一次取值来源于 buffer pool instance里的LRU列表的长度，也就列表中页面数量。 scan_depth第二次取值来自 srv_LRU_scan_depth与LRU 列表长度比较后的小值。也就是当LRU列表的长度大于参数 innodb_lru_scan_depth(默认1024）时，等于 innodb_lru_scan_depth，否则等于LRU长度。 scan_depth作为参数，传递给 buf_flush_do_batch函数，再经过几次传递之后，最后传递给 buf_flush_LRU_list_batch 函数的max 参数变量
26.4.4.1.1 buf_flush_LRU_list_batch函数从buffer的LRU列表末尾取出block,满足下面所有条件，才进入循环体： 1.如果bpage存在。 2.且count+evict_count27.4.4.2 buf_flush_do_batch函数该函数在pc_flush_slot中的被调用语句： slot-‐>succeeded_list = buf_flush_do_batch( buf_pool, BUF_FLUSH_LIST, slot-‐>n_pages_requested, page_cleaner-‐>lsn_limit, &slot-‐>n_flushed_list); 参数解释： buf_pool:需要进行刷新的缓存池实例指针。 slot-‐>n_pages_requested：为page_cleaner_flush_pages_recommendation 函数为该buffer_pool建议的需要被刷新的页面数量，实际刷新的页面并不一定等于该值。后面将详细介绍。 page_cleaner-‐>lsn_limit：也为recommendation函数最后部分的语句所赋值*lsn_limit = LSN_MAX。 BUF_FLUSH_LIST：刷新类型，“脏”列表，即对“脏”页列表进行刷新。 &slot-‐>n_flushed_list：作为返回参数，被赋值该buffer b ool instance实际的被刷新的页面数。其最终调用buf_do_flush_list_batch(buf_pool, min_n, lsn_limit);来进行刷新其中min_n来自slot-‐>n_pages_requested， lsn_limit来自page_cleaner-‐>lsn_limit，即LSN_MAX28.4.4.2.1 buf_do_flush_list_batch函数循环中止条件：直到 count >= min_n 或者脏页列表为空。即所刷新的page等于 page_cleaner_flush_pag es_recommendation函数所建议的刷新数量，或者“脏”页列表为空。因为page cleaner线程调用该函数做批量刷新的时候，lsn_limit 参数值为极大值，因此无需考虑page的 oldest_modification。29.4.5 等待所有缓冲池实例刷新完成—pc_wait_finished 在buf_flush_page_cleaner_coordinator函数的调用如下: pc_wait_finished(&n_flushed_lru, &n_flushed_list); 注释： n_flushed_lru:用来接收从lru列表中刷新了多少页面。 n_flushed_list:用于接收从”脏”页列表（flush列表）刷新了多少页面。在pc_wait_finished函数体内 os_event_wait(page_cleaner-‐>is_finished); 等待被刷新完成事件唤醒，如果需要刷新的数量比较多，而磁盘的io能力比较差等，将导致刷新不能及时完成。下一次刷新循环无法“准时”进行。如果相邻两次刷新的启动时间间隔4000ms,错误日志将有相关提示信息。30.回顾“3.4 缓存池的有关参数”中提到的参数缓冲池有关参数 innodb_buffer_pool_size innodb_buffer_pool_instances innodb_flushing_avg_loops innodb_max_dirty_pages_pct innodb_max_dirty_pages_pct_lwm innodb_io_capacity_max innodb_io_capacity innodb_lru_scan_depth innodb_adaptive_flushing innodb_adaptive_flushing_lwm innodb_old_blocks_time innodb_old_blocks_pct 。。。。。。每个参数的含义？是否已有非常直观的认识？除最后两个参数，其他参数均在讲解源码中涉及。31.
27.4.4.2 buf_flush_do_batch函数该函数在pc_flush_slot中的被调用语句： slot-‐>succeeded_list = buf_flush_do_batch( buf_pool, BUF_FLUSH_LIST, slot-‐>n_pages_requested, page_cleaner-‐>lsn_limit, &slot-‐>n_flushed_list); 参数解释： buf_pool:需要进行刷新的缓存池实例指针。 slot-‐>n_pages_requested：为page_cleaner_flush_pages_recommendation 函数为该buffer_pool建议的需要被刷新的页面数量，实际刷新的页面并不一定等于该值。后面将详细介绍。 page_cleaner-‐>lsn_limit：也为recommendation函数最后部分的语句所赋值*lsn_limit = LSN_MAX。 BUF_FLUSH_LIST：刷新类型，“脏”列表，即对“脏”页列表进行刷新。 &slot-‐>n_flushed_list：作为返回参数，被赋值该buffer b ool instance实际的被刷新的页面数。其最终调用buf_do_flush_list_batch(buf_pool, min_n, lsn_limit);来进行刷新其中min_n来自slot-‐>n_pages_requested， lsn_limit来自page_cleaner-‐>lsn_limit，即LSN_MAX
28.4.4.2.1 buf_do_flush_list_batch函数循环中止条件：直到 count >= min_n 或者脏页列表为空。即所刷新的page等于 page_cleaner_flush_pag es_recommendation函数所建议的刷新数量，或者“脏”页列表为空。因为page cleaner线程调用该函数做批量刷新的时候，lsn_limit 参数值为极大值，因此无需考虑page的 oldest_modification。
29.4.5 等待所有缓冲池实例刷新完成—pc_wait_finished 在buf_flush_page_cleaner_coordinator函数的调用如下: pc_wait_finished(&n_flushed_lru, &n_flushed_list); 注释： n_flushed_lru:用来接收从lru列表中刷新了多少页面。 n_flushed_list:用于接收从”脏”页列表（flush列表）刷新了多少页面。在pc_wait_finished函数体内 os_event_wait(page_cleaner-‐>is_finished); 等待被刷新完成事件唤醒，如果需要刷新的数量比较多，而磁盘的io能力比较差等，将导致刷新不能及时完成。下一次刷新循环无法“准时”进行。如果相邻两次刷新的启动时间间隔4000ms,错误日志将有相关提示信息。
30.回顾“3.4 缓存池的有关参数”中提到的参数缓冲池有关参数 innodb_buffer_pool_size innodb_buffer_pool_instances innodb_flushing_avg_loops innodb_max_dirty_pages_pct innodb_max_dirty_pages_pct_lwm innodb_io_capacity_max innodb_io_capacity innodb_lru_scan_depth innodb_adaptive_flushing innodb_adaptive_flushing_lwm innodb_old_blocks_time innodb_old_blocks_pct 。。。。。。每个参数的含义？是否已有非常直观的认识？除最后两个参数，其他参数均在讲解源码中涉及。
31.