MariaDB Memory Allocation
Plan MariaDB memory usage by sizing global caches, per-connection buffers, and engine-specific settings to avoid swapping and out-of-memory conditions.
Quick Recommendations
Size MariaDB against available RAM, not total installed RAM.
On a dedicated host, available RAM means total RAM minus operating system needs and other running services. In containers or VMs, use the effective memory limit and leave headroom for the OS, filesystem cache, and temporary per-connection allocations.
Start with these defaults, depending on your choice of storage engines:
Mostly InnoDB: Set innodb_buffer_pool_size to about 60% to 70% of available RAM. Keep key_buffer_size small, such as
16Mto64M, unless you use MyISAM indexes.Mostly MyISAM: Set key_buffer_size to about 20% of available RAM. Set innodb_buffer_pool_size to
0only if you do not use InnoDB at all.Mixed engines: Prioritize InnoDB first. Then size key_buffer_size to actual MyISAM index usage.
Hosts with less than 4 GB of RAM: Use lower percentages and leave extra headroom.
Use these as starting points:
Start from the packaged
my.cnformy.ini.Change only the major cache settings first.
Fix slow queries with indexes, schema changes, or query changes before tuning smaller buffers.
Keep the settings in the
[mariadb]section.Restart the server after file-based changes.
Memory problems usually come from one of two causes:
Global caches are too large.
Per-connection buffers are too large for the current concurrency.
How MariaDB Uses Memory
MariaDB uses three broad kinds of memory:
Fixed global allocations
Storage engine caches such as innodb_buffer_pool_size, key_buffer_size and aria_pagecache_buffer_size.
Optional caches such as query_cache_size.
Dynamic global structures
Metadata- and connection-related structures such as table_open_cache, table_definition_cache, and thread_cache_size.
Limits such as max_connections do not allocate memory by themselves, but they raise the possible peak memory footprint.
Per-connection and per-query allocations
Execution buffers such as join_buffer_size, mrr_buffer_size, read_buffer_size, and sort_buffer_size.
Temporary in-memory tables controlled by max_heap_table_size and tmp_memory_table_size.
Temporary blob storage and engine-specific work areas.
On containers, cgroups, or managed environments, use the memory limit seen by MariaDB. Do not size caches against the physical RAM of the host.
Confirm memory pressure.
Check whether the host is swapping or being killed by the OOM killer.
On the MariaDB side, inspect overall memory use:
If the server is swapping, reduce major caches before tuning anything else.
Review non-default variables.
List variables that differ from the compiled defaults:
This can yield a result like this:
If the server is running out of memory, the cause is often in this list.
Reduce the biggest consumers first.
Start with the variables that move total memory the most:
Then review per-connection buffers, especially if concurrency is high:
Large per-connection buffers are safe only when few sessions use them at the same time.
Re-test under real concurrency.
After each change, recheck swapping, memory use, and query latency.
Avoid tuning many memory variables at once. Change the largest settings first, then re-measure.
What Is the Key Buffer?
MyISAM does two different things for caching:
Index blocks (1KB each, BTree structured, from
.MYIfile) live in the "key buffer".Data block caching (from .MYD file) is left to the OS, so be sure to leave a bunch of free space for this. Caveat: Some flavors of OS always claim to be using over 90%, even when there is really lots of free space.
To inspect the key buffer, run this statement:
The result is something like this:
Calculate Key_read_requests / Key_reads. If it is high (over 10), the key buffer is big enough, otherwise you should adjust the key_buffer_size value.
What Is the Buffer Pool?
InnoDB performs all its caching in the buffer pool, whose size is controlled by innodb_buffer_pool_size. By default, it contains 128MiB of data and index blocks from the open tables (see innodb_page_size), plus some maintenance overhead. Focus on total buffer pool size.
Another Algorithm
Use this to set the main cache settings to the minimum. This can be important for systems with lots of other processes, and/or where RAM is 2GB or smaller.
Run SHOW TABLE STATUS for all the tables in all the databases.
Add up index length for all MyISAM tables. Set key_buffer_size no larger than that size.
Add up Data_length + Index_length for all the InnoDB tables. Set innodb_buffer_pool_size to no more than 110% of that total.
If that leads to swapping, cut both settings back. Suggest cutting them down proportionately.
Run this to see the values for your system. (This query may run a while if you have a large set of tables.)
Query Memory Allocation
There are two variables that dictate how memory is allocated while MariaDB parses and executes a query. query_prealloc_size defines the standard buffer for query execution, and query_alloc_block_size defines the size of extra memory blocks if query_prealloc_size is not large enough. Getting these variables right can reduce memory fragmentation in the server.
Legacy and Specialized Scenarios
The sections below cover older platforms, specialized hardware, and edge cases. Use them only if they match your environment.
Mutex Bottleneck
This is mostly a historical concern.
Older MySQL and MariaDB releases could hit mutex contention around a few shared structures:
MyISAM's
key_bufferThe Query Cache
InnoDB's
buffer_pool
On current MariaDB releases, this is much less important than:
sizing the buffer pool correctly
avoiding unnecessary query cache usage
fixing slow queries and bad indexes
sizing connection concurrency realistically
Legacy Platform Notes
These notes only apply to older or unusual deployments.
32-Bit Operating Systems
On a 32-bit OS, MariaDB cannot effectively use large amounts of RAM.
RAM above 4 GB is not usable by a 32-bit process.
The operating system may impose a much lower per-process limit.
Large cache settings can fail with out-of-memory errors even when the host still has free RAM.
Example: FreeBSD's maxdsiz, which defaults to 512MB.
If you must run MariaDB on a 32-bit platform, keep cache settings conservative.
If you get an error like [ERROR] /usr/libexec/mysqld: Out of memory (Needed xxx bytes), it probably means that MariaDB exceeded what the OS is willing to give it. Decrease the cache settings.
64-Bit OS with 32-Bit MariaDB
The OS can use more than 4 GB, but the MariaDB process still cannot.
If you are in this configuration, upgrade MariaDB to 64-bit if possible.
Until then, keep cache sizes below the effective 32-bit process limit.
64-Bit OS and MariaDB
This is the standard deployment target these days.
Use the recommendations at the top of this page. They are more accurate than the old platform-specific rules of thumb.
To find the values of the max_connections and thread_stack variable, issue this:
Each thread takes some amount of RAM. This used to be about 200KB; 100 threads would be 20MB, not a significant size. If you have max_connections > 1000, you are talking about 200MB, maybe more. Having that many connections, though, likely implies other issues that should be addressed.
Thread pooling can change how concurrency behaves, but it does not remove the need to budget memory for peak active sessions.
Thread stack overruns are rare. If they occur, adjust thread_stack carefully and test.
table_open_cache
The OS has some limit on the number of open files it will let a process have. Each table needs 1 to 3 open files. Each partition is effectively a table. Most operations on a partitioned table open all partitions.
In Unix, ulimit tells you what the file limit is. The maximum value is in the tens of thousands, but sometimes it is set to only 1024. This limits you to about 300 tables. More discussion on ulimit
You can see how well your system is performing via SHOW GLOBAL STATUS; and computing the opens/second via Opened_files / Uptime If this is more than, say, 5, table_open_cache should be increased. If it is less than, say, 1, you might get improvement by decreasing table_open_cache.
table_open_cache defaults to 2000.
Query Cache
Set query_cache_type to OFF and query_cache_size to 0 unless measurements show a clear benefit.
The query cache can be very fast for repeated identical SELECT statements, but it has significant trade-offs:
Every eligible
SELECTmust consult it.A write to a table invalidates cached results for that table.
Contention around the shared cache can hurt concurrency.
If you decide to use it:
Keep query_cache_size small, usually no more than
50M.Prefer query_cache_type =
DEMAND.Use
SQL_CACHEonly on queries that benefit from reuse.
To see whether it helps, issue this statement:
Review the result:
A rough read hit rate is:
Qcache_hits / Qcache_inserts
If that ratio stays low, disable the query cache.
thread_cache_size
The thread_cache_size rarely needs manual tuning. Keep it small but non-zero unless you have evidence that connection creation is a bottleneck.
This setting controls how many disconnected threads are kept ready for reuse. It does not limit concurrent connections; max_connections does.
A very high value usually wastes memory without helping throughput.
Binary Logs
If you enable binary logging for replication or point-in-time recovery, logs will accumulate until they are purged.
Set a retention policy explicitly. On current MariaDB releases, prefer binlog_expire_logs_seconds. Older setups may still use expire_logs_days.
Choose a retention period that matches your recovery and replication requirements.
Swapping
Swapping usually hurts MariaDB badly.
For dedicated MariaDB hosts on Linux, a low vm.swappiness value often works best because it reduces the chance that large caches are pushed to disk under pressure.
In practice:
Avoid heavy swapping.
Leave enough free RAM for the operating system.
Use a low
vm.swappinessvalue if your platform and workload support it.
Do not use this as a substitute for proper memory sizing.
NUMA
NUMA can cause uneven memory allocation on multi-socket systems.
To see if your machine is a NUMA host, run this command (available on Linux):
If the output yields available: 1 nodes (0), all CPUs share memory equally. If it indicates 2 or mode nodes, the machine is a NUMA host.
If you see swapping or memory imbalance on a NUMA host, check the platform configuration before changing MariaDB settings. In some environments, interleaving memory allocation can avoid one NUMA node filling early while others stay underused.
Possible mitigations include:
BIOS or firmware memory interleaving.
OS-level NUMA configuration.
Setting innodb_numa_interleave where supported.
Only tune NUMA after you confirm it is part of the problem.
Huge Pages
Huge pages can reduce page table overhead for large memory allocations.
For InnoDB-heavy systems with large buffer pools, explicit huge pages can help. Transparent huge pages are often less predictable and can hurt latency, so review your OS defaults and current MariaDB release behavior before changing anything.
If you use huge pages:
Size them to match the intended buffer pool usage.
Validate startup and allocation behavior.
Measure latency and throughput after enabling them.
Do not enable huge pages blindly on small or memory-constrained systems.
ENGINE=MEMORY
The Memory Storage Engine is a little-used alternative to MyISAM and InnoDB. The data is not persistent, so it has limited uses. The size of a MEMORY table is limited to max_heap_table_size, which defaults to 16MB. I mention it in case you have changed the value to something huge; this would be stealing from other possible uses of RAM.
How to Set Variables
In your configuration file (for instance, my.cnf), set a variable like this (variable_name = value), in the [mariadb] section, then restart the server for the changes to take effect:
For better readability of large values, use M for million and G for billion (note that the multiplier is 1024):
1M (Megabyte) = 10242 (1048576 bytes).
1G (Gigabyte) = 10243 (1073741824 bytes).
Most settings can also be changed on the live system, by running a query like this:
Setting variables like that isn't persistent across server starts.
However, you can do both – set the variable with a SET statement (so that it becomes immediately available), and in the configuration file (so that it's persistent when the server is restarted).
Note that you cannot use M or G when setting variable values with a SET statement.
To see the setting of a global variable, run a statement like this:
You may want to do both (SET and modify my.cnf) in order to make the change immediately and have it so that the next restart (for whatever reason) will again get the value.
Application Connection Pools
Application servers and web servers can create far more database connections than MariaDB can use efficiently.
Size the application pool explicitly.
Keep max_connections high enough for real traffic, but not so high that worst-case per-connection buffers can exhaust RAM.
Prefer connection pooling over one-connection-per-request patterns.
Tools
These tools can help identify obvious configuration problems, but they often assume worst-case memory use.
One common warning looks like this:
Treat that as an upper bound, not a prediction. It usually assumes all max_connections are active and all are using large per-query buffers at the same time.
Another warning can look like this:
This implies that OPTIMIZE TABLE might help. Use it for tables with either a high percentage of "free space" (see SHOW TABLE STATUS) or where you know you run a lot of DELETE and/or UPDATE statements. Still, don't bother to optimze too often. Once a month should suffice.
See Also
Attribution
Rick James wrote the original version of this page. Rick James' site, original source: random.
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?