MySQL-optimized kick on Linux improves MySQL performance

Now most of the environment that MySQL runs is on Linux, how to optimize on the Linux operating system according to MySQL, we give some general simple strategy here. These methods help improve the performance of MySQL.

Now most of the environment that MySQL runs is on Linux, how to optimize on the Linux operating system according to MySQL, we give some general simple strategy here. These methods help improve the performance of MySQL.

Gossip less, get to the point.

First, the CPU

First, start with the CPU.

If you look closely, there will be an interesting phenomenon on some servers:

When you cat/proc/cpuinfo, you will find that the CPU frequency is not the same as its nominal frequency:

This is the Intel e5-2620 CPU, he is 2.00G * 24 CPU, but we found that the 5th CPU frequency is 1.2G.

What is the reason for this?

These are all based on the latest CPU technology: energy-saving mode.

Operating system and CPU hardware, when the system is not busy, in order to save power and reduce the temperature, it will reduce the CPU frequency.

This is a boon for environmentalists and for resisting global warming, but it could be a disaster for MySQL.

To ensure that MySQL can take full advantage of CPU resources, it is recommended to set the CPU to maximum performance mode.

This setting can be set in the BIOS and operating system, and of course, it is better and more thorough to set this option in the BIOS.

Because of the different BIOS types, setting the CPU for maximum performance mode varies widely, we don't show how to set it up here.

Second, memory

Then we look at the memory side, what we can optimize.

1. Numa

The non-uniform storage access structure (Numa:non-uniform memory access) is also the latest in RAM management technology. It corresponds to a symmetric multiprocessor structure (Smp:symmetric multi-processor). The simple team do the following:

, detailed NUMA information we do not introduce here.

But we can intuitively see that the cost of accessing memory is the same for SMP, but in the NUMA architecture, access to local memory and non-local memory are not the same.

Corresponding to this feature, on the operating system, we can set the process memory allocation method. The methods currently supported include:

In short, that is, you can specify that memory is allocated locally, allocated at some CPU nodes, or polled.

Unless it is set to--interleave=nodes polling allocation, where memory can be allocated on any NUMA node, the other way, even if there is memory remaining on the other NUMA nodes, Linux does not allocate the remaining memory to the process. Instead, swap is used to get the memory.

An experienced system administrator or DBA knows how bad the database performance is due to swap.

So the simplest way to do this is to turn off the feature.

The methods for shutting down the features are: You can temporarily turn off this feature from the BIOS, the operating system, and the startup process.

A) because of the different types of bios, how to turn off NUMA varies widely, we do not show how to set up here specifically.

b) Close in the operating system, you can add numa=off directly to the/etc/grub.conf kernel line, as follows:

You can also set vm.zone_reclaim_mode=0 to reclaim memory as much as possible.

c) Turn off the NUMA feature when MySQL is started:

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

2.vm.swappiness

Vm.swappiness is the strategy by which the operating system controls physical memory exchange. The value it allows is a percentage of the value, the minimum is 0, the maximum run 100, and the value defaults to 60.

Vm.swappiness set to 0 means that as little as possible swap,100 means to swap out inactive memory pages as much as possible.

Specifically, when the memory is basically full, the system will determine whether to swap out the inactive memory that is seldom used in memory, or to release the cache of the data.

Cache is cached in the data read from the disk, according to the program's local principle, the data may be read in the next; inactive memory, as the name implies, is those that are mapped by the application, but "long time" unused memory.

We can use Vmstat to see the amount of inactive memory:

With/proc/meminfo you can see more detailed information:

Here we have a further in-depth discussion of inactive inactive memory.

In Linux, memory may be in three states: Free,active and inactive.

As we all know, Linux kernel maintains many LRU lists internally to manage memory, such as Lru_inactive_anon, Lru_active_anon, Lru_inactive_file, Lru_active_file, lru_ Unevictable. Where Lru_inactive_anon, Lru_active_anon is used to manage anonymous pages, Lru_inactive_file, Lru_active_file is used to manage page caches pages cache.

Depending on the access of the memory pages, the system kernel will move active active memory to the inactive list at irregular intervals, and the inactive memory can be swapped into swap.

In general, MySQL, especially InnoDB management memory cache, it occupies more memory, not often access to a lot of memory, these memory if the Linux wrong swap out, will waste a lot of CPU and IO resources. InnoDB to manage the cache itself, the cache's file data takes up memory and has little benefit for InnoDB.

So, we'd better set the vm.swappiness=0 on the MySQL server.

We can do this by adding a line to the sysctl.conf:

and use Sysctl-p to make the parameter effective.

Third, the file system

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

1.mount

We recommend adding noatime,nobarrier two options to the file system's mount parameter.

With Noatime mount, the file system does not update the corresponding access time when the program accesses the corresponding file or folder.

In general, Linux records three times for files, change time, modify hours, and access timing.

We can check the file three times via stat:

Where access time refers to the date when the file was last read, modify time refers to when the text content of the file last changed, and change time refers to the end of the file's inode changes (such as location, user attributes, group properties, etc.).

Generally speaking, the files are read and write less, and we seldom care about when a certain file has been accessed recently.

Therefore, we recommend using the Noatime option so that the file system does not log access time to avoid wasting resources.

Many file systems now force the underlying device to flush the cache when the data is submitted, avoiding data loss, called write barriers.

However, in fact, our database server underlying storage device either uses a RAID card, the RAID card itself battery can be power-down protection, or flash card, it also has a self-protection mechanism to ensure that the data is not lost. So we can safely use the Nobarrier mount file system.

The Setup method is as follows:

For Ext3, the EXT4 and ReiserFS file systems can specify barrier=0 on Mount, and for XFS you can specify the Nobarrier option.

2.deadline

There is also an optimized master key for improving IO on the file system, which is deadline.

Before Flash technology, we used mechanical disks to store data, the mechanical disk seek time is the most important factor affecting its speed, directly resulting in its very limited IO per second (IOPS), in order to try to sort and merge multiple requests to achieve a single seek to meet the purpose of multiple IO requests, The Linux file system designs a variety of IO scheduling strategies that have been applied to various scenarios and storage devices.

The IO scheduling strategies for Linux include: Deadline scheduler,anticipatory scheduler,completely Fair Queuing (CFQ), NOOP.

Detailed scheduling of each scheduling strategy we do not describe here in detail, here we mainly introduce CFQ and DEADLINE,CFQ is the Linux kernel 2.6.18 after the default scheduling policy, it claims to each IO request is fair, this scheduling strategy for most applications are applicable.

However, if the database has two requests, a request 3 Io, a request 10,000 Io, due to the absolute fairness, this request for 3 times IO needs to compete with the other 10,000 IO requests, may wait for thousands of IO completion to return, resulting in a very slow response time. And if in the process of processing, and a lot of IO requests are sent over, some IO requests may even have been unable to get the dispatch was "starved". And deadline that a request does not wait too long in the queue to starve, which is more appropriate for a database application.

Real-time settings, we can

To set the SDA's scheduling policy to deadline.

We can also add elevator=deadline directly to/etc/grub.conf's kernel line to be permanently active.

Summarize

<li "=" ">cpu Aspects:

Turn off power protection mode

<li "=" "> Memory:

vm.swappiness = 0

Turn off NUMA

<li "=" "> File system:

Mount the system with Noatime,nobarrier

The IO scheduling policy is modified to deadline.

MySQL-optimized kick on Linux improves MySQL performance