Analyzing MySQL Memory Exhaustion and Optimizing with Alternative Allocators

Symptoms : A developer reported that a test server’s MySQL process was using almost all of the 32 GB RAM (about 29 GB), as shown by the top command output.

Environment : The server runs MySQL 5.6.51 in a development‑installed test environment with 32 GB total memory.

Analysis – Startup and Logs : The MySQL instance had been started only a week ago. The error log repeatedly recorded messages such as “InnoDB: Database was not shutdown normally!” roughly every ten days, indicating unexpected shutdowns. OS logs confirmed that MySQL triggered the OOM killer.

2022-02-24 03:03:42 20981 [Note] InnoDB: Database was not shutdown normally!
2022-03-13 02:31:40 4134 [Note] InnoDB: Database was not shutdown normally!
2022-03-31 02:31:08 6846 [Note] InnoDB: Database was not shutdown normally!
2022-04-12 02:31:41 1159 [Note] InnoDB: Database was not shutdown normally!
2022-04-23 04:41:51 6773 [Note] InnoDB: Database was not shutdown normally!
2022-05-04 02:31:52 2499 [Note] InnoDB: Database was not shutdown normally!
2022-05-13 04:56:06 23010 [Note] InnoDB: Database was not shutdown normally!
2022-05-30 02:31:33 3244 [Note] InnoDB: Database was not shutdown normally!

OS Log Confirmation :

# grep oom-killer /var/log/messages* /var/log/messages-20220605
May 30 02:31:30 vm10-136-9-24 kernel: mysqld invoked
oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0

Configuration Check : The my.cnf file mostly used default values; notably innodb_buffer_pool_size was only the default 128 MB.

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

The instance had over 500 connections, most in SLEEP state. The performance_schema tables showed structural errors, likely because the data dictionary was not upgraded after a version change, preventing collection of many memory‑diagnostic metrics.

2022-06-09 11:19:08 27468 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
... (multiple similar errors omitted) ...
mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

InnoDB Status Memory Snapshot :

BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 736104382
Buffer pool size 8191
Free buffers 1024
Database pages 6851
...

The data dictionary alone consumed about 700 MB. Using lsof revealed that MySQL opened a huge number of MyISAM partition files (each partition uses two file handles), which contributed heavily to memory usage.

lsof|grep "#P#"|grep -E "MYD$|MYI$"|wc -l
29826

MyISAM Engine Details : Data blocks are cached by the OS, while index blocks are cached in the key buffer, whose size is controlled by key_buffer_size . The default value was only 8 MB.

mysql> show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+

MySQL uses the GLIBC allocator by default. By invoking malloc_stats() via gdb , the article captured detailed arena memory usage, showing that the total system‑allocated memory for MySQL was about 28.4 GB while the actually used memory was only ~2.9 GB, indicating severe fragmentation.

gdb -ex "call (void) malloc_stats()" --batch -p $(pidof mysqld)

Arena 0:
system bytes = 2001301504
in use bytes = 250961264
... (many arena lines omitted) ...
Total (incl. mmap):
system bytes = 1166893056
in use bytes = 348358880
max mmap regions = 55
max mmap bytes = 1919492096

By saving the output to /tmp/fx.txt and processing it with awk , the author calculated the allocated versus used memory:

# awk '{if($1 == "system") total+=$NF; else if ($1 == "in") used+=$NF }END{print total/1024/1024/1024,used/1024/1024/1024}' /tmp/fx.txt
28.4044 2.87976

To mitigate fragmentation, the MySQL service was restarted with the Google tcmalloc memory allocator. After several days, top showed MySQL memory usage stabilizing around 5 GB.

Conclusion : Alternative allocators such as jemalloc and tcmalloc can significantly reduce memory fragmentation and improve performance in high‑concurrency, multi‑core workloads. The article notes that MongoDB uses tcmalloc and Redis uses jemalloc, illustrating the broader relevance of choosing an appropriate memory allocator.