MySQL Optimization on Linux

MySQL Optimization on Linux

Currently, most of the MySQL running environments are on Linux. Here we provide some general and simple policies on how to optimize MySQL on the Linux operating system. These methods help improve MySQL performance.

If you have less time to talk about it, go to the topic.

I. CPU

Start with CPU.

If you check it carefully, some servers may have an interesting phenomenon: When you cat/proc/cpuinfo, you will find that the CPU frequency is different from its nominal frequency:

#cat /proc/cpuinfo processor : 5model name : Intel(R) Xeon(R) CPU E5-2620 0 @2.00GHz ...cpu MHz : 1200.000

This is the Intel E5-2620 CPU, Which is 2.00G * 24 CPU, but we found that the frequency of 5th CPU is 1.2G.

Why?

These are all due to the latest CPU Technology: energy-saving mode. When the operating system is not busy with the CPU hardware, it will reduce the CPU frequency to save power and reduce the temperature. This is a good news for environmental protection and global warming resistance, but it may be a disaster for MySQL.

To ensure that MySQL can fully utilize CPU resources, we recommend that you set the CPU to the maximum performance mode. This setting can be set in BIOS and operating system. Of course, it is better and more thorough to set this option in BIOS. Due to the differences between various BIOS types, setting the CPU to the maximum performance mode varies significantly, so we will not detail how to set it here.

Ii. Memory

Then let's look at the memory, which of the following can be optimized.

I) Let's first look at numa.

Inconsistent storage Access structure (NUMA: Non-Uniform Memory Access) is also the latest Memory management technology. It corresponds to the Symmetric Multi-Processor structure (SMP: Symmetric Multi-Processor. Simple teams are as follows:

, The detailed NUMA information is not described here. However, we can intuitively see that the cost of SMP access to memory is the same; but in the NUMA architecture, the cost of local memory access is different from that of non-local memory access. Based on this feature, we can set the memory allocation mode for processes on the operating system. Currently, the following methods are supported:

--interleave=nodes--membind=nodes--cpunodebind=nodes--physcpubind=cpus--localalloc--preferred=node

In short, you can specify the number of CPU nodes that are allocated locally or in polling mode. Unless it is set to -- interleave = nodes Round Robin, that is, the memory can be allocated on any NUMA node. In other ways, even if there is memory surplus on other NUMA nodes, Linux does not allocate the remaining memory to this process, but uses SWAP to obtain the memory. Experienced system administrators or DBAs know how poor the database performance caused by SWAP is.

So the simplest method is to disable this feature.

You can disable this feature temporarily when starting a process in the BIOS, operating system, or operating system.

A) due to the differences in various BIOS types, how to disable NUMA varies greatly. We will not detail how to set it here.

B) disable it in the operating system. You can add numa = off at the end of the kernel line of/etc/grub. conf, as shown below:

 
 

  
  
kernel /vmlinuz-2.6.32-220.el6.x86_64 ro root=/dev/mapper/VolGroup-root   rd_NO_LUKS 
  
  
LANG=en_US.UTF-8 rd_LVM_LV=VolGroup/root rd_NO_MD quiet   SYSFONT=latarcyrheb-sun16 rhgb 
  
  
crashkernel=auto rd_LVM_LV=VolGroup/swap  rhgb crashkernel=auto quiet KEYBOARDTYPE=pc 
  
  
KEYTABLE=us rd_NO_DM numa=off   
 
 

In addition, you can set vm. zone_reclaim_mode = 0 to recycle the memory as much as possible.

C) when MySQL is started, disable the NUMA feature:

numactl --interleave=all  mysqld &

Of course, the best way is to disable it in BIOS.

Ii) let's take a look at vm. swappiness.

Vm. swappiness is the operating system's policy to control physical memory switching. The value is a percentage value. The minimum value is 0 and the maximum value is 100. The default value is 60. Vm. swappiness is set to 0, which indicates that swap is minimized, and 100 indicates that inactive memory pages are switched out as much as possible.

Specifically, when the memory is basically full, the system will determine based on this parameter whether to swap inactive memory rarely used in the memory or to release the data cache. The cache caches data read from the disk. According to the program's local principle, the data may be read again later. As the name suggests, inactive memory is mapped by applications, but the memory is not used for "long time.

We can use vmstat to see the number of inactive memory:

#vmstat -an 1  procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----  r b swpd free  inact  active si so bi bo in cs us sy id wa st  1 0 0 27522384 326928 1704644 0 0 0 153 11 10 0 0 100 0 0  0 0 0 27523300 326936 1704164 0 0 0 74 784 590 0 0 100 0 0  0 0 0 27523656 326936 1704692 0 0 8 8 439 1686 0 0 100 0 0  0 0 0 27524300 326916 1703412 0 0 4 52 198 262 0 0 100 0 0

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

#cat /proc/meminfo | grep -i inact  Inactive: 326972 kB  Inactive(anon): 248 kB  Inactive(file): 326724 kB

Here we will further discuss inactive memory in depth. In Linux, memory may be in three states: free, active, and inactive. As we all know, Linux Kernel maintains many LRU lists internally for memory management, such as LRU_INACTIVE_ANON, LRU_ACTIVE_ANON, LRU_INACTIVE_FILE, LRU_ACTIVE_FILE, and LRU_UNEVICTABLE. Here, LRU_INACTIVE_ANON and LRU_ACTIVE_ANON are used to manage anonymous pages, LRU_INACTIVE_FILE and LRU_ACTIVE_FILE are used to manage page caches page cache. The system kernel will occasionally move the active memory to the inactive list based on the access status on the memory page. These inactive memories can be exchanged to swap.

In general, MySQL, especially InnoDB, manages the memory cache. It occupies a large amount of memory and may not frequently access it. If these memories are exchanged by Linux errors, it will waste a lot of CPU and IO resources. InnoDB manages the cache by itself. The cached file data occupies the memory, which is of almost no benefit to InnoDB.

Therefore, we 'd better set vm. swappiness = 0 on the MySQL server.

You can add a row in sysctl. conf:

echo "vm.swappiness = 0" >>/etc/sysctl.conf

And use sysctl-p to make the parameter take effect.

Iii. File System

Finally, let's take a look at the optimization of the file system.

I) we recommend that you add the noatime and nobarrier options to the mount parameter of the file system.

When noatime mount is used, the file system does not update the corresponding access time when the program accesses the corresponding file or folder. In general, Linux records the file for three times: change time, modify time, and access time.

We can use stat to view the file's three times:

stat libnids-1.16.tar.gz  File: `libnids-1.16.tar.gz'  Size: 72309 Blocks: 152 IO Block: 4096 regular file  Device: 302h/770d Inode: 4113144 Links: 1  Access: (0644/-rw-r--r--) Uid: ( 0/ root) Gid: ( 0/ root)   Access  : 2008-05-27 15:13:03.000000000 +0800  Modify: 2004-03-10 12:25:09.000000000 +0800  Change: 2008-05-27 14:18:18.000000000 +0800

The access time indicates the last time the file was read, and the modify time indicates the time when the file's text content was last changed, change time refers to the time when the inode of a file changes (such as location, user attribute, and group attribute. In general, files are read-write-less, and we seldom care about the time when a file has been accessed.

Therefore, we recommend that you use the noatime option so that the file system does not record the access time to avoid wasting resources.

Many file systems force the underlying device to refresh the cache when submitting data to avoid data loss. This is called write barriers. However, in fact, the underlying storage device of our database server either uses a RAID card, and the RAID card's battery can be protected by power loss; or uses a flash card, which also has a self-protection mechanism to ensure data will not be lost. Therefore, we can safely use nobarrier to mount the file system. The setting method is as follows:

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

Ii) The file system also has a master key to improve IO, that is, deadline.

Before Flash technology, we used mechanical disks to store data. The track time of a mechanical disk is the most important factor affecting its speed, which directly leads to the I/O (IOPS) It can do per second) very limited. In order to sort and Merge multiple requests as much as possible to achieve the goal of one tracing to satisfy multiple IO requests, the Linux File System has designed multiple IO scheduling policies, applicable to various scenarios and storage devices.

Linux's IO scheduling policies include Deadline scheduler, Anticipatory scheduler, Completely Fair Queuing (CFQ), and NOOP. The detailed scheduling methods of each scheduling policy are not described in detail here. Here we mainly introduce CFQ and Deadline. CFQ is the default scheduling policy after Linux kernel 2.6.18, it claims to be fair to every IO request, and this scheduling policy applies to most applications. However, if the database has two requests, one with three I/O operations and one with 10000 I/O operations, the three I/O requests must compete with the other 10000 I/O requests, it may take thousands of I/O operations to return, resulting in a very slow response time. In addition, if there are a lot of IO requests in the processing process, please send them one after another. Some IO requests may even be unable to get scheduling and are "starved to death ". While deadline ensures that a request does not wait in the queue for too long, resulting in starvation. It is more suitable for applications such as databases.

Real-time settings, we can use

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

To set the sda scheduling policy to deadline.

You can also add elevator = deadline at the end of the kernel line of/etc/grub. conf to take effect permanently.

Summary

CPU:

Power off protection mode

Memory:

Vm. swappiness = 0

Disable numa

File System:

Mount the system with noatime and nobarrier

Modify the IO scheduling policy to deadline.