MySQL database optimization practices

Recently, we have sorted out some experience on optimizing Percona, Linux, Flashcache, and hardware devices and shared them with you: hardware 1. when the BBWCRAID card is enabled, the write cache (BatteryBackedWriteCache) significantly improves the IO performance. Because power loss may result in data loss, it must be supported by the battery. Battery depends

Recently, we have sorted out some experience on optimizing Percona, Linux, Flashcache, and hardware devices and shared them with you: hardware 1. battery Backed Write cache is available for enabling BBWC raid cards. Writing Cache significantly improves IO performance. Because power loss may result in data loss, it must be supported by Battery. Battery depends

Recently, we have sorted out some experience on optimizing Percona, Linux, Flashcache, and hardware devices:

Hardware

1. Enable BBWC

Raid cards all have Battery Backed Write cache, which significantly improves IO performance. Because power loss may result in data loss, it must be supported by Battery. Battery charge and discharge regularly, usually about 90 days. When the power usage is found to be lower than a threshold value, the write cache Policy is changed from writeback to writethrough, which means that the write cache will be invalid, at this time, if the system has a large number of IO operations, it may obviously feel that the IO response speed is slow. Currently, the new RAID card has built-in flash storage. After power loss, the data written to the cache will be written into flash. This ensures that the data will never be lost, but still requires battery support.

There are two solutions: 1. manual charge/discharge triggering can be performed at low business valleys to reduce the impact on applications; 2. set the write cache Policy to force write back, and keep the write cache Policy writeback even if the battery fails. This poses a risk of data loss after power loss.

At present, some hardware manufacturers provide capacitor-powered raid cards. If there is no problem of battery charge/discharge, you can contact your hardware manufacturer.

2. RAID card configuration

Disable read cache: the cache capacity on the raid card is limited. We select direct to read data and ignore the read cache.

Disable pre-read: the pre-read function of the RAID card has almost no improvement for random IO, so the pre-read function is disabled.

Disable disk cache: In general, if RAID is used, the system will disable the disk cache by default. You can also use the command to forcibly disable the disk cache.

The preceding settings can be completed through the RAID card command line. For example, the RAID card of the LSI chip uses the megacli command.

3. Enable Fastpath

Fastpath is a new feature of LSI. It is optimized for SSD in the RAID controller. The fastpath feature can maximize the SSD capability. If you use SSD for RAID, you can enable the fastpath function. For fastpath features, you can download materials from the LSI official website and consult your hardware vendor.

4. Fusionio Parameter Adjustment

Basically, no adjustment is required for Fusionio, and the following three parameters may improve the 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

Enable MSI interruption. Enable it if the device supports it.

Options iomemory-vsl use_large_pcie_rx_buffer = 1

Enabling Large PCIE buffer may improve performance.

Operating System

1. IO Scheduling Algorithm

Linux has four IO scheduling algorithms: CFQ, Deadline, Anticipatory, and NOOP. CFQ is the default IO scheduling algorithm. In a completely random access environment, the performance difference between CFQ and Deadline and NOOP is very small, but once there is a large continuous IO, CFQ may increase the response latency of small IO, therefore, we recommend that you change the database environment to the deadline algorithm to be more stable. Our environment uses the deadline Algorithm in a unified manner.

IO scheduling algorithms are designed based on disks. Therefore, reducing head movement is one of the most important considerations. However, after using Flash storage devices, you do not need to consider head movement. You can use the NOOP algorithm. The meaning of NOOP is NonOperation, which means that no I/O optimization is performed, and I/O is processed in FIFO mode based on requests.

Reduce pre-read:/sys/block/sdb/queue/read_ahead_kb. The default value is 128, and the value is 16.

Increase queue:/sys/block/sdb/queue/nr_requests. The default value is 128, and the value is adjusted to 512.

2. NUMA settings

We recommend that you disable NUMA for a single instance. There are three ways to disable NUMA: 1. hardware layer, which is disabled 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. Some hardware can be set in BIOS.

Single-host multi-instance, see: http://www.hellodb.net/2011/06/mysql_multi_instance.html

3. File System settings

We use the XFS file system. XFS has two settings: su (stripe size) and sw (stirpe width). We need to set these two parameters based on hardware RAID, for example, if 10 disks are used as raid 10, the strip size is 64 K, XFS is set to su = 64 K, and sw = 10.

Xfs? Mount parameters: ults, rw, noatime, nodiratime, noikeep, nobarrier, allocsize = 8 M, attr2, largeio, inode64, swalloc

Database

1. Flashcache Parameters

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

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

Flashcache parameter settings:

Flashcache. fast_remove = 1: Enable the fast remove feature. when the machine is disabled, you do not need to write dirty blocks in the cache to the disk.

Flashcache. reclaim_policy = 1: dirty block flushing policy, 0: FIFO, 1: LRU.

Flashcache. dirty_thresh_pct = 90: dirty block threshold value on each hash set in flashcache.

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

Flashecache. write_merge = 1: Enable write merge to improve disk write performance.

2. Percona Parameters

Innodb_page_size: If fusionio is used, the performance of 4 K is the best; If SAS disk is used, it is set to 8 K. If there are many full table scans, you can set it to 16 K. A relatively small page size can improve the cache hit rate.

Innodb_adaptive_checkpoint: If fusionio is used, set it to 3 to increase the refresh frequency to 0.1 seconds. If SAS disk is used, set it to 2 and use estimate to refresh dirty pages.

Innodb_io_capacity: Based on IOPS capability settings, fuionio can be used to set more than 10000.

Innodb_flush_neighbor_pages = 0: For fusionio or SSD, disable this function because random IO is good enough.

Innodb_flush_method = ALL_O_DIRECT: MySQL for the public version can only set database file read and write to DirectIO. For Percona, you can set log and data files to direct read and write. However, I am not sure about the impact of this parameter on innodb_flush_log_at_trx_commit,

Innodb_read_io_threads = 1: Set the pre-read thread to 1. Because the linear pre-read effect is not obvious, you do not need to set a larger value.

Innodb_write_io_threads = 16: sets the number of write threads to 16 to improve the write capability.

Innodb_fast_checksum = 1: Enable the Fast checksum feature.

Monitoring

1. fusionio monitoring: fio-status Command

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

Thresholds: write-committed CED: 96.00%, read-only: 94.00%

Lifetime data volumes:

Logical bytes written: 2,664,888,862,208

Logical bytes read ??? 171,877,629,608,448

Physical bytes written: 27,665,550,363,560

Physical bytes read ?? 223,382,659,085,448

2. flashcache monitoring: dmsetup status

Read hit percent (99)

Write hit percent (51)

Dirty write hit percent (44)

-EOF-

Recently, we have sorted out some experience on optimizing Percona, Linux, Flashcache, and hardware devices and shared them with you: hardware 1. battery Backed Write cache is available for enabling BBWCRAID cards. Writing Cache significantly improves IO performance. Because power loss may result in data loss, it must be supported by Battery. Battery charge and discharge regularly, usually about 90 days. When the power usage is found to be lower than a threshold value, the write cache Policy is changed from writeback to writethrough, which means that the write cache will be invalid, at this time, if the system has a large number of IO operations, it may obviously feel that the IO response speed is slow. Currently, the new RAID card has built-in flash storage. After power loss, the data written to the cache will be written into flash. This ensures that the data will never be lost, but still requires battery support. There are two solutions: 1. manual charge/discharge triggering can be performed at low business valleys to reduce the impact on applications; 2. set the write cache Policy to force write back, and keep the write cache Policy writeback even if the battery fails. This poses a risk of data loss after power loss. At present, some hardware manufacturers provide capacitor-powered raid cards. If there is no problem of battery charge/discharge, you can contact your hardware manufacturer. 2. Disable read cache for RAID card configuration: the cache capacity on the raid card is limited. We select direct mode to read data and ignore read cache. Disable pre-read: the pre-read function of the RAID card has almost no improvement for random IO, so the pre-read function is disabled. Disable disk cache: In general, if RAID is used, the system will disable the disk cache by default. You can also use the command to forcibly disable the disk cache. The preceding settings can be completed through the RAID card command line. For example, the RAID card of the LSI chip uses the megacli command. 3. Enabling Fastpath is a new feature of LSI. It has been optimized for SSD in the RAID Controller and uses Fastpath to maximize SSD capabilities. If you use SSD for RAID, you can enable the fastpath function. For fastpath features, you can download materials from the LSI official website and consult your hardware vendor. 4. basically, Fusionio does not need to be adjusted. The following three parameters may improve performance: options iomemory-vsl use_workqueue = 0. For Fusionio devices, Linux IO scheduling is ignored, it is equivalent to using NOOP. Options iomemory-vsl disable-msi = 0 enable MSI interruption. If the device supports this function, enable it. Options iomemory-vsl use_large_pcie_rx_buffer = 1 enable Large PCIE buffer, which may improve performance. Operating System 1. IO scheduling algorithm Linux has four types of IO scheduling algorithms: CFQ, Deadline, Anticipatory and NOOP. CFQ is the default IO scheduling algorithm. In a completely random access environment, the performance difference between CFQ and Deadline and NOOP is very small, but once there is a large continuous IO, CFQ may increase the response latency of small IO, therefore, we recommend that you change the database environment to the deadline algorithm to be more stable. Our environment uses the deadline Algorithm in a unified manner. IO scheduling algorithms are designed based on disks. Therefore, reducing head movement is one of the most important considerations. However, after using Flash storage devices, you do not need to consider head movement. You can use the NOOP algorithm. The meaning of NOOP is NonOperation, which means that no I/O optimization is performed, and I/O is processed in FIFO mode based on requests. Reduce pre-read:/sys/block/sdb/queue/read_ahead_kb. The default value is 128. adjust it to 16 to increase the queue:/sys/block/sdb/queue/nr_requests. The default value is 128, adjusted to 5122. NUMA is used to set up a single-host single-instance. We recommend that you disable NUMA in three ways: 1. hardware layer, which is disabled 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. Some hardware can be set in BIOS. For more information, see: http://www.hellodb.net/2011/06/mysql_multi_instance.html3 . For file system settings, we use the XFS file system. XFS has two settings: su (stripe size) and sw (stirpe width). You need to set these two parameters based on the hardware layer RAID, for example, if 10 disks are used as raid 10, the strip size is 64 K, XFS is set to su = 64 K, and sw = 10. Xfs? Mount parameter: defaults, rw, noatime, nodiratime, noikeep, nobarrier, allocsize = 8 M, attr2, largeio, inode64, swalloc database 1. the Flashcache parameter creates flashcache: flashcache_create-B 4 k cachedev/dev/sdc/dev/sdb. The size of the flashcache block is the same as that of the Percona page. Flashcache parameter settings: flashcache. fast_remove = 1: Enable the fast remove feature. when the machine is disabled, no dirty blocks in the cache need to be written to the disk. Flashcache. reclaim_policy = 1: dirty block flushing policy, 0: FIFO, 1: LRU. Flashcache. dirty_thresh_pct = 90: dirty block threshold value on each hash set in flashcache. Flashcache. cache_all = 1: cache all content, which can be filtered by blacklist. Flashecache. write_merge = 1: Enable write merge to improve disk write performance. 2. Percona parameter innodb_page_size: If fusionio is used, 4 K has the best performance. If SAS disk is used, set it to 8 K. If there are many full table scans, you can set it to 16 K. A relatively small page size can improve the cache hit rate. Innodb_adaptive_checkpoint: If fusionio is used, set it to 3 to increase the refresh frequency to 0.1 seconds. If SAS disk is used, set it to 2 and use estimate to refresh dirty pages. Innodb_io_capacity: Based on IOPS capability settings, fuionio can be used to set more than 10000. Innodb_flush_neighbor_pages = 0: For fusionio or SSD, disable this function because random IO is good enough. Innodb_flush_method = ALL_O_DIRECT: MySQL for the public version can only set database file read and write to DirectIO. For Percona, you can set log and data files to direct read and write. However, I'm not sure about the impact of this parameter on innodb_flush_log_at_trx_commit. innodb_read_io_threads = 1: Set the pre-read thread to 1. Because the linear pre-read effect is not obvious, you don't need to set it to be greater. Innodb_write_io_threads = 16: sets the number of write threads to 16 to improve the write capability. Innodb_fast_checksum = 1: Enable the Fast checksum feature. Monitoring 1. fusionio monitoring: fio-status Command Media status: Healthy; Reserves: 100.00%, warn at 10.00% Thresholds: write-committed CED: 96.00%, read-only: 94.00% Lifetime [...]