最近一次故障，由于开发同学大量的slow sql导致数据库响应缓慢，通过ptstack发现大量的thread都处在os_thread_sleep状态，
所以打算从底层分析到底为何会有如此多的sleep。
下面罗列一下当时详细的堆栈情况： - Thread 273 (Thread 0x2acecc040700 (LWP 182856)):
- #0 0x00000033b9ce1373 in select () from /lib64/libc.so.6
- #1 0x000000000096d1af in os_thread_sleep(unsigned long) ()
- #2 0x00000000009cd334 in srv_conc_enter_innodb(trx_t*) ()
- #3 0x0000000000917028 in ha_innobase::write_row(unsigned char*) ()
- #4 0x00000000005c5c3d in handler::ha_write_row(unsigned char*) ()
- #5 0x00000000006e4245 in write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) ()
- #6 0x00000000006e9cc9 in mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool) ()
- #7 0x00000000006fcb5c in mysql_execute_command(THD*) ()
- #8 0x0000000000701f38 in mysql_parse(THD*, char*, unsigned int, Parser_state*) ()
- #9 0x000000000070375c in dispatch_command(enum_server_command, THD*, char*, unsigned int) ()
- #10 0x000000000078e158 in threadpool_process_request(THD*) ()
- #11 0x000000000078f12d in worker_main(void*) ()
- #12 0x0000000000b03a83 in pfs_spawn_thread ()
- #13 0x00000033ba0079d1 in start_thread () from /lib64/libpthread.so.0
- #14 0x00000033b9ce88fd in clone () from /lib64/libc.so.6
通过堆栈，我们很清晰的看到，函数通过srv_conc_enter_innodb调用os_thread_sleep ,进而进入sleep 状态。
接下来，我们略过其他的冗长的函数，直接从srv_conc_enter_innodb看看此时mysql到底在做什么 。
首先贴一下处理该部分逻辑的代码段，关于该代码段的具体含义，后面会给出。 - /*********************************************************************//**
- Handle the scheduling of a user thread that wants to enter InnoDB. Setting
- srv_adaptive_max_sleep_delay > 0 switches the adaptive sleep calibration to
- ON. When set, we want to wait in the queue for as little time as possible.
- However, very short waits will result in a lot of context switches and that
- is also not desirable. When threads need to sleep multiple times we increment
- os_thread_sleep_delay by one. When we see threads getting a slot without
- waiting and there are no other threads waiting in the queue, we try and reduce
- the wait as much as we can. Currently we reduce it by half each time. If the
- thread only had to wait for one turn before it was able to enter InnoDB we
- decrement it by one. This is to try and keep the sleep time stable around the
- "optimum" sleep time. */
-
- static void
- srv_conc_enter_innodb_with_atomics(
- /*===============================*/
- trx_t* trx) /*!< in/out: transaction that wants
- to enter InnoDB */
- {
- ulint n_sleeps = 0;
- ibool notified_mysql = FALSE;
- ut_a(!trx->declared_to_be_inside_innodb);
- for (;;) {
- ulint sleep_in_us;
- if (srv_conc.n_active < (lint) srv_thread_concurrency) {
- ulint n_active;
-
- /* Check if there are any free tickets. */
- n_active = os_atomic_increment_lint(
- &srv_conc.n_active, 1);
-
- if (n_active <= srv_thread_concurrency) {
-
- srv_enter_innodb_with_tickets(trx);
-
- if (notified_mysql) {
- (void) os_atomic_decrement_lint(
- &srv_conc.n_waiting, 1);
- thd_wait_end(trx->mysql_thd);
- }
-
- if (srv_adaptive_max_sleep_delay > 0) {
- if (srv_thread_sleep_delay > 20
- && n_sleeps == 1) {
-
- --srv_thread_sleep_delay;
- }
-
- if (srv_conc.n_waiting == 0) {
- srv_thread_sleep_delay >>= 1;
- }
- }
- return;
- }
-
- /* Since there were no free seats, we relinquish the overbooked ticket. */
-
- (void) os_atomic_decrement_lint(&srv_conc.n_active, 1);
- }
- if (!notified_mysql) {
- (void) os_atomic_increment_lint(&srv_conc.n_waiting, 1);
-
- /* Release possible search system latch this thread has */
- if (trx->has_search_latch) {
- trx_search_latch_release_if_reserved(trx);
- }
-
- thd_wait_begin(trx->mysql_thd, THD_WAIT_USER_LOCK);
- notified_mysql = TRUE;
- }
-
- trx->op_info = "sleeping before entering InnoDB";
- sleep_in_us = srv_thread_sleep_delay;
-
- /* Guard against overflow when adaptive sleep delay is on. */
- if (srv_adaptive_max_sleep_delay > 0
- && sleep_in_us > srv_adaptive_max_sleep_delay) {
- sleep_in_us = srv_adaptive_max_sleep_delay;
- srv_thread_sleep_delay = sleep_in_us;
- }
- os_thread_sleep(sleep_in_us);
- trx->innodb_que_wait_timer += sleep_in_us;
- trx->op_info = "";
- ++n_sleeps;
-
- if (srv_adaptive_max_sleep_delay > 0 && n_sleeps > 1) {
- ++srv_thread_sleep_delay;
- }
- }
- }
从代码，我们来看看两部分内容。 一.如果目前thread running的个数，已经超过了innodb_thread_concurrency，该thread会如何处置呢 ？ 我们来看看重点部分： - trx->op_info = "sleeping before entering InnoDB";
- sleep_in_us = srv_thread_sleep_delay;
- /* Guard against overflow when adaptive sleep delay is on. */
-
- if (srv_adaptive_max_sleep_delay > 0
- && sleep_in_us > srv_adaptive_max_sleep_delay) {
- sleep_in_us = srv_adaptive_max_sleep_delay;
- srv_thread_sleep_delay = sleep_in_us;
- }
- os_thread_sleep(sleep_in_us);
- trx->innodb_que_wait_timer += sleep_in_us;
- trx->op_info = "";
- ++n_sleeps;
- if (srv_adaptive_max_sleep_delay > 0 && n_sleeps > 1) {
- ++srv_thread_sleep_delay;
- }
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是如何处理这部分逻辑:
- if (srv_conc.n_active < (lint) srv_thread_concurrency) {
- ulint n_active;
- /* Check if there are any free tickets. */
- n_active = os_atomic_increment_lint( &srv_conc.n_active, 1);
- if (n_active <= srv_thread_concurrency) {
- srv_enter_innodb_with_tickets(trx);
- if (notified_mysql) {
- (void) os_atomic_decrement_lint(&srv_conc.n_waiting, 1);
- thd_wait_end(trx->mysql_thd);
- }
- if (srv_adaptive_max_sleep_delay > 0) {
- if (srv_thread_sleep_delay > 20
- && n_sleeps == 1) {
- --srv_thread_sleep_delay;
- }
- if (srv_conc.n_waiting == 0) {
- srv_thread_sleep_delay >>= 1;
- }
- }
- return;
- }
-
- /* Since there were no free seats, we relinquish
- the overbooked ticket. */
- (void) os_atomic_decrement_lint( &srv_conc.n_active, 1);
- }
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切换频繁.如何合理的设置该值，需要进一步的测试报告。
阅读(7563) | 评论(1) | 转发(1) |
