1 引言

pt 执行 SQL 工单报错 replicas have too many rows in one chunk，本文进行复现并分析处理方法。

2 现象

2.1 报错

单号：1377955

具体报错如下所示。

2022-09-16T09:41:06 Error copying rows from `bd_dms_spl170`.`jy_unload` to `bd_dms_spl170`.`_jy_unload_new`: 2022-09-16T09:41:06 Cannot copy table `bd_dms_spl170`.`jy_unload` because on the master it would be checksummed in one chunk but on these replicas it has too many rows:
30338 rows on MSS-4suz7bjrcm
9328 rows on MSS-sbblzv9hzr
The current chunk size limit is 4000 rows (chunk size=1000 * chunk size limit=4.0).

EXECUTE FAIL AT 2022-09-16 09:41:06

从报错来看，因为在主库根据统计信息，表行数小于 chunk 行数大小上限（默认4000），可以在一个 chunk 中进行处理。而在从库统计信息中看，表的行数超过了 chunk 行数大小上限，无法在一个 chunk 中进行处理，因此报错退出。

报错的根本原因是主从统计信息不一致，下面将具体介绍。

2.2 相关案例

实际上，该报错并非第一次出现，之前也出现过类似的报错，如下所示。

单号：944549

EXECUTE START AT 2022-05-16 10:56:30
2022-05-16T10:56:31 Error copying rows from `roadarea_manager`.`road_area_statistics_daily` to `roadarea_manager`.`_road_area_statistics_daily_new`: 2022-05-16T10:56:31 Cannot copy table `roadarea_manager`.`road_area_statistics_daily` because on the master it would be checksummed in one chunk but on these replicas it has too many rows:
10381712 rows on MSS-yr1w813oie
The current chunk size limit is 4000 rows (chunk size=1000 * chunk size limit=4.0).

EXECUTE FAIL AT 2022-05-16 10:56:31

查看数据库，表是空表，不过碎片较多（>17G），原因是该表用于结转，因此大量空间未释放。

mysql> show table status like 'road_area_statistics_daily' G
*************************** 1. row ***************************
           Name: road_area_statistics_daily
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 65536
      Data_free: 18919456768
 Auto_increment: 95834029
    Create_time: 2021-08-14 14:30:34
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 路区日统计表
1 row in set (0.00 sec)

怀疑是主从统计信息不一致导致，因此在主库手动更新统计信息。

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

再次执行，执行成功。

Successfully altered `roadarea_manager`.`road_area_statistics_daily`.

2.3 复现过程

复现的过程比较波折，重点是模拟出主从统计信息不一致的场景。

主要是分别通过以下三种方式尝试模拟主从统计信息不一致：

大量更新带来的主从统计信息不一致；
调整参数 innodb_stats_auto_recalc 关闭磁盘数据表的自动更新；
手动更新内存中的统计信息。

3 分析

3.1 pt-online-schema-change

3.1.1 分块

pt 在拷贝数据过程中会首先将数据拆分为多个块（chunk），从而保证拷贝每个 chunk 的用时稳定在 chunk-time 时间内，避免导致服务器负载过高。

The data copy process is performed in small chunks of data, which are varied to attempt to make them execute in a specific amount of time (see –chunk-time). This process is very similar to how other tools, such as pt-table-checksum, work.

分块通常使用主键或唯一键实现。如下所示，pt 执行时查看数据库连接，可以看到基于主键分块的数据拷贝。

mysql> select * from information_schema.processlist where info like 'INSERT LOW_PRIORITY IGNORE%' G
*************************** 1. row ***************************
     ID: 1269
   USER: admin
   HOST: 127.0.0.1:49315
     DB: cctest
COMMAND: Query
   TIME: 0
  STATE: Sending data
   INFO: INSERT LOW_PRIORITY IGNORE INTO `cctest`.`_t2_new` (`id`, `a`, `create_time`, `tenement_code`, `b`, `c`, `d`) SELECT `id`, `a`, `create_time`, `tenement_code`, `b`, `c`, `d` FROM `cctest`.`t2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4372561')) AND ((`id` <= '4520395')) LOCK IN SHARE MODE /*pt-online-schema-change 1587 copy nibble*/
1 row in set (0.00 sec)

计算多次查询的块大小，稳定在 10w 以上，有可能是分批处理。

3011733-2896552=115181
4520395-4372561=147834
5049026-4922305=126721

3.1.2 参数

pt-osc 工具主要通过以下三个参数控制 chunk 的大小：

chunk-size，用于控制每个 chunk 中的数据行数，默认等于 1000。没有指定时初始 chunk 大小等于1000，后续 chunk 将动态调整大小。指定时关闭调整，每个 chunk 大小等于指定值；
chunk-size-limit，用于控制每个 chunk 中的最大行数上限（chunk size * chunk-size-limit），默认等于 4。当表中没有唯一键时，无法准确判断 chunk 的大小，因此提供该参数用于容错。指定为 0 时表示关闭 chunk 限制检测；
chunk-time，用于控制每个 chunk 数据拷贝的用时，默认等于 0.5s。大于 0 时表示执行用时固定，chunk 大小可变。指定为 0 时表示 chunk 大小固定，执行用时可变，作用等同于指定 chunk-size。

当前线上环境使用的 pt 命令如下所示，其中没有指定 chunk 大小，因此使用的都是默认值。

因此，初始 chunk 大小等于1000，后续 chunk 将动态调整大小，保持每次数据拷贝的执行用时稳定在 0.5s，最大 chunk 等于 4000。

pt-online-schema-change u="{mysql_user}",p="{mysql_password}",h="{mysql_ip}",
P={mysql_port},D="{mysql_database}",t="{mysql_table}" 
--alter "{mysql_sql}" --nocheck-replication-filters 
--charset=utf8 --max-load=Threads_running=50 --critical-load=Threads_running=500,Threads_connected=800 
--recursion-method=processlist,hosts --max-lag=1 --check-interval=30 
--statistics --print --execute 
--progress percentage,20

拷贝过程中判断 chunk 大小时使用 explain 语句查看执行计划，如果发现一个 chunk 中的行数超出限制，pt-osc 工具将跳过该 chunk。

Do not copy chunks this much larger than the desired chunk size.

The tool usesto estimate how many rows are in the chunk. If that estimate exceeds the desired chunk size times the limit, then the tool skips the chunk.

该特性与 pt-table-checksum 工具类似，因此可以参考 pt-table-checksum 工具的使用。

3.2 pt-table-checksum

大数据块（Too large chunk size）可能导致数据校验报错，具体报错如下所示。

Skipping table because on the master it would be checksummed in one chunk but on these replicas it has too many rows:
xxxxx rows on db_name.table_name
The current chunk size limit is xxxxx rows (chunk size=xxxx * chunk size limit=5).

报错的原因是主从统计信息不一致。

Skipping the table is a common issue with pt-table-checksum and can be caused by different/outdated table statistics on the master or slave side.

在分析原因过后，还需要复现作为证明，因此就需要模拟出主从统计信息不一致的场景。

4 复现

4.1 大量更新

首先尝试模拟大量更新后带来的主从统计信息不一致。

4.1.1 执行成功

准备一张 200w 数据的表。

mysql> show table status like 't2' G
Rows: 2311712

删除其中 100w 条数据，大约一半。

mysql> select * from t2 limit 1000000,3;
+---------+------+---------------------+---------------+------+------+------+
| id      | a    | create_time         | tenement_code | b    | c    | d    |
+---------+------+---------------------+---------------+------+------+------+
| 3873107 | a    | 2022-03-14 20:38:57 |               |    1 | NULL | 1.22 |
| 3873108 | a    | 2022-03-14 20:38:57 |               |    1 | NULL | 1.22 |
| 3873109 | a    | 2022-03-14 20:38:57 |               |    1 | NULL | 1.22 |
+---------+------+---------------------+---------------+------+------+------+
3 rows in set (0.33 sec)

mysql> delete from t2 where id<3873107;
Query OK, 1000000 rows affected (10.85 sec)

对比主从的 mysql.innodb_table_stats 数据表，n_rows 统计项差异不大。

MySQL pt 执行报错 replicas have too many rows in one chunk — image-20220924212416692

pt 执行成功，没有复现。

[root@test ~]# pt-online-schema-change -u admin -p ** -h 127.0.0.1 -P 3358 D=cctest,t=t2 --alter="engine = innodb;" --critical-load=Threads_running=500,Threads_connected=800 --max-load=Threads_running=50 --progress percentage,20 --statistics --print --max-lag=500 --recursion-method=processlist --check-interval=30 --nocheck-replication-filters --charset=utf8 --execute
...
Successfully altered `cctest`.`t2`.

然后，尝试将表中剩余数据全部删除后重新执行 pt。

4.1.2 执行报错

执行 delete 语句将表中数据全部删除。

mysql> delete from t2;
Query OK, 1316908 rows affected (14.73 sec)

pt 执行报错，报错复现。

[root@test Inception]# pt-online-schema-change -u admin -p ** -h 127.0.0.1 -P 3358 D=cctest,t=t2 --alter="engine = innodb;" --critical-load=Threads_running=500,Threads_connected=800 --max-load=Threads_running=50 --progress percentage,20 --statistics --print --max-lag=500 --recursion-method=processlist --check-interval=30 --nocheck-replication-filters --charset=utf8 --execute
Found 1 slaves:
test2 -> x.x.x.x:3358
Will check slave lag on:
test2 -> x.x.x.x:3358
...
# Event  Count
# ====== =====
# INSERT     0
`cctest`.`t2` was not altered.
2022-09-24T21:30:43 Error copying rows from `cctest`.`t2` to `cctest`.`_t2_new`: 2022-09-24T21:30:43 Cannot copy table `cctest`.`t2` because on the master it would be checksummed in one chunk but on these replicas it has too many rows:
  354342 rows on exps-test2
The current chunk size limit is 4000 rows (chunk size=1000 * chunk size limit=4.0).

对比主从统计信息，其中主库的统计信息短时间内更新到准确，从库还没有及时更新。

4.1.3 再次执行

再次执行，执行成功，没有报错。

Successfully altered `cctest`.`t2`.

查看从库统计信息，约等于主库。

mysql> select * from mysql.innodb_table_stats where database_name='cctest' and table_name='t2' G
*************************** 1. row ***************************
           database_name: cctest
              table_name: t2
             last_update: 2022-09-24 21:30:54
                  n_rows: 0
    clustered_index_size: 2244
sum_of_other_index_sizes: 4683
1 row in set (0.00 sec)

根据 mysql.innodb_table_stats 表的 last_update 字段可知，主从的统计信息更新的时间差等于 21:30:54 – 21:30:41 = 13 s。

因此在这 13 s 内执行 pt 都有可能导致报错。

但是，随后发现，实际上还不算复现，原因是当前有主从延迟，如果等到主从延迟消失以后再执行就无法复现了。

上面提到，主从统计信息更新的时间差约等于 13s，而主从执行 delete 分别用时 14.73s 左右，因此最大主从延迟将近 30s，进而可以判断刚才复现时从库有延迟。

而 pt 命令中指定最大延迟 –max-lag=1，因此与线上环境不同，不算复现。

查看从库当前的主从延迟，果然还没重放完成。

        Seconds_Behind_Master: 22

然后，考虑通过关闭从库的统计信息更新实现。

4.2 调整参数

4.2.1 执行成功

根据网络资料，关闭 innodb_stats_auto_recalc 参数可以关闭服务器自动重新计算统计数据。

主库，重新插入数据。

mysql> insert into t2 select * from t2_bak where id>=3873107;         
Query OK, 1316908 rows affected (15.75 sec)
Records: 1316908  Duplicates: 0  Warnings: 0

从库，关闭 innodb_stats_auto_recalc 参数。

mysql> set global innodb_stats_auto_recalc=0;
Query OK, 0 rows affected (0.00 sec)

主库，删除数据。

mysql> delete from t2;                                                
Query OK, 1316908 rows affected (13.80 sec)

对比主从的统计信息与执行计划，结果显示从库在关闭 innodb_stats_auto_recalc 参数后，数据表中数据不变，但执行计划中行数发生变化。

在主从延迟消失后，pt 执行成功，没有报错。

2022-09-24T21:50:05 Copying approximately 1 rows...
...
Successfully altered `cctest`.`t2`.

因此表明当前关闭 innodb_stats_auto_recalc 并没有导致主从统计信息不一致。

可见，innodb_stats_auto_recalc 参数仅用于控制磁盘的统计信息，内存中的统计信息还会自动更新，这也与官方文档中的表述一致。

Causes InnoDB to automatically recalculate persistent statistics after the data in a table is changed substantially.

此外，在执行完 DDL 以后，重新查看从库的 innodb_table_stats 数据表，结果显示统计信息更新，猜测是重建表时触发自动更新，忽略 innodb_stats_auto_recalc 参数。

mysql> select * from mysql.innodb_table_stats where database_name='cctest' and table_name='t2' G
*************************** 1. row ***************************
           database_name: cctest
              table_name: t2
             last_update: 2022-09-24 21:41:12
                  n_rows: 0
    clustered_index_size: 1
sum_of_other_index_sizes: 5
1 row in set (0.00 sec)

那么，有没有办法可以关闭内存中的统计信息更新呢？

实际上，可以通过直接修改 mysql.innodb_table_stats 系统表并刷新数据表（清除缓存）实现对内存中统计信息的更新。

4.3 手动更新

4.3.1 执行失败

上文中提到，默认最大 chunk 等于 4000，因此理论上主从分别大于与小于 4000 就可以复现。

主库，数据行数小于 4000。

mysql> insert into t2 select * from t2_bak limit 2000;
Query OK, 2000 rows affected (0.05 sec)
Records: 2000  Duplicates: 0  Warnings: 0

从库，手动更新索引信息，指定行数超过 4000，并开启 innodb_stats_auto_recalc 参数。

mysql> set global innodb_stats_auto_recalc=1;
Query OK, 0 rows affected (0.00 sec)

mysql> update mysql.innodb_table_stats set n_rows=20000 where database_name='cctest'' and table_name='t2';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush table t2;
Query OK, 0 rows affected (0.00 sec)

如下图所示，当前主从统计信息不一致。

pt 执行失败，成功复现。

2022-09-24T22:02:50 Copying approximately 2000 rows...
...
# Event  Count
# ====== =====
# INSERT     0
`cctest`.`t2` was not altered.
2022-09-24T22:02:50 Error copying rows from `cctest`.`t2` to `cctest`.`_t2_new`: 2022-09-24T22:02:50 Cannot copy table `cctest`.`t2` because on the master it would be checksummed in one chunk but on these replicas it has too many rows:
  20000 rows on exps-test2
The current chunk size limit is 4000 rows (chunk size=1000 * chunk size limit=4.0).

接下来验证是否从库报错阈值等于 4000。

4.3.2 测试阈值

分别指定从库统计信息中的行数等于 4001 与 4000。

首先指定行数等于 4001。

mysql> update mysql.innodb_table_stats set n_rows=4001 where database_name='cctest' and table_name='t2';

pt 执行失败。

The current chunk size limit is 4000 rows (chunk size=1000 * chunk size limit=4.0).

指定行数等于 4000。

mysql> update mysql.innodb_table_stats set n_rows=4000 where database_name='cctest' and table_name='t2';

pt 执行成功。

Successfully altered `cctest`.`t2`.

到目前为止，复现报错，并证明复现条件是主从统计信息分别大于与小于 4000。

5 处理

针对该问题有多种处理方法，如：

手动更新统计信息，也可以定期更新表的统计信息，比如1个月/2周一次全量更新；
增加参数 –chunk-size-limit，放大每个 chunk 中的最大行数上限；
设置参数 –chunk-size-limit=0，关闭 chunk 限制检测；
小表建议使用 online DDL，执行起来可能要比 pt 更快，理论上锁表风险很低，主从延迟也小。

最终，线上环境选择小表建议使用 online DDL，原因是实现简单有效。

6 知识点

6.1 统计信息

6.1.1 存储方式

InnoDB 以表为单位收集统计信息，并提供了以下两种存储统计信息的方式：

内存（非永久性），执行 explain、show table status like 等命令中查看到的就是内存中的统计信息；
磁盘（永久性），具体是保存在 mysql.innodb_index_stats、mysql.innodb_table_stats 两张系统表中。

系统变量 innodb_stats_transient_sample_pages、innodb_stats_transient_sample_pages 分别用于控制永久性与非永久性统计时采样的页面数量，默认值等于 8。理论上值越大，统计准确度越高，IO 成本越高。

原因是统计信息中最重要的指标是 n_rows，而 InnoDB 在统计一个表中有多少行记录的大致流程为：

按照一定的算法（并不是纯粹随机的）从聚簇索引中选择 innodb_stats_transient_sample_pages 个叶子节点页面；
统计每个页面中包含的纪录数量，然后计算一个页面中平均包含的记录数量；
每页的平均记录数量乘以全部叶子节点数量，结果就是该表的 n_rows 值。

在数据的增删改过程中，表中数据不断变化，统计信息也会随之变化。此外，MySQL 还支持手动更新统计信息。

6.1.2 更新方式

MySQL 支持多种方式更新统计信息：

系统变量 innodb_stats_auto_recalc 用于控制当发生变动的记录数量超过阈值（10%）时是否将内存中的最新数据更新到磁盘数据表；
analyze table，立即重新计算统计信息；
update table & flush table，直接修改表中数据并刷新数据表，可以实现对内存中统计信息的更新。

MySQL 为了提高其性能，会将部分数据缓存在内存中，flush table 用于刷新表（清除缓存）。

6.2 chunk

pt-online-schema-change 工具很重视使用过程中对数据库的性能影响，用户体验较好。

一方面提供了负载参数支持用户自定义最大负载，达到阈值后工具将暂停执行。

当前线上环境使用的 pt 命令中指定负载参数 –max-load、–critical-load。

--max-load=Threads_running=50 --critical-load=Threads_running=500,Threads_connected=800

另一方面，在拷贝数据过程中会首先将数据拆分为多个块（chunk），从而保证拷贝每个 chunk 的用时稳定在 chunk-time 时间内，避免导致服务器负载过高。此外，还支持用户自定义最大 chunk 限制与最大执行用时或关闭校验。

pt-table-checksum 工具中也是类似的原理。当主从 chunk 大小差异较大时执行将跳过该表（skip），而 pt-online-schema-change 工具中将报错退出。

7 结论

pt 执行 SQL 工单报错 replicas have too many rows in one chunk，本文中复现报错并分析处理方法。

报错的根本原因是主从统计信息不一致，复现条件是主从统计信息分别大于与小于最大 chunk 限制。

原理是 pt-osc 拷贝过程中判断 chunk 大小时使用 explain 语句查看执行计划，如果发现一个 chunk 中的行数超出限制，pt-osc 工具将跳过该 chunk。

通过手动更新从库内存中的统计信息模拟主从统计信息不一致，最终复现报错。

8 待办

flush table

参考教程

pt-online-schema-change document

https://docs.percona.com/percona-toolkit/pt-online-schema-change.html

How to Handle pt-table-checksum Errors

https://www.percona.com/blog/2018/04/06/how-to-handle-pt-table-checksum-errors/

Skipping table – it would be checksummed in 1 chunk but replicas have too many rows

https://forums.percona.com/t/skipping-table-it-would-be-checksummed-in-1-chunk-but-replicas-have-too-many-rows/3807

《MySQL是怎样运行的：从根儿上理解 MySQL》

原文始发于微信公众号（丹柿小院）：MySQL pt 执行报错 replicas have too many rows in one chunk

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/194453.html

MySQL pt 执行报错 replicas have too many rows in one chunk