1

How Triggers May Significantly Affect the Amount of Memory Allocated to Your MyS...

 2 years ago
source link: https://www.percona.com/blog/how-triggers-may-significantly-affect-the-amount-of-memory-allocated-to-your-mysql-server/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

How Triggers May Significantly Affect the Amount of Memory Allocated to Your MySQL Server

Triggers Affect Memory Allocated to Your MySQL ServerMySQL 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
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
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.

MySQL
$ 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
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
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:

MySQL
$ 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
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
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
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
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
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.

STAY UP-TO-DATE With Percona!

p

Join 50,000+ of your fellow open-source enthusiasts! Our newsletter provides updates on Percona open source software releases, technical resources, and valuable MySQL, MariaDB, PostgreSQL, and MongoDB-related articles. Get information about Percona Live, our technical webinars, and upcoming events and meetups where you can talk with our experts.

Enter your work email address:*

By submitting my information I agree that Percona may use my personal data in send communication to me about Percona services. I understand that I can unsubscribe from the communication at any time in accordance with the Percona Privacy Policy.

Author

Sveta Smirnova

Sveta joined Percona in 2015. Her main professional interests are problem solving, working with tricky issues, bugs, finding patterns that can solve typical issues quicker and teaching others how to deal with MySQL issues, bugs and gotchas effectively. Before joining Percona Sveta worked as a Support Engineer in the MySQL Bugs Analysis Support Group in MySQL AB-Sun-Oracle.

She is the author of the book "MySQL Troubleshooting" and JSON UDF functions for MySQL.

Leave a Reply Cancel reply


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK