mysql 数据库缓存调优之解决The total number of locks exceeds the lock table size错误

环境：mysql5.6.2 主从同步（备注：需操作主库和从库）一、InnoDB表执行大批量数据的更新，插入，删除操作时会出现这个问题，需要调整InnoDB全局的innodb_buffer_pool_size的值来解决这个问题，并且重启mysql服务。遇到的问题：mysql报错如下：The total number of locks exceeds the lock table size错误二、解决

zxfBdd

1401人浏览 · 2021-09-23 15:29:18

zxfBdd · 2021-09-23 15:29:18 发布

环境：

mysql5.6.2 主从同步（备注：需操作主库和从库）

一、InnoDB表执行大批量数据的更新，插入，删除操作时会出现这个问题，需要调整InnoDB全局的innodb_buffer_pool_size的值来解决这个问题，并且重启mysql服务。

遇到的问题：

mysql报错如下：

The total number of locks exceeds the lock table size错误

二、解决办法

2.1、登录主库查看buffer_pool_size大小

[root@a2 ~]# mysql -uroot -p （备注：主库）

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 54

Server version: 5.6.24 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

#mysql数据库buffer_pool大小为：8MB

mysql> show variables like "%_buffer%";

+-------------------------------------+----------------+

| Variable_name | Value |

+-------------------------------------+----------------+

| innodb_buffer_pool_size | 8388608 |

22 rows in set (0.00 sec)

需要调优为：2G-3G

2.2、调优方法

#关闭数据库（备注：主从同步状态正常的情况下关闭主库）

[root@a1 mysql]# /etc/init.d/mysqld stop

Shutting down MySQL........... SUCCESS!

#修改配置文件（备注：在主库上面修改配置文件）

[root@a1 mysql]# vi /roobo/server/mysql/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

#[client]

#port = 3306

#socket = /tmp/mysql.sock

[mysqld_safe]

err-log=/var/log/mysqld.log

pid-file=/var/lib/mysql/mysqld.pid

default-character-set = utf8

[mysqld]

port = 3306

socket = /tmp/mysql.sock

#innodb_buffer_pool_size=8MB #原文件

innodb_buffer_pool_size=2GB #修改为2G或 3G，根据表大小，进行调优。

basedir = /roobo/server/mysql

datadir = /roobo/mysqldata/database

pid-file = /var/lib/mysql/mysql.pid

character_set_server = utf8

log-bin = /roobo/server/mysql/data/mysql-bin

server_id = 1

#启动数据库（备注：在主库上面操作）

[root@a1 mysql]# /etc/init.d/mysqld start

Starting MySQL. SUCCESS!

#再登录数据库检查是否修改成功（备注：在主库上面操作）

[root@a1 mysql]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.24-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

#修改成2G

mysql> show variables like "%_buffer%";

+-------------------------------------+----------------+

| Variable_name | Value |

+-------------------------------------+----------------+

| bulk_insert_buffer_size | 8388608 |

| innodb_buffer_pool_dump_at_shutdown | OFF |

| innodb_buffer_pool_dump_now | OFF |

| innodb_buffer_pool_filename | ib_buffer_pool |

| innodb_buffer_pool_instances | 8 |

| innodb_buffer_pool_load_abort | OFF |

| innodb_buffer_pool_load_at_startup | OFF |

| innodb_buffer_pool_load_now | OFF |

| innodb_buffer_pool_size | 2147483648 |

| innodb_change_buffer_max_size | 25 |

| innodb_change_buffering | all |

| innodb_log_buffer_size | 8388608 |

| innodb_sort_buffer_size | 1048576 |

| join_buffer_size | 262144 |

| key_buffer_size | 8388608 |

| myisam_sort_buffer_size | 8388608 |

| net_buffer_length | 16384 |

| preload_buffer_size | 32768 |

| read_buffer_size | 131072 |

| read_rnd_buffer_size | 262144 |

| sort_buffer_size | 262144 |

| sql_buffer_result | OFF |

+-------------------------------------+----------------+

22 rows in set (0.00 sec)

mysql> exit

#备注：从库也要调优相同参数，否则会导致数据库主从不同步

#关闭数据库（备注：主从同步状态正常的情况下关闭从库）

[root@a2 mysql]# /etc/init.d/mysqld stop

Shutting down MySQL........... SUCCESS!

#修改配置文件（备注：在从库上面修改配置文件）

[root@a2 mysql]# vi my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
#[client]
#port = 3306
#socket = /tmp/mysql.sock
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysqld.pid
default-character-set = utf8

[mysqld]
port = 3306
socket = /tmp/mysql.sock
#innodb_buffer_pool_size=8MB
innodb_buffer_pool_size=2GB #把8M修改成2G
basedir = /roobo/server/mysql
datadir = /roobo/mysqldata/database
pid-file = /var/lib/mysql/mysql.pid
character_set_server = utf8
server-id = 2
read_only = 1

#启动数据库（备注：在从主库上面操作）

[root@a2 mysql]# /etc/init.d/mysqld start

Starting MySQL. SUCCESS!

#再登录数据库检查是否修改成功（备注：在从库上面操作）

[root@a2 mysql]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.24-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

<br>#修改成2G

mysql> show variables like "%_buffer%";

+-------------------------------------+----------------+

| Variable_name | Value |

+-------------------------------------+----------------+

| bulk_insert_buffer_size | 8388608 |

| innodb_buffer_pool_dump_at_shutdown | OFF |

| innodb_buffer_pool_dump_now | OFF |

| innodb_buffer_pool_filename | ib_buffer_pool |

| innodb_buffer_pool_instances | 8 |

| innodb_buffer_pool_load_abort | OFF |

| innodb_buffer_pool_load_at_startup | OFF |

| innodb_buffer_pool_load_now | OFF |

| innodb_buffer_pool_size | 2147483648 |

| innodb_change_buffer_max_size | 25 |

| innodb_change_buffering | all |

| innodb_log_buffer_size | 8388608 |

| innodb_sort_buffer_size | 1048576 |

| join_buffer_size | 262144 |

| key_buffer_size | 8388608 |

| myisam_sort_buffer_size | 8388608 |

| net_buffer_length | 16384 |

| preload_buffer_size | 32768 |

| read_buffer_size | 131072 |

| read_rnd_buffer_size | 262144 |

| sort_buffer_size | 262144 |

| sql_buffer_result | OFF |

+-------------------------------------+----------------+

22 rows in set (0.00 sec)

mysql> exit

#再登录从库，查看主从同步情况（备注：在从库上面操作）

[root@a2 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 55

Server version: 5.6.24 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.7.19.129

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000015

Read_Master_Log_Pos: 67365

Relay_Log_File: mysql-relay-bin.000036

Relay_Log_Pos: 67528

Relay_Master_Log_File: mysql-bin.000015

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 67365

Relay_Log_Space: 67864

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 4afce67d-d694-11e8-94c2-00163e0a2eec

Master_Info_File: /roobo/mysqldata/database/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> exit

Bye

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

基于《2023腾讯云容器和函数计算技术实践精选集》—探索腾讯云TKE的Docker容器、Serverless和微服务优势

腾讯云开发者社区

一站式加速、安全防护，EdgeOne为金融行业数字化升级保驾护航

腾讯云开发者社区

全球安全加速一站式底座，EdgeOne以极致玩家体验撬动游戏行业增长

腾讯云开发者社区

所有评论(0)

查看更多评论

zxfBdd

@u011250186

已为社区贡献2条内容