Diagnose and Fix High MySQL Memory Usage (2026)

The MySQL server is consuming an excessive amount of RAM, leading to system instability and performance degradation.

Common Causes and Fixes:

1. Insufficient innodb_buffer_pool_size:

Diagnosis: Check current memory usage with free -h and compare it to innodb_buffer_pool_size in your my.cnf (or my.ini). A common rule of thumb is to set innodb_buffer_pool_size to 50-75% of your total system RAM on a dedicated database server.
Fix: Edit my.cnf (typically located at /etc/mysql/my.cnf, /etc/my.cnf, or /etc/mysql/mysql.conf.d/mysqld.cnf). Find the [mysqld] section and adjust innodb_buffer_pool_size. For example, on a 16GB RAM server, you might set:
```
innodb_buffer_pool_size = 10G
```
Restart MySQL: sudo systemctl restart mysql (or mysqld).
Why it works: The InnoDB buffer pool caches data and indexes in memory. If it’s too small, MySQL constantly has to fetch data from disk, leading to high I/O and often increased CPU usage, which can indirectly appear as high memory usage due to caching of frequently accessed data blocks that don’t fit.

2. Large tmp_table_size and max_heap_table_size:

Diagnosis: Monitor Created_tmp_tables and Created_tmp_disk_tables status variables: SHOW GLOBAL STATUS LIKE 'Created_tmp%';. If Created_tmp_disk_tables is a significant fraction of Created_tmp_tables, complex queries are spilling to disk, which is inefficient and can indirectly consume memory.
Fix: Increase tmp_table_size and max_heap_table_size in my.cnf within the [mysqld] section. These should generally be set to the same value. For example:
```
tmp_table_size = 256M
max_heap_table_size = 256M
```
Restart MySQL.
Why it works: These settings control the maximum size of in-memory temporary tables. If a temporary table exceeds tmp_table_size (or max_heap_table_size if it’s a MEMORY storage engine table), it’s converted to an on-disk table, which is much slower. While this is primarily about disk I/O, the creation and management of large in-memory temporary tables before they spill can consume significant RAM.

3. Excessive sort_buffer_size and join_buffer_size:

Diagnosis: High values for these per-session buffers, especially with many concurrent connections, can lead to substantial memory consumption. There isn’t a direct status variable for current usage, but if you’ve recently increased them and see memory spikes, this is a likely culprit.
Fix: Reduce sort_buffer_size and join_buffer_size in my.cnf under [mysqld]. The default values are often sufficient. For example, try:
```
sort_buffer_size = 2M
join_buffer_size = 1M
```
Restart MySQL.
Why it works: These buffers are allocated per thread that needs them. A large sort_buffer_size is used for operations like ORDER BY or GROUP BY that can’t use an index, and join_buffer_size is used for joins that can’t use an index. If you have hundreds of connections performing such operations simultaneously, the memory allocated for these buffers can multiply rapidly.

4. Large max_connections with low thread_cache_size:

Diagnosis: Check max_connections in my.cnf and the current number of active connections (SHOW GLOBAL STATUS LIKE 'Threads_connected';). If max_connections is very high (e.g., >500) and the server is under load, many threads might be created and destroyed frequently, or simply held open.
Fix: Adjust max_connections to a reasonable number based on your application’s needs and server resources. Also, increase thread_cache_size to allow MySQL to reuse threads. For example, if you typically have 100-200 active connections:
```
max_connections = 200
thread_cache_size = 20
```
Restart MySQL.
Why it works: Each connection consumes some memory. A very high max_connections setting, especially if not fully utilized, can be a misconfiguration. thread_cache_size helps by keeping a pool of ready-to-use threads, reducing the overhead of thread creation and destruction, which itself consumes memory and CPU.

5. Query Cache (if enabled and misconfigured):

Diagnosis: If you’re on an older MySQL version (prior to 8.0 where it was removed) and have query_cache_type enabled, it can consume memory. Check SHOW GLOBAL STATUS LIKE 'Qcache%';. High numbers for Qcache_queries_in_cache and Qcache_free_memory can indicate it’s being used, but also that it might be fragmented or too small.
Fix: For MySQL versions where it exists, it’s generally recommended to disable the query cache for most modern workloads. In my.cnf:
```
query_cache_type = 0
query_cache_size = 0
```
Restart MySQL.
Why it works: The query cache stores the results of SELECT statements. While intended to speed up identical queries, it has significant overhead for invalidation and memory management, especially with frequent writes. Disabling it removes this memory overhead and often improves performance on write-heavy or complex query workloads.

6. Other Per-Session Buffers:

Diagnosis: Similar to sort_buffer_size and join_buffer_size, buffers like read_buffer_size and read_rnd_buffer_size are allocated per thread. If these are set excessively high, memory usage can climb.
Fix: Reduce these values in my.cnf under [mysqld]. Defaults are usually fine.
```
read_buffer_size = 128K
read_rnd_buffer_size = 256K
```
Restart MySQL.
Why it works: These are used for sequential reads (read_buffer_size) and reading rows after sorting or a ORDER BY that doesn’t use an index (read_rnd_buffer_size). Like other per-session buffers, their memory impact is multiplied by the number of active connections.

After fixing these common issues, the next error you might encounter is related to open_files_limit if your increased buffer sizes and connection counts require more file descriptors, or a Too many connections error if max_connections is still too low for your application’s peak load.