监控信息如下:
当时的mysqld堆栈信息如下:
点击(此处)折叠或打开
- Thread 50 (Thread 0x2afb6591a700 (LWP 7020)):
- #0 0x0000003c34ef808f in __lll_unlock_wake_private () from /lib64/libc.so.6
- #1 0x0000003c34e9dccc in _L_unlock_2226 () from /lib64/libc.so.6
- #2 0x0000003c34e9db01 in __tz_convert () from /lib64/libc.so.6
- #3 0x0000000000783012 in Time_zone_system::gmt_sec_to_TIME(st_mysql_time*, long) const ()
- #4 0x00000000007c177b in Field_timestampf::get_date_internal(st_mysql_time*) ()
- #5 0x00000000007c23dd in Field_temporal_with_date::val_str(String*, String*) ()
- #6 0x0000000000672fa8 in Protocol_text::store(Field*) ()
- #7 0x00000000006722f5 in Protocol::send_result_set_row(List<Item>*) ()
- #8 0x00000000006c77af in select_send::send_data(List<Item>&) ()
- #9 0x00000000006db6b0 in end_send(JOIN*, st_join_table*, bool) ()
- #10 0x00000000006d6c4f in evaluate_join_record(JOIN*, st_join_table*) ()
- #11 0x00000000006d6fbb in sub_select(JOIN*, st_join_table*, bool) ()
- #12 0x00000000006d61e8 in JOIN::exec() ()
- #13 0x000000000071f0d5 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, SQL_I_List<st_order>*, SQL_I_List<st_order>*, Item*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) ()
- #14 0x000000000071f935 in handle_select(THD*, select_result*, unsigned long) ()
- #15 0x000000000057fe02 in execute_sqlcom_select(THD*, TABLE_LIST*) ()
- #16 0x00000000006f935c in mysql_execute_command(THD*) ()
- #17 0x00000000006fe5e8 in mysql_parse(THD*, char*, unsigned int, Parser_state*) ()
- #18 0x00000000006ffdc9 in dispatch_command(enum_server_command, THD*, char*, unsigned int) ()
- #19 0x0000000000789a08 in threadpool_process_request(THD*) ()
- #20 0x000000000078a9dd in worker_main(void*) ()
- #21 0x0000000000b4a8c3 in pfs_spawn_thread ()
- #22 0x0000003c352079d1 in start_thread () from /lib64/libpthread.so.0
- #23 0x0000003c34ee88fd in clone () from /lib64/libc.so.6
深入分析mysql时区相关的函数:
点击(此处)折叠或打开
- void
- Time_zone_system::gmt_sec_to_TIME(MYSQL_TIME *tmp, my_time_t t) const
- {
- struct tm tmp_tm;
- time_t tmp_t= (time_t)t;
- localtime_r(&tmp_t, &tmp_tm);
- localtime_to_TIME(tmp, &tmp_tm);
- tmp->time_type= MYSQL_TIMESTAMP_DATETIME;
- adjust_leap_second(tmp);
- }
继续进入localtime_r函数
点击(此处)折叠或打开
- struct tm *
- __tz_convert (const time_t *timer, int use_localtime, struct tm *tp)
- {
- long int leap_correction;
- int leap_extra_secs;
- if (timer == NULL)
- {
- __set_errno (EINVAL);
- return NULL;
- }
- __libc_lock_lock (tzset_lock);
- /* Update internal database according to current TZ setting.
- POSIX.1 8.3.7.2 says that localtime_r is not required to set tzname.
- This is a good idea since this allows at least a bit more parallelism. */
- tzset_internal (tp == &_tmbuf && use_localtime, 1);
- if (__use_tzfile)
- __tzfile_compute (*timer, use_localtime, &leap_correction,
- &leap_extra_secs, tp);
- else
- {
- if (! __offtime (timer, 0, tp))
- tp = NULL;
- else
- __tz_compute (*timer, tp, use_localtime);
- leap_correction = 0L;
- leap_extra_secs = 0;
- }
- if (tp)
- {
- if (! use_localtime)
- {
- tp->tm_isdst = 0;
- tp->tm_zone = "GMT";
- tp->tm_gmtoff = 0L;
- }
- if (__offtime (timer, tp->tm_gmtoff - leap_correction, tp))
- tp->tm_sec += leap_extra_secs;
- else
- tp = NULL;
- }
- __libc_lock_unlock (tzset_lock);
- return tp;
- }
很清楚的看到,在调用系统时区转换时,有全局锁__libc_lock_lock的保护,导致线程并发环境下,系统性能受限。
而在time_zone=system时,会调用该函数从而获取系统的时区。如果将time_zone='+8:00'则不会调用系统时区.
对此,真实的模拟了线上的case(分别对time_zone=system和time_zone='+8:00'做了压测):点击(此处)折叠或打开
- mysqlslap --no-defaults -u$user -p$password --create-schema=DianPingTS -h$host -P$port --number-of-queries=1000000000 --concurrency=30 --query="select AddTime,UpdateTime from $table where addtime>='2015-07-01 15:21:35' and addtime<='2015-07-01 17:33:35';"