MySQL optimized Linux system level tuning

Http://www.cnblogs.com/digdeep/p/4885948.html

MySQL typically runs on Linux systems. Tuning for MySQL is generally divided into Linux OS-level tuning and MySQL-level tuning (and of course, architectural, business, application-level tuning). The operating system is mainly to manage and allocate hardware resources, so the system level tuning includes hardware tuning, that is, tuning hardware parameters. Linux system level tuning is generally divided into CPU tuning, memory tuning, disk tuning, network tuning, Linux background service tuning and so on.

1. CPU Tuning

1.1 CPU Energy-saving mode

The CPU in the server environment must turn off the power saving mode, and the power saving mode does not fit in the server environment. Because he will automatically give the CPU to fall into hibernation mode! General notebook computer, mobile phone in order to life time, just need. There are two ways to turn off the power-saving mode of the CPU:

1) in the BIOS settings, completely shut down;

2) Turn off the services Cpuspeed and irqbalance in Linux;

[Email protected] ~]# chkconfig--level cpuspeed off[[email protected] ~]# Chkconfig | grep cpuspeedcpuspeed        0:off   1:on    2:off   3:off   4:off   5:off   6:off[[email protected] ~]# Chkconfig--level irqbalance off[[email protected] ~]# Chkconfig | grep irqbalanceirqbalance      0:off   1:off   2:off   3:off   4:off   5:off   6:off

Cpuspeed is the backend service that is responsible for CPU power saving, while Irqbalance is responsible for sending interrupts to a CPU that does not hibernate when Cpuspeed adjusts some or several CPUs into sleep mode. Shutting down the irqbalance will send all interrupts evenly to all CPUs.

1.2 Turning off the NUMA of the CPU

NUMA can cause mysqld to generate swap and severely affect performance. Because the CPU and memory of the NUMA architecture are bind, if there is not enough memory in the CPU's own node, the swap is generated, even if there is a large amount of free memory in other node, it will not be used. This is a flaw in NUMA. There are several ways to turn off the CPU NUMA:

1) in the Biso configuration;

2) Numactl--interleave=all

[Email protected] ~]# Numactl--interleave=all

Interleave=all is actually the memory in each node of the NUMA architecture and re-virtual into a shared memory for allocation, but unlike SMP, because there are inter-connect between every two node, This avoids the drawbacks of SMP architecture bus contention.

To see if the CPU is dormant causing a decrease in frequency:

[[email protected] ~]# cat/proc/cpuinfoprocessor       : 0vendor_id       : Genuineintelcpu family:      6model           : 23model name      : Intel (R) Xeon (r) CPU           E5405  @ 2.00GHzstepping        : 10cpu MHz         : 1995.288

See if the CPU above mhz:1995.288 is the same as the actual.

See also: http://www.cnblogs.com/digdeep/p/4847484.html

2. Memory tuning

The main memory is to prevent the swap from occurring. Because of the swap, from memory access directly down to the hard disk access, the speed of random access decreased by 10 6 times, that is, 100,000 times times. Sequential access is reduced by about 10 times times.

2.1 Prevent Swap:

1) Turn off the NUMA of the CPU to prevent the swap caused by NUMA;

2) set Vm.swappiness=1; Add in/etc/sysctl.conf: Vm.swappiness=1, then sysctl-p; You can also sysctl Vm.swappiness=1 temporary changes, and then sysctl-p

Note: The default behavior of vm.swappiness = 0 in Rhel/centos 6.4 and the updated kernel is modified, if you continue to set vm.swappiness = 0,

May cause system memory to overflow, causing MySQL to be accidentally killed. So here we set the 1 instead of the traditional 0.

3) Set/proc/$ (Pidof-s mysqld)/Oom_adj to a smaller value ( -15,-16 or-17) to try to avoid MySQL being shut down due to insufficient memory

[Email protected] ~]# echo-17 >/proc/$ (pidof mysqld)/oom_adj[[email protected] ~]# cat/proc/$ (pidof mysqld)/oom_adj -17

The variables in this Oom_adj range from 15 to-16. Bigger is easier to kill when memory is low. 17 means that the process will not be killed and kill other processes when memory is low.

4) Use Hugepage to avoid swap out; But Huagepage also has a price (causing page contention to escalate).

2.2 Set memory in BIOS to maximum performance mode;

2.3 Adjusting disk cache behavior for flushing to disks

Because Linux defaults to a large number of file caches, that is, a lot of memory for disk cache. This will affect MySQL's use of memory. So we can adjust the disk cache to be refreshed when the dirty block reaches a large percentage. vm.dirty_background_ratio=10; The default value is 10, which means that when the number of dirty pages in disk cache reaches 10%, the Pdflush kernel thread is called to flush disk cache asynchronously ; vm.dirty_ratio=20; Indicates that when the number of dirty pages in disk cache reaches 20%, a synchronized disk cache refresh will block the IO operation of the application process in the system ! We can lower the vm.dirty_background_ratio to reduce the effect of disk cache on MySQL memory, but it may increase the drive IO because the file cache is reduced and the page fault of other processes is increased; (Vm.dirty_ Background_ratio/vm.dity_ratio with Backround to indicate asynchronous flush, without a synchronous flush. )

See also: http://www.cnblogs.com/digdeep/p/4850460.html

3. Tuning of disk IO

The tuning of disk IO involves tuning the file system and tuning the disk.

3.1 Tuning of file systems

1) file System selection: Ext4 performance is better than XFS prior to rhel6.4 because XFS has a lock contention bug. But at 6.4, the XFS bug was fixed. Tests show that XFS performance is better than EXT4.

2) file Mount option: When the file is mounted, noatime,nodiratimeis enabled and can be modified in/etc/fstab. A description of the relevant options is available in the Man Mount Manual.

4) file Mount option: Barrier/nobarrier (can enable nobarrier option to provide performance if battery protection is available)

If your disks is battery-backed in one-or another, disabling barriers may safely improve performance. The Mount options

"Barrier" and "nobarrier" can also is used to enable or disable barriers, for consistency with other EXT4 mount options.

5) file Mount option: Data={journal|ordered|writeback}, enabling Data=writeback may improve performance if battery protection is available.

Journal:all data is committed into the journal prior to being written into the main filesystem.
Ordered:this is the default mode. All data are forced directly out to the main file system prior to its meta-data being committed

to the journal.
Writeback:data ordering is not preserved-data could be written to the main filesystem after its metadata have been commi Tted to

The journal. This was rumoured to be the highest-throughput option. IT guarantees internal filesystem integrity, however

It can allow the old data into appear in files after a crash and journal recovery.

6) XFS mount option: inode64, if using an XFS file system, and a partition capacity greater than 1T, you need to use the INODE64 option to mount, otherwise it may be wrong to report "Disk full"

See also: http://imysql.cn/2013/02/21/using-xfs-with-large-partition-for-backup.html

See also: http://www.cnblogs.com/digdeep/p/4857987.html

3.2 Tuning of disks

1) IO scheduling algorithm: MySQL server must not use the default CFQ scheduling algorithm . If it is an SSD, then the NoOp scheduling algorithm should be used, and if it is a disk, the deadline scheduling method should be used.

Modification Method:

[Email protected] ~]# cat/sys/block/sda/queue/schedulernoop anticipatory deadline [Cfq][[email protected] ~]# echo NoOp >  /sys/block/sda/queue/scheduler[[email protected] ~]# Cat/sys/block/sda/queue/scheduler[noop] Anticipatory Deadline CFQ

This is a temporary modification and the restart is invalid. Permanently modified, you need to modify the Elevator=deadline or NoOp in the file/boot/grub/menu.lst:

# Vi/boot/grub/menu.lst
Kernel/boot/vmlinuz-2.6.18-8.el5 ro root=label=/ elevator=deadline rhgb quiet

2) Increase the memory, can make MySQL cache larger content, reduce IO operation.

3) disk RAID10 or change SSD;

4) The appropriate use of NoSQL (REDIS/MONGDB/SSDB) to reduce the burden of MySQL, you can also structure master-slave reduce the master IO pressure.

5) Use Memcache or Reids in front of MySQL to add a layer of cache, reduce disk IO;

6) When there is an array card, set the array write policy to WB, or even Force WB (if there is a double-power protection, or the data security requirements are not particularly high)

See also: http://www.cnblogs.com/digdeep/p/4863502.html

4. Network Tuning

Network tuning is divided into hardware level and TCP/IP software level parameter tuning.

4.1 Network hardware tuning:

1) Change the delay smaller, throught larger network card;

2) Dual NIC binding, load balancing and high availability;

4.2 TCP/IP parameter tuning:

1) Socket buffer parameter adjustment:

1>/proc/sys/net/ipv4/tcp_mem TCP global cache, in memory page (4k);

Corresponding kernel parameters: Net.ipv4.tcp_mem, can be modified in the/etc/sysctl.conf;

2>/proc/sys/net/ipv4/tcp_rmem receive buffer in bytes

Corresponding kernel parameters: Net.ipv4.tcp_rmem, can be modified in the/etc/sysctl.conf;

3>/proc/sys/net/ipv4/tcp_wmem receive buffer in bytes

Corresponding kernel parameters: Net.ipv4.tcp_wmem, can be modified in the/etc/sysctl.conf;

4>/proc/sys/net/core/rmem_default Receive buffer default size, Unit bytes

Corresponding kernel parameters: Net.core.rmem_default, can be modified in the/etc/sysctl.conf;

5>/proc/sys/net/core/rmem_max Receive buffer maximum size, Unit bytes

Corresponding kernel parameters: Net.core.rmem_max, can be modified in the/etc/sysctl.conf;

6>/proc/sys/net/core/wmem_default Send buffer default size, Unit bytes

Corresponding kernel parameters: Net.core.rmem_default, can be modified in the/etc/sysctl.conf;

7>/proc/sys/net/core/wmem_max send buffer maximum size, per byte

Corresponding kernel parameters: Net.core.rmem_max, can be modified in the/etc/sysctl.conf;

2) Offload configuration :

The tso,checksum and other functions are given to the NIC hardware to complete:

Ethtool-k eth0 Rx On|off

Ethtool-k eth0 TX On|off

Ethtool-k Eth0 TSO On|off

3) Adjust the receive queue and send queue of the network card:

1> Receive queue:/proc/sys/net/core/netdev_max_backlog corresponding kernel parameter: Net.core. Netdev_max_backlog

2> Send queue:

View size: ifconfig eth0 | grep txqueue

Modified size: ifconfig eth0 txqueuelen 20000

4) The number of large SYN half-connection tcp_max_syn_backlog :

Sysctl-w Net.ipv4. Tcp_max_syn_backlog=4096

You can also configure it in the/etc/sysctl.conf file.

5) Net.core.somaxconn :

This parameter is a 3-time handshake, a connection has been established, the number of waits to be accepted and then processed. The default is 128, we can adjust to 65535, even larger. That is, Grams has a connection that accommodates more waiting for processing .

MTU size tuning :

If both the NIC and the network interface layer on both sides of the TCP connection support the large MTU, then we can configure the network to use a larger MTU size and not cause the cut to reassemble the send.

Configuration command: Ifconfig eth0 MTU 9000 up

6) Close_wait and time_wait for TCP connections

If there are too many close_wait and time_wait states for the TCP connection, you need to tune the TCP keepalive related parameters and the TCP recycle-related parameters respectively.

The tuning of TCP/IP is extremely complex, see blog: http://www.cnblogs.com/digdeep/p/4869010.html

5. Tuning of various backend daemon in Linux systems

Linux system has a variety of background daemon, that is, a variety of service, for the MySQL server, a lot of unnecessary service can be shut down.

MySQL's minimal back-end service can only be:Crond,sshd,rsyslog,network,sysstat

Of course if there is a need can be added other services.

Use chkconfig--level servicename off; can be closed. 35 indicates shutdown at level = 3 and Levels =5 at RunLevel.

Deamon tuning can be referenced by: http://wubx.net/category/optimize/

6. Tuning of Ulimit resource limits

Ulimit provides control over the resources available to the shell and to processes started by it, on systems th At allow such control.

The soft limit is the value of the kernel enforces for the corresponding resource. The hard limit acts as a ceiling for the soft limit.

The Ulimit command controls the limits of the resources that the shell can obtain, and also controls the resources that can be obtained by the processes started in the shell. Divided into soft limit and hard limit.

Ulimit's Handbook:

User Limits-limit The use of system-wide resources.    Syntax Ulimit [-ACDFHLMNPSSTUV] [limit]options-s change and report the soft limit associated with a resource.    -H change and report the hard limit associated with a resource.    -A All current limits is reported.    -c The maximum size of core files created.    -d The maximum size of a process ' s data segment. -F The maximum size of files created by the shell (default option)-L The maximum size that can is locked into memor    Y.-M The maximum resident set size.    -N The maximum number of open file descriptors.    -P The pipe buffer size.    -s the maximum stack size.    -T the maximum amount of CPU time in seconds.    -U The maximum number of processes available to a single user. -V The maximum amount of virtual memory available to the process. Ulimit provides control over the resources available to the shell and to processes started by it, on systems CH Control. The soft LImit is the value of the kernel enforces for the corresponding resource. The hard limit acts as a ceiling for the soft limit. An unprivileged process could only set it soft limit to a value of the range from 0 up to the hard limit, and (irreversibly ) lower its hard limit. A privileged process can make arbitrary changes to either limit value. If limit is given, it is the new value of the specified resource. Otherwise, the current value of the soft limit for the specified resource is printed, unless the '-H ' option is SUPPLIED.W Hen setting new limits, if neither '-h ' nor '-s ' is supplied, both the hard and soft limits are set. restricting per User processes (-u) can is useful for limiting the potential effects of a fork bomb. Values are in 1024-byte increments, except for '-t ', which is in seconds, '-P ', which is in units of 512-byte blocks, and '-n ' and '-U ', which is unscaled values. The return status is zero unless an invalid option was supplied, a non-numeric argument other than UnlimitEd is supplied as a limit, or an error occurs while setting a new limit.ulimit are a bash built in command. 

To view all current restrictions:

[[email protected] ~]# ulimit-acore file size          (blocks,-c) 0data seg size           (Kbytes,-D) unlimitedscheduling Priori Ty             (-e) 0file size               (blocks,-f) unlimitedpending signals (-I                 ) 7908max locked memory       (Kbytes, L) 64max me Mory Size         (Kbytes,-m) unlimitedopen files                      (-N) 1024pipe size            (bytes,-p) 8POSIX message queues     (by TES,-Q) 819200real-time priority              (-R) 0stack size              (Kbytes,-s) 10240cpu time               (seconds,-T) Unlimitedmax u Ser processes              (-u) 7908virtual memory          (Kbytes,-V) unlimitedfile locks                      (-X) Unlimited

In general, we can easily be limited on open files to make an error. We can temporarily modify and permanently modify:

Ulimit-n 8192

Permanent modification, need to modify the file /etc/security/limits.conf, plus:

* Soft nofile 10240* hard Nofile 20480

After the change, you also need to add a line to VI /etc/pam.d/login :

Session Required Pam_limits.so

7. Summary:

Tuning of Linux systems and hardware, in addition to some general tuning. Other such as TCP/IP tuning, we first want to use the relevant various commands to find out where the bottleneck, and then the right remedy.

Reference:

http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=207641943&idx=1&sn= d124286ea8811950be5a872d57a27357

http://wubx.net/category/optimize/

Http://imysql.cn/2015/05/24/mysql-optimization-reference-1.shtml

Http://imysql.cn/2013/02/21/using-xfs-with-large-partition-for-backup.html

MySQL optimized Linux system level tuning