📋

Description

This error indicates that a MySQL storage engine, such as InnoDB or MyISAM, has run out of available memory to complete an operation. It typically occurs when executing complex queries, handling large datasets, or during high concurrency, preventing the requested action from completing.

💬

Error Message

Out of memory in storage engine '%s'.

🔍

Known Causes

3 known causes

⚠️

Insufficient Server RAM

The physical server hosting MySQL does not have enough total memory (RAM) to satisfy the combined demands of the database server and other running applications.

⚠️

MySQL Configuration Limits

Key MySQL memory-related parameters (e.g., `innodb_buffer_pool_size`, `tmp_table_size`, `key_buffer_size`) are set too low for the current workload, limiting the memory available to storage engines.

⚠️

Complex Queries or Large Data

Operations involving very large result sets, complex joins, sorting, or the creation of large temporary tables can consume significant memory, exceeding available limits.

🛠️

Solutions

3 solutions available

1. Increase InnoDB Buffer Pool Size medium

Allocate more RAM to InnoDB for caching data and indexes.

1

Determine the current `innodb_buffer_pool_size`. You can do this by querying MySQL or checking your configuration file.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

2

Calculate a new, larger value for `innodb_buffer_pool_size`. A common recommendation is 50-75% of available RAM on a dedicated database server, but avoid exceeding total system RAM.

SET GLOBAL innodb_buffer_pool_size = 4294967296; -- Example: 4GB

3

To make this change permanent, edit your MySQL configuration file (e.g., `my.cnf` or `my.ini`). Find the `[mysqld]` section and update or add the `innodb_buffer_pool_size` directive.

[mysqld]
innodb_buffer_pool_size = 4G

4

Restart the MySQL server for the changes to take effect.

sudo systemctl restart mysql

2. Optimize Query Performance and Reduce Memory Usage advanced

Tune inefficient queries that are consuming excessive memory.

1

Identify slow or memory-intensive queries. Use the MySQL slow query log and `SHOW PROCESSLIST` to find problematic queries.

SHOW FULL PROCESSLIST;

2

Analyze query execution plans using `EXPLAIN` to understand how queries are being processed and identify areas for optimization.

EXPLAIN SELECT * FROM your_table WHERE some_column = 'value';

3

Add appropriate indexes to tables for columns used in `WHERE` clauses, `JOIN` conditions, and `ORDER BY` clauses.

CREATE INDEX idx_column_name ON your_table (column_name);

4

Rewrite queries to be more efficient. Avoid `SELECT *` if not necessary, use `LIMIT` where applicable, and consider breaking down complex queries into smaller ones.

SELECT column1, column2 FROM your_table WHERE some_column = 'value' LIMIT 100;

5

Consider reducing the size of large tables through archiving or deleting old, unnecessary data.

DELETE FROM your_table WHERE date_column < '2023-01-01';

3. Adjust Other Relevant MySQL Memory Parameters advanced

Fine-tune other buffer and cache settings that contribute to memory usage.

1

Review and potentially adjust `tmp_table_size` and `max_heap_table_size`. These control the maximum size of in-memory temporary tables.

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

2

If you have many complex sorts or joins that create large temporary tables in memory, increasing these values might help, but be cautious as they can consume significant RAM.

SET GLOBAL tmp_table_size = 268435456; -- Example: 256MB
SET GLOBAL max_heap_table_size = 268435456; -- Example: 256MB

3

Add these settings to your MySQL configuration file (`my.cnf` or `my.ini`) for persistence.

[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M

4

Consider `join_buffer_size` and `sort_buffer_size`. These are per-connection buffers, so increasing them too much can lead to overall memory exhaustion if you have many connections.

SHOW VARIABLES LIKE 'join_buffer_size';
SHOW VARIABLES LIKE 'sort_buffer_size';

5

Restart the MySQL server after making configuration changes.

sudo systemctl restart mysql

🔗

Related Errors

5 related errors

MySQL Error 3015: Storage Engine Memory Exhaustion