MySql教程
触发器如何显著影响分配给 MySQL的内存
本文主要是介绍触发器如何显著影响分配给 MySQL的内存，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
MySQL使用一个特殊的内存buffer来存放活跃的表描述符，叫做table open cache。这个buffer是通过参数table_open_cache来控制的，table_open_cache定义了MySQL缓存中可以存放的表描述符的最大数量。参数table_open_cache_instances存储了表缓存实例的数量。
可以通过检查状态变量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，而是表和存储例程缓存设计。不过，做好准备是件好事，所以当以比预期更快的速度耗尽内存时，你不会感到惊讶。特别是如果有许多定义很长的触发器。
此外，触发器定义越长，消耗的内存就越多。
原文：https://www.percona.com/blog/how-triggers-may-significantly-affect-the-amount-of-memory-allocated-to-your-mysql-server/
这篇关于触发器如何显著影响分配给 MySQL的内存的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
MySQL读写分离入门：轻松掌握数据库读写分离技术12-07
-
MySQL读写分离入门教程12-07
-
MySQL分库分表入门详解12-07
-
MySQL分库分表入门指南12-07
-
MySQL慢查询入门：快速掌握性能优化技巧12-07
-
MySQL入门：新手必读的简单教程12-07
-
MySQL入门：从零开始学习MySQL数据库12-07
-
MySQL索引入门：新手快速掌握MySQL索引技巧12-07
-
BinLog学习：MySQL数据库BinLog入门教程12-06
-
Binlog学习：MySQL数据库的日志管理入门教程12-06
-
MySQL入门教程：从零开始掌握MySQL数据库12-06
-
MySQL读写分离入门教程：实现高性能数据库架构12-06
-
MySQL分库分表入门教程12-06
-
MySQL慢查询优化入门教程12-06
-
MySQL索引入门教程：提升查询效率的必备技能12-06
栏目导航
