How to Fix "max memory exceeded" in MySQL

Why does MySQL show “max memory exceeded”?

MySQL combines large global buffers with per-connection buffers. When many sessions open, per-thread allocations multiply. If the sum of global buffers plus (Max_used_connections × per-thread buffers) exceeds physical RAM, the server logs “max memory exceeded” and may crash.

How do I estimate peak memory quickly?

Run:

SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW VARIABLES WHERE Variable_name IN (
'innodb_buffer_pool_size','key_buffer_size',
'sort_buffer_size','join_buffer_size',
'read_buffer_size','tmp_table_size');

Add global buffers, then multiply each per-thread buffer by Max_used_connections. Compare with free -m or /proc/meminfo.

Which server variables control memory?

Global buffers

innodb_buffer_pool_size
key_buffer_size
query_cache_size -- removed in 8.0 but still seen in my.cnf

Per-connection buffers

sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size
tmp_table_size / max_heap_table_size

How do I change these variables safely?

Edit my.cnf and restart, or issue at runtime:

SET GLOBAL innodb_buffer_pool_size = 6G;
SET GLOBAL sort_buffer_size = 2M;
SET GLOBAL tmp_table_size = 256M;
SET GLOBAL max_connections = 200;

Reduce per-thread buffers first—small changes free huge amounts when many sessions connect. Increase innodb_buffer_pool_size slowly while watching swap and Innodb_buffer_pool_reads.

How do I locate memory-hungry queries?

Enable the Performance Schema or slow_query_log with log_slow_extra = ON. Review performance_schema.events_statements_summary_by_digest for statements with high Sort_merge_passes, Created_tmp_disk_tables, or big current_allocated.

Example: ecommerce query that blew RAM

SELECT c.id, c.name,
SUM(o.total_amount) AS lifetime_value
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY lifetime_value DESC;

On 50 M Orders, MySQL built a 1 GB temp table in memory and failed. Fixes: add INDEX Orders(customer_id), lower tmp_table_size to 256 MB so oversized temps spill to disk, and consider ORDER BY NULL when ordering is unnecessary.

Best practices to avoid “max memory exceeded”

• Cap max_connections; rely on connection pooling.
• Keep per-thread buffers ≤2 MB.
• Size innodb_buffer_pool_size to 60-70 % of RAM, never 100 %.
• Monitor MemAvailable and Max_used_connections daily.
• Run mysqltuner or pt-variable-advisor monthly.

Common mistakes

Setting huge tmp_table_size

Developers increase temp-table memory hoping for speed. Under concurrency, each session allocates the full size, exhausting RAM. Keep it under 256 MB and optimize queries instead.

Ignoring Max_used_connections

Some teams tune memory for average load. A sudden traffic spike re-creates the high-connection scenario that first caused the error. Always plan for the recorded peak.

See also

MySQL manual: Memory Use