Shulou(Shulou.com)06/01 Report--

This article introduces the knowledge about "how to optimize Mysql running environment on Linux". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Those who have been exposed to Mysql know that it is a database. Many users know how to use Mysql database, but they know little about the optimization of Mysql running environment. If you want to master Mysql database, you should also understand some of the optimization of Mysql running environment.

1. Modify Linux default IO scheduling algorithm.

Linux default IO scheduling algorithm is cfq, need to be modified to dealine, if SSD or PCIe-SSD devices, need to be modified to noop, you can use the following two modifications.

1, online dynamic modification, restart failure.

echo "deadline" 》 /sys/block/sda/queue/scheduler

Tips: sda here represents the hard disk you need to modify, according to your actual situation.

Modify/etc/grub.conf to take effect permanently.

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

Focus on the elevator parameter. If you set the kernel, you need to restart the system to take effect.

Finally, you can observe the difference between before and after modification through cat /sys/block/sda/queue/scheduler.

Second, expand the file descriptor

This parameter is frequently modified, and highly concurrent programs will modify it.

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

ulimit -n 51200

2. Modify the configuration file and take effect permanently.

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

* hard nofile 51200

The requested URL/etc/security/limits.conf was not found on this server.

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

* hard nproc 51200

Finally modify the/etc/pam.d/login file to add

session required /lib64/security/pam_limits.so

Restart the system and use ulimit -a to see if it works.

III. Disable numa feature

NUMA of the new generation architecture is not suitable for running databases. It is intended to improve memory utilization, but the actual effect is not good. Instead, it may lead to the memory of one CPU remaining, but the other is insufficient, causing swap problems. Therefore, it is recommended to close or modify NUMA scheduling mechanism.

1. Modify/etc/grub.conf to close NUMA and take effect after restarting.

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

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

The new mysqld_safe script comes with the following content, so you don't need to add it, 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 not supported on this platform"

exit 1

fi

4. Modify Happiness Settings

Swappiness is a kernel parameter in linux that controls the policy of swapping out physical memory. It allows a percentage value, the minimum is 0, the maximum is 100, the default value is 60.

# sysctl -a | grep swappiness

vm.swappiness = 60

What exactly does this setting mean?

vm. swap is set to 0 to minimize swap usage, and 100 to swap inactive memory pages into swap or free cache (cache is similar to read-ahead files).

Inactive memory means memory mapped by a program but unused for a "long time." We can use vmstat to see how much inactive memory 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

Before Centos 7, this value was recommended to be set to 0, but in newer versions of the kernel, this setting may cause OOM (memory overflow), and then the kernel will kill the mysqld process that uses the most memory.

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

Add a line to the/etc/sysctl.conf file.

vm.swappiness = 1

# sysctl -p //Run this command to make the configuration take effect immediately.

Optimize file system mount parameters.

First of all about what file system to use, a lot of tests online, fireworks. The general conclusion is as follows.

xfs> ext3:xfs performance is better than ext3

ext4> ext3: ext4 performance is better than ext3

ext4 ？？？Xfs: xfs or ext4, which is better? I can't say exactly...

Then there are the parameters used to mount the file system:

defaults, noatime, noditime, barrier=0 (nobarrier used on xfs)

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

Noatime means no record of access time, nodiratime means no record of access time for directories.

barrier=0, means to turn off the barrier function. The main purpose of barrier is to ensure the security of disk write data, but it will reduce performance. If there is a battery backup power supply such as BBU to ensure that the control card does not lose power instantly, then this function can be safely turned off.

You can check to see if the configuration works with a single command.

# 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

"How to optimize Mysql runtime environment on Linux" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.