Error
Error Code:
1041
MariaDB Error 1041: Out of Memory
Description
Error 1041 indicates that the MariaDB server process (`mysqld`) or the system itself has run out of available memory. This typically happens when the server attempts to allocate more memory than is currently available, either due to high demand, insufficient physical RAM, or operating system resource limits.
Error Message
Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
Known Causes
4 known causesExcessive MariaDB Memory Use
The MariaDB server process (`mysqld`) might be configured to use, or is actively consuming, a large amount of memory due to complex queries, large datasets, or unoptimized settings.
Other Processes Consuming RAM
Other applications or services running on the same server are consuming a significant portion of the available memory, leaving insufficient resources for MariaDB.
Insufficient System Resources
The server hosting MariaDB may simply not have enough physical RAM or swap space allocated to handle the combined memory requirements of all running processes.
Operating System Resource Limits
The operating system's `ulimit` settings or other kernel parameters might be restricting the maximum amount of memory a single process, like `mysqld`, can allocate.
Solutions
4 solutions available1. Reduce Memory Consumption of Running Queries medium
Identify and optimize or terminate memory-intensive queries currently running.
1
Connect to your MariaDB instance using a client like `mysql` or DBeaver.
mysql -u your_user -p2
Identify running queries and their memory usage. Look for queries with high `Rows_examined` or those that are taking a long time to complete. The `SHOW PROCESSLIST` command can be a starting point, but for more detailed memory analysis, you might need to examine system monitoring tools.
SHOW FULL PROCESSLIST;3
If a specific query is consuming excessive memory, consider optimizing it. This might involve adding indexes, rewriting the query, or breaking it down into smaller parts.
EXPLAIN SELECT ...;4
If a query is stuck or non-critical, you can terminate it to free up resources.
KILL <process_id>;5
Monitor system memory usage using tools like `top`, `htop`, or `free` to observe the impact of terminating queries.
top2. Adjust MariaDB's Memory Configuration advanced
Tune MariaDB's buffer pool and other memory-related settings to better suit available RAM.
1
Locate your MariaDB configuration file. This is typically `my.cnf` or `my.ini` and can be found in locations like `/etc/mysql/`, `/etc/my.cnf`, or `/etc/mysql/mariadb.conf.d/`.
2
Backup your configuration file before making any changes.
sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup3
Edit the configuration file using a text editor. Key parameters to adjust include `innodb_buffer_pool_size` (for InnoDB tables) and `key_buffer_size` (for MyISAM tables). A common recommendation is to set `innodb_buffer_pool_size` to 50-70% of your available RAM, but this depends heavily on your workload and other running services.
[mysqld]
innodb_buffer_pool_size = 2G # Example: adjust based on your RAM
key_buffer_size = 128M # Example: adjust based on your RAM4
Consider other memory-related parameters like `tmp_table_size`, `max_heap_table_size`, `sort_buffer_size`, and `join_buffer_size`. However, be cautious with these as increasing them too much can lead to other issues.
[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M5
Save the changes to the configuration file.
6
Restart the MariaDB service for the changes to take effect.
sudo systemctl restart mariadb7
Monitor system and MariaDB memory usage after the restart to ensure the changes have resolved the error.
top
free -m3. Increase System Limits with ulimit medium
Configure operating system limits to allow the `mysqld` process to allocate more memory.
1
Determine the current memory limits for the `mysqld` process. You can often find this by looking at the process owner and then checking `ulimit` for that user. Alternatively, you can try to identify the `mysqld` process ID (PID) and use `cat /proc/<PID>/limits`.
pgrep mysqld2
Edit the `/etc/security/limits.conf` file (or a file within `/etc/security/limits.d/`) to set the `as` (address space) limit for the user running `mysqld`. The value should be set to `unlimited` or a sufficiently large number (e.g., in kilobytes). Replace `mysql` with the actual user that `mysqld` runs as.
# /etc/security/limits.conf
mysql soft as unlimited
mysql hard as unlimited3
For systemd-based systems, you might need to configure limits in the `mysqld.service` file. Find the service file (e.g., `/usr/lib/systemd/system/mariadb.service` or `/etc/systemd/system/mariadb.service.d/override.conf`) and add or modify `LimitAS`.
[Service]
LimitAS=infinity4
Reload the systemd manager configuration if you modified a service file.
sudo systemctl daemon-reload5
Restart the MariaDB service.
sudo systemctl restart mariadb6
Verify the limits for the running `mysqld` process. You might need to restart the shell session or the `mysqld` process itself for the changes to fully apply.
cat /proc/<PID>/limits | grep 'Max address space'4. Increase Swap Space medium
Add or increase swap space on your server to provide a fallback for memory when RAM is exhausted.
1
Check existing swap space. It's generally recommended to have swap space equal to or double your RAM, depending on your workload and RAM size.
sudo swapon --show
free -m2
If you need to add swap space, create a swap file. Choose a size (e.g., 4GB).
sudo fallocate -l 4G /swapfile3
Set the correct permissions for the swap file.
sudo chmod 600 /swapfile4
Format the file as swap space.
sudo mkswap /swapfile5
Enable the swap file.
sudo swapon /swapfile6
Make the swap file permanent by adding it to `/etc/fstab`.
/swapfile none swap sw 0 07
Verify that the swap space has been added.
sudo swapon --show
free -m8
Restart MariaDB to see if the error is resolved. Note that relying heavily on swap can degrade performance.
sudo systemctl restart mariadb