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

This article mainly introduces "sharing of Linux performance tuning skills for MySQL". In daily operation, I believe that many people have doubts about the sharing of Linux performance tuning techniques for MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "sharing Linux performance tuning skills for MySQL"! Next, please follow the editor to study!

Linux performance tuning skills for MySQL

File system (Filesystem)

1) use the ext4 or xfs file system, and the mount option uses the noatime option. The system records information such as file creation, modification and last access by default, and recording last access time requires a certain amount of overhead. Using the noatime option without recording the last access time can improve the performance of the system.

2) IO scheduling algorithm chooses NOOP or Deadline.

Echo deadline > / sys/block/sda/queue/scheduleradd "elevator=deadline" to grub.conf

Memory Optimization (Memory&Swap)

Give priority to memory

Use memory as much as possible and use less swap. The system uses swap only when there is not enough memory.

Echo 0 > / proc/sys/vm/swappinessadd "vm.swappiness = 0" to / etc/sysctl.conf

Shielding NUMA characteristic

Setting the interleave parameter value of numactl to all allows all processors to cross access to all memory, consistent memory access (UMA, Uniform Memory Access).

Numactl-interleave=all

Node Interleaving: Enable or Disable? The function of cross-access mode is described in detail.

The MySQL "swap insanity" problem and the effects of the NUMA architecture deeply analyzes MySQL's use of swap and the impact of NUMA architecture on MySQL performance.

NUMA (Non-Uniform Memory Access) non-uniform memory access architecture is a memory design scheme for multi-core processors. For each processor, NUMA divides global memory into local memory (local memory) and non-local memory (no-local memory). Processors access local memory much faster than non-local memory.

CPU optimization

Check whether the energy saving option is enabled in CPU, and ondemand indicates that it is in a state of energy saving. [Centos 5.x]

Cat / sys/devices/system/cpu/cpu1/cpufreq/scaling_governorondemandps ax | grep kndemand

2778? S < 0:00 [kondemand/0]

2779? S < 0:00 [kondemand/1]

2780? S < 0:00 [kondemand/2]

2781? S < 0:00 [kondemand/3]

2782? S < 0:00 [kondemand/4]

2783? S < 0:00 [kondemand/5]

2784? S < 0:00 [kondemand/6]

2785? S < 0:00 [kondemand/7]

The above process status shows that all eight cores have energy saving mode turned on.

In addition, whether the current clock frequency of the cpu in / proc/cpuinfo is consistent with the number in "model name" can tell whether the cpu is in a state of energy saving. As follows, the 2.13GHz shown by "model name" and the 1867.000MHz CPU shown by "cpu Mhz" do not reach the maximum clock frequency and are in a state of energy saving.

Cat / proc/cpuinfo

Processor: 0

Vendor_id: GenuineIntel

Cpu family: 6

Model: 26

Model name: Intel (R) Xeon (R) CPU E5506 @ 2.13GHz

Stepping: 5

Cpu MHz: 1867.000

Cache size: 4096 KB

Centos6.x compiles the kernel without a cpufreq performance module, so you cannot use "cat / sys/devices/system/cpu/cpu1/cpufreq/scaling_governor" to check whether cpu is energy efficient.

Watch grep\ "cpu MHz\" / proc/cpuinfo

Check the operating frequency of the cpu.

At this point, the study of "sharing Linux performance tuning skills for MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.