如何解决由触发器导致 MySQL 内存溢出？

#清空缓存

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

[root@test ~]# cat test.sh
for i in `seq 1 1 8`
do
mysql -uroot -p test -e "select * from test;"
done

[root@test ~]# sh test.sh

mysql> show variables like '%table_open_cache_instances%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache_instances | 8 |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 119.61 KiB |
+---------------+
1 row in set (0.00 sec)

mysql> \d|
mysql> CREATE TRIGGER trigger_test BEFORE INSERT ON test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000' SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, calle
'> at holds how many table descriptors MySQL should store in the cache and table_open_cache_instances t
'> hat stores the number of the table cache instances. So with default values of table_open_cache=4000
'> and table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 t
'> able descriptors each. These table cache instances could be accessed concurrently, allowing DML to u
'> se cached table descriptors without locking each other. If you use only tables, the table cache doe
'> s not require a lot of memory, because descriptors are lightweight, and even if you significantly increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take u
'> p to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a hu
'> ge number for this number of open tables. However, if your tables have triggers, it changes the gam
'> e.'; END|
Query OK, 0 rows affected (0.00 sec)

#清空缓存

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

[root@test ~]# cat test.sh
for i in `seq 1 1 8`
do
mysql -uroot -p test -e "select * from test;"
done

[root@test ~]# sh test.sh

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 438.98 KiB |
+---------------+
1 row in set (0.00 sec)

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

mysql> show variables like '%table_open_cache_instances%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache_instances | 1 |
+----------------------------+-------+
1 row in set (0.00 sec)

SELECT current_alloc FROM sys.memory_global_by_current_bytes WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 119.61 KiB |
+---------------+
1 row in set (0.00 sec)

mysql> #访问表

mysql> system sh test.sh

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 159.53 KiB |
+---------------+
1 row in set (0.00 sec)