mysql5.6对于thread_running_concurrency处理的源码分析

时间：2015-06-19 02:14:44 阅读：367 评论：0 收藏：0 [点我收藏+]

最近一次故障，由于开发同学大量的slow sql导致数据库响应缓慢，通过ptstack发现大量的thread都处在os_thread_sleep状态， 所以打算从底层分析到底为何会有如此多的sleep。 下面罗列一下当时详细的堆栈情况： <div> <div class="codeheads"> 点击(此处)折叠或打开 </div> <div id="code353" class="codeText"> <ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"> <li> Thread 273 (Thread 0x2acecc040700 (LWP 182856)): </li> <li> #0 0x00000033b9ce1373 in select () from /lib64/libc.so.6 </li> <li> #1 0x000000000096d1af in os_thread_sleep(unsigned long) () </li> <li> #2 0x00000000009cd334 in srv_conc_enter_innodb(trx_t*) () </li> <li> #3 0x0000000000917028 in ha_innobase::write_row(unsigned char*) () </li> <li> #4 0x00000000005c5c3d in handler::ha_write_row(unsigned char*) () </li> <li> #5 0x00000000006e4245 in write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) () </li> <li> #6 0x00000000006e9cc9 in mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool) () </li> <li> #7 0x00000000006fcb5c in mysql_execute_command(THD*) () </li> <li> #8 0x0000000000701f38 in mysql_parse(THD*, char*, unsigned int, Parser_state*) () </li> <li> #9 0x000000000070375c in dispatch_command(enum_server_command, THD*, char*, unsigned int) () </li> <li> #10 0x000000000078e158 in threadpool_process_request(THD*) () </li> <li> #11 0x000000000078f12d in worker_main(void*) () </li> <li> #12 0x0000000000b03a83 in pfs_spawn_thread () </li> <li> #13 0x00000033ba0079d1 in start_thread () from /lib64/libpthread.so.0 </li> <li> #14 0x00000033b9ce88fd in clone () from /lib64/libc.so.6 </li> </ol> </div> </div> 通过堆栈，我们很清晰的看到，函数通过srv_conc_enter_innodb调用os_thread_sleep ,进而进入sleep 状态。 接下来，我们略过其他的冗长的函数，直接从srv_conc_enter_innodb看看此时mysql到底在做什么。 首先贴一下处理该部分逻辑的代码段，关于该代码段的具体含义，后面会给出。 <div> <div class="codeheads"> 点击(此处)折叠或打开 </div> <div id="code88" class="codeText"> <ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"> <li> /*********************************************************************//** </li> <li> Handle the scheduling of a user thread that wants to enter InnoDB. Setting </li> <li> srv_adaptive_max_sleep_delay > 0 switches the adaptive sleep calibration to </li> <li> ON. When set, we want to wait in the queue for as little time as possible. </li> <li> However, very short waits will result in a lot of context switches and that </li> <li> is also not desirable. When threads need to sleep multiple times we increment </li> <li> os_thread_sleep_delay by one. When we see threads getting a slot without </li> <li> waiting and there are no other threads waiting in the queue, we try and reduce </li> <li> the wait as much as we can. Currently we reduce it by half each time. If the </li> <li> thread only had to wait for one turn before it was able to enter InnoDB we </li> <li> decrement it by one. This is to try and keep the sleep time stable around the </li> <li> "optimum" sleep time. */ </li> <li> </li> <li> static void </li> <li> srv_conc_enter_innodb_with_atomics( </li> <li> /*===============================*/ </li> <li>         trx_t* trx) /*!< in/out: transaction that wants </li> <li>                                         to enter InnoDB */ </li> <li> { </li> <li>         ulint n_sleeps = 0; </li> <li>         ibool notified_mysql = FALSE; </li> <li>         ut_a(!trx->declared_to_be_inside_innodb); </li> <li>         for (;;) { </li> <li>                 ulint sleep_in_us; </li> <li>                 if (srv_conc.n_active < (lint) srv_thread_concurrency) { </li> <li>                         ulint n_active; </li> <li> </li> <li>                         /* Check if there are any free tickets. */ </li> <li>                         n_active = os_atomic_increment_lint( </li> <li>                                 &srv_conc.n_active, 1); </li> <li> </li> <li>                         if (n_active <= srv_thread_concurrency) { </li> <li> </li> <li>                                 srv_enter_innodb_with_tickets(trx); </li> <li> </li> <li>                                 if (notified_mysql) { </li> <li>                                         (void) os_atomic_decrement_lint( </li> <li>                                                 &srv_conc.n_waiting, 1); </li> <li>                                                    thd_wait_end(trx->mysql_thd); </li> <li>                                 } </li> <li> </li> <li>                                 if (srv_adaptive_max_sleep_delay > 0) { </li> <li>                                         if (srv_thread_sleep_delay > 20 </li> <li>                                             && n_sleeps == 1) { </li> <li> </li> <li>                                                 --srv_thread_sleep_delay; </li> <li>                                         } </li> <li> </li> <li>                                         if (srv_conc.n_waiting == 0) { </li> <li>                                                 srv_thread_sleep_delay >>= 1; </li> <li>                                         } </li> <li>                                 } </li> <li>                                 return; </li> <li>                         } </li> <li> </li> <li> /* Since there were no free seats, we relinquish the overbooked ticket. */ </li> <li> </li> <li> (void) os_atomic_decrement_lint(&srv_conc.n_active, 1); </li> <li>   } </li> <li>    if (!notified_mysql) { </li> <li>                         (void) os_atomic_increment_lint(&srv_conc.n_waiting, 1); </li> <li> </li> <li>   /* Release possible search system latch this thread has */ </li> <li>    if (trx->has_search_latch) { </li> <li>                          trx_search_latch_release_if_reserved(trx); </li> <li>                         } </li> <li> </li> <li> thd_wait_begin(trx->mysql_thd, THD_WAIT_USER_LOCK); </li> <li>                         notified_mysql = TRUE; </li> <li>  } </li> <li> </li> <li>                 trx->op_info = "sleeping before entering InnoDB"; </li> <li>                 sleep_in_us = srv_thread_sleep_delay; </li> <li> </li> <li>                 /* Guard against overflow when adaptive sleep delay is on. */ </li> <li>                 if (srv_adaptive_max_sleep_delay > 0 </li> <li>                     && sleep_in_us > srv_adaptive_max_sleep_delay) { </li> <li>                         sleep_in_us = srv_adaptive_max_sleep_delay; </li> <li>                         srv_thread_sleep_delay = sleep_in_us; </li> <li>                 } </li> <li>                                os_thread_sleep(sleep_in_us); </li> <li>                 trx->innodb_que_wait_timer += sleep_in_us; </li> <li>                 trx->op_info = ""; </li> <li>                 ++n_sleeps; </li> <li> </li> <li>                 if (srv_adaptive_max_sleep_delay > 0 && n_sleeps > 1) { </li> <li>                         ++srv_thread_sleep_delay; </li> <li>                 } </li> <li>         } </li> <li> } </li> </ol> </div> </div> 从代码，我们来看看两部分内容。 一.如果目前thread running的个数，已经超过了innodb_thread_concurrency，该thread会如何处置呢？ 我们来看看重点部分： <div> <div class="codeheads"> 点击(此处)折叠或打开 </div> <div id="code50" class="codeText"> <ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"> <li> trx->op_info = "sleeping before entering InnoDB"; </li> <li>             sleep_in_us = srv_thread_sleep_delay; </li> <li>                 /* Guard against overflow when adaptive sleep delay is on. */ </li> <li> </li> <li>                 if (srv_adaptive_max_sleep_delay > 0 </li> <li>                     && sleep_in_us > srv_adaptive_max_sleep_delay) { </li> <li>                         sleep_in_us = srv_adaptive_max_sleep_delay; </li> <li>                         srv_thread_sleep_delay = sleep_in_us; </li> <li>                 } </li> <li>                                os_thread_sleep(sleep_in_us); </li> <li>                 trx->innodb_que_wait_timer += sleep_in_us; </li> <li>                 trx->op_info = ""; </li> <li>                 ++n_sleeps; </li> <li>                 if (srv_adaptive_max_sleep_delay > 0 && n_sleeps > 1) { </li> <li>                         ++srv_thread_sleep_delay; </li> <li>                 } </li> </ol> </div> </div>  1.设置该thread需要sleep的时间为srv_thread_sleep_delay(默认10ms).  2.判断需要sleep的时间是否大于srv_adaptive_max_sleep_delay（默认150ms),如果大于该值，调整为该值.(避免sleep 时间过久，性能受到影响).  3.开始sleep，如果该线程已经sleep过一次了，则下次sleep的时间在上次基础上加1（说明系统比较繁忙，该thread多次醒来都无法得到处理，不如继续让它多睡一会儿）.    二：如果thread running的个数，还未超过innodb_thread_concurrency，也就是说队列尚未满，此时thread可以进入innodb，从而得到及时的处理。  此时，我们看看，mysql是如何处理这部分逻辑:          <div> <div class="codeheads"> 点击(此处)折叠或打开 </div> <div id="code848" class="codeText"> <ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"> <li> if (srv_conc.n_active < (lint) srv_thread_concurrency) { </li> <li>                         ulint n_active; </li> <li>                         /* Check if there are any free tickets. */ </li> <li>                         n_active = os_atomic_increment_lint( &srv_conc.n_active, 1); </li> <li>                         if (n_active <= srv_thread_concurrency) { </li> <li>                             srv_enter_innodb_with_tickets(trx); </li> <li>                                 if (notified_mysql) { </li> <li>                                       (void) os_atomic_decrement_lint(&srv_conc.n_waiting, 1); </li> <li>                                                    thd_wait_end(trx->mysql_thd); </li> <li>                                 } </li> <li>                           if (srv_adaptive_max_sleep_delay > 0) { </li> <li>                                         if (srv_thread_sleep_delay > 20 </li> <li>                                             && n_sleeps == 1) { </li> <li>                                           --srv_thread_sleep_delay; </li> <li>                                         } </li> <li>                                         if (srv_conc.n_waiting == 0) { </li> <li>                                                 srv_thread_sleep_delay >>= 1; </li> <li>                                         } </li> <li>                                 } </li> <li>                                 return; </li> <li>                         } </li> <li> </li> <li>                         /* Since there were no free seats, we relinquish </li> <li>                         the overbooked ticket. */ </li> <li>                         (void) os_atomic_decrement_lint( &srv_conc.n_active, 1); </li> <li>                 } </li> </ol> </div> </div> 1.将当前活跃的线程数量加1: os_atomic_increment_lint(&srv_conc.n_active, 1) 2.由于可能有很多thread 满足 srv_conc.n_active < (lint) srv_thread_concurrency 条件，从而进入执行os_atomic_increment_lint 但由于该语句是原子性，所以保证了每次加1必须是串行的。同时，加1完成之后，需要再一次的判断当前活跃的线程数是否超过了设置的innodb_thread_concurrency. bug： 比如srv_thread_concurrency=3 if (srv_conc.n_active < (lint) srv_thread_concurrency) {                         ulint   n_active; /*第一个thread 由于满足条件，将要开始执行n_active加1操作。（未执行） */ /*第二个thread 由于满足条件，将要开始执行n_active加1操作。（未执行） */ /*第三个thread 由于满足条件，将要开始执行n_active加1操作。（未执行） */ /*第四个thread 由于满足条件，将要开始执行n_active加1操作。（未执行） */ /*第五个thread 由于满足条件，将要开始执行n_active加1操作。（未执行） */ /*第六个thread 由于满足条件，将要开始执行n_active加1操作。（未执行） */       /* Check if there are any free tickets. */ n_active = os_atomic_increment_lint(&srv_conc.n_active, 1); /* 此时，上述6个线程依次加1，则n_active=6,下面代码尚未执行 */ /*再次执行下面的判断时，由于不满足条件，从而进入sleep等待，等到下次sleep完之后，发现thread running queue已经满了，从而被误伤到.*/  if (n_active <= srv_thread_concurrency) {     ...   }    所以最好对n_active 进行加锁保护。 3.满足2的条件，如果该thread是由于之前sleep状态醒来，发现活跃线程数小于innodb_thread_concurrency，则将wating thread的数量减1. 如果该thread之前有过一次sleep,且srv_thread_sleep_delay > 20，则将srv_thread_sleep_delay减1（系统不繁忙，可能sleep更短的时间就可以进入innodb了）。 4.判断当前等待队列的情况(wating thread的个数)，如果waiting thread的个数为0，说明目前等待队列非常空闲，设置srv_thread_sleep_delay为之前的1/2,表示系统很空闲。设置较小的值，避免因为偶尔的性能影响，较大的降低系统性能  if (srv_conc.n_waiting == 0) {     srv_thread_sleep_delay >>= 1;  } 。 总结： 从上述代码中，我们可以很清楚的看到，通过系统的繁忙状态，来控制wating thread 进行sleep的时间。 在系统比较繁忙的时候，sleep的时间相应增加，系统低负载的时候，sleep的时间相应减少，从而更加灵活的缓解系统的压力。 srv_thread_sleep_delay的设置，过大可能导致系统在空闲时，由于稍微的抖动，导致系统性能显著下降。而设置过小，又会导致在 在系统负载较大时，CPU切换频繁.如何合理的设置该值，需要进一步的测试报告。                                <div> </div>

原文：http://blog.chinaunix.net/uid-20708886-id-5085516.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)