How to Fix "max memory exceeded" in MariaDB

How do I fix the “max memory exceeded” error in MariaDB?

The error occurs when a query or connection tries to allocate more memory than MariaDB's configured limits allow.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why does MariaDB show “max memory exceeded”?

MariaDB calculates a worst-case memory footprint for each connection. If the product of join_buffer_size, sort_buffer_size, read_buffer_size, and other per-thread buffers exceeds max_connections * key_buffer_size, the server aborts the query and raises this error.

How can I inspect current memory settings?

Run SHOW VARIABLES LIKE '%_buffer_size' and SHOW GLOBAL STATUS LIKE 'Max_used_connections'. Compare the values against total RAM.This highlights which buffers are oversized for your workload.

What system variables control per-query memory?

Key variables are join_buffer_size, sort_buffer_size, read_buffer_size, read_rnd_buffer_size, tmp_table_size, and max_heap_table_size. Lowering them or using session-level overrides prevents runaway memory allocation.

Can I fix the error without restarting MariaDB?

Yes. Issue SET SESSION join_buffer_size = 2M (or similar) before heavy queries.Session changes apply immediately and do not affect other users.

How do I set safe global defaults?

Edit my.cnf under the [mysqld] section:

join_buffer_size = 2M
sort_buffer_size = 2M
tmp_table_size = 256M
max_heap_table_size = 256MRestart MariaDB to apply. Size each buffer so that max_connections × (buffer_sum) is less than 70-80 % of RAM.

Which query patterns are most affected?

Large ORDER BY, multi-table JOINs, and implicit in-memory temporary tables cause spikes.Rewrite queries to add selective WHERE clauses, proper indexes, or break them into smaller steps.

Example: lowering memory for a complex join

Before running an analytics join between Orders, OrderItems, and Products, set buffers for the session:

SET SESSION join_buffer_size = 1M;
SET SESSION sort_buffer_size = 1M;
SELECT c.name, SUM(oi.quantity) AS items
FROM Customers c
JOIN Orders o ON c.id = o.customer_id
JOIN OrderItems oi ON o.id = oi.order_id
GROUP BY c.name;
The query finishes without triggering the memory error.

Best practices to avoid future errors

Monitor Max_used_memory regularly, cap max_connections realistically, and always size buffers conservatively.Use EXPLAIN to ensure queries leverage indexes and avoid full scans.

Common mistakes

Over-allocating buffers globally: Setting join_buffer_size=64M looks harmless but multiplies by every thread. Keep defaults low and tune per session.

Ignoring temporary table limits: If tmp_table_size is smaller than needed, MariaDB writes to disk instead of failing. Balance performance and safety; set it high enough yet within RAM limits.

Need more help?

Enable the Performance Schema’s memory_summary_by_thread_by_event_name table to pinpoint which allocations spike during problem periods.

Why How to Fix "max memory exceeded" in MariaDB is important

How to Fix "max memory exceeded" in MariaDB Example Usage


-- Identify customers with >$1,000 in orders without exceeding buffers
SET SESSION join_buffer_size = 1M;
SET SESSION sort_buffer_size = 1M;
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
HAVING SUM(o.total_amount) > 1000
ORDER BY lifetime_value DESC;

How to Fix "max memory exceeded" in MariaDB Syntax


-- Session override for a single analytics query
SET SESSION join_buffer_size = 2M;
SET SESSION sort_buffer_size = 2M;

-- Global defaults in my.cnf
[mysqld]
join_buffer_size     = 2M
sort_buffer_size     = 2M
read_buffer_size     = 128K
read_rnd_buffer_size = 256K
tmp_table_size       = 256M
max_heap_table_size  = 256M
max_connections      = 200

-- Verify settings
SHOW VARIABLES LIKE '%buffer_size%';
SHOW GLOBAL STATUS LIKE 'Max_used_memory%';

Common Mistakes

Allocating huge global buffers: Setting join_buffer_size or sort_buffer_size to dozens of MB multiplies memory by every active thread, exhausting RAM quickly. Fix by lowering global values and using session-level overrides for rare heavy jobs.
Leaving tmp_table_size too low: An undersized tmp_table_size forces disk writes and slows queries. Users then raise buffer sizes indiscriminately, causing memory errors. Instead, raise tmp_table_size and max_heap_table_size moderately and tune per-query buffers down.