Shuo
Shuo I'm a DBA(Database Administrator), we can share and discuss MySQL, MongoDB, Redis and other databases here, also including learning Python, Shell, Golang together.

MySQL使用原生OnlineDDL进行结构变更报错:The total number of locks exceeds the lock table size


MySQL使用原生OnlineDDL进行结构变更报错:The total number of locks exceeds the lock table size

情况介绍

阿里云RDS MySQL5.6在使用原生OnlineDDL进行结构变更时报错:

1
2
ERROR 1206 (HY000): The total number of locks exceeds the lock table size,

在错误日志中:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
=====================================

InnoDB: WARNING: over 67 percent of the buffer pool is occupied by

lock heaps or the adaptive hash index! Check that your

transactions do not set too many row locks.

Your buffer pool size is 64 MB. Maybe you should make the buffer pool bigger?

Starting the InnoDB Monitor to print diagnostics, including lock heap and hash index sizes.

......

问题排查

1. 确认环境参数:

查看当前的配置:max_write_lock_count =102400,该参数在MySQL5.6 64位的环境默认值为18446744073709551615 ~

2.解决

发现这个参数配置得太小了,但是阿里云RDS默认权限不允许大于102400,所以采用pt-online-schema-change进行。

若为自建的MySQL Server,可以配置:
set global max_write_lock_count =18446744073709551615

在MySQL官方文档中,对于该参数的说明:

comments powered by Disqus