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错误
二、解决办法
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
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
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 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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 -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
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
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
