Recently found a customer development company to deploy the production MySQL database innodb_buffer_pool_size is the default value, physical memory is 32G, the results are obvious, the performance problem is very large.
It is advisable to set the 50%~80% of Innodb_buffer_pool_size as physical memory.
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
-bash-4.1$./mysql-uroot-penter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 3Server version:5.7.19-log mysql Community Server (GPL) Copyright (c), Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> mysql> show variables like ' innodb_buffer_pool_size '; +-------- -----------------+----------+| variable_name | Value |+-------------------------+----------+| Innodb_buffer_pool_size | 67108864 |+-------------------------+----------+1 row in Set (0.00 sec) mysql> use test; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> INSERT INTO t select * from T; ERROR 1206 (HY000): The total number of locksexceeds the lock table sizemysql> mysql> set global innodb_buffer_pool_size=1024000*1024; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> insert INTO t select * from T; Query OK, 67108864 rows affected (8 min 53.81 sec) records:67108864 duplicates:0 warnings:0mysql>
MySQL rror 1206 (HY000):