Recommendations for Safe / Stable Memory Allocation in MySQL Server
(Doc ID 2520749.1)
Last updated on FEBRUARY 07, 2024
Applies to:
MySQL Server - Version 5.7 and laterInformation in this document applies to any platform.
Goal
To explain the importance of understanding memory allocation in MySQL Server.
Assuming host system is dedicated to MySQL and is not virtualised as virtualisation can add extra complexity due to contention for shared resources, to the environment.
The high level aim of any database configuration is to maximise stability and efficiency of the software which can often increase performance, by appropriate memory allocation.
Even if the host has more RAM than is necessary for the dataset, it is still beneficial to allocate buffers according to a known "good config", adjusting over time as per ongoing capacity planning.
This excludes the obvious parameters for , eg buffer pool and redo log etc.
https://dev.mysql.com/doc/refman/en/server-system-variables.html
Another key aim for stability and performance is to avoid unnecessary disk activity for the OS, eg SWAP.
The aim is to prioritize sustained database stability, over potentially higher peak performance, by having the DB run with a smaller and more efficient GLOBAL memory allocation and for it to allocate the memory it needs on demand for its clients.
Additional documents included for suggesting appropriate memory allocation via my.cnf for key variables and capacity planning to avoid performance issues in the future.
Solution
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Goal |
Solution |
Numa |
Default variable values |
Simple Example |
Avoid Swap |
Additional Reading |
References |