Optimizing MySQL running environment on Linux lets you know more about MySQL

Access to MySQL is to know that it is a database, a lot of users know how to use the MySQL database, but the MySQL operating environment is not known to optimize the optimization, if you want to master MySQL database, the operation of the MySQL environment optimization also need to know some, The following small series to introduce you to Linux to optimize the operating environment of the MySQL method.

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.

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:

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.

Ulimit-n 51200

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

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

* 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

* Hard Nproc 51200

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

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.

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.

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 for swapping out physical memory. It allows a value of a percentage, the smallest is 0, the largest is 100, and the default is 60. You can use the following command to observe

# 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.

# 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.

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:

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.

# 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

Above is the Linux optimized MySQL running environment method introduced, through to the MySQL Operation environment optimization, can reduce the MySQL load.