☰

MySQL 中长时间运行的查询加上重命名表争用导致数据库瘫痪。如何避免？

tag-icon • tag-icon mysql
MySQL 中长时间运行的查询加上重命名表争用导致数据库瘫痪。如何避免？

我们有一个日常流程，每天将数据加载到生产 MySQL 数据库的“暂存”表（一组驻留在暂存架构上的 MySQL 表）中。一旦数据加载完成，我们就会通过以下流程使其生效：

rename table myschema_live.customer to myschema_staging.tmp_customer
rename table myschema_staging.customer to myschema_live.customer
rename table myschema_staging.tmp_customer to myschema_staging.customer

这对我们来说非常有用。但是，我们最近遇到了此过程中的一个致命缺陷，即长时间运行的选择查询阻止了重命名，然后导致我们的数据库瘫痪。这里涉及大约 30 个表，我们运行的 SQL 命令试图在一个事务中重命名所有 30 个表。

我猜想长时间运行的选择查询阻止了其中一个重命名命令获取表上的锁以执行重命名。同时，重命名事务的其余部分确实获取了其他表上的锁，从而阻止了其他查询的运行。然后整个数据库都在等待长时间运行的选择完成。这听起来对吗？

我的问题是：

  1. 我们如何才能避免这种情况的发生？
  2. 如果等待的时间超过一定时间，sql 命令（通过 mysql 命令的 shell 脚本执行）是否可能会超时？
  3. 为了重命名的目的，有没有更好的方法来获取表上的锁？
  4. 是否可以通过编程查找长时间运行的查询，如果发现则不运行重命名脚本？

欢迎任何想法。

我们正在使用带有 InnoDB 的 MySQL 5.7。

答案1

我们最终采取了多种措施来解决我们的问题。

  1. 在运行重命名语句之前，我们针对表实施了查询information_schema.processlist以识别长时间运行的查询。如果识别出任何长时间运行的查询，则我们将重命名错误并强制进行手动恢复。我们的查询：

select id, user, db, state, info, time from information_schema.processlist where id = (select id from information_schema.processlist where command='Query' order by time desc limit 1)

然后我们检查time是否大于 60 秒，如果条件为真，则取消重命名。

  1. 我们改变了锁定等待超时值从其默认的 1 年更改为 60 秒。此变量会影响任何查询等待获取表上的元数据锁的时间，超过该时间查询将超时。因此，如果我们的重命名语句（需要元数据锁）等待超过 60 秒，则重命名语句将失败。

  2. 虽然没有直接关系，但我们还将重命名语句移至单个事务，以便所有重命名语句都成功或全部失败，而不是像我们原来的流程那样，一些语句可能完成，而其他语句则处于待处理状态。从此：

rename table myschema_live.customer to myschema_staging.tmp_customer;
rename table myschema_staging.customer to myschema_live.customer;
rename table myschema_staging.tmp_customer to myschema_staging.customer;
<repeat these rename statements for 29 other tables>

到

rename table myschema_live.customer to myschema_staging.tmp_customer,
       table myschema_staging.customer to myschema_live.customer,
       table myschema_staging.tmp_customer to myschema_staging.customer,
       <add 29 more tables worth of renames here>;

相关内容