MySQL Operating environment optimization (Linux system)

This article mainly introduces the MySQL Operation Environment optimization (Linux system), this article optimizes the Linux default IO scheduling algorithm, expands the file descriptor, disables the NUMA characteristic, modifies the swappiness setting, optimizes the file system mount parameter and so on the configuration, needs the friend to be possible to refer to under

　　First, modify the Linux default IO scheduling algorithm.

Linux default IO scheduling algorithm for CFQ, need to modify to Dealine, if it is SSD or PCIE-SSD equipment, need to modify to NoOp, you can use the following two ways to modify.

1, online dynamic modification, restart failure.

The code is as follows:

echo "Deadline" >/sys/block/sda/queue/scheduler

Tips: The SDA here represents the hard drive you need to modify, depending on your actual situation.

2, modify/etc/grub.conf, permanent entry into force.

Modify the/etc/grub.conf configuration file and add a configuration to the kernel line, for example:

The code is as follows:

kernel/vmlinuz-2.6.32-279.el6.x86_64 ro root=uuid=e01d6bb4-bd74-404f-855a-0f700fad4de0 Rd_NO_LUKS rd_NO_LVM LANG=en _us. UTF-8 RD_NO_MD sysfont=latarcyrheb-sun1

6 Crashkernel=auto keyboardtype=pc keytable=us rd_no_dm elevator=deadline rhgb quiet

The main concern elevator this parameter, set the kernel of the words need to restart the system in order to take effect.

Finally, it can be observed by Cat/sys/block/sda/queue/scheduler, before and after the change of the difference.

　　Ii. enlargement of the document descriptor

This is a constant modification of the parameters, the high concurrency of the program will be modified.

1, dynamic modification, restart failure, can only use root, and the current session is valid.

The code is as follows:

Ulimit-n 51200

2, modify the configuration file, the permanent entry into force.

Add a row to the/etc/security/limits.conf configuration file

The code is as follows:

* Hard Nofile 51200

To enlarge the number of open processes nprocess/etc/security/limits.conf

Add a row to the/etc/security/limits.conf configuration file

The code is as follows:

* Hard Nproc 51200

Last modified/etc/pam.d/login file add

The code is as follows:

Session required/lib64/security/pam_limits.so

After restarting the system, use the ULIMIT-A command to see if it takes effect.

　　Third, disable NUMA features

The new-generation architecture of NUMA is not suitable for running the database, it is intended to improve memory utilization, but the actual effect is not good, but may lead to a CPU memory remaining, but the other is not enough, the problem of swap, it is recommended to close or modify the NUMA scheduling mechanism.

1, modify/etc/grub.conf close Numa, restart after the entry into force.

The code is as follows:

kernel/vmlinuz-2.6.32-279.el6.x86_64 ro root=uuid=e01d6bb4-bd74-404f-855a-0f700fad4de0 Rd_NO_LUKS rd_NO_LVM LANG=en _us. UTF-8 RD_NO_MD sysfont=latarcyrheb-sun1

6 Crashkernel=auto keyboardtype=pc keytable=us rd_no_dm elevator=deadline numa=off rhgb quiet

2. Modify the/etc/init.d/mysql or Mysqld_safe script to set up the NUMA scheduling mechanism when starting the mysqld process, for example.

In the new version of the Mysqld_safe script with the following content, so do not need to add, you can see if your Mysqld_safe script has the following content.

The code is as follows:

If True && test $numa _interleave-eq 1

Then

# Locate Numactl, ensure it exists.

if! My_which numactl >/dev/null 2>&1

Then

Log_error "Numactl command not found, required For–numa-interleave"

Exit 1

# Attempt to run a command, ensure it works.

Elif! Numactl–interleave=all true

Then

Log_error "Numactl failed, check if NUMACTL is properly installed"

Fi # Launch mysqld with Numactl.

cmd= "$cmd Numactl–interleave=all"

Elif Test $numa _interleave-eq 1

Then

Log_error "–numa-interleave is isn't supported on this platform"

Exit 1

Fi

　　Iv. Modify swappiness Settings
swappiness is a kernel parameter of Linux that controls the strategy of swapping out physical memory. It allows a percentage of the value, the smallest is 0, the largest is 100, the default is 60. Can be observed by following command

The code is as follows:

# Sysctl-a | grep swappiness

Vm.swappiness = 60

What effect does this set value have?

Vm.swappiness is set to 0 to minimize the use of swap,100 means to swap inactive memory pages into swap or to release the cache (cache is similar to a read-only file).

Inactive memory means that the program maps, but "long time" unused memory. We can use Vmstat to see how much inactive memory there is in the system.

The code is as follows:

# vmstat-a 1

Procs ——— –memory ———-— swap–-–io--–system–-–cpu--

R B swpd free inact active si so bi bo in CS us sy ID WA St

0 0 16930764 192752 3673320 28601732 0 0 19 31 0 0 1 0 99 0-0

1 0 16930764 192752 3673320 28601732 0 0 0 0 1136 271 0 0 100 0-0

0 0 16930764 192748 3673320 28601732 0 0 0 56 1167 325 1 0 99 0-0

0 0 16930764 192740 3673320 28601732 0 0 0 40 1039 164 0 0 100 0-0

This value is recommended to be set to 0 before Centos7, but in the new version of the kernel, this setting may cause oom (memory overflow), and then kernel will kill the mysqld process with the most memory.

So now this value is recommended to set to 1, set the method as follows:

Adds a row to the/etc/sysctl.conf file.

The code is as follows:

vm.swappiness = 1

# Sysctl-p//Run this command to have the configuration take effect immediately.

　　Optimize file system mount parameters.

First about what file system to use, online a lot of testing, fireworks blinds. The approximate conclusion is as follows.

XFS > EXT3:XFS performance is superior to ext3

Ext4 > EXT3:EXT4 performance is superior to ext3

Ext4??? Xfs:xfs and Ext4 who is better? I say it's not accurate ...

Then you mount the parameters that the file system uses:

The code is as follows:

Defaults,noatime,nodiratime,barrier=0 (using Nobarrier on XFS)

File system mount parameters are modified in the/etc/fstab file, and the restart time takes effect.

Noatime indicates that the access time is not logged, Nodiratime does not record the access time for the directory.

Barrier=0, which indicates that the barrier feature is turned off. The main purpose of barrier is to ensure the security of disk write data, but it can degrade performance. If there are bbu such as battery backup power to ensure that the control card is not instantaneous power off, then this function can be assured that the bold shutdown.

You can use the command to see if the configuration is effective.

The code is as follows:

# cat/proc/mounts

Rootfs/rootfs RW 0 0

PROC/PROC proc Rw,nosuid,nodev,noexec,relatime 0 0

Sysfs/sys Sysfs rw,seclabel,nosuid,nodev,noexec,relatime 0 0

Devtmpfs/dev Devtmpfs rw,seclabel,nosuid,relatime,size=8188388k,nr_inodes=2047097,mode=755 0 0

Devpts/dev/pts devpts rw,seclabel,relatime,gid=5,mode=620,ptmxmode=000 0 0

Tmpfs/dev/shm Tmpfs rw,seclabel,nosuid,nodev,relatime 0 0

/DEV/SDA3/EXT4 rw,seclabel,relatime,noatime,nodiratime,barrier=0,data=ordered 0 0

None/selinux selinuxfs rw,relatime 0 0

Devtmpfs/dev Devtmpfs rw,seclabel,nosuid,relatime,size=8188388k,nr_inodes=2047097,mode=755 0 0

/proc/bus/usb/proc/bus/usb Usbfs rw,relatime 0 0

None/proc/sys/fs/binfmt_misc Binfmt_misc rw,relatime 0 0

/dev/sda1/boot ext4 rw,seclabel,noatime,nodiratime,barrier=0,data=ordered 0 0