MYSQL 版本迁移带来严重生产事故“的”分析

原创

wx5c241fe5127d0 2024-06-27 09:52:58 ©著作权

文章标签 mysql 数据库 MySQL PostgreSQL 文章分类 Python 后端开发

©著作权归作者所有：来自51CTO博客作者wx5c241fe5127d0的原创作品，请联系作者获取转载授权，否则将追究法律责任

MYSQL 版本迁移带来严重生产事故“的”分析_PostgreSQL

这两年一直在做MySQL迁移到PolarDB for MySQL的问题，基本上是迁移一个项目反馈都是好的，优秀的，没有马失前蹄，终于在昨天掉进了陷马坑，MySQL 迁移到 POLARDB FOR MYSQL 后报表任务无法运行，业务强制回滚了。

这里分析一下原因，这个部分不光适合 MYSQL TO POLARDB FOR MYSQL ，也适合 MYSQL TO MYSQL 的版本迁移或者 MYSQL TO 以国产为套壳的MYSQL 数据库产品。

先说显现问题的状态，在迁移到PolarDB for mysql后从节点的CPU 打满，并且在开启弹性后整体的弹性的PCU 也打满，原先MySQL 配置也就16C 64G的配置，而迁移到PolarDB后，CPU用到了31CPU，迟迟不能下降持续100% 的运行频率进行。

经过分析发现是从节点大量运行报表的SQL导致问题，SQL这里就不展示了有业务的敏感性，语句很长，之前在RDS MYSQL 上运行是可以的，但为什么迁移到PolarDB for MYSQL 就出现问题。

经过和阿里云的工程师进行分析后，主要的问题点在于版本，我们都知道MYSQL 8的功能更新点较多，尤其8.00 -8.032,我最近也总结了相关的版本的更新的一些要点，需要的可以看下面的链接，后续还有8.024-8.037的MySQL版本更新的梳理贴。

MySQL 8.0 版本更新要点列表（8.0-8.0.23）

MYSQL 版本迁移带来严重生产事故“的”分析_PostgreSQL_02

8.0.24 -- 8.0.37 将在下周发文

MYSQL 版本迁移带来严重生产事故“的”分析_MySQL_03

为什么MySQL RDS产品可以做报表，POLARDB FOR MYSQL 在运行这个部分，不可以的关键在于版本，MYSQL RDS 的版本为8.0.25 ，而POALRDB FOR MYSQL 8.01 的版本是8.0.13, 在mysql 8.018上更新了一个SQL语句的处理功能 HASH JOIN，

查阅了官方的文档，关于Hash join的解释部分 https://dev.mysql.com/doc/refman/8.4/en/hash-joins.html 在具有hash join功能的MySQL数据库系统中，哈希连接内存使用可以通过join_buffer_size来进行控制，哈希链接使用的内存不能超过join_buffer_size的数量当超过这个内存的需求的情况下，将引发磁盘缓冲来解决问题，性能会降低，所以在mysql8.0.18后的数据库系统要注意的是 join_buffer_size的部分设置的是否有问题，太小等。同时如果太多的语句都需要进行临时文件生成，超过open_file_limit的设置，则语句可以能无法运行成功。

hash join 使用分为构建阶段和探测阶段，在构建阶段会构建出内存hash table, 将需要构建的列的值都写入到内存表中，然后就开始探测的阶段，在探测的阶段开始读取另一个表中的行并和内存中的数据进行比较，进行数据的关系行的匹配。这样相对于nestloop的方式在 JOIN 等值计算中有相当好的性能。

MYSQL 版本迁移带来严重生产事故“的”分析_mysql_04

MYSQL 版本迁移带来严重生产事故“的”分析_数据库_05

mysql> select * from countries limit 1;
+------------+--------------+
| country_id | country_name |
+------------+--------------+
|          1 | Country1     |
+------------+--------------+
1 row in set (0.00 sec)

mysql> select * from persons limit 1;
+-----------+------------+------------+
| person_id | given_name | country_id |
+-----------+------------+------------+
|         1 | Person1    |        434 |
+-----------+------------+------------+
1 row in set (0.01 sec)

mysql> select count(*) from countries;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from persons;
+----------+
| count(*) |
+----------+
|   895831 |
+----------+
1 row in set (0.04 sec)


SET optimizer_switch = 'block_nested_loop=off,batched_key_access=off,mrr=off,hash_join=on';


ANALYZE TABLE countries;
ANALYZE TABLE persons;

EXPLAIN FORMAT=TREE
SELECT given_name, country_name
FROM persons
JOIN countries ON persons.country_id = countries.country_id;

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (countries.country_id = persons.country_id)  (cost=0.70 rows=1)
    -> Table scan on countries  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on persons  (cost=0.35 rows=1)    

mysql>

结论：在MySQL 5.7到8.0版本升级的过程中，需要注意SQL语句处理的变化，不光是SQL语句的本身，如group by，union 等语句的撰写的变化，与此同时还要注意 SQL 执行计划的变化，无论是从低版本到高版本，还是高版本迁移到其他类MySQL数据库，一定要注意版本之间的差异，否则产生业务回滚或生产事故那就是必然的了！

最后一定要注意类 MYSQL的产品，把MYSQL 往类MYSQL的产品迁移一定要关注版本的差异。

Austindatabases 公众号，主要围绕数据库技术（PostgreSQL, MySQL, Mongodb, Redis, SqlServer，PolarDB, Oceanbase 等）和职业发展，国外数据库大会音译,国外大型IT信息类网站文章翻译，等，希望能和您共同发展。

MYSQL 版本迁移带来严重生产事故“的”分析_数据库_06