Recommendations for Safe / Stable Memory Allocation in MySQL Server (Doc ID 2520749.1)

Last updated on FEBRUARY 07, 2024

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.