Error
Error Code:
3950
MySQL Error 3950: Out of Memory
Description
Error 3950 indicates that the MySQL server or a specific operation has run out of available RAM to complete a task. This typically occurs when the database process attempts to allocate more memory than the operating system or configured limits allow.
Error Message
Out of memory
Known Causes
4 known causesInsufficient Server RAM
The physical server hosting MySQL does not have enough total RAM to support the database and other running processes.
Over-allocated MySQL Buffers
MySQL configuration parameters, such as `innodb_buffer_pool_size` or `key_buffer_size`, are set too high, collectively exceeding the server's available memory.
Large Temporary Table Usage
Complex queries involving large sorts, joins, or aggregations require significant memory for temporary tables, exceeding `tmp_table_size` or `max_heap_table_size` limits.
Excessive Concurrent Connections
A high number of simultaneous client connections, each requiring memory, can collectively exhaust the server's available RAM.
Solutions
4 solutions available1. Reduce Buffer Pool Size easy
Temporarily decrease the InnoDB buffer pool size to free up memory.
1
Connect to your MySQL server using a client like `mysql` or MySQL Workbench.
2
Check the current `innodb_buffer_pool_size`.
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';3
If the current value is very high (e.g., more than 50-70% of available RAM), reduce it. For example, to set it to 2GB (2147483648 bytes):
SET GLOBAL innodb_buffer_pool_size = 2147483648;4
To make this change permanent, edit your MySQL configuration file (e.g., `my.cnf` or `my.ini`) and update the `innodb_buffer_pool_size` parameter. Restart the MySQL server for the permanent change to take effect.
[mysqld]
innodb_buffer_pool_size = 2G2. Increase System RAM or Swap medium
Add more physical RAM to the server or configure swap space.
1
If possible, physically install more RAM into the server hosting your MySQL instance. This is the most effective long-term solution.
2
If adding RAM isn't an immediate option, configure or increase swap space. This allows the operating system to move less-used memory pages to disk, freeing up RAM for MySQL.
sudo fallocate -l 4G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile
3
To make swap permanent, add an entry to `/etc/fstab`.
/swapfile none swap sw 0 04
After making changes, restart the MySQL service.
sudo systemctl restart mysql3. Optimize Query Performance advanced
Identify and optimize slow or memory-intensive queries.
1
Enable the MySQL slow query log to identify queries that are taking too long or consuming excessive resources.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Log queries taking longer than 1 second
SET GLOBAL log_output = 'FILE';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';2
Analyze the slow query log using tools like `mysqldumpslow` or Percona Toolkit's `pt-query-digest` to pinpoint problematic queries.
pt-query-digest /var/log/mysql/mysql-slow.log > /tmp/slow_query_analysis.txt3
For identified slow queries, ensure appropriate indexes are present. Use `EXPLAIN` to analyze query execution plans.
EXPLAIN SELECT * FROM your_table WHERE some_column = 'value';4
Rewrite inefficient queries, avoid `SELECT *`, use `LIMIT` when appropriate, and break down complex queries into smaller, more manageable ones.
4. Adjust Other Memory-Related Variables medium
Tune other MySQL variables that can impact memory usage.
1
Investigate and potentially reduce the size of other memory buffers. Some common ones include:
SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%cache%';2
Consider reducing `sort_buffer_size` and `join_buffer_size` if you have many queries involving sorting or complex joins.
SET GLOBAL sort_buffer_size = 2097152; -- Example: 2MB
SET GLOBAL join_buffer_size = 2097152; -- Example: 2MB3
Similarly, `read_buffer_size` and `read_rnd_buffer_size` can be adjusted. Be cautious as reducing these too much can impact performance.
SET GLOBAL read_buffer_size = 131072; -- Example: 128KB
SET GLOBAL read_rnd_buffer_size = 262144; -- Example: 256KB4
Remember to make permanent changes by editing your MySQL configuration file and restarting the server.
[mysqld]
sort_buffer_size = 2M
join_buffer_size = 2M