My Oracle Support Banner

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

Last updated on MARCH 04, 2020

Applies to:

MySQL Server - Version 5.7 and later
Information 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.

 

NOTE: Unless explicitly required via iterative performance testing - variables should be left  at  their default values.

This excludes the  obvious parameters for , eg buffer  pool and redo log etc.

https://dev.mysql.com/doc/refman/5.7/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


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