Shulou(Shulou.com)06/01 Report--

As you all know, the in-memory numa feature of linux systems is strongly recommended to turn off on servers running mysql services. Because of this feature, it is easy to cause memory leaks: it is found that there is still physical memory left, but the system is already using swap memory.

Numa memory features: for example, a machine has 2 processors and 4 memory blocks. We combine one processor and two blocks of memory, which is called a NUMA node, so that the machine has two NUMA node. In terms of physical distribution, the physical distance between the NUMA node processor and the memory block is smaller, so the access is faster. For example, the machine is divided into left and right processors (cpu1, cpu2), with two memory blocks (memory1.1, memory1.2, memory2.1,memory2.2) on both sides of each processor, so that NUMA node1's cpu1 accesses memory1.1 and memory1.2 faster than memory2.1 and memory2.2. Therefore, if the mode of using NUMA can ensure that the CPU in this node can only access the memory blocks in this node, then this kind of efficiency is the highest.

In fact, because the mysql database server will generally deploy mysql only one service in the run, and will not deploy a number of applications in the run. So you generally want mysql to monopolize the resources of the entire server (excluding the resources left for the operating system to run). Therefore, according to this business characteristic, it is not recommended to enable the numa memory feature on the mysql server.

What if you turn off the numa feature?

1. BLOS level:

Due to the differences of various BLOS types between different systems, the settings are different. It no longer unfolds here.

2. Operating system level:

You can add numa=off directly at the end of the kernel line of / etc/grub.conf.

3. MySQL level:

Modify the startup script directly:

Numactl-interleave=all mysqld-defaults-file=/etc/my.cnf &

4. Set innodb_numa_interleave parameters:

MySQL5.7.9 version +, added the parameter innodb_numa_interleave. According to the official documentation, when innodb_numa_interleave=1 is set, the numa memory allocation policy for the mysqld process is set to MPOL_INTERLEAVE, and once the Innodb buffer pool is allocated, the policy is reset back to MPOL_DEFAULT. Of course, whether this parameter is valid must be based on the fact that mysql is compiled on a linux system that supports the numa feature.

In MySQL5.7.17 version +, the WITH_NUMA parameter has been added to the CMake compiler software to compile mysql on linux systems that support the numa feature.

Note: innodb_numa_interleave is currently not supported in the mysql5.7.17 binary package, detailed bug information: https://bugs.mysql.com/bug.php?id=80288. If you need to avoid this situation, it is recommended to download the source package and compile mysql directly on the system.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.