Chinaunix首页 | 论坛 | 博客
  • 博客访问: 364132
  • 博文数量: 79
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 42
  • 用 户 组: 普通用户
  • 注册时间: 2014-03-30 12:25
文章分类

全部博文(79)

文章存档

2019年(1)

2017年(19)

2016年(25)

2015年(30)

2014年(4)

分类: Mysql/postgreSQL

2015-06-18 16:48:15

      最近一次故障,由于开发同学大量的slow sql导致数据库响应缓慢,通过ptstack发现大量的thread都处在os_thread_sleep状态,
所以打算从底层分析到底为何会有如此多的sleep。


下面罗列一下当时详细的堆栈情况:

点击(此处)折叠或打开

  1. Thread 273 (Thread 0x2acecc040700 (LWP 182856)):
  2. #0 0x00000033b9ce1373 in select () from /lib64/libc.so.6
  3. #1 0x000000000096d1af in os_thread_sleep(unsigned long) ()
  4. #2 0x00000000009cd334 in srv_conc_enter_innodb(trx_t*) ()
  5. #3 0x0000000000917028 in ha_innobase::write_row(unsigned char*) ()
  6. #4 0x00000000005c5c3d in handler::ha_write_row(unsigned char*) ()
  7. #5 0x00000000006e4245 in write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) ()
  8. #6 0x00000000006e9cc9 in mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool) ()
  9. #7 0x00000000006fcb5c in mysql_execute_command(THD*) ()
  10. #8 0x0000000000701f38 in mysql_parse(THD*, char*, unsigned int, Parser_state*) ()
  11. #9 0x000000000070375c in dispatch_command(enum_server_command, THD*, char*, unsigned int) ()
  12. #10 0x000000000078e158 in threadpool_process_request(THD*) ()
  13. #11 0x000000000078f12d in worker_main(void*) ()
  14. #12 0x0000000000b03a83 in pfs_spawn_thread ()
  15. #13 0x00000033ba0079d1 in start_thread () from /lib64/libpthread.so.0
  16. #14 0x00000033b9ce88fd in clone () from /lib64/libc.so.6
通过堆栈,我们很清晰的看到,函数通过srv_conc_enter_innodb调用os_thread_sleep ,进而进入sleep 状态。
接下来,我们略过其他的冗长的函数,直接从srv_conc_enter_innodb看看此时mysql到底在做什么 。

首先贴一下处理该部分逻辑的代码段,关于该代码段的具体含义,后面会给出。

点击(此处)折叠或打开

  1. /*********************************************************************//**
  2. Handle the scheduling of a user thread that wants to enter InnoDB. Setting
  3. srv_adaptive_max_sleep_delay > 0 switches the adaptive sleep calibration to
  4. ON. When set, we want to wait in the queue for as little time as possible.
  5. However, very short waits will result in a lot of context switches and that
  6. is also not desirable. When threads need to sleep multiple times we increment
  7. os_thread_sleep_delay by one. When we see threads getting a slot without
  8. waiting and there are no other threads waiting in the queue, we try and reduce
  9. the wait as much as we can. Currently we reduce it by half each time. If the
  10. thread only had to wait for one turn before it was able to enter InnoDB we
  11. decrement it by one. This is to try and keep the sleep time stable around the
  12. "optimum" sleep time. */

  13. static void
  14. srv_conc_enter_innodb_with_atomics(
  15. /*===============================*/
  16.         trx_t* trx) /*!< in/out: transaction that wants
  17.                                         to enter InnoDB */
  18. {
  19.         ulint n_sleeps = 0;
  20.         ibool notified_mysql = FALSE;
  21.         ut_a(!trx->declared_to_be_inside_innodb);
  22.         for (;;) {
  23.                 ulint sleep_in_us;
  24.                 if (srv_conc.n_active < (lint) srv_thread_concurrency) {
  25.                         ulint n_active;

  26.                         /* Check if there are any free tickets. */
  27.                         n_active = os_atomic_increment_lint(
  28.                                 &srv_conc.n_active, 1);

  29.                         if (n_active <= srv_thread_concurrency) {

  30.                                 srv_enter_innodb_with_tickets(trx);

  31.                                 if (notified_mysql) {
  32.                                         (void) os_atomic_decrement_lint(
  33.                                                 &srv_conc.n_waiting, 1);
  34.                                                    thd_wait_end(trx->mysql_thd);
  35.                                 }

  36.                                 if (srv_adaptive_max_sleep_delay > 0) {
  37.                                         if (srv_thread_sleep_delay > 20
  38.                                             && n_sleeps == 1) {

  39.                                                 --srv_thread_sleep_delay;
  40.                                         }

  41.                                         if (srv_conc.n_waiting == 0) {
  42.                                                 srv_thread_sleep_delay >>= 1;
  43.                                         }
  44.                                 }
  45.                                 return;
  46.                         }

  47. /* Since there were no free seats, we relinquish the overbooked ticket. */

  48. (void) os_atomic_decrement_lint(&srv_conc.n_active, 1);
  49.   }
  50.    if (!notified_mysql) {
  51.                         (void) os_atomic_increment_lint(&srv_conc.n_waiting, 1);

  52.   /* Release possible search system latch this thread has */
  53.    if (trx->has_search_latch) {
  54.                          trx_search_latch_release_if_reserved(trx);
  55.                         }

  56. thd_wait_begin(trx->mysql_thd, THD_WAIT_USER_LOCK);
  57.                         notified_mysql = TRUE;
  58.  }

  59.                 trx->op_info = "sleeping before entering InnoDB";
  60.                 sleep_in_us = srv_thread_sleep_delay;

  61.                 /* Guard against overflow when adaptive sleep delay is on. */
  62.                 if (srv_adaptive_max_sleep_delay > 0
  63.                     && sleep_in_us > srv_adaptive_max_sleep_delay) {
  64.                         sleep_in_us = srv_adaptive_max_sleep_delay;
  65.                         srv_thread_sleep_delay = sleep_in_us;
  66.                 }
  67.                                os_thread_sleep(sleep_in_us);
  68.                 trx->innodb_que_wait_timer += sleep_in_us;
  69.                 trx->op_info = "";
  70.                 ++n_sleeps;

  71.                 if (srv_adaptive_max_sleep_delay > 0 && n_sleeps > 1) {
  72.                         ++srv_thread_sleep_delay;
  73.                 }
  74.         }
  75. }
从代码,我们来看看两部分内容。
一.如果目前thread running的个数,已经超过了innodb_thread_concurrency,该thread会如何处置呢 ?
我们来看看重点部分:

点击(此处)折叠或打开

  1. trx->op_info = "sleeping before entering InnoDB";
  2.             sleep_in_us = srv_thread_sleep_delay;
  3.                 /* Guard against overflow when adaptive sleep delay is on. */

  4.                 if (srv_adaptive_max_sleep_delay > 0
  5.                     && sleep_in_us > srv_adaptive_max_sleep_delay) {
  6.                         sleep_in_us = srv_adaptive_max_sleep_delay;
  7.                         srv_thread_sleep_delay = sleep_in_us;
  8.                 }
  9.                                os_thread_sleep(sleep_in_us);
  10.                 trx->innodb_que_wait_timer += sleep_in_us;
  11.                 trx->op_info = "";
  12.                 ++n_sleeps;
  13.                 if (srv_adaptive_max_sleep_delay > 0 && n_sleeps > 1) {
  14.                         ++srv_thread_sleep_delay;
  15.                 }


 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是如何处理这部分逻辑:         

点击(此处)折叠或打开

  1. if (srv_conc.n_active < (lint) srv_thread_concurrency) {
  2.                         ulint n_active;
  3.                         /* Check if there are any free tickets. */
  4.                         n_active = os_atomic_increment_lint( &srv_conc.n_active, 1);
  5.                         if (n_active <= srv_thread_concurrency) {
  6.                             srv_enter_innodb_with_tickets(trx);
  7.                                 if (notified_mysql) {
  8.                                       (void) os_atomic_decrement_lint(&srv_conc.n_waiting, 1);
  9.                                                    thd_wait_end(trx->mysql_thd);
  10.                                 }
  11.                           if (srv_adaptive_max_sleep_delay > 0) {
  12.                                         if (srv_thread_sleep_delay > 20
  13.                                             && n_sleeps == 1) {
  14.                                           --srv_thread_sleep_delay;
  15.                                         }
  16.                                         if (srv_conc.n_waiting == 0) {
  17.                                                 srv_thread_sleep_delay >>= 1;
  18.                                         }
  19.                                 }
  20.                                 return;
  21.                         }

  22.                         /* Since there were no free seats, we relinquish
  23.                         the overbooked ticket. */
  24.                         (void) os_atomic_decrement_lint( &srv_conc.n_active, 1);
  25.                 }

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切换频繁.如何合理的设置该值,需要进一步的测试报告。
                              

阅读(1945) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~