技术译文 | MySQL 与主流分支版本上执行 ANALYZE TABLE 安全么？

外向笑小鸭子
外向笑小鸭子 2024-01-02 10:41:10 50265
分类专栏： 资讯

有时，需要使用 ANALYZE TABLE[1] 命令手动更新表和索引统计信息。在不进一步探讨这种需求的原因的情况下，我想就与在生产系统上运行命令相关的开销来聊聊这个话题。然而，这里讨论的开销与深入表行收集统计信息的通常成本无关，我们可以通过设置样本页数[2] 来控制。

五年前，我的同事 Sveta 发布了一篇不错的博客文章[3]，介绍了 Percona Server for MySQL 中引入的一项改进，以解一些不必要的等待：

《ANALYZE TABLE 不再是阻塞操作》[4]

从历史上看，在 MySQL 中运行 ANALYZE TABLE 命令的问题是查询需要在表的表定义缓存条目上使用排他锁。这使得查询等待任何长时间运行的查询完成，但也可能触发级联等待其他传入请求。简而言之，ANALYZE 可能会导致高负载生产环境中出现较高得延时。

从那时起，MySQL/Percona/MariaDB 都发生了一些变化，但今天仍然存在许多生产系统会受影响的版本。让我们回顾一下这些年来情况的演变。

1MySQL

该问题适用于 MySQL 8.0.23 之前的所有版本。5.7 系列没有任何改进（顺便说一句，本月将达到 EOL！），这意味着即使是最新的 5.7.43 也会受到影响。以下是您可能会遇到的示例场景：

mysql > select @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 5.7.43    | MySQL Community Server (GPL) |
+-----------+------------------------------+
1 row in set (0.00 sec)

mysql > show processlist;
+----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+
| Id | User     | Host      | db   | Command | Time | State                   | Info                                                           |
+----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+
|  4 | msandbox | localhost | db1  | Query   |   54 | Sending data            | select avg(k) from sbtest1 where pad not like '%f%' group by c |
| 13 | msandbox | localhost | db1  | Query   |   29 | Waiting for table flush | analyze table sbtest1                                          |
| 17 | msandbox | localhost | db1  | Query   |    0 | starting                | show processlist                                               |
| 18 | msandbox | localhost | db1  | Query   |   15 | Waiting for table flush | select * from sbtest1 where id=100                             |
+----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+
4 rows in set (0.00 sec)

一个慢查询导致 ANALYZE 在等待，且另一个通常非常快的查询现在也在等待。

同样的情况也可能发生在 MySQL 8.0 系列，包括 8.0.23。幸运的是，8.0.24 版本中修复了这个问题。我们只能在发行说明[5] 中读到一些关于此问题解决得评论：

事实上，从版本 8.0.24 开始，运行慢查询期间的类似测试会导致即时查询执行：

mysql > select @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 8.0.24    | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)

mysql > analyze table sbtest1;
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| db1.sbtest1 | analyze | status   | OK       |
+-------------+---------+----------+----------+
1 row in set (0.00 sec)

不过，即使是 8.1 版本，我们仍然可以在官方文档中找到警告，如下所示：

ANALYZE TABLE removes the table from the table definition cache, which requires a flush lock. If there are long running statements or transactions still using the table, subsequent statements and transactions must wait for those operations to finish before the flush lock is released. Because ANALYZE TABLE itself typically finishes quickly, it may not be apparent that delayed transactions or statements involving the same table are due to the remaining flush lock.

2Percona

如上所述，为了解决此错误报告，Percona 引入了修复程序并删除了不必要的表定义缓存锁。

使用 Percona 时，从版本 5.6.38 和 5.7.20 开始运行 ANALYZE TABLE 已经是安全的，因为这些是当时活跃的版本。

自第一个版本[6]（包括第一个 GA 版本 8.0.13-3 ）以来，Percona Server for MySQL 版本 8.0 就没有这个问题，因为改进是从 Percona Server for MySQL 5.7 系列合并而来的。

3MariaDB

ANALYZE TABLE 的问题发生于 10.5.3 之前的所有 MariaDB 版本。在版本 10.5.4 中，Percona 得以解决。

因此，当您在 10.5.3 或更低版本以及任何以前的系列（甚至是最新的 10.4.31）中运行查询时，可能会出现类似的情况：

mysql > select @@version,@@version_comment;
+----------------+-------------------+
| @@version      | @@version_comment |
+----------------+-------------------+
| 10.5.3-MariaDB | MariaDB Server    |
+----------------+-------------------+
1 row in set (0.000 sec)

mysql > show processlist;
+----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+----------+
| Id | User     | Host      | db   | Command | Time | State                   | Info                                                           | Progress |
+----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+----------+
|  4 | msandbox | localhost | db1  | Query   |   18 | Sending data            | select avg(k) from sbtest1 where pad not like '%f%' group by c |    0.000 |
| 13 | msandbox | localhost | db1  | Query   |   16 | Waiting for table flush | analyze table sbtest1                                          |    0.000 |
| 14 | msandbox | localhost | db1  | Query   |   14 | Waiting for table flush | select * from sbtest1 where id=100                             |    0.000 |
| 15 | msandbox | localhost | NULL | Query   |    0 | starting                | show processlist                                               |    0.000 |
+----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+----------+
4 rows in set (0.000 sec)

mysql > select @@version,@@version_comment;
+-----------------+-------------------+
| @@version       | @@version_comment |
+-----------------+-------------------+
| 10.4.31-MariaDB | MariaDB Server    |
+-----------------+-------------------+
1 row in set (0.000 sec)

mysql > show processlist;
+----+-------------+-----------+------+---------+------+--------------------------+----------------------------------------------------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                    | Info                                                           | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+----------------------------------------------------------------+----------+
|  1 | system user |           | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL                                                           |    0.000 |
|  2 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                                                           |    0.000 |
|  3 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                                                           |    0.000 |
|  4 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                                                           |    0.000 |
|  5 | system user |           | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL                                                           |    0.000 |
|  9 | msandbox    | localhost | db1  | Query   |   18 | Sending data             | select avg(k) from sbtest1 where pad not like '%f%' group by c |    0.000 |
| 18 | msandbox    | localhost | db1  | Query   |   16 | Waiting for table flush  | analyze table sbtest1                                          |    0.000 |
| 19 | msandbox    | localhost | db1  | Query   |   12 | Waiting for table flush  | select * from sbtest1 where id=100                             |    0.000 |
| 22 | msandbox    | localhost | NULL | Query   |    0 | Init                     | show processlist                                               |    0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+----------------------------------------------------------------+----------+
9 rows in set (0.000 sec)

4总结

只要您的数据库在最新版本的 MySQL 或 MariaDB 上运行，运行 ANALYZE TABLE 就应该绝对安全，不会导致任何意外的停顿。

Percona Server for MySQL 系列：5.6.38+、5.7.20+ 和 8.0.x 的用户都是安全的。

MariaDB 用户必须升级到 10.5.4 或更高版本以避免锁定问题。

参考资料

[1]

ANALYZE TABLE: https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html

[2]

sample pages: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages

[3]

Percona Server for MySQL: https://www.percona.com/mysql/software/percona-server-for-mysql

[4]

ANALYZE TABLE 不再是阻塞操作: https://www.percona.com/blog/analyze-table-is-no-longer-a-blocking-operation/

[5]

news 8.0.24: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-24.html

[6]

Percona-Server-8.0.13-3: https://docs.percona.com/percona-server/8.0/release-notes/Percona-Server-8.0.13-3.html?_gl=11glf6qs*_gcl_auMjkxOTk3NDAuMTY5NzkzNzUzMg.._gaMTM2Mjk4NTIwNC4xNjkwMTA2NTM2_ga_DXWV0B7PSNMTcwMDYyMjk5OS43LjEuMTcwMDYyMzY5Mi4zMy4wLjA.

 

本文原文：https://www.percona.com/blog/is-analyze-table-safe-on-a-busy-mysql-database-server/

本文关键字：#MySQL# #Percona# #MariaDB#  #ANALYZE#

网站声明：如果转载，请联系本站管理员。否则一切后果自行承担。

本文链接：https://www.xckfsq.com/news/show.html?id=34098
赞同 0
评论 0 条

相关文章

  • 技术译文 | mysql 8.2 支持读写分离！ 2024-01-02 52173 浏览

    我们一直在等待的mysql读写分离功能[1]现在终于可以使用了！在规模上，我们在副本之间分配读取，但这必须在应用程序中以某种方式进行管理：在某个地方写入并在其他地方读取。在mysql8.2[2]中，mysqlRouter现在能够识别读取和写入，并将它们路由到主实例（如果是InnoDB集群），或者路由到异步复制源以进行写入，将其路由到辅助实例或副本以进行读取。为了说明这一点，我部署了最简单的架构：M

  • 技术译文 | mysql 与主流分支版本上执行 ANALYZE TABLE 安全么？ 2023-11-27 49830 浏览

    有时，需要使用ANALYZETABLE[1]命令手动更新表和索引统计信息。在不进一步探讨这种需求的原因的情况下，我想就与在生产系统上运行命令相关的开销来聊聊这个话题。然而，这里讨论的开销与深入表行收集统计信息的通常成本无关，我们可以通过设置样本页数[2]来控制。五年前，我的同事Sveta发布了一篇不错的博客文章[3]，介绍了PerconaServerformysql中引入的一项改进，以解一些不必要

  • 技术译文 | 一份全是“错误”答案的 mysql 面试题 2023-12-20 50260 浏览

    在以往的一些面试过程中，我发现了一些有趣的回答，它们很容易被为归类为“错误答案”。但有时，这些答案却发人深省或包含深刻的含义。本文涉及一些常见的mysql问答，这些问答可能看起来“错误”或“有趣”，但实际上还有更多内容。我将分享一些看似“错误”或异想天开的答案，并仔细研究它们提供的宝贵经验和观点。让我们来一次mysql面试吧！1一些有趣的问答问：如何改进慢查询？答：我们根本不执行查询。避免的查询就

  • 技术译文 | 一封写给 mysql 8.2 贡献者的感谢信 2024-01-02 49680 浏览

    mysql的最新版本8.2（2023年10月25日）是第二个Innovation版本。这个新的Innovation版本让我们更接近第一个mysqlLTS版本！这个新的Innovation版本包含了很多伟大社区的贡献。mysql8.2.0包含来自Meta、NikolaiKostrigin、Meng-HsiuJiang(Amazon)、RichardDang、ShaohuaWang、HaoLu、Wen

  • 技术译文 | Python 程序如何使用 mysql 8.2 读写分离 2024-01-02 50824 浏览

    如您所知，mysql8.2发布了最令人期待的功能之一：读写分离[1]。在这篇文章中，我们将了解如何将它与mysql-Connector/Python[2]一起使用。1架构为了使用我们的Python程序，我们将使用InnoDBCluster。以下是在mysqlShell中查询Cluster的状态：JS>cluster.status(){"clusterName":"fred","defaultRep

  • 技术译文 | 年底了，可以信任 ChatGPT 来解决数据库问题了么？ 2024-01-02 48175 浏览

    2023年会被称为生成式AI之年吗？我不知道，但可以肯定的是，ChatGPT从2022底开始引发了很多行业围绕该技术的巨大变革。确实是有趣的时刻，我们目睹了一些改变我们世界的事情。我在二月份玩了一下ChatGPT，看看它如何响应与数据库相关的问题，发现它令人印象深刻，同时也很烦人。我不喜欢它的是，如果没有足够的数据，它就会编造“事实”。我多次成功地发现它在撒谎，在我证明它是错误之后，机器人道歉了，

  • 技术译文 | 一份全是“错误”答案的 mysql 面试题 2024-01-02 48718 浏览

    在以往的一些面试过程中，我发现了一些有趣的回答，它们很容易被为归类为“错误答案”。但有时，这些答案却发人深省或包含深刻的含义。本文涉及一些常见的mysql问答，这些问答可能看起来“错误”或“有趣”，但实际上还有更多内容。我将分享一些看似“错误”或异想天开的答案，并仔细研究它们提供的宝贵经验和观点。让我们来一次mysql面试吧！1一些有趣的问答问：如何改进慢查询？答：我们根本不执行查询。避免的查询就

  • 技术译文 | mysql 8 中 utf8mb4 的强大：释放多语言数据的潜力 2024-01-02 50060 浏览

    1了解utf8mb4在深入探讨utf8mb4的好处之前，我们先澄清一下utf8mb4代表什么。在mysql中，“utf8”是指支持Unicode字符集的字符编码，每个字符最多使用三个字节。然而，mysql中原始的utf8实现并没有涵盖所有Unicode字符。另一方面，utf8mb4是utf8的修改版本，它支持完整的Unicode字符集，包括表情符号和其他补充字符，每个字符最多使用四个字节。MySQ

  • 技术译文 | mysql 备份和恢复最佳实践：终极指南 2024-01-02 48903 浏览

    随着企业和应用程序越来越依赖mysql数据库来管理其关键数据，确保数据可靠性和可用性变得至关重要。在这个数字信息时代，强大的备份和恢复策略是应用程序稳定性的支柱。本文中，我们将回顾所有常用的mysql备份和恢复策略，它们是任何应用程序的基石。对应您的特定场景，有多个选项可供选择，每个选项都要求我们考虑相关问题以做出明智的决策。1为什么mysql备份很重要？mysql备份在保护数据完整性、防止各种不

  • 技术译文 | 从印刷机到数据库：自动化如何提高生产力和改善生活 2024-01-02 52621 浏览

    1自动化的历史人类长期以来一直梦想着自动化的前景。我们最早的祖先将其想象为众神可以使用的东西。在荷马的《奥德赛》中，希腊锻造和工艺之神赫菲斯托斯利用自动化[1]来完成简单的重复性劳动。佛教传说中提到有自动守卫看守佛陀的舍利。风车和水车的全部目的是使繁重的劳动自动化，例如磨碎谷物或填充羊毛。最近，我的祖母每次把衣服扔进洗衣机时都会很兴奋，她说：“我以前用手做这件事，但现在机器帮我做！”今天，我们的祖

外向笑小鸭子
外向笑小鸭子L0
粉丝 0 发表 622 + 关注 私信
上周热门
如何使用 StarRocks 管理和优化数据湖中的数据？  2941
【软件正版化】软件正版化工作要点  2860
统信UOS试玩黑神话：悟空  2819
信刻光盘安全隔离与信息交换系统  2712
镜舟科技与中启乘数科技达成战略合作，共筑数据服务新生态  1246
grub引导程序无法找到指定设备和分区  1213
华为全联接大会2024丨软通动力分论坛精彩议程抢先看！  163
点击报名 | 京东2025校招进校行程预告  162
2024海洋能源产业融合发展论坛暨博览会同期活动-海洋能源与数字化智能化论坛成功举办  160
华为纯血鸿蒙正式版9月底见！但Mate 70的内情还得接着挖...  157
本周热议
我的信创开放社区兼职赚钱历程 40
今天你签到了吗？ 27
信创开放社区邀请他人注册的具体步骤如下 15
如何玩转信创开放社区—从小白进阶到专家 15
方德桌面操作系统 14
我有15积分有什么用? 13
用抖音玩法闯信创开放社区——用平台宣传企业产品服务 13
如何让你先人一步获得悬赏问题信息？（创作者必看） 12
2024中国信创产业发展大会暨中国信息科技创新与应用博览会 9
中央国家机关政府采购中心：应当将CPU、操作系统符合安全可靠测评要求纳入采购需求 8
热门标签更多

加入交流群

请使用微信扫一扫!