Error 5 occurs when MySQL cannot allocate the requested memory block, either due to insufficient system RAM or misconfigured buffer settings. This critical error halts query execution and requires immediate memory tuning or hardware upgrade.
Error 5 (EE_OUTOFMEMORY) indicates that the MySQL server or client process cannot allocate the memory it needs to complete an operation. When MySQL requests a memory block from the operating system and the allocation fails, this error is raised. This is a critical error that can occur during query execution, sorting large result sets, processing complex joins, or handling large bulk data loads. Unlike some SQL errors that a handler can catch, EE_OUTOFMEMORY aborts execution immediately before error handlers can intervene. The error message typically includes the exact number of bytes the system tried to allocate: "Out of memory (Needed %u bytes)". The root cause is either that the server's physical RAM is exhausted, or MySQL has hit its configured memory limits because buffer pool and per-connection buffer settings are too large.
First, verify that the server actually has available RAM:
On Linux/macOS:
free -gh
# or for more detail:
cat /proc/meminfoCheck top processes consuming RAM:
top -b | head -20
# or
htopOn Windows:
Open Task Manager and check available RAM in the Performance tab.
If MySQL is not the culprit (resident memory < available), stop other services consuming RAM (backups, monitoring, web servers) or add physical RAM to the server.
The error log contains clues about which operation and how much memory failed:
# Linux/macOS - find error log
sudo tail -50 /var/log/mysql/error.log
# or
sudo tail -50 /var/log/mysqld.logWindows:
C:\ProgramData\MySQL\MySQL Server 8.0\Data\HOSTNAME.errLook for the exact "Needed %u bytes" message. If it shows a small number (< 1MB), the issue is likely configuration or fragmentation. If huge (> 100MB), you likely have insufficient RAM.
Also check OS logs for OOM Killer events:
dmesg | tail -20
grep -i oom /var/log/syslogCheck current memory-related settings:
SHOW VARIABLES WHERE variable_name IN (
'innodb_buffer_pool_size',
'key_buffer_size',
'sort_buffer_size',
'join_buffer_size',
'read_buffer_size',
'read_rnd_buffer_size',
'tmp_table_size',
'max_heap_table_size',
'max_connections'
);Calculate maximum possible memory usage:
Max Memory = innodb_buffer_pool_size
+ key_buffer_size
+ ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) × max_connections)This total should not exceed 60% of available system RAM. If it does, you've found your problem.
Lower the buffers that multiply by max_connections:
Edit /etc/mysql/my.cnf or /etc/my.cnf:
[mysqld]
# Reduce per-connection buffers to reasonable defaults
sort_buffer_size=256K
join_buffer_size=256K
read_buffer_size=256K
read_rnd_buffer_size=512K
# Cap maximum connections based on available memory
max_connections=100For sessions needing larger buffers, increase them temporarily:
SET SESSION sort_buffer_size = 1M;
SET SESSION join_buffer_size = 1M;
-- Run your large ORDER BY query here
SET SESSION sort_buffer_size = DEFAULT;Restart MySQL:
sudo systemctl restart mysqlThe buffer pool is usually the largest consumer. For a dedicated server:
# On a 16GB server with 75% allocated to MySQL:
# innodb_buffer_pool_size = 12G (16GB × 0.75)Edit my.cnf:
[mysqld]
# For 8GB server: 6GB
innodb_buffer_pool_size=6G
# For 4GB server: 2.5G
# innodb_buffer_pool_size=2500M
# For 2GB server: 1G
# innodb_buffer_pool_size=1GRules of thumb:
- Dedicated MySQL server: 50-70% of RAM
- Shared server: 25-50% of RAM
- Development/test: 10-25% of RAM
Restart MySQL and monitor memory usage:
# Monitor MySQL process memory in real-time
watch -n 2 'ps aux | grep mysqld | grep -v grep'Large temporary tables in memory consume significant RAM:
SHOW VARIABLES WHERE variable_name IN ('tmp_table_size', 'max_heap_table_size');If these are set to large values (> 100M) and you have many connections, reduce them:
[mysqld]
# Limit in-memory temp tables to 16MB
tmp_table_size=16M
max_heap_table_size=16MLarger temporary tables automatically spill to disk (using InnoDB), which is fine for performance.
For stored procedures creating large temporary tables:
-- Use InnoDB for temp tables, which can spill to disk
CREATE TEMPORARY TABLE my_temp (
id INT,
data VARCHAR(1000)
) ENGINE=InnoDB;Memory-heavy operations like large ORDER BY can be optimized:
Find resource-intensive queries:
-- Enable slow query log to identify memory-heavy queries
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log = ON;
-- Check which queries need large sort buffers
SHOW FULL PROCESSLIST;Optimization strategies:
1. Add indexes on ORDER BY / GROUP BY columns to avoid sorting
2. Limit result sets with LIMIT before sorting
3. Break large queries into smaller ones
Example:
-- BAD: Sorts entire table before limiting
SELECT * FROM large_table ORDER BY created_at DESC LIMIT 10;
-- GOOD: Limit first, sort on indexed column
SELECT * FROM large_table ORDER BY id DESC LIMIT 10;32-bit MySQL is limited to a 4GB address space, which fills quickly:
# Check if your MySQL is 32-bit or 64-bit
file /usr/sbin/mysqld
# Output: x86-64 (64-bit) or Intel 80386 (32-bit)
# Or via SQL
SELECT @@version_compile_os;If 32-bit, upgrade to 64-bit:
# Ubuntu/Debian
sudo apt-get install mysql-server-8.0-64bit
# RHEL/CentOS
sudo yum install mysql-community-server-64bit64-bit MySQL can address terabytes of RAM, eliminating the memory ceiling issue.
Memory Calculation for Production:
A conservative formula for sizing MySQL:
- innodb_buffer_pool_size: 50% of available RAM
- OS and other processes: 30% of RAM
- MySQL per-connection buffers: 20% of RAM
On a 32GB server: innodb_buffer_pool_size = 16GB, max_connections = 150 with 256K per-connection buffers.
Performance Schema Memory Instrumentation:
MySQL 5.7+ supports memory tracking via Performance Schema:
SELECT * FROM sys.memory_global_by_current_bytes
WHERE event_name NOT LIKE '%performance_schema%'
ORDER BY current_size_mb DESC;This identifies which internal structures consume the most memory.
Swap as a Stopgap:
Adding swap on the server prevents OOM-killer crashes but severely slows MySQL (disk I/O is 1000x slower than RAM). Use swap only as a temporary measure while you reduce buffers or add hardware.
Memory Leaks:
Rarely, MySQL itself has memory leaks. Monitor over 24+ hours:
# Check MySQL process memory growth
ps aux | grep mysqld | grep -v grep | awk '{print $6}'If memory grows steadily without connections increasing, update MySQL to the latest patch version or file a bug with MySQL support.
Cloud Deployment Considerations:
On cloud providers (AWS RDS, Azure Database for MySQL), memory is often the first constraint. Right-size instances carefully and use their monitoring dashboards to find slow queries causing memory spikes.
Windows-Specific:
On Windows, use Task Manager or ProcessExplorer to monitor mysqld.exe memory. The "Working Set" column shows physical RAM; if it's consistently at max, reduce innodb_buffer_pool_size or max_connections.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL