Understand MySQL memory usage
MySQL memory utilization can appear high, even if the service is relatively idle.
All services are subject to operating overhead, but some services, including MySQL, pre-allocate memory. This can lead to a false impression that the service is misbehaving, when it is actually operating under normal conditions.
The InnoDB buffer pool
Arguably, the most important MySQL component is the InnoDB Buffer Pool.
Every time an operation happens to a table (read or write), the page where the records (and indexes) are located is loaded into the Buffer Pool.
This means that if the data you read and write the most has its pages in the Buffer Pool, the performance will be better than if you have to read pages from disk. When there are no more free pages in the pool, older pages must be evicted and if they were modified, synchronized back to disk (checkpointing).
The MySQL Reference says:
The buffer pool is an area in main memory where InnoDB caches table
and index data as it is accessed. The buffer pool permits frequently
used data to be accessed directly from memory, which speeds up
processing.
And How MySQL Uses Memory says:
InnoDB allocates memory for the entire buffer pool at server startup,
using `malloc()` operations. The `innodb_buffer_pool_size` system
variable defines the buffer pool size. Typically, a recommended
`innodb_buffer_pool_size` value is 50 to 75 percent of system memory.
However, the InnoDB buffer pool isn't the only pre-allocated buffer.
Global buffers
The InnoDB buffer pool is part of the global buffers MySQL allocates to improve performance of database operations.
An explanation of these various buffers (or code areas) can be found in the MySQL documentation: How MySQL Uses Memory.
Using a 4 GB service as an example, a view of the global buffers shows what memory has been allocated:
SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,
format_bytes(SUM(current_alloc)) AS current_alloc
FROM sys.x$memory_global_by_current_bytes
GROUP BY SUBSTRING_INDEX(event_name,'/',2)
ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area | current_alloc |
+---------------------------+---------------+
| memory/innodb | 1.37 GiB |
| memory/performance_schema | 213.22 MiB |
| memory/sql | 18.73 MiB |
| memory/mysys | 8.82 MiB |
| memory/temptable | 1.00 MiB |
| memory/mysqld_openssl | 459.71 KiB |
| memory/mysqlx | 3.25 KiB |
| memory/myisam | 728 bytes |
| memory/csv | 120 bytes |
| memory/vio | 80 bytes |
+---------------------------+---------------+
Although allocated, the buffers may be relatively empty:
SELECT CONCAT(FORMAT(A.num * 100.0 / B.num,2),'%') `BufferPool %` FROM
(SELECT variable_value num FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;
+--------------+
| BufferPool % |
+--------------+
| 0.45% |
+--------------+
High memory utilization, by itself, does not indicate a performance issue.
In the above example, the global buffers consume ~1.6 GiB of memory; almost half the RAM on a 4 GB service. However, this does not denote any particular issue, but rather, standard operating conditions.
When memory issues are suspected, or the service is encountering Out of memory conditions, the buffers, queries, and concurrency should be examined to determine if:
- The buffer pool is full and checkpointing frequently
- The sum of the buffer pools are greater than the available service memory
- Queries are generating excessive temporary (spill) files
Service memory limits
The practical memory limit will always be less than the service physical memory limit.
All services are subject to operating overhead:
- A small amount of memory is required by the operating system kernel to manage system resources, including networking functions and disk cache.
- Aiven's cloud data platform requires memory to monitor availability, provide metrics, logging and manage backups.
A server or node's usable memory can be calculated as:
usable memory = RAM - overhead
Where:
overheadis 350 MiB (≈ 0.34 GiB).
Services may utilize optional components, service integrations, connection pooling, or plug-ins, which are not included in overhead calculations.
If a service is overcommitted, the operating system, management layer, backups or availability monitoring, may fail status checks or operations due to resource contention. In severe instances, the node may fail completely with an out-of-memory condition.
Out of memory conditions
Many processes request more memory from the kernel than they will ever use or need. In these cases, the kernel overallocates memory. This allows it to satisfy multiple processes requesting more memory than is available, which is not used or is freed by the time any other process actually needs it.
However, if enough processes start using all their allocated memory
simultaneously there may not be enough physical memory available and an
Out Of Memory (OOM) condition occurs.
This situation is critical and must be resolved immediately.
The solution that the Linux kernel employs is to invoke the
Out of Memory Killer (or OOM Killer). This reviews all running processes
and kills one or more of them to free up system memory and keep the system running.
The OOM Killer selects process to kill based on an oom_score; a
calculation that balances how much memory the process is using with how
long the process has been running.
Processes that have been running for a long time are less likely to be killed. Subprocesses are summed with parent processes in terms of memory usage, so a process which forks many subprocesses, but itself does not use a lot of memory, may still be killed.
In most instances, the hosted data service, or a child process, will have the highest memory footprint and be a prime candidate for termination when the OOM Killer inspects the running processes.
Aiven's cloud data platform leverages kernel namespaces or containers to isolate processes from each other. Isolation has several benefits, including:
- A smaller footprint for security‑related concerns
- A smaller blast radius for failure
- Greater control of system resources
Left unchecked, the OOM Killer may opt to kill the primary service.
This is undesirable as unclean termination of the primary service can
lead to data loss, inconsistency, or corrupted backups.
Further, if Aiven's management platform detects that the primary service is unavailable for , the service will be marked as down and a failover will occur.
To mitigate this scenario, namespaces are used,
some with additional memory limits, in combination with an oom_score_adjust on the primary
process, to coax the OOM Killer into selection of less critical
processes.
This will still result in a service restart, but in a more controlled process, where the database is shut down, rather than killed; exposure to data loss is limited and recovery is faster when the service restarts, often avoiding failover.
Out of Memory conditions can still lead to unexpected behavior, including data unavailable or data loss conditions.
Avoid running low on memory
The OOM killer only runs when the system is critically low on memory. To prevent it from running, either reduce your memory usage or increase the available memory.
For most databases, the service memory footprint can often be reduced by:
- Reducing concurrency or implementing connection pooling
- Tuning queries to limit result sets
- Tuning indexes for query load
- Dropping unused objects from storage
In cases where the working set no longer fits into memory, consider scaling your service.
Related pages