MySQL5.7 closes the NUMA method

Non-uniform storage access structure (NUMA: Non-Uniform Memory Access) is also the latest memory management technology. It corresponds to the symmetric multiprocessor architecture (SMP: Symmetric Multi-Processor).

MySQL's problems with the NUMA architecture:

1). The scale of CPU develops exponentially due to Moore's Law, while the bus develops slowly, which leads to the bottleneck of multi-core CPU sharing memory through one bus.

2)。 So the NUMA appeared, and the CPU was divided into several Chip (not more than 4) on average, each Chip has its own memory controller and memory slot.

3) .CPU accesses the memory inserted on its own Chip faster, while accessing the memory associated with other CPU (hereinafter referred to as Remote Access) is about three times slower.

4)。 So the Linux kernel defaults to the CPU affinity memory allocation strategy, which makes the memory page in the same Core/Chip as the calling thread as much as possible.

5)。 Since there is no dynamic adjustment strategy for memory pages, most memory pages are concentrated on CPU 0.

6)。 And because the Reclaim default policy gives priority to the elimination of memory on / Swap this Chip, a large amount of useful memory is swapped out.

7)。 When the swapped page is accessed, the problem occurs in the form of soaring database response time or even blocking.

Therefore, it is recommended to shut down NUMA for a stand-alone instance of MySQL. There are three ways to close it:

1. Hardware layer, setting shutdown in BIOS

2.OS kernel, setting numa=off at startup

3. You can change the memory allocation policy to interleave with the numactl command.

Log in to the Linux system, modify the / etc/init.d/mysqld file, and add numactl-- interleave all

Vi / etc/init.d/mysqld

Find the following line

# Give extra arguments to mysqld with the my.cnf file. This script

# may be overwritten at next upgrade.

$bindir/mysqld_safe-datadir= "$datadir"-pid-file= "$mysqld_pid_file_path" $other_args > / dev/null &

Wait_for_pid created "$!"$mysqld_pid_file_path"; return_value=$?

Change the line $bindir/mysqld_safe-- datadir= "$datadir" to:

/ usr/bin/numactl-interleave all $bindir/mysqld_safe-datadir= "$datadir"-pid-file= "$mysqld_pid_file_path" $other_args > / dev/null &

Wait_for_pid created "$!"$mysqld_pid_file_path"; return_value=$?

Then restart the MySQL service.

# service mysqld restart