Error
Error Code:
34
MySQL Error 34: Memory Capacity Exceeded
Description
This error indicates that MySQL has attempted to allocate memory beyond a configured limit or available system resources. It typically occurs when the database server is under heavy load, processing large queries, or when its memory configuration is insufficient for the current workload.
Error Message
Memory capacity exceeded (capacity %llu bytes)
Known Causes
4 known causesInsufficient MySQL Memory Limits
MySQL has various configuration parameters (e.g., `innodb_buffer_pool_size`, `tmp_table_size`) that limit the memory it can use. If these are set too low for the current workload, this error can occur.
Operating System Memory Exhaustion
The server's operating system may have run out of available RAM or swap space, preventing MySQL from allocating more memory, even if its internal limits permit it.
Excessive Query Memory Usage
Executing very large or complex SQL queries, such as those involving huge sorts, temporary tables, or joins on massive datasets, can demand significant transient memory, leading to this error.
High Number of Active Connections
Each active client connection consumes some memory. A sudden surge or consistently high number of concurrent connections can cumulatively exceed available memory.
Solutions
3 solutions available1. Increase MySQL's `max_allowed_packet` easy
Adjust the maximum packet size MySQL can handle to accommodate larger queries or data transfers.
1
Connect to your MySQL server as a user with administrative privileges.
mysql -u root -p2
Check the current value of `max_allowed_packet`.
SHOW VARIABLES LIKE 'max_allowed_packet';3
Increase `max_allowed_packet` to a larger value. A common starting point for larger packets is 64MB or 128MB. You can set it globally or for the current session.
SET GLOBAL max_allowed_packet = 67108864; -- 64MB
-- Or for the current session:
-- SET max_allowed_packet = 67108864;4
To make this change permanent, edit your MySQL configuration file (`my.cnf` or `my.ini`). Locate the `[mysqld]` section and add or modify the `max_allowed_packet` line. Restart the MySQL server for the changes to take effect.
[mysqld]
max_allowed_packet = 64M5
After restarting, verify the new value.
SHOW VARIABLES LIKE 'max_allowed_packet';2. Optimize Large Queries and Data Operations medium
Reduce memory footprint by optimizing queries and breaking down large data operations into smaller chunks.
1
Analyze your queries. Identify any excessively large `SELECT` statements, especially those involving `JOIN`s on many tables, large `WHERE` clauses, or complex subqueries. Use `EXPLAIN` to understand query execution plans.
EXPLAIN SELECT ... FROM ... WHERE ...;2
Break down large `INSERT` or `UPDATE` statements into smaller batches. Instead of inserting thousands of rows in one go, insert them in chunks of hundreds or tens.
INSERT INTO your_table (col1, col2) VALUES (val1a, val2a), (val1b, val2b), ...;
-- Instead of:
-- INSERT INTO your_table (col1, col2) VALUES (val1a, val2a), (val1b, val2b), ..., (val1z, val2z);3
Consider processing large data dumps or migrations in stages. Load data into a staging table first, then process and insert it into the final table in smaller, manageable transactions.
LOAD DATA INFILE 'large_file.csv' INTO TABLE staging_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
-- Then process and insert in batches:
INSERT INTO final_table (col1, col2) SELECT col1, col2 FROM staging_table LIMIT 1000;
-- Repeat until staging_table is empty.4
Review application code that interacts with the database. Ensure that it's not fetching excessively large result sets or performing operations that would require significant memory on the database server.
No direct code snippet, but review application logic for fetching data, e.g., `SELECT *` without limits.3. Adjust MySQL Server Memory Configuration advanced
Tune key MySQL server variables that control memory allocation to prevent exceeding system limits.
1
Identify memory-intensive MySQL configuration variables. Key variables include: `innodb_buffer_pool_size`, `key_buffer_size` (for MyISAM), `sort_buffer_size`, `join_buffer_size`, `read_buffer_size`, `read_rnd_buffer_size`, `tmp_table_size`, and `max_heap_table_size`.
SHOW VARIABLES LIKE '%buffer_size%';
SHOW VARIABLES LIKE '%pool_size%';
SHOW VARIABLES LIKE '%tmp_table_size%';
SHOW VARIABLES LIKE '%heap_table_size%';2
Review your server's total RAM. Do not allocate more memory to MySQL than your system can afford. Use tools like `free -h` (Linux) or Task Manager (Windows) to check available RAM.
free -h3
Gradually decrease values for per-session buffers like `sort_buffer_size`, `join_buffer_size`, `read_buffer_size`, and `read_rnd_buffer_size`. These are allocated per connection/thread, and high values can quickly exhaust memory.
SET GLOBAL sort_buffer_size = 256 * 1024; -- Example: Reduce to 256KB
SET GLOBAL join_buffer_size = 256 * 1024; -- Example: Reduce to 256KB4
Ensure `innodb_buffer_pool_size` is appropriately set. For InnoDB, this is the most crucial buffer. It should typically be set to 50-70% of available RAM on a dedicated database server.
SET GLOBAL innodb_buffer_pool_size = 4G; -- Example: 4GB5
Adjust `tmp_table_size` and `max_heap_table_size`. If you have complex queries that create large temporary tables, increasing these might help, but monitor memory usage closely. They are often set to the same value.
SET GLOBAL tmp_table_size = 128 * 1024 * 1024; -- Example: 128MB
SET GLOBAL max_heap_table_size = 128 * 1024 * 1024; -- Example: 128MB6
For permanent changes, edit your MySQL configuration file (`my.cnf` or `my.ini`) and restart the MySQL server.
[mysqld]
innodb_buffer_pool_size = 4G
sort_buffer_size = 256K
join_buffer_size = 256K
tmp_table_size = 128M
max_heap_table_size = 128M