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

Database system is usually the core application of enterprises, so some special optimizations are needed for Linux systems running MySQL.

I am not MySQL Daniel, this paper does not involve MySQL parameter optimization, only some summary of Linux system optimization.

CPU

Modern operating systems are multi-task and multi-user operating systems, and servers can support more and more CPU. For multi-CPU servers, there are many architectures, the common ones are NUMA and SMP.

SMP

SMP is Symmetric Multi-Processing, symmetrical multiprocessing. In this architecture, each processor has the same status and the same right to use resources. They share the same main memory and are controlled by an operating system.

Due to the sharing of the same main memory, with the increase of CPU, the contention for memory resources will also increase, resulting in a waste of CPU resources, so the CPU will increase, and the system can not be promoted linearly. The experimental results show that the utilization rate of SMP is the best when the number of CPU of CPU server is 2-4.

NUMA

NUMA, Non-Uniform Memory Access, non-uniform memory access, under this architecture, each CPU has its own dedicated memory and memory controller (probably within the CPU), so that CPU's memory contention will be reduced.

Each CPU has its own running queue, and the kernel will balance the running queues of each memory (rebalancing). When the running process of CPU 0 is scheduled to the running queue of CPU 1, because the process needs the original data, then CPU 1 may need to access the dedicated memory of CPU 0 (memory controller requesting CPU 0). At this time, it needs to cross CPU slots and require multiple CPU clock cycles.

Under the NUMA structure, CPU should be guaranteed to access its own memory as much as possible, and the common method is process binding (CPU affinity).

An illustration of two architectures:

NUMA architecture is not suitable for running database services. Because each CPU uses its own memory, one CPU may run out of memory, while another CPU has a lot of memory left, resulting in a waste of memory resources, or even a situation in which the system has been using swap space while there is more memory left (see MySQL "swap insanity" for details).

The memory allocation method for the NUMA architecture can be set, but the easiest way is to turn off the NUMA feature.

You can use numastat or numastat-p to view the NUMA structure CPU memory access status

Close NUMA

Close from BIOS

Turn it off in the operating system and append numa=off to the kernel line of / etc/grub/grub.conf

Modify / etc/init.d/mysql or mysqld_safe scripts, which is complex, not easy to manage, and is not recommended

When you start MySQL, turn off the NUMA feature and use numactl-- interleave=all mysqld &

The best way is still 1 and 2.

Memoryvm.swappiness

Vm.swappiness is the operating system's strategy for using swap partitions, with values ranging from 0 to 100, with a system default of 60. 0. A value of 0 means using as little swap,100 as possible means swapping out the memory pages of inactive as much as possible. When the system memory is used to a certain amount, the system determines whether to use swap partition or not according to this parameter.

As the use of swap will lead to a sharp decline in database performance, it is recommended not to use swap, which is dangerous, because when the system appears OOM (Out Of Memory), the system will start OOM-killer, which may kill the MySQL process, cause business interruption, and even lose data.

I suggest setting a smaller swap partition and setting vm.swappiness=0, and don't forget to write in / etc/sysctl.conf permanently. Swap partitions should not be too large because a large amount of memory swapping can occur if the vm.swappiness is not set properly.

Note: in newer kernels (2.6.32-303.el6 and later), the swap partition policy has been adjusted for systems with vm.swappiness set to 0, and the system will never use swap partitions, which means that if memory exhaustion occurs, the system will start OOM-killer to kill memory-consuming processes. So you can set vm.swappiness=1.

Note again: before Kernel 2.6.28 (such as CentOS 5), there is Bug, no matter how big the vm.swappiness is set, due to memory management algorithm problems, Linux may swap MySQL memory to the swap partition, if you often encounter this situation, the way to fix it is to upgrade your kernel, or consider using this script A perl script you can run from cron.

I/OIO scheduler

According to the characteristics of mechanical disk, Linux introduces a variety of IO schedulers to optimize IO performance. To put it simply, the strategy to optimize disk read and write is to merge and rearrange IO requests. The merge operation merges IO requests from adjacent sectors into one, and rearranges IO requests in sector logical address order.

There is only one IO scheduler for Linux2.4. Several IO schedulers have been introduced since 2.6. I will not introduce them here, but you can refer to my other article-IO Subsystem for Linux performance Optimization.

The default IO scheduling algorithm for Linux is CFQ, which may starve to death in IO requests.

The following figure shows the test results in Choosing an I Scheduler for Red Hat Enterprise Linux O Scheduler for Red Hat Enterprise Linux 4 and the 2.6 Kernel.

Judging from many actual test scenarios, Deadline is more suitable for running MySQL databases.

Modify IO scheduling algorithm to Deadline

Echo deadline > / sys/block/sda/queue/scheduler

It should be noted here that the behaviors of the usual scheduling algorithms are merge requests and sort requests, which are optimized according to the characteristics of mechanical disks. For systems using SSD hard disks, because there is no head seek, disk rotation positioning and other operations, it is meaningless to use the usual scheduling algorithm for SSD hard disks, so we use a special scheduling algorithm NOOP (NO OPeration), that is, do not operate on IO requests. Deal with it directly according to FIFO rules.

Set the scheduling algorithm to NOOP.

Echo noop > / sys/block/sda/queue/scheduler

Filesystem

Modern operating systems use journal file systems, and Linux generally uses ext4 or xfs. I don't know the specific performance of the two. It is said that XFS is more suitable for large files IO.

For the mount parameters of the file system, it is recommended to use two options: noatime,nobarrier.

Noatime

By default, each time the file is read or modified (and the modification needs to be read first), the system updates the atime and writes to the file metadata.

When you mount a file system using noatime, the atime of the file is not updated when you access the file, and atime is equivalent to mtime. Disk performance can be improved by 0-10%.

Most of the time, closing the atime of a file will only get a small performance improvement (this term is quoted in IMB Redbook), but it is also better than nothing, mounting in / etc/fstab

/ dev/sdb / mountlocation ext4 default, noatime 0 0

Nobarrier

Barrier is a means to ensure the WAL (Write Ahead Logging) of the log file system. When the data is written to the disk, it should be written to the journal area first, and then to the actual corresponding position of the disk. In order to speed up the disk writing speed, the disk will have built-in cache, and the data will generally be written to the cache of the disk first.

Cache can speed up disk writing, but the disk generally sorts the cached data in cache to write to disk in the optimal numerical order, which may cause the data to be written out of order with the actual journal records. Once the system crashes, the disk should refer to the journal area to recover the data next time, but at this time the journal record order is different from the actual data write order, which will cause the data to be "restored" to an inconsistent state. Barrier, like its name, fence, first add a "fence" to ensure that the journal is always written to the record first, and then the corresponding data is written to the disk, which ensures the correctness of disk recovery after the system crash, but has an impact on write performance.

The underlying storage device of the database server either uses the RAID card, and the battery of the RAID card itself can be protected by power-off, or the Flash card, which also has a self-protection mechanism to ensure that the data will not be lost. So we can mount the file system safely using nobarrier. The setting method is as follows:

For ext3,ext4 and reiserfs file systems, you can specify barrier=0; at mount. For xfs, you can specify the nobarrier option.

Summary

For CPU, disable NUMA

For memory, set vm.swappiness=0

For IO, use deadline or noop scheduling strategy, and mount file system using noatime,nobarrier.

For an in-depth understanding of MySQL tuning in the operating system, you can read this PDF-- Linux and Hmp W optimizations for MySQL

There are also some scripts that can simply judge and analyze the operation of MySQL. You can read this-- MySQL Performance Tuning Scripts and Know-How.

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.