Shulou(Shulou.com)05/31 Report--

This article introduces you how to optimize the MySQL database and practice, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Recently, we have sorted out some optimization experiences about Percona,Linux,Flashcache and hardware devices and shared them with you:

Hardwares

1. Turn on BBWC

Raid cards have to write cache (Battery Backed Write Cache), write cache to improve the performance of IO is very obvious, because the power loss will lose data, so it must be supported by the battery. The battery will be charged and discharged regularly, usually for about 90 days. When the charge is found to be lower than a certain threshold, the write cache policy will be set from writeback to writethrough, which is equivalent to the failure of writing cache. If the system has a large number of IO operations, it may feel that the response speed of IO becomes slower. At present, the new raid card has built-in flash storage, and the data written to cache will be written to flash after power off, which ensures that the data will never be lost, but it still needs battery support.

There are two solutions:

(1) manually triggered charge and discharge can be done at a low point of business to reduce the impact on the application.

(2) set the write cache policy to force writeback, and keep the write cache policy to writeback even if the battery fails, so there is a risk of data loss after power failure.

At present, some hardware manufacturers provide capacitor-powered raid cards, there is no battery charge and discharge problems, you can contact their own hardware manufacturers.

2. Raid card configuration

Turn off reading cache: the capacity of cache on raid card is limited, so we choose direct mode to read data, thus ignoring reading cache.

Turn off pre-reading: the read-ahead function of the raid card has almost no improvement for random IO, so turn the read-ahead function off.

Turn off disk cache: in general, if you use RAID, the cache of the disk will be turned off by default, or you can force it with the command.

The above settings can be done through the raid card command line, such as the raid card of the LSI chip uses the megacli command.

3. Enable the Fastpath function

Fastpath is a new feature of LSI, which is optimized for SSD in the RAID controller. Using the fastpath feature can maximize the ability of SSD. If you use SSD to do RAID, you can turn on the fastpath function. For fastpath features, you can download materials from the LSI website and consult your own hardware provider.

4.Fusionio parameter adjustment

Basically, Fusionio does not require any adjustments, and the following three parameters may improve performance:

Options iomemory-vsl use_workqueue=0

For fusionio devices, ignoring Linux IO scheduling is equivalent to using NOOP.

Options iomemory-vsl disable-msi=0

Turn on MSI interrupt, if the device supports it.

Options iomemory-vsl use_large_pcie_rx_buffer=1

Opening Large PCIE buffer may improve performance.

Operating system

1.IO scheduling algorithm

Linux has four IO scheduling algorithms: CFQ,Deadline,Anticipatory and NOOP,CFQ are the default IO scheduling algorithms. Under completely random access environment, the performance difference between CFQ and Deadline,NOOP is very small, but once there is a large continuous IO,CFQ, the response delay of small IO may be increased, so the database environment is recommended to be modified to deadline algorithm, the performance is more stable. Our environment uniformly uses the deadline algorithm.

IO scheduling algorithms are based on disk design, so reducing head movement is one of the most important considerations, but after using Flash storage devices, we no longer need to consider the problem of head movement, we can use NOOP algorithm. NOOP means NonOperation, which means that no IO optimization will be done, and IO will be handled exactly as a request to FIFO.

Reduce pre-reading: / sys/block/sdb/queue/read_ahead_kb, default 128, adjusted to 16.

Increase the queue: / sys/block/sdb/queue/nr_requests, default 128, adjust to 512.

2.NUMA Settin

For a stand-alone instance, it is recommended to disable NUMA. There are three ways to close it:

(1) hardware layer, set the shutdown in BIOS.

(2) OS kernel, set numa=off at startup.

(3) you can use the numactl command to change the memory allocation policy to interleave (crossover), and some hardware can be set in BIOS.

For more than one instance on a single machine, please refer to http://www.hellodb.net/2011/06/mysql_multi_instance.html

3. File system settin

We use the XFS file system. XFS has two settings: su (stripe size) and sw (stirpe width). We need to set these two parameters according to the hardware layer RAID. For example, 10 disks are used as RAID10, and the stripe size is 64K, which is set to su=64K,sw=10.

Xfs mount parameter: defaults,rw,noatime,nodiratime,noikeep,nobarrier,allocsize=8M,attr2,largeio,inode64,swalloc

Data base

1.Flashcache parameter

Create flashcache:flashcache_create-b 4k cachedev / dev/sdc / dev/sdb

Specifies that the block size of flashcache is the same as the page size of Percona.

Flashcache parameter settings:

Flashcache.fast_remove = 1: turn on the fast remove feature and shut down the machine without writing dirty blocks in cache to disk.

Flashcache.reclaim_policy = 1: dirty block brushing strategy, 0VlFIFO Magi 1RU.

Flashcache.dirty_thresh_pct = the threshold of dirty blocks on each hash set on 90:flashcache.

Flashcache.cache_all = 1:cache all content, which can be filtered with a blacklist.

Flashecache.write_merge = 1: turn on write merge to improve the performance of write disks.

2.Percona parameter

Innodb_page_size: the performance is best if you use fusionio,4K; if you use SAS disk, set it to 8K. If the full table is scanned a lot, it can be set to 16K. Smaller page size can improve the hit rate of cache.

Innodb_adaptive_checkpoint: if you use fusionio, set it to 3, increase the refresh rate to 0.1s; use SAS disk, set to 2, and use estimate to refresh dirty pages.

Innodb_io_capacity: according to the IOPS capability setting, you can set more than 10000 using fuionio.

Innodb_flush_neighbor_pages = 0: for fusionio or SSD, this feature is turned off because random IO is good enough.

Innodb_flush_method=ALL_O_DIRECT: public MySQL can only set the read and write of database files to DirectIO. For Percona, you can set log and data files to read and write in direct mode. But I'm not sure how this parameter affects innodb_flush_log_at_trx_commit.

Innodb_read_io_threads = 1: set the read-ahead thread to 1, because the effect of linear read-ahead is not obvious, so there is no need to set it larger.

Innodb_write_io_threads = 16: set the number of writing threads to 16 to improve the ability to write.

Innodb_fast_checksum = 1: enable the Fast checksum feature.

Supervision and control

1.fusionio monitoring: fio-status command

Media status: Healthy; Reserves: 100.005%, warn at 10.00%

Thresholds: write-reduced: 96.00%, read-only: 94.00%

Lifetime data volumes:

Logical bytes written: 2664888862208

Logical bytes read: 171877629608448

Physical bytes written: 27665550363560

Physical bytes read: 223382659085448

2.flashcache monitoring: dmsetup status

Read hit percent (99)

Write hit percent (51)

Dirty write hit percent (44)

On the MySQL database optimization program and practice is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.