MySQL third article ~ about optimization of the Linux server itself

Summary: MySQL service optimization is divided into 1 server itself optimization of the performance optimization of the 2mysql itself today let's discuss the optimization performance of the server itself

Introduction to Optimization of one parameter

1 Selection of file systems

Linux Common File System:

Ext3, Ext4, XFS, ReiserFS, JFS

One of the most commonly used is EXT4, XFS. It is not recommended to use a file system below the EXT4 version. Mainstream default is EXT4

2 IO scheduling algorithm

NoOp anticipatory deadline CFG Four algorithms are described below, respectively

1) CFQ (fully fair queuing I/O scheduler)

The starting point for CFQ is to sort the IO addresses to meet as many IO requests as possible with as few disk rotations as possible. With the CFQ algorithm, the throughput of the SAS disk is greatly improved. However, compared to NoOp's disadvantage is that the first-come IO request is not necessarily satisfied, there may be starvation situation.

It can be seen that it is not processed in the order of successive requests, and you can imagine it as cost-based

2) NOOP (Elevator-type dispatch program)

The algorithm implements the most simple FIFO queue, and all IO requests operate roughly in the order of first served. The reason is "approximate", because noop on the basis of FIFO also made the merger of adjacent IO requests, not completely in accordance with the first-in-one rule to meet the IO request. It can be seen that the approximate sequence IO is processed, and then the neighboring IO may be merged in real time

3) Deadline (Deadline scheduler)

Based on CFQ, deadline solves the extreme situation of Io request starvation. In addition to the IO sort queue that CFQ itself has, the deadline provides a FIFO queue for read io and write Io, respectively. The maximum wait time for a read FIFO queue is 500ms, and the maximum wait time for a write FIFO queue is 5s. The IO request priority in the FIFO queue is higher than in the CFQ queue, and the read FIFO queue has a higher priority than the write FIFO queue. The priority can be expressed as follows:

FIFO (Read) > FIFO (write) > CFQ can see that this algorithm takes precedence over read io and write IO

Deadline is the best choice for the database environment (ORACLE rac,mysql, etc.).

4) as (expected I/O scheduler)

The focus of CFQ and deadline is on satisfying fragmented IO requests. For continuous IO requests, such as sequential reads, no optimizations are made. To meet the random IO and sequential IO mix scenarios, Linux also supports the anticipatory scheduling algorithm. Anticipatory on the basis of deadline, for each read IO set a 6ms waiting time window. If the OS receives a read IO request from an adjacent location within this 6ms, it can be satisfied immediately.

In the traditional SAS disk, CFQ, DEADLINE, anticipatory are good choices, and for the dedicated database server, DEADLINE throughput and response time are good. However, on the emerging SSD, such as SSD, Fusion io, the simplest noop is probably the best algorithm, because the other three algorithms are optimized based on shortening the seek time, and the SSD has no so-called seek time and the IO response time is very short.

3 Maximum number of system open files

The maximum number of open files is related to file handles, which can cause a program error if the number of open files (such as a table file) is too large. So advocate set to maximum open number 65535

4 NUMA

The NUMA access policy defaults to local, which takes precedence over native memory, and the memory is bound to the CPU. Numa allocates multiple NUMA nodes, the database itself is very memory-intensive, resulting in a single NUMA node memory that is easily exhausted, and when memory is not enough, virtual memory frequently exchanges data with the hard disk, resulting in a dramatic decrease in performance. The default test is not to allow memory to be obtained from other NUMA nodes. This is particularly critical for MONGODB services, which can cause a CPU spike. So it's best to turn NUMA off

5 Swap occupancy ratio

MySQL use of server memory to avoid the use of a large number of swap space, otherwise it is easy to cause performance problems. This parameter can be adjusted to control how often Linux may use swap, and the smaller it will be, the less often the swap is used. But note that setting the swappiness=0 does not necessarily mean that there is no swap, and it is possible that Oom is set to 0.

6 File Cache Parameters

Dirty_ratio and Dirty_background_ratio

In simple terms, refresh the dirty page ratio

1 when the percentage of dirty pages (relative to all available memory, that is, free memory pages + recyclable memory pages) reaches Dirty_background_ratio, the kernel's flusher thread begins to write back the dirty page data. All available memory is not equal to total system memory.

2 The percentage of dirty pages (relative to all available memory, that is, free memory pages + recyclable memory pages) when Dirty_ratio is reached, the process that performs disk writes begins to write back the dirty data itself. All available memory is not equal to total system memory.

These two parameters are not contradictory, will trigger the parameter 2 and then if the system dirty page number continues to grow will trigger the parameter 1

Two script detection and recommendations

#!/usr/bin/python
#coding =utf-8
Import commands
def zhixing (a,b,c):
(status, Output) = Commands.getstatusoutput (a)
Print "######%s#######"% (b,)
Print output
Print "*******%s*******"% (c,)
Zhixing ("Df-th|awk ' {print $1,$2} ' |grep-v ' Tmpfs '", "View File System", "recommended data partition for XFS")
Zhixing ("Cat/sys/block/sda/queue/scheduler", "View io scheduling algorithm", "recommended using deadline algorithm, do not use CFG algorithm")
Zhixing ("Ulimit-a|grep ' Open Files '", "View File Open number", "recommended set to system maximum 65535")
Zhixing ("Grep-i numa/var/log/dmesg", "Numa is turned on", "strongly recommended to turn NUMA off")
Zhixing ("sysctl-a | grep swappiness "," swap occupancy ratio "," Recommended value set to 1-10 ")
Zhixing ("sysctl-a | grep dirty_ratio "," dirty Refresh dirty page than 1 "," set to 20 better ")
Zhixing ("sysctl-a | grep dirty_background_ratio "," dirty Refresh dirty page than 2 "," set to 5-10 better ")

Three related solutions

1 How to turn off NUMA

1. Hardware layer, set off in BIOS;

2.OS kernel, set numa=off at startup;

3. You can modify the memory allocation policy to Interleave (cross) with the Numactl command

2 Modifying the IO scheduling algorithm

echo ' deadline ' >/sys/block/sdb/queue/scheduler

3 Modifying the Swap occupancy ratio

/etc/sysctl.conf

4 modifying file cache parameters

/etc/sysctl.conf

These are my suggestions for optimizing Linux parameters, which are not covered in the discussion for hardware and RAID disk groups

MySQL third article ~ about optimization of the Linux server itself