共计 3417 个字符，预计需要花费 9 分钟才能阅读完成。
这篇文章给大家介绍 MySQL 数据库优化的方案与实践是怎样的，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。
最近一段时间，我们整理了一些关于 Percona，Linux，Flashcache，硬件设备的优化经验，分享给大家：
硬 件
1. 开启 BBWC
RAID 卡都有写 cache(Battery Backed Write Cache)，写 cache 对 IO 性能的提升非常明显，因为掉电会丢失数据，所以必须由电池提供支持。电池会定期充放电，一般为 90 天左右，当发现电量低 于某个阀值时，会将写 cache 策略从 writeback 置为 writethrough，相当于写 cache 会失效，这时如果系统有大量的 IO 操作，可能 会明显感觉到 IO 响应速度变慢。目前，新的 RAID 卡内置了 flash 存储，掉电后会将写 cache 的数据写入 flash 中，这样就可以保证数据永不丢 失，但依然需要电池的支持。
解决方案有两种：
(1) 人工触发充放电，可以选择在业务低谷时做，降低对应用的影响。
(2)设置写 cache 策略为 force write back，即使电池失效，也保持写 cache 策略为 writeback，这样存在掉电后丢失数据的风险。
目前，有一些硬件厂家提供了电容供电的 RAID 卡，没有电池充放电的问题，可以联系自己的硬件厂商。
2.RAID 卡配置
关闭读 cache：RAID 卡上的 cache 容量有限，我们选择 direct 方式读取数据，从而忽略读 cache。
关闭预读：RAID 卡的预读功能对于随机 IO 几乎没有任何提升，所以将预读功能关闭。
关闭磁盘 cache：一般情况下，如果使用 RAID，系统会默认关闭磁盘的 cache，也可以用命令强制关闭。
以上设置都可以通过 RAID 卡的命令行来完成，比如 LSI 芯片的 RAID 卡使用 megacli 命令。
3. 开启 Fastpath 功能
Fastpath 是 LSI 的新特性，在 RAID 控制器为 SSD 做了了优化，使用 fastpath 特性可以最大程度发挥出 SSD 的能力。如果使用 SSD 做 RAID 的方式，可以开启 fastpath 功能。关于 fastpath 特性，可以从 LSI 官网下载资料，并咨询自己的硬件提供商。
4.Fusionio 参数调整
基本上，Fusionio 无需做任何调整，下列三个参数可能会提升性能：
options iomemory-vsl use_workqueue=0
对于 fusionio 设备，忽略 Linux IO 调度，相当于使用 NOOP。
options iomemory-vsl disable-msi=0
开启 MSI 中断，如果设备支持，则打开。
options iomemory-vsl use_large_pcie_rx_buffer=1
打开 Large PCIE buffer，可能会提升性能。
操作系统
1.IO 调度算法
Linux 有四种 IO 调度算法：CFQ，Deadline，Anticipatory 和 NOOP，CFQ 是默认的 IO 调度算法。完全随机的访问环境 下，CFQ 与 Deadline，NOOP 性能差异很小，但是一旦有大的连续 IO，CFQ 可能会造成小 IO 的响应延时增加，所以数据库环境建议修改为 deadline 算法，表现更稳定。我们的环境统一使用 deadline 算法。
IO 调度算法都是基于磁盘设计，所以减少磁头移动是最重 要的考虑因素之一，但是使用 Flash 存储设备之后，不再需要考虑磁头移动的问题，可以使用 NOOP 算法。NOOP 的含义就是 NonOperation，意味着不会做任何的 IO 优化，完全按照请求来 FIFO 的方式来处理 IO。
减少预读：/sys/block/sdb/queue/read_ahead_kb，默认 128，调整为 16。
增大队列：/sys/block/sdb/queue/nr_requests，默认 128，调整为 512。
2.NUMA 设置
单机单实例，建议关闭 NUMA，关闭的方法有三种：
(1) 硬件层，在 BIOS 中设置关闭。
(2) OS 内核，启动时设置 numa=off。
(3) 可以用 numactl 命令将内存分配策略修改为 interleave(交叉)，有些硬件可以在 BIOS 中设置。
单机多实例，请参考：http://www.hellodb.net/2011/06/mysql_multi_instance.html
3. 文件系统设置
我们使用 XFS 文件系统，XFS 有两个设置：su(stripe size) 和 sw(stirpe width)，要根据硬件层 RAID 来设置这两个参数，比如 10 块盘做 RAID10，条带大小为 64K，XFS 设置为 su=64K，sw=10。
xfs mount 参数：defaults,rw,noatime,nodiratime,noikeep,nobarrier,allocsize=8M,attr2,largeio,inode64,swalloc
数据库
1.Flashcache 参数
创建 flashcache：flashcache_create -b 4k cachedev /dev/sdc /dev/sdb
指定 flashcache 的 block 大小与 Percona 的 page 大小相同。
Flashcache 参数设置：
flashcache.fast_remove = 1：打开 fast remove 特性，关闭机器时，无需将 cache 中的脏块写入磁盘。
flashcache.reclaim_policy = 1：脏块刷出策略，0：FIFO，1：LRU。
flashcache.dirty_thresh_pct = 90：flashcache 上每个 hash set 上的脏块阀值。
flashcache.cache_all = 1：cache 所有内容，可以用黑名单过滤。
flashecache.write_merge = 1：打开写入合并，提升写磁盘的性能。
2.Percona 参数
innodb_page_size：如果使用 fusionio，4K 的性能最好; 使用 SAS 磁盘，设置为 8K。如果全表扫描很多，可以设置为 16K。比较小的 page size，可以提升 cache 的命中率。
innodb_adaptive_checkpoint：如果使用 fusionio，设置为 3，提高刷新频率到 0.1 秒; 使用 SAS 磁盘，设置为 2，采用 estimate 方式刷新脏页。
innodb_io_capacity：根据 IOPS 能力设置，使用 fuionio 可以设置 10000 以上。
innodb_flush_neighbor_pages = 0：针对 fusionio 或者 SSD，因为随机 IO 足够好，所以关闭此功能。
innodb_flush_method=ALL_O_DIRECT：公版的 MySQL 只能将数据库文件读写设置为 DirectIO，对于 Percona 可以将 log 和数据文件设置为 direct 方式读写。但是我不确定这个参数对于 innodb_flush_log_at_trx_commit 的影响。
innodb_read_io_threads = 1：设置预读线程设置为 1，因为线性预读的效果并不明显，所以无需设置更大。
innodb_write_io_threads = 16：设置写线程数量为 16，提升写的能力。
innodb_fast_checksum = 1：开启 Fast checksum 特性。
监 控
1.fusionio 监控：fio-status 命令
Media status: Healthy; Reserves: 100.00%, warn at 10.00%
Thresholds: write-reduced: 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 监控：dmsetup status
read hit percent(99)
write hit percent(51)
dirty write hit percent(44)
关于 MySQL 数据库优化的方案与实践是怎样的就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
