MySQL optimization summary on Linux

MySQL optimization summary on Linux

Currently, most of the MySQL running environments are on Linux. Here we provide some general and simple policies on how to optimize MySQL on the Linux operating system. These methods help improve MySQL performance.

If you have less time to talk about it, go to the topic.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

I. CPU

Start with CPU.

If you check it carefully, some servers may have an interesting phenomenon: When you cat/proc/cpuinfo, you will find that the CPU frequency is different from its nominal frequency:

1. # Cat/proc/cpuinfo
2. Processor: 5
3. Model name: Intel (R) Xeon (R) CPU E5-26200@2.00GHz
4. ...
5. Cpu MHz: 1200.000

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

Why?

These are all due to the latest CPU Technology: energy-saving mode. When the operating system is not busy with the CPU hardware, it will reduce the CPU frequency to save power and reduce the temperature. This is a good news for environmental protection and global warming resistance, but it may be a disaster for MySQL.

To ensure that MySQL can fully utilize CPU resources, we recommend that you set the CPU to the maximum performance mode. This setting can be set in BIOS and operating system. Of course, it is better and more thorough to set this option in BIOS. Due to the differences between various BIOS types, setting the CPU to the maximum performance mode varies significantly, so we will not detail how to set it here.

Ii. Memory

Then let's look at the memory, which of the following can be optimized.

I) Let's first look at numa.

Inconsistent storage Access structure (NUMA: Non-Uniform Memory Access) is also the latest Memory management technology. It corresponds to the Symmetric Multi-Processor structure (SMP: Symmetric Multi-Processor. Simple teams are as follows:

, The detailed NUMA information is not described here. However, we can intuitively see that the cost of SMP access to memory is the same; but in the NUMA architecture, the cost of local memory access is different from that of non-local memory access. Based on this feature, we can set the memory allocation mode for processes on the operating system. Currently, the following methods are supported:

1. -- Interleave = nodes
2. -- Membind = nodes
3. -- Cpunodebind = nodes
4. -- Physcpubind = cpus
5. -- Localalloc
6. -- Preferred = node

In short, you can specify the number of CPU nodes that are allocated locally or in polling mode. Unless it is set to -- interleave = nodes Round Robin, that is, the memory can be allocated on any NUMA node. In other ways, even if there is memory surplus on other NUMA nodes, Linux does not allocate the remaining memory to this process, but uses SWAP to obtain the memory. Experienced system administrators or DBAs know how poor the database performance caused by SWAP is.

So the simplest method is to disable this feature.

You can disable this feature temporarily when starting a process in the BIOS, operating system, or operating system.

A) due to the differences in various BIOS types, how to disable NUMA varies greatly. We will not detail how to set it here.

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

1. 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 recycle the memory as much as possible.

C) when MySQL is started, disable the NUMA feature:

1. Numactl -- interleave = all mysqld &

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

Ii) let's take a look at vm. swappiness.

Vm. swappiness is the operating system's policy to control physical memory switching. The value is a percentage value. The minimum value is 0 and the maximum value is 100. The default value is 60. Vm. swappiness is set to 0, which indicates that swap is minimized, and 100 indicates that inactive memory pages are switched out as much as possible.

Specifically, when the memory is basically full, the system will determine based on this parameter whether to swap inactive memory rarely used in the memory or to release the data cache. The cache caches data read from the disk. According to the program's local principle, the data may be read again later. As the name suggests, inactive memory is mapped by applications, but the memory is not used for "long time.

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

1. # Vmstat-an 1
2. Procs ----------- memory ------------- swap ------- io ------ system ------- cpu -----
3. R B swpd free inact active si so bi bo in cs us sy id wa st
4. 10027522384326928170464400015311100010000
5. 000275233003269361704164000747845900010000
6. 000275236563269361704692008843916860010000
7. 000275243003269161703412004521982620010000

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

1. # Cat/proc/meminfo | grep-I inact
2. Inactive: 326972 kB
3. Inactive (anon): 248 kB
4. Inactive (file): 326724 kB

Here we will further discuss inactive memory in depth. In Linux, memory may be in three states: free, active, and inactive. As we all know, Linux Kernel maintains many LRU lists internally for memory management, such as LRU_INACTIVE_ANON, LRU_ACTIVE_ANON, LRU_INACTIVE_FILE, LRU_ACTIVE_FILE, and LRU_UNEVICTABLE. Here, LRU_INACTIVE_ANON and LRU_ACTIVE_ANON are used to manage anonymous pages, LRU_INACTIVE_FILE and LRU_ACTIVE_FILE are used to manage page caches page cache. The system kernel will occasionally move the active memory to the inactive list based on the access status on the memory page. These inactive memories can be exchanged to swap.

In general, MySQL, especially InnoDB, manages the memory cache. It occupies a large amount of memory and may not frequently access it. If these memories are exchanged by Linux errors, it will waste a lot of CPU and IO resources. InnoDB manages the cache by itself. The cached file data occupies the memory, which is of almost no benefit to InnoDB.

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

You can add a row in sysctl. conf:

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

And use sysctl-p to make the parameter take effect.

For more details, please continue to read the highlights on the next page:

1 2 Next Page