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
Start with these defaults, depending on your choice of storage engines:
Mostly : Set to about 20% of available RAM. Set to 0 only if you do not use InnoDB at all.
Mixed engines: Prioritize InnoDB first. Then 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.cnf or my.ini.
Change only the major cache settings first.
Fix slow queries with indexes, schema changes, or query changes before tuning smaller buffers.
Memory problems usually come from one of two causes:
Global caches are too large.
Per-connection buffers are too large for the current concurrency.
MariaDB uses three broad kinds of memory:
Fixed global allocations
Storage engine caches such as , and .
Optional caches such as .
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.
does two different things for caching:
Index blocks (1KB each, BTree structured, from .MYI file) 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 / . If it is high (over 10), the key buffer is big enough, otherwise you should adjust the value.
InnoDB performs all its caching in the , whose size is controlled by . By default, it contains 128MiB of data and index blocks from the open tables (see ), plus some maintenance overhead. Focus on total buffer pool size.
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 for all the tables in all the databases.
Add up index length for all MyISAM tables. Set no larger than that size.
Add up Data_length + Index_length for all the InnoDB tables. Set 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.)
There are two variables that dictate how memory is allocated while MariaDB parses and executes a query. defines the standard buffer for query execution, and 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.
The sections below cover older platforms, specialized hardware, and edge cases. Use them only if they match your environment.
This is mostly a historical concern.
Older MySQL and MariaDB releases could hit mutex contention around a few shared structures:
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
These notes only apply to older or unusual deployments.
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.
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.
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 and 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 > 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.
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 ; and computing the opens/second via / If this is more than, say, 5, should be increased. If it is less than, say, 1, you might get improvement by decreasing .
defaults to 2000.
Set to OFF and to 0 unless measurements show a clear benefit.
The can be very fast for repeated identical SELECT statements, but it has significant trade-offs:
Every eligible SELECT must 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 small, usually no more than 50M .
Use SQL_CACHE only 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.
The 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; does.
A very high value usually wastes memory without helping throughput.
If you enable for replication or point-in-time recovery, logs will accumulate until they are purged.
Set a retention policy explicitly. On current MariaDB releases, prefer . Older setups may still use .
Choose a retention period that matches your recovery and replication requirements.
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:
Leave enough free RAM for the operating system.
Use a low vm.swappiness value if your platform and workload support it.
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.
Only tune NUMA after you confirm it is part of the problem.
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.
The is a little-used alternative to and . The data is not persistent, so it has limited uses. The size of a MEMORY table is limited to , 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.
In your (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:
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 servers and web servers can create far more database connections than MariaDB can use efficiently.
Size the application pool explicitly.
Keep 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.
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 are active and all are using large per-query buffers at the same time.
Another warning can look like this:
This implies that might help. Use it for tables with either a high percentage of "free space" (see ) or where you know you run a lot of DELETE and/or UPDATE statements. Still, don't bother to optimize too often. Once a month should suffice.
Rick James wrote the original version of this page. , original source: .
This page is licensed: CC BY-SA / Gnu FDL