Error
Error Code:
1037
MySQL Error 1037: Server Out of Memory
Description
This error indicates that the MySQL server process attempted to allocate memory but failed because the system or MySQL's configured limits were exhausted. It typically occurs under heavy load, due to large operations, or when resource settings are too low for the current demand.
Error Message
Out of memory; restart server and try again (needed %d bytes)
Known Causes
4 known causesInsufficient System RAM
The server hosting MySQL does not have enough physical or virtual memory to satisfy MySQL's allocation requests.
MySQL Configuration Limits
MySQL's internal memory settings, such as `innodb_buffer_pool_size` or `tmp_table_size`, are set too high for the available system resources or are being exceeded by operations.
Excessive Workload or Connections
A high number of concurrent connections, complex queries, or large data operations are consuming more memory than the server can provide.
Memory Leak or Bug
Less commonly, a memory leak within the MySQL server or an underlying library can lead to gradual memory exhaustion.
Solutions
4 solutions available1. Restart the MySQL Server easy
A simple restart can free up temporary memory leaks.
1
Connect to your server via SSH or a terminal.
2
Identify the MySQL service name. This can vary depending on your OS and installation method.
sudo systemctl status mysql3
Restart the MySQL service.
sudo systemctl restart mysql4
If the above command doesn't work, try the service name 'mysqld'.
sudo systemctl restart mysqld5
On older systems or different installations, you might use 'service'.
sudo service mysql restart2. Review and Adjust MySQL Memory Configuration medium
Optimize key memory-related variables in your MySQL configuration file.
1
Locate your MySQL configuration file. Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or `/etc/mysql/mysql.conf.d/mysqld.cnf`.
2
Open the configuration file with a text editor (e.g., `nano`, `vim`).
sudo nano /etc/my.cnf3
Look for and adjust the following key variables. Reduce their values if they are set too high, especially if your server has limited RAM.
[mysqld]
# ... other settings ...
innodb_buffer_pool_size = 256M # Example: Reduce from a larger value
key_buffer_size = 16M # Example: Reduce if not using MyISAM heavily
max_connections = 100 # Example: Reduce if too many connections are active
query_cache_size = 0 # Often disabled in modern MySQL versions
4
Save the changes to the configuration file.
5
Restart the MySQL server for the changes to take effect (refer to Solution 1).
3. Increase Server RAM or Swap Space advanced
If the server consistently runs out of memory, it might be undersized for the workload.
1
Determine the current RAM usage of your server. Use tools like `htop`, `top`, or `free -m`.
free -m2
If RAM is consistently near 100%, consider adding more physical RAM to the server. This is the most effective long-term solution.
3
Alternatively, if adding physical RAM is not immediately feasible, increase the swap space. This acts as virtual RAM, though it's slower.
sudo fallocate -l 2G /swapfile # Create a 2GB swap file
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile
4
To make the swap permanent, add an entry to `/etc/fstab`.
/swapfile none swap sw 0 05
Monitor MySQL and server performance after these changes.
4. Identify and Optimize Memory-Hungry Queries advanced
Long-running or inefficient queries can consume excessive memory.
1
Enable the slow query log in your MySQL configuration file (`my.cnf`). Set `slow_query_log = 1` and `long_query_time` to a reasonable value (e.g., 1 or 2 seconds).
[mysqld]
# ... other settings ...
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
2
Restart the MySQL server to apply the slow query log settings.
3
After some time, analyze the slow query log file using tools like `mysqldumpslow` or by manual inspection.
mysqldumpslow /var/log/mysql/mysql-slow.log4
Identify queries that are frequently appearing or taking a long time. Use `EXPLAIN` to understand their execution plan and optimize them by adding appropriate indexes, rewriting the query, or breaking down complex operations.
EXPLAIN SELECT * FROM your_table WHERE column = 'value';
5
Consider using tools like `pt-query-digest` from Percona Toolkit for more advanced slow query log analysis.