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

This article introduces the relevant knowledge of "MYSQL optimization method on Linux". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

1. CPU

Let's start with CPU.

If you look closely, there's 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 Intel E5-2620 CPU, it is 2.00G * 24 CPU, but we found that the fifth CPU frequency is 1.2G.

What is the reason for this?

These are actually derived from CPU*** technology: energy-saving mode.

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

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 CPU resources, it is recommended to set CPU to *** performance mode.

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

Due to the differences in various BIOS types, setting CPU to *** performance mode varies greatly, we will not specifically show how to set it here.

II. Memory

Then we look at memory, what we can optimize.

1. numa

NUMA (Non-Uniform Memory Access) is also a memory management technology. It corresponds to Symmetric Multi-Processor (SMP) architecture. The simple teams are as follows:

As shown in the figure, we will not introduce detailed NUMA information here.

However, we can intuitively see that SMP accesses memory at the same cost; however, under NUMA architecture, local memory access and non-local memory access costs are different.

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

In short, this means that you can specify whether memory is allocated locally, among certain CPU nodes, or by polling.

Unless it is set to--interleave=nodes polling allocation, that is, memory can be allocated on any NUMA node in this way, other ways even if there is memory remaining on other NUMA nodes, Linux will not allocate the remaining memory to this process, but SWAP to obtain memory.

Experienced system administrators or DBAs know how much database performance degradation SWAP can cause.

The easiest way to do this is to turn off this feature.

There are ways to disable the feature: temporarily disable it from the BIOS, operating system, and boot process.

a) Due to the differences between various BIOS types, how to close NUMA varies greatly, so we will not show how to set it specifically here.

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

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

c) When MySQL is started, disable NUMA feature:

Of course, the *** way is to close in BIOS.

2.vm.swappiness

vm.Swappiness is the operating system's policy of controlling physical memory swapping out. The allowed value is a percentage value, with a minimum of 0, *** runs 100, which defaults to 60.

vm. swap is set to 0 to minimize swaps, and 100 to swap inactive memory pages as much as possible.

Specifically, when the memory is basically full, the system will judge whether to exchange the inactive memory that is rarely used in memory or release the cache of data according to this parameter.

Cache stores data read from disk, which, depending on program locality, may be read later;inactive memory, as the name suggests, is memory mapped by an application but unused for a "long time."

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

You can see more details at/proc/meminfo:

Here we discuss inactive memory further.

In Linux, memory can be in three states: free, active, and inactive.

The 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. LRU_INACTIVE_ANON, LRU_ACTIVE_ANON are used to manage anonymous pages, LRU_INACTIVE_FILE , LRU_ACTIVE_FILE are used to manage page caches.

The kernel occasionally moves active memory to the inactive list based on page accesses, and inactive memory can be swapped to swap.

In general, MySQL, especially InnoDB management memory cache, it takes up more memory, infrequently accessed memory will also be a lot, if these memories are exchanged by Linux errors, will waste a lot of CPU and IO resources. InnoDB manages its own cache, which takes up memory for cached file data and has almost no benefit to InnoDB.

So, we set vm. happiness =0 on MySQL server ***.

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

And use sysctl -p to make this parameter effective.

III. File system

*** Let's look at file system optimization

1.mount

We recommend adding noatime and 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.

Linux records three times: change time, modify time, and access time.

We can use stat to view three times of a file:

The access time refers to the time when the file *** is read once, the modify time refers to the time when the text content *** of the file changes, and the change time refers to the time when the inode*** of the file changes (such as position, user attribute, group attribute, etc.).

In general, files are read more than written, and we rarely care about when a file was last accessed.

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

Many file systems now force the underlying device to flush cache when data is committed to avoid data loss, known as write barriers.

However, in fact, the underlying storage devices of our database servers either use RAID cards, and the batteries of the RAID cards themselves can be protected from power loss; or Flash cards, which also have self-protection mechanisms to ensure that data will not be lost. So we can safely mount file systems using nobarrier.

The settings are as follows:

For ext3, ext4, and reiserfs file systems, you can specify barrier=0 at mount; for xfs, you can specify the nobarrier option.

2.deadline

There is also an optimization *** key on the file system to improve IO, which is the deadline.

Before Flash technology, we all used mechanical disks to store data. The seek time of mechanical disks is the most important factor affecting its speed, which directly leads to its very limited IO(IOPS) per second. In order to sort and merge multiple requests as much as possible to achieve the goal of satisfying multiple IO requests with one seek, Linux file system has designed a variety of IO scheduling policies, which have been applied to various scenarios and storage devices.

Linux IO scheduling policies include Deadline scheduler, Anticipatory scheduler, Completely Fair Queuing(CFQ), and NOOP.

CFQ is the default scheduling policy after Linux kernel 2.6.18, which claims to be fair to every IO request. This scheduling policy is applicable to most applications.

However, if the database has two requests, one request 3 IO times, one request 10000 IO times, because of absolute fairness, this request of 3 IO times needs to compete with the other 10000 IO requests, and it may have to wait for thousands of IO to complete before returning, resulting in its response time is very slow. And if there are many IO requests sent one after another in the process of processing, some IO requests may not even be scheduled and may be "starved". Deadline takes into account that a request does not wait too long in the queue to starve, which is more suitable for database applications.

Real-time settings, we can use

To set sda's scheduling policy to deadline.

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

summary

CPU:

Turn off power protection mode

Memory:

vm.swappiness = 0

Close numa

File system:

mount the system with noatime, nobarrier

IO scheduling policy modified to deadline.

"MYSQL optimization method on Linux" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.