📋

Description

MySQL Error 1041 indicates that the MySQL server or the underlying operating system has run out of available memory. This critical error prevents the database from performing requested operations, often due to high resource demand or insufficient system resources.

💬

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 causes

⚠️

Insufficient System Memory

The server hosting MySQL lacks sufficient physical RAM, often due to other demanding applications or services consuming available memory.

⚠️

MySQL Resource Misconfiguration

MySQL's buffer and cache settings (e.g., `innodb_buffer_pool_size`) are configured to request more memory than the system can provide.

⚠️

Excessive Query Demands

A high volume of concurrent connections, complex queries, or memory-intensive operations (like large sorts or joins) are exhausting memory resources.

⚠️

Operating System Resource Limits

OS-level limits, such as `ulimit` settings, prevent the MySQL process from accessing available memory, even if physical RAM exists.

🛠️

Solutions

4 solutions available

1. Reduce MySQL Memory Usage with Configuration Tuning medium

Adjust MySQL configuration parameters to lower its memory footprint.

1

Identify your MySQL configuration file. This is typically `my.cnf` or `my.ini` located in `/etc/mysql/`, `/etc/`, or the MySQL data directory.

2

Open the configuration file with a text editor (e.g., `nano`, `vim`).

sudo nano /etc/mysql/my.cnf

3

Locate and adjust the following key parameters. Start with small reductions if unsure.

[mysqld]
# Reduce buffer pool size (adjust based on available RAM, typically 50-70% of total RAM)
innodb_buffer_pool_size = 256M

# Reduce query cache size (often better to disable on modern MySQL versions)
query_cache_size = 0
query_cache_type = 0

# Reduce sort buffer size (per connection)
sort_buffer_size = 1M

# Reduce join buffer size (per connection)
join_buffer_size = 1M

# Reduce read buffer size (per thread)
read_buffer_size = 1M

# Reduce readrnd buffer size (per thread)
read_rnd_buffer_size = 1M

# Reduce thread cache size (if you have many connections, but not excessively large)
thread_cache_size = 16

4

Save the changes and restart the MySQL service for the new settings to take effect.

sudo systemctl restart mysql

2. Increase System Memory Limits with ulimit medium

Allow the `mysqld` process to allocate more memory by adjusting system resource limits.

1

Check the current memory limits for the `mysqld` process. You can often find the process ID (PID) using `ps aux | grep mysqld`.

ps aux | grep mysqld

2

View the current limits for the `mysqld` process using its PID. The relevant limit is often `max locked memory` (often aliased as `mlock` or `memlock`).

cat /proc/<PID>/limits | grep 'max locked memory'

3

Edit the `/etc/security/limits.conf` file to set higher limits. Add or modify these lines (replace `mysql` with the user MySQL runs as if different, and `unlimited` or a specific value like `4G` for the limit):

mysql  soft  memlock  unlimited
mysql  hard  memlock  unlimited

4

For systemd-based systems, you might also need to configure the systemd service unit for MySQL. Create or edit a drop-in file for the MySQL service (e.g., `/etc/systemd/system/mysql.service.d/override.conf`).

[Service]
LimitMEMLOCK=infinity

5

Reload the systemd daemon and restart the MySQL service.

sudo systemctl daemon-reload
sudo systemctl restart mysql

3. Add Swap Space to the System easy

Create or increase swap space to provide virtual memory when physical RAM is exhausted.

1

Check if swap is already configured and its size.

sudo swapon --show
free -h

2

Create a swap file. The size (e.g., `4G`) should be at least equal to your RAM, or more depending on your workload.

sudo fallocate -l 4G /swapfile
sudo chmod 600 /swapfile

3

Format the file as swap space.

sudo mkswap /swapfile

4

Enable the swap file.

sudo swapon /swapfile

5

Make the swap file permanent by adding it to `/etc/fstab`.

/swapfile none swap sw 0 0

6

Restart MySQL (or the entire server if the issue is widespread).

sudo systemctl restart mysql

4. Identify and Optimize Memory-Hungry Queries advanced

Find and fix SQL queries that are consuming excessive memory.

1

Enable the MySQL slow query log to capture queries that take a long time to execute, as these often consume significant memory.

[mysqld]
log_slow_admin_statements = 1
log_queries_not_using_indexes = 1
long_query_time = 1
log_slow_queries = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

2

Restart MySQL to apply the logging configuration.

sudo systemctl restart mysql

3

Analyze the slow query log using tools like `mysqldumpslow` or `pt-query-digest` to identify the most problematic queries.

sudo mysqldumpslow /var/log/mysql/mysql-slow.log

4

For identified slow queries, use `EXPLAIN` to understand their execution plan and identify areas for optimization (e.g., missing indexes, inefficient joins).

EXPLAIN SELECT ... FROM ... WHERE ...;

5

Add appropriate indexes to tables involved in slow queries, rewrite queries for better efficiency, or consider denormalization if necessary.

CREATE INDEX index_name ON table_name (column_name);

🔗

Related Errors

5 related errors

MySQL Error 1041: Out of Memory