Tips for optimizing Mysql runtime environment on Linux

I have contacted Mysql to know that it is a database. Many users know how to use Mysql database, but little is known about the optimization of Mysql runtime environment. If you want to master Mysql Database, the optimization of Mysql runtime environment should also understand some, the following small series will introduce you to the Linux optimization Mysql operating environment.

First, modify the default Linux IO scheduling algorithm.

The default IO scheduling algorithm for linux is cfq, which needs to be modified to dealine. If it is an SSD or PCIe-SSD device, it needs 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: The sda ​​here represents the hard drive you need to modify, modify it according to your actual situation.

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

Mainly concerned about the parameter of the elevator, if you set the kernel, you need to restart the system to take effect.

Finally, you can observe the difference between pre-modification and post-modification by cat /sys/block/sda/queue/scheduler.

Second, expand the file descriptor

This is a frequently modified parameter, high-concurrency programs 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 to take effect permanently.

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

* hard nofile 51200

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

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

After rebooting the system, use the ulimit -a command to check whether it takes effect.

Third, disable the numa feature

The next-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 is still There is a surplus, but the other one is not enough, and the swap problem occurs, so it is recommended to turn off or modify the scheduling mechanism of NUMA.

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

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 the mysqld process to start. NUMA scheduling mechanism, for example.

The following content is included in the new mysqld_safe script, so you don't need to add it. You can check 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< Br>

# 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
Previous12Next Total 2 Pages