触发器如何显著影响分配给 MySQL的内存
2021-12-18 12:16 abce 阅读(484) 评论(0) 编辑 收藏 举报
可以通过检查状态变量Opened_tables来检查一下是否需要增加table_open_cache的大小。如果你并不经常执行flush tables命令，却发现Opened_tables的值非常大，那就应该考虑增加table_open_cache的大小了。table_open_cache_instances将table_open_cache分成多个实例，从而减少会话之间的竞争。一个dml语句只需要锁定一个table_open_cache_instance。
在MySQL5.7中，table_open_cache的默认值是2000，table_open_cache_instances的默认值是16，MySQL会创建16个独立的内存buffer，其中每个可以存放2000/16=125个表描述符。这些表缓存实例可以被并发访问，这样dml在使用表描述符的时候不会互相阻塞。
如果你只有一个表，表缓存不需要太多的内存，因为表的描述符数量是轻量级的，即使你增加table_open_cache的值，需要的内存也不会太多。例如，4000张表会消耗4000*4K=16MB的内存。100000张表也只是会消耗390MB的内存。
然而，如果你的表上有触发器，那游戏的规则就不是这样了。
mysql> CREATE TABLE tc_test( f1 INT); Query OK, 0 rows affected (0,03 sec) mysql> INSERT INTO tc_test VALUES(1); Query OK, 1 row affected (0,01 sec)
然后，我执行flush tables操作，看看使用了多少内存：
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,02 sec)mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 2 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 60.50 KiB |
+---------------+
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';
Empty set (0,00 sec)
接下来访问这个表，将其放到缓存中。
$ for i in `seq 1 1 16`; do mysql test -e "SELECT * FROM tc_test"; done
...
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 20 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 75.17 KiB |
+---------------+
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';
Empty set (0,01 sec)
16个表描述符，占用缓存空间还不到16KiB。
现在让我们在表上创建一些触发器，看看情况是否会发生变化。
mysql> CREATE TRIGGER tc_test_ai AFTER INSERT ON tc_test FOR EACH ROW
-> BEGIN
-> SIGNAL SQLSTATE '45000' SET message_text='Very long string.
-> MySQL stores table descriptors in a special memory buffer, called table open cache.
-> This buffer could be controlled by configuration variables table_open_cache that
-> holds how many table descriptors MySQL should store in the cache and table_open_cache_instances
-> that 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
-> table descriptors each. These table cache instances could be accessed concurrently, allowing DML
-> to use cached table descriptors without locking each other. If you use only tables, the table cache
-> does 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
-> up to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a huge
-> number for this number of open tables. However, if your tables have triggers, it changes the game.';
-> END|
刷新一下表缓存，看看内存的使用。
初始状态：
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 2 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 60.50 KiB |
+---------------+
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';
Empty set (0,00 sec)
将表放入缓存之后：
$ for i in `seq 1 1 16`; do mysql test -e "SELECT * FROM tc_test"; done
...
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 20 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 75.17 KiB |
+---------------+
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 |
+---------------+
| 611.12 KiB |
+---------------+
1 row in set (0,00 sec)
可以看到，额外增加了75.17KiB的表缓存，memory/sql/sp_head::main_mem_root消耗了611.12KiB内存。即"Mem root for parsing and representation of stored programs."
这表示每次将表放入缓存，相关联的触发器也被放入了内存中，内存中存放了触发器的定义。
flush tables命令清空了存储函数和表缓存。
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,01 sec)
mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,00 sec)
触发器越多，消耗的内存越多。
例如，如果我们创建五个触发器：
mysql> \d |
mysql> CREATE TRIGGER tc_test_bi BEFORE INSERT ON tc_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,01 sec)
mysql> CREATE TRIGGER tc_test_bu BEFORE UPDATE ON tc_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,02 sec)
mysql> CREATE TRIGGER tc_test_bd BEFORE DELETE ON tc_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 that 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 table descriptors each. These table cache instances could be accessed concurrently, allowing DML to use cached table descriptors without locking each other. If you use only tables, the table cache does 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 up to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a huge number for this number of open tables. However, if your tables have triggers, it changes the game.'; END|
Query OK, 0 rows affected (0,01 sec)
mysql> CREATE TRIGGER tc_test_au AFTER UPDATE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, call
ed ta a
t holds how many table descriptors MySQL should store in the cache and table_open_cache_instances th
at stores the number of the table cache instances. So with default values of table_open_cache=4000 a
nd table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 ta
ble descriptors each. These table cache instances could be accessed concurrently, allowing DML to us
e cached table descriptors without locking each other. If you use only tables, the table cache does
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 up
to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a hug
e number for this number of open tables. However, if your tables have triggers, it changes the game
.'; END|
Query OK, 0 rows affected (0,01 sec)
mysql> CREATE TRIGGER tc_test_ad AFTER DELETE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, call
ed table open cache. This buffer could be controlled by configuration variables table_open_cache tha
t holds how many table descriptors MySQL should store in the cache and table_open_cache_instances th
at stores the number of the table cache instances. So with default values of table_open_cache=4000 a
nd table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 ta
ble descriptors each. These table cache instances could be accessed concurrently, allowing DML to us
e cached table descriptors without locking each other. If you use only tables, the table cache does
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 up
to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a hug
e number for this number of open tables. However, if your tables have triggers, it changes the game
.'; END|
Query OK, 0 rows affected (0,01 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 35 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 446.23 KiB |
+---------------+
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 |
+---------------+
| 3.58 MiB |
+---------------+
1 row in set (0,00 sec)
memory/sql/sp_head::main_mem_root变成了6倍。
mysql> SELECT 3.58*1024/611.12; +------------------+ | 3.58*1024/611.12 | +------------------+ | 5.998691 | +------------------+ 1 row in set (0,00 sec)
触发器定义的长度影响内存的分配，通过memory/sql/sp_head::main_mem_root可以看出。
假如，我们像这样定义触发器：
mysql> DROP TABLE tc_test;
Query OK, 0 rows affected (0,02 sec)
mysql> CREATE TABLE tc_test( f1 INT);
Query OK, 0 rows affected (0,03 sec)
mysql> INSERT INTO tc_test VALUES(1);
Query OK, 1 row affected (0,01 sec)
mysql> \d |
mysql> CREATE TRIGGER tc_test_ai AFTER INSERT ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,01 sec)
mysql> CREATE TRIGGER tc_test_au AFTER UPDATE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,04 sec)
mysql> CREATE TRIGGER tc_test_ad AFTER DELETE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,01 sec)
mysql> CREATE TRIGGER tc_test_bi BEFORE INSERT ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,01 sec)
mysql> CREATE TRIGGER tc_test_bu BEFORE UPDATE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,02 sec)
mysql> CREATE TRIGGER tc_test_bd BEFORE DELETE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,01 sec)
mysql> \d ;
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 2 |
+---------------+-------+
1 row in set (0,01 sec)
mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 60.50 KiB |
+---------------+
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';
Empty set (0,00 sec)
$ for i in `seq 1 1 16`; do mysql test -e "select * from tc_test"; done
...
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 35 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 446.23 KiB |
+---------------+
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 |
+---------------+
| 1.89 MiB |
+---------------+
1 row in set (0,00 sec)
使用了1.89MiB内存而不是3.58MiB内存。
单个表缓存实例需要更少的内存来存储触发器定义。上面定义的6个小的触发器，只需要121KiB内存，而不是1.89MiB：
mysql> SHOW GLOBAL VARIABLES LIKE 'table_open_cache_instances';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache_instances | 1 |
+----------------------------+-------+
1 row in set (0,00 sec)
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 2 |
+---------------+-------+
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';
Empty set (0,00 sec)
$ for i in `seq 1 1 16`; do mysql test -e "select * from tc_test"; done
...
mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 121.12 KiB |
+---------------+
1 row in set (0,00 sec)
结论
当访问具有触发器的表时，即使未触发，它们的定义也会放入缓存中。这在MySQL BUG #86821 中报告的，并被Oracle关闭为"不是BUG"。这当然不是BUG，而是表和存储例程缓存设计。不过，做好准备是件好事，所以当以比预期更快的速度耗尽内存时，你不会感到惊讶。特别是如果有许多定义很长的触发器。
此外，触发器定义越长，消耗的内存就越多。
