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

Today, the editor will share with you the relevant knowledge points of MySQL optimization skills in Linux system, which are detailed in content and clear in logic. I believe most people still know too much about this, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

1. Prevents the operating system from updating the atime property of a file

Atime is a file attribute in Linux/UNIX system. Every time a file is read, the operating system writes back the read operation time to disk. For database files that read and write frequently, recording the access time of the files is generally useless, but it will increase the burden on the disk system and affect the performance of IWeiO! Therefore, it is possible to prevent the operating system from writing atime information by setting the mount familiarity of the file system, thus reducing the burden on the disk Imax O. The methods are as follows:

(1) modify the file system configuration file / etc/fstab, and specify the noatime option:

UUID=33958004-e8a7-4135-844f-707a5537e86a / data ext4 noatime 0 1 (2) re-mount the file system for its changes to take effect:

[root@MySQL-01] # mount-o remount / data2. Adjust Ipaw O scheduling algorithm

For more information, please refer to the choice of Icano scheduling algorithm mentioned in the previous article.

(1) View the Icano scheduling algorithm supported by the current system:

[root@MySQL-01 ~] # dmesg | grep-i scheduler io scheduler noop registered io scheduler anticipatory registered io scheduler deadline registered io scheduler cfq registered (default) [root@MySQL-01 ~] # (2) View the Imax O scheduling algorithm used by the current device (/ dev/sda):

[root@MySQL-01 ~] # cat / sys/block/sda/queue/scheduler noop anticipatory deadline [cfq] [root@MySQL-01 ~] # (3) modify the I root@MySQL-01 O scheduling algorithm used by the current device. Ordinary disks can choose Deadline,SSD. We can choose to use NOOP or Deadline.

[root@MySQL-01 ~] # echo "deadline" > > / sys/block/sda/queue/scheduler [root@MySQL-01 ~] # cat / sys/block/sda/queue/scheduler noop anticipatory [deadline] cfq [root@MySQL-01 ~] # permanently modify the elevator= scheduling algorithm by modifying the kernel boot parameters to increase the name of the scheduling algorithm.

[root@MySQL-01 ~] # vim / boot/grub/menu.lst changed content:

[root@MySQL-01 ~] # grep "deadline" / boot/grub/menu.lst kernel / vmlinuz-2.6.32-220.el6.x86_64 ro root=UUID=c0618639-a967-4601-bca7-cc3b99c5c332 elevator=deadline rd_NO_LUKS rd_NO_LVM.UTF-8 rd_NO_MD quiet SYSFONT=latarcyrheb-sun16 rhgb crashkernel=auto KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM [root@MySQL-01 ~] # 3.NUMA Architecture Optimization

From the perspective of system architecture, the current commercial servers can be divided into three categories:

(1) symmetric multiprocessor architecture (Symmetric Multi-Processor,SMP)

(2) inconsistent storage access architecture (Non-Uniform Memory Access,NUMA)

(3) massive parallel processing architecture (Massive Parallel Processing,MPP)

Generally speaking, there are more servers based on SMP or NUMA architecture. I will only elaborate on the NUMA framework here, while other children's shoes can consult the data on their own.

NUMA divides a computer into multiple nodes (Node), each node has multiple CPU, the node uses a common memory controller, and the nodes connect and exchange information through interconnection modules, so all the memory of the node is equal to all the CPU of this node, but different for all CPU of other nodes. Therefore, each CPU can access the entire system memory, but the memory speed of accessing the local node is the fastest (without going through the interconnection module), and the memory speed of accessing the non-local node is slower (through the interconnection module), that is, the speed of CPU accessing memory is related to the distance of the node, which is called Node Distance. As shown below:

Displays the node status of the current NUMA:

[root@localhost] # numactl-- hardware available: 2 nodes (0-1) node 0 cpus: 0 246 node 0 size: 16338 MB node 0 free: 136 MB node 1 cpus: 13 57 node 1 size: 16384 MB node 1 free: 66 MB node distances: node 0 10: 10 20 1: 20 10 [root@localhost ~] # free-m total used free shared buffers cached Mem: 32060 31856 204 0362 13016-/ + buffers/cache: 18477 13582 Swap: 7999 6 7993 [root@localhost ~] #

There are two nodes on the server: Node 0 and Node 1 Magi Node 0. The local memory of 16GB Magi Node 1 is about 16GB. You can see that the system has a total of 32GB memory.

The distance between nodes (Node Distance) is a manifestation of the cost that node 1 has to pay to access memory on node 0. In the above example, the local memory declaration distance of the Linux node is 10, and the non-local memory declaration distance is 20. 0.

NUMA's memory allocation strategies are divided into the following four types:

(1) default default: always assigned on the local node (on the node on which the current process is running)

(2) bind bind: force the assignment to the specified node

(3) Cross-interleave: cross-allocate memory on all nodes or specified nodes

(4) priority preferred: assigned on the specified node, otherwise assigned on other nodes

Displays the current system NUMA policy:

[root@localhost ~] # numactl-- show policy: default preferred node: current physcpubind: 0 1 2 3 4 5 6 7 cpubind: 0 1 nodebind: 0 1 membind: 0 1 [root@localhost] #

Because the default memory allocation policy of NUMA is to give priority to the allocation of local memory in the CPU where the process is located, it will lead to uneven memory allocation among CPU nodes. When a CPU node runs out of memory, it will cause SWAP instead of allocating memory from remote nodes. This is the Swap Insanity phenomenon.

MySQL is a single-process multithreaded database. When NUMA uses the default memory allocation strategy, the MySQL process will be and will only be assigned to one node of NUMA. Suppose the MySQL process is allocated to Node 1 to run, and the local memory of this node is 8GB, while MySQL is configured with 14GB memory. The 14GB memory allocated by MySQL exceeds the node local memory (14GB-8GB=6GB). The Linux system would rather use Swap than the physical memory of other nodes. In this case, it can be observed that although the system still has a lot of physical memory available, the MySQL process is already using Swap.

MySQL does not support the features of NUMA well. If only one MySQL instance is running on a stand-alone machine, you can choose to disable NUMA. There are two ways to do so:

(1) hardware layer, set the shutdown in BIOS

(2) OS kernel layer, set numa=off at startup

Modify / etc/grub.conf to add numa=off

[root@MySQL-01 ~] # vim / etc/grub.conf [root@MySQL-01 ~] # grep 'numa' / etc/grub.conf kernel / vmlinuz-2.6.32-220.el6.x86_64 ro root=UUID=c0618639-a967-4601-bca7-cc3b99c5c332 elevator=deadline rd_NO_LUKS rd_NO_LVM.UTF-8 rd_NO_MD quiet SYSFONT=latarcyrheb-sun16 rhgb crashkernel=auto KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM numa=off [root@MySQL-01 ~] # or change the memory allocation policy of NUMA to interleave through the numactl command

/ usr/bin/numactl-- interleave=all / usr/local/mysql-5.1.66/bin/mysqld_safe-- defaults-file=/usr/local/mysql-5.1.66/my.cnf thus specifies that the memory allocation policy for MySQL startup is interleave

If you run multiple MySQL instances on a single machine, you can bind different MySQL instances to different CPU nodes, configure appropriate MySQL memory parameters, and use bound memory allocation tests to force memory allocation on local nodes.

4.vm.swappiness adjustment

Swappiness is a strategy used by the operating system to control physical memory swapping out. The value it allows is a percentage, with a minimum of 0 and a maximum of 100, which defaults to 60. Setting vm.swappiness to 0 means that as little swap,100 as possible means swapping out the memory pages of inactive as much as possible. Specifically: when the memory is basically full, the system will use this parameter to determine whether to swap out the inactive memory that is rarely used in memory, or to release the cache of the data. Cache caches data read from disk, which is likely to be read next according to the locality principle of the program; inactive memory, as its name implies, is memory that is mapped by the application but not used "for a long time".

We can use vmstat to see the amount of memory in inactive:

[root@MySQL-01] # vmstat-an 1 5 procs-memory- swap---io---- system---cpu- r b swpd free inact active si so bi bo in cs us sy id wa st 0 4892 1194972 234208 492404 0 05 38 32 83 0 1 99 00 00 4892 1194964 234208 492420 00 00 24 29 00 00 4892 1194964 234208 492420 00 00 12 21 00 10000 4892 1194964 234208 492420 00 00 20 24 00 00 4892 1194964 234208 492420 00 13 21 00 [root@MySQL-01] #

You can see more detailed information through / proc/meminfo:

[root@MySQL-01 ~] # cat / proc/meminfo | grep-i inact Inactive: 234188 kB Inactive (anon): 3228 kB Inactive (file): 230960 kB [root@MySQL-01 ~] # Linux, memory may be in three states: free,active and inactive. As we all know, Linux Kernel maintains a number of LRU lists internally to manage memory, such as LRU_INACTIVE_ANON, LRU_ACTIVE_ANON, LRU_INACTIVE_FILE, LRU_ACTIVE_FILE, LRU_UNEVICTABLE. LRU_INACTIVE_ANON and LRU_ACTIVE_ANON are used to manage anonymous pages, and LRU_INACTIVE_FILE and LRU_ACTIVE_FILE are used to manage page caches page cache. The kernel of the system will periodically move the active active memory to the inactive list according to the access of the memory page, and the inactive memory can be swapped into the swap. Generally speaking, MySQL, especially InnoDB manages memory cache, it takes up a lot of memory, and there will be a lot of memory accessed infrequently. If these memories are mistakenly swapped out by Linux, it will waste a lot of CPU and IO resources. InnoDB manages the cache itself, and cache's file data takes up memory, which is of little benefit to InnoDB. Therefore, we'd better set up vm.swappiness=0 on the server of MySQL.

We can do this by adding a line to sysctl.conf (if your kernel version is 2.6.32-303.el6 and later, set vm.swappiness = 1):

[root@MySQL-01 ~] # echo "vm.swappiness = 0" > > / etc/sysctl.conf [root@MySQL-01 ~] # sysctl-p another way is to enable large memory pages in innodb, which has the same effect as the above method. For more information, please refer to the InnoDB enabled large memory page mentioned in the previous article.

5.CPU optimization

Check whether the energy saving option is turned on in CPU

[root@localhost ~] # grep-E'^ model name | ^ cpu MHz' / proc/cpuinfo model name: Intel (R) Xeon (R) CPU L5520 @ 2.27GHz cpu MHz: 2266.602 model name: Intel (R) Xeon (R) CPU L5520 @ 2.27GHz cpu MHz: 2266.602 model name: Intel (R) Xeon (R) CPU L5520 @ 2.27GHz cpu MHz : 2266.602 model name: Intel Xeon (R) CPU L5520 @ 2.27GHz cpu MHz: 2266.602 model name: Intel (R) Xeon (R) CPU L5520 @ 2.27GHz cpu MHz: 2266.602 model name: Intel (R) Xeon (R) CPU L5520 @ 2.27GHz cpu MHz: 2266.602 model name Intel (R) Xeon (R) CPU L5520 @ 2.27GHz cpu MHz: 2266.602 model name: Intel (R) Xeon (R) CPU L5520 @ 2.27GHz cpu MHz: 2266.602 [root@localhost ~] #

If it is found that the frequency of CPU is not the same as its nominal frequency, then the energy saving mode is turned on.

You can turn it off using the following command:

For CPUFREQ in / sys/devices/system/cpu/cpu*/cpufreq/scaling_governor; do [- f $CPUFREQ] | | continue; echo-n performance > $CPUFREQ; done energy saving mode: the operating system cooperates with CPU hardware. When the system is not busy, it will reduce the CPU frequency in order to save power and lower the temperature. For MySQL, it could be a disaster. In order to ensure that MySQL can make full use of the resources of CPU, it is recommended to set CPU to the maximum performance mode. This setting can be set in BIOS and the operating system, of course, it is better to set this option in BIOS.

These are all the contents of the article "what are the MySQL optimization techniques in Linux system?" Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

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.