Linux Optimizations for MySQL

Selection of 1.OS file systems

When using a Linux system, you should use the EXT4 or XFS file system, which is recommended by industry veterans. View the file system methods as follows:

[Email protected] ~]# df-lhtfilesystem           Type   Size used Avail use% mounted on/dev/sda2            ext4    62G  2.2G   57G   4%/tmpfs                tmpfs   32G     0   32G   0%/dev/shm/dev/sda1            ext3    97M   26M   67M  28%/boot/dev/sda5            ext4    33G  176M   32G   1%/home/dev/mapper/ Db-mysql xfs    3.0T   18G  3.0T   1%/mysql

2. Disable the Atime property of the operating system update file

Atime is a file property under the Linux/unix system, and the operating system writes read time back to disk whenever a file is read. For read-write database files, log file access time is generally useless, but will increase the burden on the disk system, affecting I/O performance! As a result, you can be familiar with setting up the file system mount, preventing the operating system from writing atime information and reducing the disk I/O burden. Here's how:

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

Uuid=33958004-e8a7-4135-844f-707a5537e86a/data                   ext4  noatime     0    1

(2) For XFS file systems, the file system should be mounted with the nobarrier option for devices that can protect data in the cache in the event of a power outage or other host failure:

/dev/db/mysql/mysql xfs Defaults,noatime,nobarrier 0     0

(3) Re-mount the file system to make its modifications effective:

[Email protected] ~]# Mount-o remount/mysql

3. Adjust the I/O scheduling algorithm

(1) View the I/O scheduling algorithms supported by the current system:

(2) View the I/O scheduling algorithm used by the current device (/DEV/SDA):

[Email protected] ~]# Cat/sys/block/sda/queue/scheduler     

(3) Modify the current device using the I/O scheduling algorithm, ordinary disk can choose DEADLINE,SSD we can choose to use NoOp or deadline

[Email protected] ~]# echo "Deadline" >>/sys/block/sda/queue/scheduler [[email protected] ~]# cat/sys/block/sda/ Queue/scheduler                

Permanently modify the I/O scheduling algorithm, you can increase the elevator= scheduling algorithm name by modifying the kernel boot parameters

What's changed:

[[email protected] ~]# grep "Deadline"/boot/grub/menu.lst              elevator=deadline rd_no_luks RD_NO_LVM lang=en _us. UTF-8 rd_no_md quiet Sysfont=latarcyrheb-sun16 rhgb Crashkernel=auto  

4.NUMA Architecture Optimization

From a system architecture perspective, the current commercial server can be broadly divided into three categories:

(1) Symmetric multi-processor architecture (symmetric MULTI-PROCESSOR,SMP)

(2) Non-uniform Storage access Architecture (Non-uniform memory Access,numa)

(3) Massive parallel processing architecture (Massive Parallel processing,mpp)

The general server is more of an SMP or NUMA architecture. Only the NUMA schema is described here.

NUMA divides a computer into nodes, each node has multiple CPUs, internal memory controllers are used internally, nodes are connected through the Interconnect module and information is interacted with, so all the memory of the node is equal to all CPUs of the node. It is different for all CPUs in the other nodes. As a result, each CPU can access the entire system memory, but the memory of accessing the local node is the fastest (without having to go through the Interconnect module), the memory of accessing the non-local node is slow (need to pass through the Interconnect module), that is, the CPU accesses the memory speed and the distance of the node, the distance is called Node Distance.

Shows the current NUMA node condition:

[[email protected] ~]# numactl--hardwareavailable:2 nodes (0-1) node 0 cpus:0 2 4 6node 0 size:16338 mbnode 0 free:136 Mbnode 1 cpus:1 3 5 7node 1 size:16384 mbnode 1 free:66 mbnode distances:node   0   1   0:  ten   1: c6/>20  [[email protected] ~]# free-m       total used free     shared    buffers     Cachedmem:         32060      31856        204          0        362      13016-/+ buffers/cache:      18477      13582Swap:         7999          6       

The current server has two nodes node 0 and node 1,node 0 Local memory of approximately 16gb,node 1 of local memory is about 16GB, you can see that the system has a total of 32GB memory

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

The NUMA memory allocation strategy is divided into the following 4 types:

(1) Default: Always Assign on local node (allocated on node running on current process)

(2) Bind bind: Force assignment to the specified node

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

(4) Priority preferred: allocation on the specified node, failure in other nodes

Displays the current system NUMA policy:

Because the NUMA default memory allocation policy is allocated in the local memory of the CPU on which the process resides, it can result in unbalanced memory allocations between CPU nodes, which causes swap to occur when a CPU node is out of memory, rather than allocating memory from the remote node, which is the swap insanity phenomenon.

MySQL is a database of single-process multithreaded architectures, and when NUMA uses the default memory allocation policy, the MySQL process is and will only be assigned to a NUMA node. Assuming that the MySQL process is assigned to Node 1, the local memory of this node is 8GB, while MySQL is configured with 14GB memory, MySQL allocates 14GB memory, and exceeds the node local memory portion (14gb-8gb= 6GB) The Linux system prefers to use swap and does not use the physical memory of other nodes. In this case, it can be observed that although the total amount of physical memory available is much, the MySQL process has already started using swap.

MySQL features support for NUMA is not good, if the single machine running only one MySQL instance, you can choose to turn off NUMA, there are two ways to shut down:

(1) hardware layer, set off in BIOS

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

Modify/etc/grub.conf, add Numa=off

[[email protected] ~]# vim/etc/grub.conf[[email protected] ~]# 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 Lang=en_us. UTF-8 rd_no_md quiet Sysfont=latarcyrheb-sun16 rhgb crashkernel=auto  numa=off

or modify the NUMA memory allocation policy to interleave with 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

This 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 the appropriate MySQL memory parameters, and use the bound memory allocation test to force the memory to be allocated on the local node.

5.vm.swappiness Adjustment

Swappiness is the strategy by which the operating system controls physical memory exchange. The value it allows is a percentage of the value, the minimum is 0, the maximum run 100, and the value defaults to 60. Vm.swappiness set to 0 means that as little as possible swap,100 means to swap out inactive memory pages as much as possible.
Specifically, when the memory is basically full, the system will determine whether to swap out the inactive memory that is seldom used in memory, or to release the cache of the data. Cache is cached in the data read from the disk, according to the program's local principle, the data may be read in the next; inactive memory, as the name implies, is those that are mapped by the application, but "long time" unused memory.

We can use Vmstat to see the amount of inactive memory:

[Email protected] ~]# Vmstat-an 1 5procs-----------memory-------------Swap-------io------System-------CPU-----R
   b   swpd   free  inact active   si   so    bi    bo   in   CS US sy ID WA St 0  0   4892 1194972 234208 492404    0    0     5    [  0 1]  0 0 0  0   4892 1194964 234208 492420    0    0     0     0  0  0 0 0 0 0   4892 1194964 234208 492420    0    0     0     0   (  0  0) 0 0 0  0   4892 1194964 234208 492420    0    0     0  0  0 0 + 0 0 0  0   4892 1194964 234208 492420    0    0     0     0  0  0 + 0  

With/proc/meminfo you can see more detailed information:

[Email protected] ~]# Cat/proc/meminfo | Grep-i inact Inactive:         234188 kbinactive (anon):     3228 kbinactive (file):   

In Linux, memory may be in three states: Free,active and inactive. As we all know, Linux kernel maintains many LRU lists internally to manage memory, such as Lru_inactive_anon, Lru_active_anon, Lru_inactive_file, Lru_active_file, lru_ Unevictable. Where Lru_inactive_anon, Lru_active_anon is used to manage anonymous pages, Lru_inactive_file, Lru_active_file is used to manage page caches pages cache. Depending on the access of the memory pages, the system kernel will move active active memory to the inactive list at irregular intervals, and the inactive memory can be swapped into swap.
In general, MySQL, especially InnoDB management memory cache, it occupies more memory, not often access to a lot of memory, these memory if the Linux wrong swap out, will waste a lot of CPU and IO resources. InnoDB to manage the cache itself, the cache's file data takes up memory and has little benefit for InnoDB.
So, we'd better set the vm.swappiness=0 on the MySQL server.

We can do this by adding a line to the sysctl.conf:

[[email protected] ~]# echo "vm.swappiness = 0" >>/etc/sysctl.conf[[email protected] ~]# sysctl-p

Another approach is to innodb enable large memory pages, and the above method has the same effect, here is not too much to introduce.

6.CPU optimization

Check if the CPU has power saving options turned on

 [[email protected] ~]# grep-e ' ^model name|^cpu MHz '/proc/cpuinfomodel Name:intel (R) Xeon (r) CPU         L5520 @ 2.27GHzcpu Mhz:2266.602model Name:intel (R) Xeon (r) CPU L5520 @ 2.27GHzcpu MHz      : 2266.602model Name:intel (R) Xeon (r) CPU L5520 @ 2.27GHzcpu Mhz:2266.602model Name           : Intel (R) Xeon (R) CPU L5520 @ 2.27GHzcpu Mhz:2266.602model Name:intel (R) Xeon (R) CPU         L5520 @ 2.27GHzcpu Mhz:2266.602model Name:intel (R) Xeon (r) CPU L5520 @ 2.27GHzcpu MHz : 2266.602model Name:intel (R) Xeon (r) CPU L5520 @ 2.27GHzcpu Mhz:2266.602model Name: Intel (R) Xeon (r) CPU L5520 @ 2.27GHzcpu mhz:2266.602[[email protected] ~]# 

If the frequency of the CPU is found to be different from its nominal frequency, then the power-saving mode is turned on. Energy-saving mode: Operating system and CPU hardware, when the system is not busy, in order to save power and reduce the temperature, it will be the CPU frequency reduction. For MySQL, it could be a disaster. To ensure that MySQL can take full advantage of CPU resources, it is recommended to set the CPU to maximum performance mode. This setting can be set in the BIOS and operating system, and of course, setting this option in the BIOS is better and more thorough

7. Selection of memory allocation algorithms

The default memory allocation is that malloc for C now also has many optimized memory allocation algorithms, such as: Jemalloc & Tcmalloc

Take Tcmalloc as an example, it is one of the Google-perftools tools (Gperftools four tools: Tcmalloc, Heap-checker, Heap-profiler, and Cpu-profiler), which can be Google.code get the source code, the installation process no longer say, only use tcmalloc words, you can not compile the other three tools, 64-bit system needs to install the dependency package Libunwind first.

Tcmalloc after the installation is complete, execute the following command:

[[email protected] ~]# echo "/usr/local/lib" >/etc/ld.so.conf.d/usr_local_lib.conf[[email protected] ~]#/sbin/ Ldconfig

Modify the MySQL startup script Mysqld_safe to add a line after the "# Executing Mysqld_safe" line:

Export ld_preload= "/usr/local/lib/libtcmalloc.so"

The goal is to load the Tcmalloc dynamic library before starting MySQL.

Now restart the MySQL check process:

[Email protected] ~]# Lsof-n | grep tcmallocmysqld    17890      root  mem       REG        8,7    1891417      20400/usr/local/lib/ libtcmalloc.so.4.2.4

You can see that MySQL has loaded the tcmalloc.

8. File descriptors

Linux-kernel uses the file descriptor (descriptor) to access the file. The file descriptor is a non-negative integer. When you open an existing file or create a new file, the kernel returns a file descriptor. Read-write files also need to use file descriptors to specify which files to read and write. For less busy servers, a large number of files need to be opened, resulting in a limited number of file descriptors, which could result in inaccessible new files, or restricted connections.

To view the current file descriptor:

[[email protected] ~]# ulimit-n1024[[email protected] ~]# cat/proc/sys/fs/file-nr544     0       101054

The first line results in the current file descriptor, the first number in the second row indicates the number of open file descriptors that the current system has allocated, the second number is freed after allocation (no longer used), and the third number equals File-max.

To modify the file descriptor limit, you can add the following two lines in/etc/security/limits.conf:

[Email protected] ~]# vim/etc/security/limits.conf..........................* soft nofile 10240* hard Nofile 10240

After the save exits, log back in to the server and you can see:

[Email protected] ~]# ulimit-n10240

9. A little bit of hardware tips

(1) More memory: Do not expect to put all the data into memory, but try to let the memory into the library of hot data 80%.

(2) Faster CPUs: More CPUs don't always have an immediate effect (MySQL 5.6 can use 64 cores, MySQL can only run on one CPU per query), but faster CPUs are all we need.

(3) Faster storage devices: Many times the IO capability is the bottleneck of MySQL, there are SSDs or solid-state cards to choose from, please do not hesitate.

(4) RAID card: RAID 10 is our first choice, and please use live raid, enable writeback, which is good for accelerating redo log, binary log, data file.

Reference article:

Http://www.cnblogs.com/gomysql/p/3643726.html

Http://www.cnblogs.com/littlehb/archive/2013/04/14/3020336.html

Linux optimizations for MySQL