How to Fix MySQL Out of Memory Error

In this tutorial, you'll learn about How to Fix MySQL Out of Memory Error. We cover key concepts, practical examples, and best practices.

The Problem

MySQL crashes or returns:

Out of memory (Needed 16777224 bytes)

Or in the MySQL error log:

[ERROR] InnoDB: Out of memory
[ERROR] mysqld: Out of memory (Needed 16777224 bytes)

MySQL exhausted available memory due to large queries, a misconfigured buffer pool, or system memory limits.

Quick Fix

1. Reduce the InnoDB buffer pool size

The buffer pool is the largest memory consumer. Set it to 60-70% of available RAM:

# /etc/mysql/mysql.conf.d/mysqld.cnf

# Wrong — set too high for a 2GB server
innodb_buffer_pool_size = 2G

# Right — 60% of 2GB
innodb_buffer_pool_size = 1.2G

Check current size:

SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb;

2. Reduce per-connection memory buffers

Each connection uses memory for sort, join, and tmp table buffers:

# /etc/mysql/mysql.conf.d/mysqld.cnf
sort_buffer_size = 2M
read_buffer_size = 128K
read_rnd_buffer_size = 256K
join_buffer_size = 128K
tmp_table_size = 32M
max_heap_table_size = 32M

High values for these settings multiply by the number of concurrent connections.

3. Reduce max_connections

Too many connections each consuming memory can exhaust RAM:

-- Check current connections
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

-- Reduce if set too high
SET GLOBAL max_connections = 100;

# my.cnf
max_connections = 100

4. Optimize large queries

Queries that create temporary tables or sort large result sets consume extra memory:

-- Wrong — unindexed sort on large table
SELECT * FROM logs ORDER BY created_at DESC;

-- Right — add an index on created_at
CREATE INDEX idx_logs_created_at ON logs(created_at);

-- Or limit the result
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

Check for queries using filesort or temporary tables:

EXPLAIN SELECT * FROM logs ORDER BY created_at DESC;
-- Look for "Using filesort" or "Using temporary"

5. Check system memory and swap

free -h

If swap is full or the system is using a lot of memory, consider adding RAM or moving to a larger instance.

Prevention

• Set innodb_buffer_pool_size to 60-70% of available RAM.
• Monitor memory with SHOW ENGINE INNODB STATUS and system tools.
• Keep tmp_table_size and max_heap_table_size reasonable.
• Set up alerts for MySQL OOM events in your monitoring system.

Common Mistakes with out of memory

1. Overlapping type class instances that cause GHC to reject the program with ambiguous dispatch errors
2. Non-exhaustive pattern matches that compile with warnings then crash at runtime
3. Misunderstanding that String is [Char] with poor performance for large text operations

These mistakes appear frequently in real-world MYSQL code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ