MySQL实战干货 | 如何处理由时区设置引发的 SQL 执行“卡顿”?

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 小小不起眼的时区设置也能成为高并发场景的性能杀手,让我们一起来看一下其是如何发生,又如何能够避免的

作者:田杰
查询执行时间长引发应用感知 “卡顿” 的场景在数据库的日常支持和使用中并不少见,但由于时区设置引发的 SQL 执行“卡顿”仍然是一个有趣的现象,之前没有具体关注过。
这次客户的细致与坚持让我们找到了问题的源头。

1. 名词解释

序列号 名词 说明
1 CPU 使用率 非空闲的 CPU 时间占比。
2 User CPU 使用率 用户空间(user-space)应用代码消耗的 CPU 时间占比。
3 Sys CPU 使用率 系统空间(sys-space)内核代码消耗 CPU 时间占比。
4 Futex Linux 内核提供的快速用户态锁/信号量;在无竞争场景完全在用户空间中运行,但在存在竞争场景会引发系统调用。

2. 问题现象

客户 MySQL 8.0 实例在 2020-03-19 22:03 ~ 22:04 出现大量活跃连接堆积,慢日志中出现大量低成本查询,并且 CPU 使用率不高但系统 SYS CPU 使用率出现异常波动。
image.png
image.png

3. 问题排查

3.1 OS 层面

我们来考虑一下有哪些因素可能会导致卡顿:
• 物理机 OS 层面波动(通过 IO_WAIT 指标排除)。
• MySQL 自身机制。

3.2 MySQL 层面

排除掉 OS 层面异常类因素,我们开始聚焦在 mysqld 进程调用栈的分析。
为了更好的分析 MySQL 的行为,阿里数据库提供了扁鹊系统来跟踪、统计和展示确定时间内的进程内部方法调用情况。
image.png
我们分析上图可以看到 40.5% 的 CPU 时间消耗在 Time_zone_system::gmt_sec_to_TIME() 方法的调用上,就是以下这一段的代码。

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_to_TIME() 和 adjust_leap_second() 都是简单的格式转换和计算,并不涉及系统调用。
而 localtime_r() 会涉及到 glibc 中的 __localtime_r() 方法,代码如下

/* Return the `struct tm' representation of *T in local time,

   using *TP to store the result.  */

struct tm *

__localtime_r (t, tp)

     const time_t *t;

     struct tm *tp;

{

  return __tz_convert (t, 1, tp);

}

weak_alias (__localtime_r, localtime_r)

我们继续下钻来看一下 __tz_convert() 的实现,代码如下

/* Return the `struct tm' representation of *TIMER in the local timezone.

 Use local time if USE_LOCALTIME is nonzero, UTC otherwise.  */

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() 的定义,代码如下

#if IS_IN (libc) || IS_IN (libpthread)

# ifndef __libc_lock_lock

#  define __libc_lock_lock(NAME) \

  ({ lll_lock (NAME, LLL_PRIVATE); 0; })

# endif

#else

# undef __libc_lock_lock

# define __libc_lock_lock(NAME) \

  __libc_maybe_call (__pthread_mutex_lock, (&(NAME)), 0)

#endif

继续追溯 lll_lock() 的实现,代码如下

static inline void
__attribute__ ((always_inline))
__lll_lock (int *futex, int private)
{
  int val = atomic_compare_and_exchange_val_24_acq (futex, 1, 0);
  if (__glibc_unlikely (val != 0))
    {
      if (__builtin_constant_p (private) && private == LLL_PRIVATE)
        __lll_lock_wait_private (futex);
      else
        __lll_lock_wait (futex, private);
    }
}
#define lll_lock(futex, private) __lll_lock (&(futex), private)

可以看到代码中使用 atomic_compare_and_exchange_val_24_acq() 尝试对 futex 加锁。
futex 作为多个 thread 间共享的一块内存区域在多个 client thread(多个会话/查询)竞争的场景下会引发系统调用而进入系统态,导致 SYS 系统态 CPU 使用率上升。
并且该临界区保护的锁机制限制了时区转换方法 __tz_convert() 的并发度,进而出现多个会话/查询 等待获取锁进入临界区的情况,当冲突争抢激烈的场景下引发卡顿
那么是什么引发的
Time_zone_system::gmt_sec_to_TIME() 调用呢,追溯下 Field_timestampf::get_date_internal() 方法,代码如下

bool Field_timestampf::get_date_internal(MYSQL_TIME *ltime) {
  THD *thd = table ? table->in_use : current_thd;
  struct timeval tm;
  my_timestamp_from_binary(&tm, ptr, dec);
  if (tm.tv_sec == 0) return true;
  thd->time_zone()->gmt_sec_to_TIME(ltime, tm);
  return false;
}

该方法中调用了基类 Time_zone 虚函数 gmt_sec_to_TIME() 来进行带时区的秒到时间格式的转换,结合 Field_timestampf::get_date_internal() 的名称能够推断出查询中应该涉及了 timestamp 数据类型的访问。
基于上面的推测我们验证下卡顿的查询和其数据类型

# 慢查询
SELECT 
    id, 
    ......
    create_time, update_time, 
    ...... 
FROM mytab 
WHERE duid IN (?,?,?,?,? ) 
and (state in (2, 3) 
    or ptype !=0)
# 查询涉及的表
CREATE TABLE `mytab` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `duid` char(32) NOT NULL,
  ......
  `state` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `ptype` tinyint(4) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ......,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB

从上面的信息能够看到 create_time update_time 字段都是 timestamp 数据类型,验证了之前的猜测。

4. 问题解决

在上面分析的基础上可以看到调用 Time_zone_system::gmt_sec_to_TIME() 引入的 OS 层面 futex 锁竞争导致了低成本查询执行卡顿。
为了规避调用该方法,可以在实例控制台将 time_zone 参数值由 system 调整为当地时区,比如中国东 8 区时区 '+8:00'
修改后,会调用 Time_zone_offset::gmt_sec_to_TIME() 来直接在 MySQL 层面进行计算,避免访问 glibc 的函数引发 OS 层面的加解锁。
修改效果对比(对比执行同样次数的 timestamp 数据类型查询完成时间)
time_zone='system',需要约 15 分钟 完成
image.png
time_zone='+8:00',需要约 5 分钟 完成
image.png

5. 最佳实践

高并发应用如果涉及到高频次的 timestamp 类型数据访问:
• 如果确实要使用 timestamp 类型,建议控制台设置 time_zone 参数为 UTC/GMT 偏移量格式,比如 东8区 '+8:00',可以有效降低高并发查询执行开销,降低响应时间 RT。
• 由于 MySQL 5.7 版本后 Datatime 类型支持 Timestamp 类型的默认值并且支持 on update current_timestamp 属性,建议使用 Datetime 类型替换 Timestamp 类型。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
24 3
|
10天前
|
SQL 关系型数据库 MySQL
MySQL 保姆级教程(一):了解 SQL
MySQL 保姆级教程(一):了解 SQL
|
12天前
|
SQL 安全 关系型数据库
【SQL】已解决:MySQL 服务无法启动
【SQL】已解决:MySQL 服务无法启动
72 1
|
5天前
|
SQL 关系型数据库 MySQL
MySQL小白教程(进阶篇):深入理解SQL与数据管理
MySQL小白教程(进阶篇):深入理解SQL与数据管理
|
14天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
12天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
983 6
Mysql 数据库主从复制
|
12天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。
|
2天前
|
分布式计算 大数据 关系型数据库
MaxCompute产品使用合集之如何实现类似mysql实例中的数据库功能
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
18天前
|
XML Java 关系型数据库
Action:Consider the following: If you want an embedde ,springBoot配置数据库,补全springBoot的xml和mysql配置信息就好了
Action:Consider the following: If you want an embedde ,springBoot配置数据库,补全springBoot的xml和mysql配置信息就好了
|
18天前
|
关系型数据库 MySQL 数据库
关系型数据库mysql数据增量恢复
【7月更文挑战第3天】
132 2

相关产品

  • 云数据库 RDS MySQL 版