Resolving MySQL OOM Caused by Triggers and table_open_cache_instances

1 Problem Description

A replica server's memory usage kept increasing until the MySQL service was killed.

The memory monitoring view showed a usage of 92.76% and a kill event around 00:00, after which mysqld_safe restarted the process and memory rose again.

2 Investigation Process

Basic Information

Database version: MySQL 5.7.32

Operating system: Ubuntu 20.04

Host configuration: 8C 64GB innodb_buffer_pool_size: 8G

The my.cnf did not enable memory‑related monitoring, so the following setting was added:

performance-schema-instrument = 'memory/% = COUNTED'

After enabling memory monitoring and waiting, the relevant Performance Schema views displayed that the InnoDB buffer pool size was allocated correctly, but the memory/sql/sp_head::main_mem_root component consumed about 8 GB.

According to the source code, sp_head represents an instance of a stored program (procedure, function, trigger, or event).

Queries showed a large number of triggers and stored procedures in the environment.

A MySQL bug (ID 86821) mentions that a high table_open_cache_instances value can cause excessive memory usage when many large triggers are present, and recommends setting the variable to 1 in such cases.

The parameter table_open_cache_instances defaults to 8 or 16 on systems with many cores; each instance creates its own table cache, and when a table is cached, all associated triggers are stored in memory/sql/sp_head::main_mem_root . The larger the setting, the more memory is consumed by triggers and stored procedures, eventually leading to OOM.

Verification with table_open_cache_instances = 8

# Clear cache
mysql> flush tables;

for i in `seq 1 1 8`
do
  mysql -uroot -p test -e "select * from test;"
done

mysql> show variables like '%table_open_cache_instances%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache_instances | 8     |
+----------------------------+-------+

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   |
+--------------+

A trigger was then created on the test table, and the table was accessed repeatedly. The memory usage for memory/sql/sp_head::main_mem_root grew to about 439 KiB, confirming that many large triggers significantly increase memory consumption.

Verification with table_open_cache_instances = 1

# Clear cache
mysql> flush tables;

mysql> show variables like '%table_open_cache_instances%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache_instances | 1     |
+----------------------------+-------+

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   |
+--------------+

# Access table
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   |
+--------------+

With the parameter set to 1, the memory growth was much smaller.

Applying the same change to the problematic replica (setting table_open_cache_instances to 1) stabilized the memory usage and prevented further OOM incidents.

3 Summary

MySQL does not recommend using a large number of triggers or complex stored procedures.

Setting table_open_cache_instances to 1 can reduce memory consumption, though it may affect SQL performance under high concurrency; adjust according to workload.

The more triggers present, the larger the memory occupied by memory/sql/sp_head::main_mem_root , and the more memory stored procedures will use.

This article provides one direction to solve OOM; deeper kernel‑level details should be explored independently.

References

[1] sp_head: https://dev.mysql.com/doc/dev/mysql-server/latest/classsp__head.html#details

[2] Bug 86821: https://bugs.mysql.com/bug.php?id=86821