"MySQL" for some optimizations in terms of hardware

One, CPU maximum performance mode CPU utilization characteristics

• 5.1 Maximum usable 4 cores

• 5.5 Maximum usable 24 cores

• 5.6 Maximum Usable 64 cores

• Once query corresponds to a logical CPU

If you look closely, there is 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:

#cat/proc/cpuinfo Processor:5model Name:intel (R) Xeon (r) CPU e5-2620 0 @2.00ghz...cpu mhz:1200.000

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

Cause: In fact, it all stems from 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 the maximum performance mode, which can be set in the BIOS and operating system

Second, turn off 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). Simple team, don't.

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:

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

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 mode, memory can be allocated on any NUMA node outside of this mode.

In other ways, even if there is memory remaining on other NUMA nodes, Linux does not allocate the remaining memory to the process, but instead uses swap to get the memory. An experienced system administrator or DBA knows the database performance degradation caused by swap, so the simplest way to do this is to turn off the feature.

The methods for closing properties are:

• Can be turned off from BIOS setup

• Turn off the NUMA feature when MySQL is started Numactl--interleave=all mysqld &

• OS kernel, set Numa=off at startup

• Close in the operating system, you can add numa=off directly to the/etc/grub.conf kernel line, 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

Set vm.zone_reclaim_mode=0 to reclaim memory as much as possible

Third, close the 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, 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.

When the memory is basically full, the system will use this parameter to determine whether the memory is rarely used in the inactive memory swap out, or to release the data cache. 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 view the number of inactive memory:

[[email protected] ~]# 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 0  0  61432 1533944 1553788 3190400    0    0     9   216    0    0  5  2  4  0 0  0 61432 1533564 1553900 3190568    0    0     0  616 349  2 1 $  1  0 1  0  61432 1533068 1553844 3191096    0    0     0  5748 1604  455  1  1-  3  0 2  1  61432 1531952 1553812 3191984    0    0   172   1033  416  2  1 74 23  0[[email protected] ~]# cat/proc/meminfo | grep-i inact Inactive:        1557236 kbinactive (anon):   279888 Kbin Active (file):  1277348 KB
   

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

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.

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:

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

Iv. File System Recommendations Add noatime,nobarrier two options to the file system 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:

[Email protected] ~]# stat mysql-test.sh   File: ' mysql-test.sh '  size:970             blocks:8          IO block:4096   Regular filedevice:ca01h/51713d    inode:33883111    links:1access: (0644/-rw-r--r--)  Uid: (    0/    Root)   Gid: (    0/    root) access:2015-12-16 19:55:58.962535495 +0800modify:2015-12-11 09:15:38.410196493 +0800change:2015-12-11 09:15:38.493196460 +0800

• Access time refers to when the file was last read

• Modify time refers to the date when the text content of a file finally changes

• Change time refers to when the inode of a file finally changes (such as location, user attributes, group attributes, and so on)

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

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 strategy for Linux includes:Deadline scheduler,anticipatory scheduler,completely Fair Queuing (CFQ), NOOP

CFQ is the default scheduling policy after the Linux kernel 2.6.18, which claims to be fair for every IO request, and this scheduling strategy is applicable to most applications. 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 will not wait in the queue for too long to starve, which is more applicable to the database application

CFQ and Deadline,noop performance difference is very small, but once a large continuous IO,CFQ may cause small IO response delay increased, so the database environment is proposed to modify the deadline algorithm, the performance is more stable. Our environment uses the deadline algorithm uniformly.

The IO scheduling algorithm is based on disk design, so reducing head movement is one of the most important considerations, but after using flash storage devices, you no longer need to consider the problem of head movement, you can use the NOOP algorithm. The meaning of NoOp is nonoperation, which means that no IO optimizations are done, and the IO is handled exactly as requested in a FIFO manner.

Reduced pre-read:/sys/block/sdb/queue/read_ahead_kb, default 128, adjusted to 16

Increase queue:/sys/block/sdb/queue/nr_requests, default 128, adjustment to 512

Real-time settings, we can

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

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

Five, RAID optimization

The cache capacity on the Read Cache:raid card is limited, and we choose to read the data in direct mode, thus ignoring the read cache.

Turn off pre-read: The pre-read feature of the RAID card has almost no elevation for random io, so the read-ahead function is turned off.

Close disk cache: Normally, if you use RAID, the system will turn off the disk's cache by default, or you can force the shutdown with a command.

All of the above settings can be done via the RAID card's command line

Open BBWC

The RAID card has write cache (Battery backed write cache), and the write cache improves IO performance significantly, because the power-down loses data and must be supported by the battery. The battery will be charged and discharged regularly, usually about 90 days, when the power is found below a certain threshold, the write cache policy will be set from writeback to Writethrough, which is equivalent to the write cache will be invalidated, if the system has a large number of IO operations, it may be apparent that the IO response speed is slow. Currently, the new RAID card has built-in flash storage, after power-down will write cache data written to Flash, so that the data will never be lost, but still need battery support.

There are two types of solutions: 1. Manual trigger Charge and discharge, you can choose to do in the business trough, reduce the impact on the application; 2. Set the write cache policy to force write back, even if the battery fails, also keep the write cache policy as writeback, so there is a risk of data loss after power down.

Six Flashcache

Create Flashcache:flashcache_create-b 4k Cachedev/dev/sdc/dev/sdb

The block size of the specified Flashcache is the same as the page size of Percona.

Flashcache parameter settings:

Flashcache.fast_remove = 1: Turn on the fast Remove feature and do not need to write the dirty block in the cache to disk Flashcache.reclaim_policy = 1: Dirty block brush out policy, 0:FIFO, 1:lruflashcache.dirty_thresh_pct = Dirty block threshold on each hash set on 90:flashcache Flashcache.cache_all = 1:cache All contents, Can be blacklisted filter flashecache.write_merge = 1: Open write merge, improve the performance of the write disk

Vii. IOPS

Disk seek capability (disk I/O), with the current high-speed SCSI hard drive (7200 RPM) For example, this hard drive theoretically seeks 7,200 times per second, which is determined by the physical characteristics, there is no way to change. MySQL every second in a large number of complex query operations, the amount of read and write disk can be imagined. Therefore, the disk I/O is generally considered to be one of the biggest constraints on MySQL performance, for the average daily access to more than 1 million PV discuz! forum, due to disk I/O constraints, MySQL performance will be very low! To address this constraint, consider the following solutions: Use the raid-0+1 disk array, and be careful not to try to use Raid-5,mysql on the RAID-5 disk array, not as fast as you might expect.

Innodb_page_size: If using fusionio,4k is the best performance, use a SAS disk, set to 8K. If you have a lot of full table scans, you can set it to 16K. A smaller page size can increase the cache hit ratio. Innodb_adaptive_checkpoint: If you use Fusionio, set to 3, increase the refresh rate to 0.1 seconds, use a SAS disk, set to 2, and refresh dirty pages in estimate manner. Innodb_io_capacity: Based on the IOPS capability setting, use Fuionio to set more than 10000. Innodb_flush_neighbor_pages = 0: For Fusionio or SSD, this feature is turned off because the random io is good enough. Innodb_flush_method=all_o_direct: The public version of MySQL can only set the database file read and write to Directio, for Percona to set the log and data files to DIRECT read and write. But I'm not sure about the effect of this parameter on innodb_flush_log_at_trx_commit, innodb_read_io_threads = 1: Set the read-ahead thread to 1, because the effect of linear prefetching is not obvious, so you don't need to set it larger. Innodb_write_io_threads = 16: Sets the number of write threads to 16, which improves the ability to write. Innodb_fast_checksum = 1: The fast checksum feature is turned on.

Reference Documentation:

Http://www.gentoo-wiki.info/FAQ_Linux_Memory_Management
Http://bbs.gfan.com/android-4165836-1-1.html
Https://wiki.archlinux.org/index.php/CPU_Frequency_Scaling_ (%e7%ae%80%e4%bd%93%e4%b8%ad%e6%96%87)
http://www.mysqlperformanceblog.com/2013/12/07/linux-performance-tuning-tips-mysql/
http://www.woqutech.com/?p=1200
http://mp.weixin.qq.com/s?__biz=MjM5NTg2NTU0Ng==&mid=210459025&idx=5&sn= D4c6f199b3e95b456e1cf5dd672d2e4c&scene=0#rd
http://mp.weixin.qq.com/s?__biz=MjM5NDE0MjI4MA==&mid=208777870&idx=1&sn= 6efddd6283e4deb3fe55a141b0db965c&scene=1&srcid=0910kyibazqsbqzeivwahghb&key= Dffc561732c2265104613d6540d35b8ad5c92c340ed903cbbd8218ac9ba70f5b9d36aaa09033e2f9cf0e7983792311d4&ascene=1 &uin=mjm2njkwnq%3d%3d&devicetype=windows+7&version=61020020&pass_ticket= Juxi39h6la0f0shq0afmc7g2z4njxu5u71301bbdauw%3d
http://mp.weixin.qq.com/s?__biz=MzAwMDU1MTE1OQ==&mid=209406532&idx=1&sn= 2e9b0cc02bdd4a02f7fd81fb2a7d78e3&scene=2&srcid=0922i4nymvx1lj8qsr2avok3&from=timeline& Isappinstalled=0#wechat_redirect
Http://imysql.com/2015/05/24/mysql-optimization-reference-1.shtml
Http://www.hellodb.net/2011/07/mysql-linux-hardware-tuning.html
Http://www.hellodb.net/2011/06/mysql_multi_instance.html
http://blog.csdn.net/zjmzs/article/details/6046075

"MySQL" for some optimizations in terms of hardware