https://www.percona.com/blog/wp-content/uploads/2021/11/Triggers-Affect-Memory-Allocated-to-Your-MySQL-Server-300×157.png
MySQL stores active table descriptors in a special memory buffer called the table open cache. This buffer is controlled by configuration variables table_open_cache that hold the maximum number of table descriptors that MySQL should store in the cache, and table_open_cache_instances that stores the number of the table cache instances. With default values of table_open_cache=4000 and table_open_cache_instances=16, MySQL will create 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 increase the value of the table_open_cache, the required memory amount 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 tables.
However, if your tables have triggers, it changes the game.
For the test I created a table with a single column and inserted a row into it:
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)
Then I flushed the table cache and measured how much memory it uses:
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)
Then I accessed the table to put it into the cache.
$ 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 table descriptors took less than 16 KiB in the cache.
Now let’s try to create some triggers on this table and see if it changes anything.
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|
Then let’s flush the table cache and test memory usage again.
Initial state:
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)
After I put the tables into the cache:
$ 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)
As a result, in addition to 75.17 KiB in the table cache, 611.12 KiB is occupied by the memory/sql/sp_head::main_mem_root. That is the "Mem root for parsing and representation of stored programs."
This means that each time when the table is put into the table cache, all associated triggers are put into the memory buffer, storing their definitions.
FLUSH TABLES command clears the stored programs cache as well as the table cache:
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)
More triggers increase memory usage when put into the cache.
For example, if we create five more triggers and repeat our test we will see the following numbers:
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)
Numbers for the event memory/sql/sp_head::main_mem_root differ six times:
mysql> SELECT 3.58*1024/611.12; +------------------+ | 3.58*1024/611.12 | +------------------+ | 5.998691 | +------------------+ 1 row in set (0,00 sec)
Note that the length of the trigger definition affects the amount of memory allocated by the memory/sql/sp_head::main_mem_root.
For example, if we define the triggers as follow:
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)
The resulting amount of memory is 1.89 MiB instead of 3.58 MiB for the longer trigger definition.
Note that having a single table cache instance requires less memory to store trigger definitions. E.g. for our small six triggers, it will be 121.12 KiB instead of 1.89 MiB:
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)
Conclusion
When you access tables that have associated triggers, their definitions are put into the stored programs cache even when not fired. This was reported at MySQL Bug #86821 and closed as “Not a Bug” by Oracle. This is, certainly, not a bug, but the table and stored routines cache design. Still, it is good to be prepared, so you are not surprised when you run short of memory faster than you expect. Especially if you have many triggers with long definitions.
Percona Database Performance Blog