作者： pbdatacn

1. 出现长时间执行的查询的原因

2. 长时间执行的查询带来的问题

3. 如何避免长时间执行的查询

4. 如何处理长时间执行的查询

4.1 DMS 处理会话

4.2 设置查询最长执行时间

4.3 创建事件自动清理长时间执行的查询

1. 出现长时间执行的查询的原因

在使用 MySQL 的过程中，由于某些原因，比如被 SQL 注入、SQL执行效率较差、DDL 语句引起表元数据锁等待等等，会出现运行时间很长的查询。

• 由于被SQL注入而导致的长时间查询：

• 由于DDL语句引起表元数据锁等待:

2. 长时间执行的查询带来的问题

通常来说，除非是BI/报表类查询，否则长时间执行的查询对于应用缺乏意义。

消耗系统资源，比如大量长时间查询可能会引起 CPU、IOPS 和/或 连接数 使用率过高等问题。

带来系统不稳定的隐患（比如 InnoDB 引擎表上的长时间查询可能会导致 ibdata1 系统文件尺寸的增加）。

3. 如何避免长时间执行的查询

应用方面应注意增加防止 SQL 注入的保护。

在新功能模块上线前，进行压力测试，避免出现执行效率很差的 SQL 大量执行的情况。

尽量在业务低峰期进行索引创建删除、表结构修改、表维护和表删除操作。

4. 如何处理长时间执行的查询

4.1 DMS 清理会话

也可以通过命令 show processlist; 查看当前执行会话，清理长时间查询。

4.2 设置查询最长执行时间

可以通过设置 loose_max_statement_time 参数来限制查询的最长执行时间，该参数单位是 毫秒（MS）。

比如：

注：

• 修改该参数设置，对修改设置前已经存在的会话不生效；对修改设置后新创建的会话有效。
• loose_max_statement_time 限制 SQL 执行 时间；如果 DML 操作出现 InnoDB 行锁等待，锁等待时间是不计入执行时间的。

4.3 创建事件自动清理长时间执行的查询

创建 MySQL事件，自动清理长时间执行的查询。

比如下面的代码会每 5 分钟清理一次当前用户运行时间超过 1 个小时且非锁等待会话。

create event my_long_running_query_monitor
on schedule every 5 minute
starts '2015-09-15 11:00:00'
on completion preserve enable do
begin
  declare v_sql varchar(500);
  declare no_more_long_running_query integer default 0;
  declare c_tid cursor for
    select concat ('kill ',id,';') from 
    information_schema.processlist
    where time >= 3600
    and user = substring(current_user(),1,instr(current_user(),'@')-1)
    and command not in ('sleep')
    and state not like ('waiting for table%lock');
  declare continue handler for not found
    set no_more_long_running_query=1;
 
  open c_tid;
  repeat
    fetch c_tid into v_sql;
    set @v_sql=v_sql;
    prepare stmt from @v_sql;
    execute stmt;
    deallocate prepare stmt;
  until no_more_long_running_query end repeat;
  close c_tid;
end;

注：样例仅供参考，请结合应用情况自行调整监控条件和运行间隔。

1. 功能和适用范围

功能：

• 降低 CPU 使用率
• 降低 IOPS 使用率（某些情况下）
• 减少查询响应时间，提高系统的吞吐量

适用范围：

• 表数据修改不频繁、数据较静态
• 查询（Select）重复度高
• 查询结果集小于 1 MB

注：

• 查询缓存并不一定带来性能上的提升，在某些情况下（比如查询数量大，但重复的查询很少）开启查询缓存会带来性能的下降。

2. 原理

MySQL 对来自客户端的查询（Select）进行 Hash 计算得到该查询的Hash值，通过该Hash 值到查询缓存中匹配该查询的结果。

如果匹配（命中），则将查询的结果集直接返回给客户端，不必再解析、执行查询。

如果没有匹配（命中），则将 Hash 值和结果集保存在查询缓存中，以便以后使用。

查询涉及的任何一个表中数据发生变化，RDS for MySQL 将查询缓存中所有与该表相关的查询结果集全部释放（删除）。

3. 限制

• 查询必须严格一致（大小写、空格、使用的数据库、协议版本、字符集等必须一致）才可以命中，否则视为不同查询。
• 不缓存查询中的子查询结果集，仅缓存查询最终结果集。
• 不缓存存储函数（Stored Function）、存储过程（Stored Procedure）、触发器（Trigger）、事件（Event）中的查询。
• 不缓存含有每次执行结果变化的函数的查询，比如 now()、curdate()、last_insert_id()、rand()等。
• 不缓存对 mysql、information_schema、performance_schema 系统数据库表的查询。
• 不缓存使用临时表的查询。
• 不缓存产生告警（Warnings）的查询。
• 不缓存 Select … lock in share mode、Select … for update、 Select * from … where autoincrement_col is NULL 类型的查询。
• 不缓存使用用户定义变量的查询。
• 不缓存使用 Hint – SQL_NO_CACHE 的查询。

4. 设置

4.1 参数

参数设置

• query_cache_limit: 查询缓存中可存放的单条查询最大结果集、默认为 1 MB；超过该大小的结果集不被缓存。
• query_cache_size: 查询缓存的大小。
• query_cache_type: 是否开启查询缓存功能。

取值为 0 ：关闭查询功能

取值为 1 ：开启查询缓存功能，但不缓存 Select SQL_NO_CACHE 开头的查询。

取值为 2 ：开启查询缓存功能，但仅缓存 Select SQL_CACHE 开头的查询。

注：

• 修改 query_cache_type 需要重启实例（修改后实例会自动重启）。
• 参数 query_cache_size 要求设置值为 1024 的整数倍，否则会提示 “参数格式错误，请重新输入”。

4.2 开启

参数 query_cache_size 大于 0 并且 query_cache_type 设置为 1 或者 2 的情况下，查询缓存开启。

4.3 关闭

设置参数 query_cache_size 为 0 或者设置 query_cache_type 为 0 关闭查询缓存。

4.4 建议

• query_cache_size 不建议设置的过大。过大的空间不但挤占实例其他内存结构的空间，而且会增加在缓存中搜索的开销。建议根据实例规格，初始值设置为 10MB 到 100 MB 之间的值，而后根据运行使用情况调整。
• 建议通过调整 query_cache_size 的值来开启、关闭查询缓存，因为修改 query_cache_type 参数需要重启实例生效。
• 查询缓存适用于特定的场景，建议充分测试后，再考虑开启，避免引起性能下降或引入其他问题。

5. 验证效果

 SQL 命令

1. show global status like ‘Qca%’;

可以通过 show global status like ‘Qca%’ 来获取查询缓存的使用状态。

• Qcache_hits ：查询缓存命中次数。
• Qcache_inserts：将查询和结果集写入到查询缓存中的次数。
• Qcache_not_cached：不可以缓存的查询次数。
• Qcache_queries_in_cache：查询缓存中缓存的查询量。

1. Metadata lock wait 出现的场景

2. Metadata lock wait 的含义

3. 导致 Metadata lock wait 等待的活动事务

4. 解决方案

5. 如何避免出现长时间 Metadata lock wait 导致表上相关查询阻塞，影响业务

1. Metadata lock wait 出现的场景

• 创建、删除索引
• 修改表结构
• 表维护操作（optimize table、repair table 等）
• 删除表
• 获取表上表级写锁 (lock table tab_name write)

注：

• 支持事务的 InnoDB 引擎表和 不支持事务的 MyISAM 引擎表，都会出现 Metadata Lock Wait 等待现象。
• 一旦出现 Metadata Lock Wait 等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。

2. Metadata lock wait 的含义

为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此 MySQL 引入了 metadata lock ，来保护表的元数据信息。

因此在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在 Metadata lock wait 。

3. 导致 Metadata lock wait 等待的活动事务

• 当前有对表的长时间查询
• 显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚。
• 表上有失败的查询事务

4. 解决方案

• show processlist 查看会话有长时间未完成的查询，使用kill 命令终止该查询。

• 查询 information_schema.innodb_trx 看到有长时间未完成的事务， 使用 kill 命令终止该查询。

select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,
  (select 
         id, time
     from
         information_schema.processlist
     where
         time = (select 
                 max(time)
             from
                 information_schema.processlist
             where
                 state = 'Waiting for table metadata lock'
                     and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p
  where timestampdiff(second, i.trx_started, now()) > p.time
  and i.trx_mysql_thread_id  not in (connection_id(),p.id);

-- 请根据具体的情景修改查询语句
-- 如果导致阻塞的语句的用户与当前用户不同，请使用导致阻塞的语句的用户登录来终止会话

• 如果上面两个检查没有发现，或者事务过多，建议使用下面的查询将相关库上的会话终止

  -- RDS for MySQL 5.6
  
  select 
      concat('kill ', a.owner_thread_id, ';')
  from
      information_schema.metadata_locks a
          left join
      (select 
          b.owner_thread_id
      from
          information_schema.metadata_locks b, information_schema.metadata_locks c
      where
          b.owner_thread_id = c.owner_thread_id
              and b.lock_status = 'granted'
              and c.lock_status = 'pending') d ON a.owner_thread_id = d.owner_thread_id
  where
      a.lock_status = 'granted'
          and d.owner_thread_id is null;
  
  
  -- RDS for MySQL 5.5
  
  select 
      concat('kill ', p1.id, ';')
  from
      information_schema.processlist p1,
      (select 
          id, time
      from
          information_schema.processlist
      where
          time = (select 
                  max(time)
              from
                  information_schema.processlist
              where
                  state = 'Waiting for table metadata lock'
                      and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat', 'trunc'))) p2
  where
      p1.time >= p2.time
          and p1.command in ('Sleep' , 'Query')
          and p1.id not in (connection_id() , p2.id);
  
  -- RDS for MySQL 5.5 语句请根据具体的 DDL 语句情况修改查询的条件；
  -- 如果导致阻塞的语句的用户与当前用户不同，请使用导致阻塞的语句的用户登录来终止会话

   

5. 如何避免出现长时间 metadata lock wait 导致表上相关查询阻塞，影响业务

• 在业务低峰期执行上述操作，比如创建删除索引。
• 在到RDS的数据库连接建立后，设置会话变量 autocommit 为 1 或者 on，比如 set autocommit=1; 或 set autocommit=on; 。
• 考虑使用事件来终止长时间运行的事务，比如下面的例子中会终止执行时间超过60分钟的事务。

  create event my_long_running_trx_monitor
  on schedule every 60 minute
  starts '2015-09-15 11:00:00'
  on completion preserve enable do
  begin
    declare v_sql varchar(500);
    declare no_more_long_running_trx integer default 0; 
    declare c_tid cursor for
      select concat ('kill ',trx_mysql_thread_id,';') 
      from information_schema.innodb_trx 
      where timestampdiff(minute,trx_started,now()) >= 60;
    declare continue handler for not found
      set no_more_long_running_trx=1;
   
    open c_tid;
    repeat
      fetch c_tid into v_sql;
   set @v_sql=v_sql;
   prepare stmt from @v_sql;
   execute stmt;
   deallocate prepare stmt;
    until no_more_long_running_trx end repeat;
    close c_tid;
  end;

  注：请根据您自身情况，自行修改运行间隔和事务执行时长。

• 执行上述1中操作前，设置会话变量 lock_wait_timeout 为较小值，比如 set lock_wait_timeout=30; 命令可以设置 metadata lock wait 的最长时间为 30 秒；避免长时间等待元数据锁影响表上其他业务查询。

1. Innodb 引擎表行锁等待和等待超时发生的场景

2.Innodb 引擎行锁等待情况的处理

2.1 Innodb 行锁等待超时参数 innodb_lock_wait_timeout

2.2 大量行锁等待和行锁等待超时的处理a

1. Innodb 引擎表行锁等待和等待超时发生的场景

当一个 RDS MySQL 连接会话等待另外一个会话持有的互斥行锁时，会发生 Innodb 引擎表行锁等待情况。

通常情况下，持有该互斥行锁的会话（连接）会迅速的执行完相关操作并释放掉持有的互斥锁（事务提交或者回滚），进而等待的会话在行锁等待超时时间到来前获得该互斥行锁，进行下一步操作。

但在某些情况下，比如一个实例未感知到的来自客户端应用的数据库会话中断，持有该互斥行锁的会话长时间不释放该互斥行锁，此时如果有其他会话申请该互斥行锁，则会导致大量的行锁等待与行锁等待超时。

2. Innodb 引擎行锁等待情况的处理

本文提供的检查和处理方法，仅当正在发生 InnoDB 行锁等待的情况下才成立；因为 InnoDB 行锁等待默认超时时间为50秒，因此通常情况下不容易观察到行锁等待现场，可以通过将 innodb_lock_wait_timeout 参数设置为较大值来复现问题（生产环境不推荐使用过大的 innodb_lock_wait_timeout 参数值）。

2.1. Innodb 行锁等待超时参数 innodb_lock_wait_timeout

该参数控制 Innodb 行锁等待的超时时间，单位为秒，RDS 实例该参数的默认值为 50（秒）。

等待互斥锁的会话在等待 50 秒后会退出锁等待状态并返回下面的错误，这个行为称之为 Innodb 引擎表行锁等待超时。

1. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以通过下面的命令查看当前会话和全局的参数设置。

1. show variables like ‘innodb_lock_wait_timeout’; — 查看当前会话show global variables like ‘innodb_lock_w%’; — 查看全局设置

该参数支持在会话级别修改，方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间，如下：

1. set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间，单位秒

2.2. 大量行锁等待和行锁等待超时的处理

如果行锁等待和行锁等待超时持续发生，并且导致当前应用运行异常，那么需要获取到一直持有行锁的会话，并且终止该会话来释放持有的锁（会话对应的事务会回滚）。

2.2.1 检查导致锁等待和锁超时的会话

注：下面的方法必须在行锁等待正在发生的时候进行检查。

方法 1： 通过 DMS  实例信息   Innodb 锁等待查看。

方法 2：通过 DMS  实例信息  实例会话查看。

方法 3： 在 DMS 无法登录的情况下，可以通过执行下面的查询，获得导致行锁等待和行锁等待超时的会话。

1. select l.* from ( select ‘Blocker’ role, p.id, p.user, left(p.host, locate(‘:’, p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration,lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, lw.requesting_thd_id Blockee_id, lw.requesting_trx_id Blockee_trxfrom information_schema.innodb_trx tx,information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist pwhere lw.blocking_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id =lw.blocking_lock_idunionselect ‘Blockee’ role, p.id, p.user, left(p.host, locate(‘:’, p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration,lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, null, nullfrom information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo,information_schema.processlist pwhere lw.requesting_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.requested_lock_id) l order by role desc, trx_state desc;

比如：

对于复杂的多个会话相互行锁等待情况，建议先终止 Role 为 Blocker 且 trx_state 为 RUNNING 的会话；终止后再次检查，如果仍旧有行锁等待，再终止新结果中的 Role 为 Blocker 且 trx_state 为 RUNNING 的会话。

2.2.2 处理导致行锁等待和行锁等待超时的会话

对于标识为 Blocker 的会话（持有锁阻塞其他会话的 DML 操作，导致行锁等待和行锁等待超时），确认业务可以接受其对应的事务回滚的情况下，可以将其终止。

终止会话的方法请参考：RDS for MySQL如何终止会话。

比如，可以通过 Kill 命令来今后会话终止。

1. 显式 lock table

2. 隐式 lock table

在 RDS MySQL 实例日常使用中，有些情况下会发现出现 Innodb 表级锁等待的情况，下面列出常见的2个原因。

 1. 显式 lock table

执行了 lock tables tab_name read; 导致 DML 会话等待在表的表级锁上。

会话 1

lock tables tab_name read;

会话 2

会话 3

2. 隐式 lock table

mysqldump 使用默认参数进行数据导出时，会默认的开启 –lock-tables 选项，进而导致导出表上的DML操作等待在表级锁上。

会话 1

会话 2

会话 3

对于 mysqldump 方式的导出，建议在业务低峰期进行导出，并且设置 –single-transaction 选项进行 Innodb 引擎表导出，避免出现 Innodb 表级锁等待的情况。

• mysql 在发现事务中的普通语句存在死锁后，将仅保留一个事务并允许其操作，同时清除其它死锁事务，退出事务状态。
• 若事务更新语句一次仅涉及一个分区，死锁的行存在于两个分区，那么死锁过程不会立即被检测出来。多个事务的死锁更新会请求锁，直到锁超时，然后由 mysql 通知更新 error。这个 error 结果不会令分区退出事务状态，后续的操作与普通事务相同，分布式数据库将向用户返回锁超时错误。
• 若事务更新语句一次仅涉及一个分区，死锁的行存在于一个分区，那么死锁过程会立即被检测出来。多个事务的死锁更新，仅有一个被保留，其它事务将被立即回滚。由于事务更新历史中存在跨分区的可能，因此分布式数据库将强行锁定所有未通过 mysql 死锁检测且被清除的事务，强制用户只能进行 rollback 而不得进行其它任何操作。对于那个通过 mysql 死锁检测的事务，后续的操作与普通事务相同，分布式数据库将向用户返回死锁错误，后续非 rollback 语句将向用户返回仅支持 rollback 错误。
• 若事务更新语句一次涉及多个分区，死锁的行存在于两个分区，那么死锁过程不会立即被检测出来。多个事务的死锁更新会请求锁，直到锁超时，然后由 mysql 通知更新 error。这个 error 结果不会令分区退出事务状态，后续的操作与普通事务相同，分布式数据库将向用户返回数据不一致错误。
• 若事务更新语句一次涉及多个分区，死锁的行存在于一个分区，那么死锁过程会立即被检测出来。多个事务的死锁更新，仅有一个被保留，其它事务将被立即回滚。由于事务更新历史中存在跨分区的可能，因此分布式数据库将强行锁定所有未通过 mysql 死锁检测且被清除的事务，强制用户只能进行 rollback 而不得进行其它任何操作。对于那个通过 mysql 死锁检测的事务，后续的操作与普通事务相同，分布式数据库将向用户返回数据不一致错误，后续非 rollback 语句将向用户返回仅支持 rollback 错误。

内存 最大连接数
1G 300
2G 600
4G 1200
8G 2000
16G 4000
32G 8000
64G 16000
96G 24000
128G 32000
220G 64000

Linux 下 MySQL 无法访问问题排查基本步骤
1 查看 Linux 操作系统是否已经安装了 MySQL
2 检查状态
2.1 检测 MySQL 运行状态： service mysqld status
2.2 启动服务：
方法一：使用 service 命令启动 MySQL： service mysqld start
方法二：使用 mysqld 脚本来启动 MySQL：/etc/init.d/mysql start
方法三：使用 safe_mysqld 实用程序启动 MySQL 服务，此方法可以使用相关参数： safe_mysqld& //使用&表示将safe_mysqld放在后台执行。
3 修改密码
mysqladmin -u root password 这里的“密码”为我们欲新设的密码。系统会提示我们输入旧密码（若是 MySQL 刚安装，则默认密码为空）
4 如果本机可以登陆了，但是其他机器的客户端登陆报错。比如：
ERROR 1130 (00000): Host ‘xxx.xxx.xxx.xxx’ is not allowed to connect to this MySQL server
则首先查看了 iptables 的设置，确认开放了 3306 端口：
iptables -A INPUT -p tcp -m tcp –sport 3306 -j ACCEPT
iptables -A OUTPUT -p tcp -m tcp –dport 3306 -j ACCEPT
service iptables save
5 如果还是无法访问，则可能是 MySQL 的权限问题。则可以通过如下步骤排查：
在本机登录 mysql -h localhost -u root -p
show databases;
use mysql;
select Host, User, Password from user;
+———————–+——+——————————————-+
| Host | User | Password |
+———————–+——+——————————————-+
| localhost | root | *18F54215F48E644FC4E0F05EC2D39F88D7244B1A |
| localhost.localdomain | root | |
| localhost.localdomain | | |
| localhost | | |
+———————–+——+——————————————-+
可以看到如上结果，只有 localhost 才设置了访问的权限。
进入 MySQL ，创建一个新用户 user ：
格式：grant 权限 on 数据库名.表名 用户@登录主机 identified by “用户密码”。
grant select,update,insert,delete on test.* to test@ip identified by “test”;
查看结果，执行：
use mysql;
select host,user,password from user;
可以看到在user表中已有刚才创建的user用户。host字段表示登录的主机，其值可以用IP，也可用主机名，将host字段的值改为%就表示在任何客户端机器上能以user用户登录到mysql服务器，建议在开发时设为%。
修改了权限后需要执行如下语句生效：
update user set host = ‘%’ where user = ‘test’;
flush privileges;