MySQL 扩展 VARCHAR 长度遭遇问题的总结
最近,业务反馈有个扩展 VARCHAR 改表需求失败多次,需要干预处理一下。
作者:莫善,某互联网公司高级 DBA。
爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。
本文约 3600 字,预计阅读需要 12 分钟。
背景介绍
最近,业务反馈有个扩展 VARCHAR 改表需求失败多次,需要干预处理一下。
经过排查分析得出,这是由于改表系统解析改表需求得出错误的改表方案导致,即这类改表可以满足快速改表操作(直接使用 ALTER TABLE
),理论上任务下发后能马上改完,但是工单结果是执行触发 10 秒超时,最终工单失败。
原则上,VARCHAR 类型的扩展是可以满足快速改表的,我们的改表工单针对这类需求也是支持的,但是实际结果与预期不符,这到底是工单系统的 Bug?还是 MySQL 的坑呢?
本文就来总结一下 扩展 VARCHAR 长度可能会遇到的一些问题,以及我们给出的解决方案,仅供参考。
仅讨论 MySQL 5.7 及以后的版本。
MySQL Online DDL
Operation | Extending VARCHAR column size |
---|---|
In Place | Yes |
Rebuilds Table | No |
Permits Concurrent DML | Yes |
Only Modifies Metadata | Yes |
上表是 MySQL 官方文档中关于 Online DDL 章节中的一部分。可以看到关于 VARCHAR 类型的字段的扩展是可以原地改表,且仅仅改了元数据,理论上敲完回车就执行结束。
当然针对这种场景,还是有一些条件的,直接贴原话:
The number of length bytes required by a VARCHAR column must remain the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY).
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
VARCHAR 是变长类型,实际存储的内容不固定,需要 1 或者 2 个字节来表示实际长度,所以修改前和修改后,这个字节数要求是一致。
有了这个技术基础,我们的改表系统就针对这类需求做了优化,可以支持直接使用 ALTER TABLE
进行改表,如果是大表可以节省很多时间,提升效率,也因此遇到了很多问题,才有了这篇文章。
问题汇总
首先简单介绍一下我们的改表系统的处理逻辑,我们会根据业务的改表需求去选择最优的改表方案:
-
满足快速改表就直接使用
ALTER TABLE
进行操作。比如,删除索引,修改表名/列名,修改默认值/注释,扩展 VARCHAR 长度,小表添加唯一索引以及 8.0 快速加列等等。
-
不满足快速改表就优先选择 gh-ost 进行改表
binlog format
不为 ROW 则不能使用 gh-ost,添加唯一索引必须使用 gh-ost。 -
不满足 gh-ost 都会选择 pt-osc 进行改表。
其中添加唯一索引会直接失败。
那么问题来了,我们是如何判断业务改表需求是不是扩展 VARCHAR?
其实思路也很简单,就是检查改表前后的 information_schema.columns
记录,用到的 SQL 如下:
select * from information_schema.columns where table_schema = 'db' and table_name = 'table' and column_name = 'col'; # 样例数据 *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: information_schema TABLE_NAME: CHARACTER_SETS COLUMN_NAME: CHARACTER_SET_NAME ORDINAL_POSITION: 1 COLUMN_DEFAULT: IS_NULLABLE: NO DATA_TYPE: varchar CHARACTER_MAXIMUM_LENGTH: 32 CHARACTER_OCTET_LENGTH: 96 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: utf8 COLLATION_NAME: utf8_general_ci COLUMN_TYPE: varchar(32) COLUMN_KEY: EXTRA: PRIVILEGES: select COLUMN_COMMENT: GENERATION_EXPRESSION:
DATA_TYPE
值是 VARCHARCHARACTER_MAXIMUM_LENGTH
的值,要求改表后要大于等于改表前的值CHARACTER_OCTET_LENGTH
的值,要求改表前后这个值要么是都小于等于 255,要么是都大于 255- 除
DATA_TYPE/COLUMN_TYPE/CHARACTER_MAXIMUM_LENGTH/CHARACTER_OCTET_LENGTH
字段外的其余字段要求改表前后保持一致
问题一:默认值问题
我们发现,如果还改了字段名、注释、默认值这种元数据信息,依旧是可以快速改表,于是乎就进行了优化,不再比较这三个属性 COLUMN_NAME|COLUMN_COMMENT|COLUMN_DEFAULT
。
关于默认值,看起来有点复杂,最开始也是想跑偏了,认为判断 COLUMN_DEFAULT
的值就行,比较这个值前后要么都是 null,要么都不是 null。都不是 null 的情况下可以是任意值,比如可以用下面的逻辑判断改表前后是一致即可。
if(COLUMN_DEFAULT is null ,null,"")
但是有个问题,如果一个字段从 允许为 null 默认值为 1 变成 不允许为null 默认值也是 1,该值改表前后也是一致的,具体测试如下:
CREATE TABLE `tb_test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `rshost` varchar(20) DEFAULT '1' COMMENT '主机地址', `cpu_info` json DEFAULT NULL COMMENT 'cpu信息 json串', `mem_info` json DEFAULT NULL COMMENT 'mem信息 json串, 单位是GB', `io_info` json DEFAULT NULL COMMENT '磁盘io使用情况, 单位是KB', `net` json DEFAULT NULL COMMENT '网络使用情况, 单位是KB(speed单位是MB/S)', `a_time` datetime NOT NULL DEFAULT '2022-01-01 00:00:00', PRIMARY KEY (`id`), KEY `idx_a_time` (`a_time`), KEY `idx_rshost` (`rshost`) ) ENGINE=InnoDB AUTO_INCREMENT=623506728 DEFAULT CHARSET=utf8mb4 >select * from information_schema.columns where table_name = 'tb_test' and column_name = 'rshost'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: dbzz_monitor TABLE_NAME: tb_test COLUMN_NAME: rshost ORDINAL_POSITION: 2 COLUMN_DEFAULT: 1 IS_NULLABLE: YES DATA_TYPE: varchar CHARACTER_MAXIMUM_LENGTH: 30 CHARACTER_OCTET_LENGTH: 120 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: utf8mb4 COLLATION_NAME: utf8mb4_general_ci COLUMN_TYPE: varchar(30) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: 主机地址 GENERATION_EXPRESSION: 1 row in set (0.00 sec) >alter table tb_test modify `rshost` varchar(30) not null DEFAULT '1' COMMENT '主机地址'; Query OK, 1000000 rows affected (13.68 sec) Records: 1000000 Duplicates: 0 Warnings: 0 >select * from information_schema.columns where table_name = 'tb_test' and column_name = 'rshost'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: dbzz_monitor TABLE_NAME: tb_test COLUMN_NAME: rshost ORDINAL_POSITION: 2 COLUMN_DEFAULT: 1 IS_NULLABLE: NO DATA_TYPE: varchar CHARACTER_MAXIMUM_LENGTH: 30 CHARACTER_OCTET_LENGTH: 120 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: utf8mb4 COLLATION_NAME: utf8mb4_general_ci COLUMN_TYPE: varchar(30) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: 主机地址 GENERATION_EXPRESSION: 1 row in set (0.00 sec)
可以看到 COLUMN_DEFAULT
这个列的值是非 null 且不变,按照上面的判断逻辑会认为可以快速改表,但是我们知道实际上这个需求是需要 copy 数据的。
其实,关于默认值问题使用 IS_NULLABLE
的值就可以完美解决, 如果是 null 到 not null 这个值会从 yes 变成 no;如果是 not null 到 null,这个值会从 no变成 yes。
所以最终解决方案仅比较 IS_NULLABLE
即可,只要改表前后一致就认为默认值这个属性满足快速改表。
在测试这个问题的时候发现一个现象:not null 到 null 可以使用 inplace 算法,但是需要 copy 数据;null 到 not null 不能使用 inplace,请看下面的用例:
-- not null --> null可以使用inplace >alter table tb_test modify `rshost` varchar(30) DEFAULT '1' COMMENT '主机地址' ,ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (3.45 sec) Records: 0 Duplicates: 0 Warnings: 0 -- null --> not null不可以使用inplace >alter table tb_test modify `rshost` varchar(30) not null DEFAULT '1' COMMENT '主机地址' ,ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY. > -- 可以使用下面的操作查看改表进度拷贝数据的情况,第一次使用需要开启此功能 -- UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%'; -- UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%'; >SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current; +-----------------------------+----------------+----------------+ | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | +-----------------------------+----------------+----------------+ | stage/sql/copy to tmp table | 272289 | 978903 | +-----------------------------+----------------+----------------+ 1 row in set (0.00 sec) -- 为了避免测试干扰,检查events_stages_history表之前可以先清空,切记不要对线上环境做此操作。 -- TRUNCATE TABLE performance_schema.events_stages_history; >SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_history; +-----------------------------+----------------+----------------+ | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | +-----------------------------+----------------+----------------+ | stage/sql/copy to tmp table | 1000000 | 978903 | +-----------------------------+----------------+----------------+ 1 row in set (0.00 sec)
问题二:索引字段问题
过了一段时间又发现第二个问题,部分工单会触发执行 10 秒超时失败。
工单系统判断用户的改表需求,满足直接使用
ALTER TABLE
进行操作会有个 10 秒超时的兜底策略,来避免因为解析错误导致方案选择错误最终影响主从延迟。另外,也建议带上
ALGORITHM=INPLACE, LOCK=NONE
,避免因为不是使用 inplace 导致 DML 阻塞。
这个问题排查了很久都没什么眉目,反反复复的查阅文档及测试,始终都认为这个需求一定是满足快速改表的方案。实在是想不明白到底是哪里的问题,还一度认为是 MySQL 的 Bug。
下面是一张 100w 记录表的测试用例:
> show create table tb_test\G *************************** 1. row *************************** Table: tb_test Create Table: CREATE TABLE `tb_test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `rshost` varchar(30) NOT NULL DEFAULT '1' COMMENT '主机地址', `cpu_info` json DEFAULT NULL COMMENT 'cpu信息 json串', `mem_info` json DEFAULT NULL COMMENT 'mem信息 json串, 单位是GB', `io_info` json DEFAULT NULL COMMENT '磁盘io使用情况, 单位是KB', `net` json DEFAULT NULL COMMENT '网络使用情况, 单位是KB(speed单位是MB/S)', `a_time` datetime NOT NULL DEFAULT '2022-01-01 00:00:00', PRIMARY KEY (`id`), KEY `idx_a_time` (`a_time`), KEY `idx_rshost` (`rshost`) ) ENGINE=InnoDB AUTO_INCREMENT=623506728 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) > select count(*) from tb_test; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.15 sec) > alter table tb_test modify `rshost` varchar(31) NOT NULL DEFAULT '1' COMMENT '主机地址'; Query OK, 0 rows affected (3.61 sec) Records: 0 Duplicates: 0 Warnings: 0 > alter table tb_test modify `rshost` varchar(32) NOT NULL DEFAULT '1' COMMENT '主机地址', ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (3.66 sec) Records: 0 Duplicates: 0 Warnings: 0 > alter table tb_test drop index idx_rshost; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 > alter table tb_test modify `rshost` varchar(33) NOT NULL DEFAULT '1' COMMENT '主机地址', ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 > alter table tb_test modify `rshost` varchar(34) NOT NULL DEFAULT '1' COMMENT '主机地址', ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 >
可以看到 rshost
字段有一个索引,在扩展字段的时候虽然支持 inplace,但是实际上很慢,内部应该是重建索引了,后来将索引删除后就秒改了。
针对这个场景,我们的解决方案是使用 gh-ost/pt-osc 进行改表,那么问题来了,我们应该怎么判断目标字段是否是被索引了呢?
请看下面的 SQL,information_schema.STATISTICS
记录了一个表的所有索引字段信息,可以很方便的判断某个字段是否被索引。
select * from information_schema.STATISTICS where table_schema = 'db' and table_name = 'table' and column_name = 'col';
其他问题
这个问题也是执行 10 秒超时,也就是文章开头提到的业务反馈的问题,其实跟 问题二 差不多同期,但在解决了 问题二 后还是一直找不到原因及解决方案。
关于这个问题甚至都没法复现,不像 问题二 可以方便复现,当时在业务的线上库做操作又能 100% 复现,但是将他们的表及数据单独导出来放在测试环境就不行。
在业务库上测试是选了一个从库,不记录 binlog 的方式(
set sql_log_bin = 0
)。虽然不建议这么做,但是实属迫不得已,在测试环境复现不出来。
后来实在找不到原因,就跳过快速改表的方案使用改表工具进行处理,后来这个事情就算不了了之了。直到前几天业务突然找我,说之前的那个表能快速改表了。我赶紧去查看了工单详情,发现确实如业务所述,这回我就更加郁闷了,难不成是见鬼了?这玩意还自带歇业窗口的嘛?
本着严谨的态度,又去测了一下。确实是可以满足快速改表了,但是原因还是找不到,这感觉真的很难受。
最后,静下来认真梳理了一下,发现了一些猫腻。下面是我的测试思路:
1. 将线上的表导出并导入到测试环境
因为表本身就几个 G,不算大就使用了 mysqldump 进行导出导入。这个操作并非 100% 复原线上的环境,有个隐藏的变量被修改了,那就是这个表被重建了,这个跟之前业务用改表工具进行修改后的操作有点类似,所以就猜想,会不会是因为这个表本身存在空洞导致的呢。
最后通过拉历史备份,还原了一个环境进行了测试,果不其然不能快速改表。为了印证了想法,就去查了一下这个表的空洞。十分遗憾,这个表并没有存在空洞(空洞只有几 MB)。这回又郁闷了,还以为要破案了,但是不管怎么样既然怀疑是重建表能解决,那就开搞。
2. 重建前的状态
业务从 varchar(300)
扩展到 varchar(500)
,其他属性没变更。
| 1170930999 | dba | 192.168.1.100:47522 | dbzz_dbreport | Query | 45 | altering table | ALTER TABLE t_recycle_express MODIFY address VARCHAR(500) NOT NULL DEFAULT '' COMMENT '地址', ALGORITHM=INPLACE, LOCK=NONE; |
3. 重建后的状态
>ALTER TABLE t_recycle_express engine = innodb; Query OK, 0 rows affected (18 min 52.60 sec) Records: 0 Duplicates: 0 Warnings: 0 >ALTER TABLE -> t_recycle_express -> MODIFY -> address VARCHAR(500) NOT NULL DEFAULT '' COMMENT '地址'; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0
活久见,还真是重建表后就能解决了!虽然很郁闷,终究是有一个解决方案了,后期我们决定对此做个优化,将满足快速改表的工单又触发十秒超时的改为使用 gh-ost/pt-osc 重新执行,以此避免业务反复提交工单,应该能大大提升好感度。
这个问题虽然知道解决方案,但是依旧不知道原因,我猜测可能是跟统计信息不准确有关系(或者约束),要是有大佬知道原因,请告知一下。
总结
MySQL Online DDL 特性给 DBA 带来了很多的便利,提升了工作效率,我们可以基于官方的理论作为指导去优化我们的系统。但是实际情况是理论知识很简单,线上环境十分复杂,可能会遇到各种意料之外的事情,任何线上的操作都要给自己留好后路做好兜底,这是十分必要的。
我们的系统,如果没有添加 10 秒超时的兜底,那势必会因为解析错误导致选了错误的改表方案,然后导致从库延迟,可能会影响线上业务,想想都有点心慌。
这里有个注意事项,针对执行超时不能简单的使用 timeout 等属性进行控制,还需要添加检查逻辑,要到数据库里面去查一下任务是否真的已经终止了。避免因为 timeout 异常导致终止信号没有给到 MySQL,这种可能会引发一系列问题,切记切记。
以上,仅供参考。
更多技术文章,请访问:https://opensource.actionsky.com/
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台,覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库,为开发和运维提供流程自动化能力,提升上线效率,提高数据质量。
SQLE 获取
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
BPF开发: 从Hello World开始
Part 1 概述 1.背景 BPF技术被列为近些年Linux内核领域最火热的新领域之一。它成功的给Linux内核赋予了少量的动态可编程性,可以在Linux内核运行时,实时修改内核的行为,但不需要重新编译和重启内核。据此,BPF在Linux世界中: 网络 可观测性 安全 三大领域大放异彩,学习好BPF技术,对于Linux内核和应用开发者来说,是一件非常有意义的事情。 2. 什么是BPF? BPF在Linux内核中,被实现为一个非常精简的虚拟机,具有几乎性能无损的执行效率。我们可以用类似C语言的语法,编写代码,编译成可以在BPF虚拟机中运行的汇编代码,实现其强大的逻辑处理能力。 今天,我们将从 Hello World 开始,带您进入 BPF 的世界。 3. 开始BPF 按照计算机程序设计语言学习的传统,我们从经典的Hello World程序开始我们的eBPF开发之旅。首先简单对比下标准C程序和eBPF程序的异同。 C语言程序 # HelloWorld.c #include <stdio.h> int main() { printf("HelloWorld\n"); ...
- 下一篇
十年后数据库还是不敢拥抱NUMA - 续篇
背景 十年后数据库还是不敢拥抱NUMA, 这篇经典的纠正大家对NUMA 认知的文章一晃发布快3年了,这篇文章的核心结论是: 之所以有不同的NUMA Node 是不同的CPU Core 到不同的内存距离远近不一样所决定的,这是个物理距离 程序跑在不同的核上要去读写内存可以让性能差异巨大,所以我们要尽量让一个程序稳定跑在一个Node 内 默认打开NUMA Node 其实挺好的 写这个续篇是我收到很多解释,因为跨Node 导致性能抖动,所以集团在物理机OS 的启动参数里设置了 numa=off ,也就是不管BIOS 中如何设置,我们只要在OS 层面设置一下 numa=off 就能让程序稳定下来不再抖了! 我这几年也认为这是对的,只是让我有点不理解,既然不区分远近了,那物理上存在的远近距离(既抖动)如何能被消除掉的呢? 所以这个续篇打算通过测试来验证下这个问题 设置 BIOS 中有 numa node 设置的开关(注意这里是内存交错/交织),不同的主板这个BIOS设置可能不一样,但是大同小异,基本都有这个参数 Linux 启动引导参数里也可以设置numa=on(默认值)/off ,linux ...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- SpringBoot2全家桶,快速入门学习开发网站教程
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题
- CentOS6,7,8上安装Nginx,支持https2.0的开启
- CentOS7编译安装Gcc9.2.0,解决mysql等软件编译问题
- CentOS8编译安装MySQL8.0.19
- CentOS关闭SELinux安全模块
- CentOS8安装Docker,最新的服务器搭配容器使用
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- CentOS7设置SWAP分区,小内存服务器的救世主
- Docker安装Oracle12C,快速搭建Oracle学习环境