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

The following mainly brings you what are the good skills of optimizing mysql on linux. I hope these contents can bring you practical use. This is also the main purpose of this article that I edit the good skills of optimizing mysql on linux. All right, don't talk too much nonsense, let's just read the following.

1. CPU

Let's start with CPU.

If you check carefully, there is an interesting phenomenon on some CVMs: when you cat / proc/cpuinfo, you will find that the frequency of CPU is not the same as its nominal frequency:

# cat / proc/cpuinfo processor: 5 model name: Intel (R) Xeon (R) CPU E5-2620 0 @ 2.00GHz. Cpu MHz: 1200.000

This is the CPU of Intel E5-2620, and he is the CPU of 2.00G * 24, but we found that the fifth CPU has a frequency of 1.2g.

What is the reason for this column?

All of this actually comes from CPU's latest technology: energy-saving mode. Operating system and CPU hardware cooperate, when the system is not busy, in order to save electricity and reduce the temperature, it will reduce the frequency of CPU. This is a boon for environmentalists and the fight against global warming, but it could be a disaster for MySQL.

In order to ensure that MySQL can make full use of the resources of CPU, it is recommended to set CPU to the maximum performance mode. This setting can be set in BIOS and the operating system, but of course it is better and more thorough to set this option in BIOS. Due to the differences between different BIOS types, setting CPU to maximum performance mode varies greatly, so we won't show you how to set it here.

Second, memory

Then let's look at what we can optimize in terms of memory.

I) Let's take a look at numa first

Non-uniform storage access structure (NUMA: Non-Uniform Memory Access) is also the latest memory management technology. It corresponds to the symmetric multiprocessor architecture (SMP: Symmetric Multi-Processor). The simple teams are as follows:

As shown in the figure, we will not cover the detailed NUMA information here. But we can see intuitively: the cost of SMP access to memory is the same; but under the NUMA architecture, the cost of accessing local memory is not the same as that of non-local memory. Correspondingly, according to this feature, we can set the memory allocation mode of the process on the operating system. Currently supported methods include:

-interleave=nodes-- membind=nodes-- cpunodebind=nodes-- physcpubind=cpus-- localalloc-- preferred=node

In short, you can specify in-memory local allocation, allocation in certain CPU nodes, or polling allocation. Unless it is set to-- interleave=nodes polling allocation, that is, memory can be allocated on any NUMA node. In other ways, even if there is memory left on other NUMA nodes, Linux does not allocate the remaining memory to the process, but uses SWAP to obtain memory. Experienced system administrators or DBA all know how lame database performance can be caused by SWAP.

So the easiest way is to turn off this feature.

The ways to turn off the feature are: from BIOS, the operating system, you can temporarily turn off this feature when you start the process.

A) due to the differences of various BIOS types, how to disable NUMA varies greatly, so we won't show you how to set it here.

B) turn it off in the operating system, and you can add numa=off directly at the end of the kernel line of / etc/grub.conf, as shown below:

Kernel / vmlinuz-2.6.32-220.el6.x86_64 ro root=/dev/mapper/VolGroup-root rd_NO_LUKS.UTF-8 rd_LVM_LV=VolGroup/root rd_NO_MD quietSYSFONT=latarcyrheb-sun16 rhgb crashkernel=auto rd_LVM_LV=VolGroup/swap rhgb crashkernel=auto quiet KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM numa=off

In addition, you can set vm.zone_reclaim_mode=0 to reclaim memory as much as possible.

C) when starting MySQL, turn off the NUMA feature:

Numactl-- interleave=all mysqld &

Of course, the best way is to turn it off in BIOS.

Ii) Let's take a look at vm.swappiness again.

Vm.swappiness is a strategy used by the operating system to control physical memory swapping out. The value it allows is a percentage, with a minimum of 0 and a maximum of 100, which defaults to 60. Setting vm.swappiness to 0 means that as little swap,100 as possible means swapping out the memory pages of inactive as much as possible.

Specifically: when the memory is basically full, the system will use this parameter to determine whether to swap out the inactive memory that is rarely used in memory, or to release the cache of the data. Cache caches data read from disk, which is likely to be read next according to the locality principle of the program; inactive memory, as its name implies, is memory that is mapped by the application but not used "for a long time".

We can use vmstat to see the amount of memory in inactive:

# vmstat-an 1 procs-memory- swap---io---- system---cpu- r b swpd free inact active si so bi bo in cs us sy id wa st 27522384 326928 1704644 0 153 11 100 00 00 27523300 326936 1704164 00 074 784 590 00 100 00 00 0 27523656 326936 1704692 00 8 8 439 1686 00 100 00 00 0 27524300 326916 1703412 00 4 52 198 262 00 100 00

You can see more detailed information through / proc/meminfo:

# cat / proc/meminfo | grep-i inact Inactive: 326972 kB Inactive (anon): 248kB Inactive (file): 326724 kB

Here we delve further into inactive inactive memory. In Linux, memory may be in three states: free,active and inactive. As we all know, Linux Kernel maintains a number of LRU lists internally to manage memory, such as LRU_INACTIVE_ANON, LRU_ACTIVE_ANON, LRU_INACTIVE_FILE, LRU_ACTIVE_FILE, LRU_UNEVICTABLE. LRU_INACTIVE_ANON and LRU_ACTIVE_ANON are used to manage anonymous pages, and LRU_INACTIVE_FILE and LRU_ACTIVE_FILE are used to manage page caches page cache. The kernel of the system will periodically move the active active memory to the inactive list according to the access of the memory page, and the inactive memory can be swapped into the swap.

Generally speaking, MySQL, especially InnoDB manages memory cache, it takes up a lot of memory, and there will be a lot of memory accessed infrequently. If these memories are mistakenly swapped out by Linux, it will waste a lot of CPU and IO resources. InnoDB manages the cache itself, and cache's file data takes up memory, which is of little benefit to InnoDB.

Therefore, we'd better set vm.swappiness=0 on MySQL's CVM.

We can add a line to the sysctl.conf by:

Echo "vm.swappiness = 0" > > / etc/sysctl.conf

And use sysctl-p to make this parameter effective.

III. File system

Finally, let's take a look at file system optimization.

I) We recommend adding two options of noatime,nobarrier to the mount parameter of the file system.

With noatime mount, the file system does not update the corresponding access time when the program accesses the corresponding file or folder. Generally speaking, Linux records three times for the file, change time, modify time and access time.

There are three times we can view the file through stat:

Stat libnids-1.16.tar.gz File: `libnids-1.16.tar.gz' Size: 72309 Blocks: 152IO Block: 4096 regular file Device: 302h/770d Inode: 4113144 Links: 1 Access: (0644 root) Gid: (0 / root) Access: (0 / root) Access: 2008-05-27 1515 JV 13V 03.000000000 + 0800 Modify: 2003.000000000 + 0800 Change: 09.000000000 + 0800 Change

Access time refers to the time when the file was last read, modify time refers to the last time the text content of the file changed, and change time refers to the time when the inode of the file last changed (such as location, user attributes, group attributes, etc.). Generally speaking, files are read more and write less, and we seldom care about when a file has been accessed recently.

Therefore, we recommend using the noatime option so that the file system does not record access time and avoid wasting resources.

Many file systems today force the underlying device to refresh cache when data is submitted to avoid data loss, which is called write barriers. However, in fact, the underlying storage device of our database cloud server can either use raid card, and the battery of raid card itself can be protected by power down, or use Flash card, which also has a self-protection mechanism to ensure that data will not be lost. So we can mount the file system safely using nobarrier. The setting method is as follows:

"for ext3, ext4 and reiserfs file systems can specify barrier=0; at mount. Nobarrier option can be specified for xfs."

Ii) there is another optimization on the file system that improves IO *, and that is deadline.

Before Flash technology, we all use mechanical disk to store data, and the seek time of mechanical disk is the most important factor affecting its speed, which directly leads to its limited IO (IOPS) per second. In order to sort and merge multiple requests as far as possible, to achieve the goal that one seek can satisfy multiple IO requests, Linux file system designs a variety of IO scheduling strategies, which are suitable for a variety of scenarios and storage devices.

Linux's IO scheduling strategy includes: Deadline scheduler,Anticipatory scheduler,Completely Fair Queuing (CFQ), NOOP. The detailed scheduling mode of each scheduling policy is not described in detail here. Here we mainly introduce that CFQ and Deadline,CFQ are the default scheduling policies after 2.6.18 of the Linux kernel, which claims to be fair to every IO request, and this scheduling strategy is applicable to most applications. However, if the database has two requests, one request for 3 IO and one request for 10000 IO, due to absolute fairness, this request for three IO needs to compete with the other 10000 IO requests, and may have to wait for thousands of IO to complete before returning, resulting in a very slow response time. And if there are a lot of IO requests to be sent one after another in the process of processing, some IO requests may even be unable to get dispatched and starve to death. On the other hand, deadline takes into account that a request will not wait in the queue for too long to starve to death, so it is more suitable for database applications.

Real-time settings, we can use the

Echo deadline > / sys/block/sda/queue/scheduler

To set the scheduling policy of sda to deadline.

We can also add elevator=deadline directly to the end of the kernel line of / etc/grub.conf to take effect permanently.

Summary

CPU aspect

Turn off power protection mode

Memory:

Vm.swappiness = 0

Close numa

File system:

Mounting the system with noatime,nobarrier

The IO scheduling policy is changed to deadline.

For the above about what good skills to optimize mysql on linux, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.