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

This article focuses on "sharing the optimization methods of MySQL on Linux". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "sharing the optimization methods of MySQL on Linux".

Now most of the environments that MySQL runs on Linux, we give some general and simple strategies on how to optimize according to MySQL on the Linux operating system. These methods all help to improve the performance of MySQL.

Cut the gossip and get to the point.

1. CPU

Let's start with CPU.

If you check carefully, there is an interesting phenomenon on some servers: 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: 5model 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?

All of this actually comes from CPU*** 's 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.

To ensure that MySQL can make full use of CPU resources, it is recommended to set CPU to * 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 difference of different BIOS types, the performance mode of setting CPU to * * 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 a 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 remaining 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) disable it in the operating system, and add numa=off directly to 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 LANG=en_US.UTF-8 rd_LVM_LV=VolGroup/root rd_NO_MD quiet SYSFONT=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 way to * 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 running 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 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.

So, we set vm.swappiness=0 on the server of MySQL.

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

*, let's take a look at the 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 * * is read once, modify time refers to the time when the text content of the file * changes, and change time refers to the time when the inode*** of the file changes (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 server either uses raid card, and the battery of raid card itself can be protected by power down, or uses 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 also an optimized key to improve IO on the file system, 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, in order 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 various 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 kernel line of / etc/grub.conf to take effect.

Summary

For CPU:

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.

Original link: http://www.woqutech.com/?p=1200

At this point, I believe you have a deeper understanding of "sharing the optimization methods of MySQL on Linux". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.