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

In this issue, the editor will bring you about how the CentOS system optimizes the parameters of mysql. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Kernel related parameters (/ etc/sysctl.conf)

The following parameters can be placed directly at the end of the sysctl.conf file:

Net.core.somaxconn = 65535

Net.core.netdev_max_backlog = 65535

Net.ipv4.tcp_max_syn_backlog = 65535

Speed up the recycling of TCP connections:

Net.ipv4.tcp_fin_timeout = 10

Net.ipv4.tcp_tw_reuse = 1

Net.ipv4.tcp_tw_recycle = 1

Default and maximum values for receive and send buffer sizes for TCP connections:

Net.core.wmem_default = 87380

Net.core.wmem_max = 16777216

Net.core.rmem_default = 87380

Net.core.rmem_max = 16777216

Reduce the amount of TCP resources occupied by failed connections and speed up the efficiency of resource recovery

Net.ipv4.tcp_keepalive_time = 120

Net.ipv4.tcp_keepalive_intvl = 30

Net.ipv4.tcp_keepalive_probes = 3

Kernel.shmmax = 4294967295

One of the most important Linux kernel parameters that defines the maximum value of a single shared memory segment.

Note:

1. This parameter should be set large enough to accommodate the entire Innodb under one shared memory segment.

Size of the buffer pool

two。 For 64-bit linux systems, the maximum preferred value is the physical memory value-1byte, which is recommended

The value is more than half of the physical memory. Generally, the value is larger than the size of the Innodb buffer pool. You can take the physical memory-1byte.

Vm.swappiness = 0

This parameter will have a significant impact on performance when there is insufficient memory.

Linux system memory Exchange:

When a Linux system is installed, there is a special disk partition called the system swap partition.

Using the free-m command, you can see that swap is the memory swap.

Function:

When the operating system does not have enough memory, it will write some virtual memory to the swap area of the disk.

Memory swapping occurs.

There is some controversy over whether to use swap partitions on MySQL servers:

Completely disable swap partitions on the Linux system where the MySQL service resides.

Risks:

1. Degrade the performance of the operating system

two。 Easy to cause memory overflow, crash, or be dropped by the operating system kill

Conclusion:

It is necessary to keep the swap on the MySQL server, but you need to control when the swap is used.

Vm.swappiness = 0

Is to tell the Linux kernel not to use swap unless the virtual memory is completely full.

Increase resource limits (/ etc/security/limit.conf)

This file is actually the configuration file of Linux PAM, which is the plug-in authentication module.

Limit on the number of open files:

Soft nofile 65535

Hard nofile 65535

* indicates valid for all users

Soft refers to the setting that the current system takes effect.

Hard indicates the maximum value that can be set in the system

Nofile indicates that the restricted resources are the maximum number of open files

65535 the limited number

Soft cannot be greater than hard

Just add it to the end of the limit.conf file.

Conclusion: increase the number of files that can be opened to 65535 to ensure that enough file handles can be opened.

Note: changes to this file need to be rebooted to take effect.

Disk scheduling policy (/ sys/block/devname/queue/scheduler)

Cat / sys/block/devname/queue/scheduler

Scheduling policy: noop anticipatory deadline [cfg]

Noop (elevator scheduling strategy)

NOOP implements a FIFO queue that organizes Imax O requests like the way elevators work, when there is a new

When the request arrives, it merges the request after the most recent request to ensure that the request is the same media. NOOP tends to starve to death.

It is easy to write, so NOOP is the best choice for flash devices, RAM and embedded systems.

Deadline (deadline scheduling policy)

Deadline ensures that service requests are made within a deadline, which is adjustable, while the default read period

Shorter than the writing deadline. This prevents write operations from starving to death because they cannot be read, and deadline is the most important for database applications

Good choice.

Anticipatory (expected Imax O scheduling policy)

It is essentially the same as deadline, but after the last read operation, you have to wait for 6ms before you can proceed with other IWeiO.

Request scheduling. It inserts a new Icano operation into each 6ms, and merges some small write streams into one large write stream, using the

The maximum write throughput of the write delay swap area. AS is suitable for environments with more writes, such as file servers, and AS for database environment tables

It's bad right now.

Modify the scheduling policy:

Echo > / sys/block/devname/queue/scheduler

Such as echo deadline / sys/block/devname/queue/scheduler

This is how the CentOS system for mysql parameter optimization is shared by Xiaobian. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.