MySQL数据库优化的方案与实践是怎样的

本文介绍了MySQL数据库的优化方案和实践，内容非常详细。感兴趣的朋友可以参考一下，希望对你有所帮助。

最近，我们整理了一些关于Percona、Linux、Flashcache和硬件设备优化的经验与大家分享：

硬件

1.开放BBWC

RAID卡全部缓存(电池备份写缓存，可以明显提高IO性能。因为停电会丢失数据，所以必须有电池支持。电池会定期充放电，一般90天左右。当发现功率低于某个阈值时，写缓存策略将从写回设置为写通，这相当于写缓存将无效。这时如果系统有大量的IO操作，可能会明显感觉到IO响应速度慢。目前新的RAID卡内置了闪存，断电后写入缓存的数据会写入闪存，这样数据永远不会丢失，但仍然需要电池的支持。

有两种解决方案：

(1)手动触发充放电，业务低的时候可以选择做，减少对应用的影响。

(2)设置写缓存策略强制回写，即使电池出现故障也保持写缓存策略回写，会导致断电后数据丢失的风险。

目前部分硬件厂商提供电容供电的RAID卡，不存在电池充放电问题，可以联系自己的硬件厂商。

2.磁盘阵列卡配置

关闭读缓存：缓存：RAID卡上的缓存容量有限，所以我们选择直接模式读取数据，从而忽略读缓存。

关闭预读：RAID卡的预读功能对于随机IO提升不大，所以关闭预读功能。

关闭磁盘缓存：一般情况下，如果使用RAID，系统会默认关闭磁盘缓存，也可以通过命令强制关闭。

以上设置可以通过RAID卡的命令行完成，比如LSI芯片的RAID卡使用megacli命令。

3.打开快速路径功能

Fastpath是大规模集成电路的新功能，固态硬盘在RAID控制器中进行了优化。使用快速路径功能可以最大限度地提高固态硬盘的性能。如果固态硬盘用作RAID，可以打开快速路径功能。对于fastpath功能，您可以从LSI官方网站下载资料，并咨询自己的硬件提供商。

4.Fusionio参数调整

基本上Fusionio不需要做任何调整，以下三个参数可能会提升性能：

options iomemory-vsl use _ workqueue=0

对于fusionio设备，忽略Linux IO调度相当于使用NOOP。

选项iomemory-vsl disable-msi=0

打开MSI中断，或者如果设备支持，将其打开。

选项iomemory-vsl use _ large _ pcie _ rx _ buffer=1

打开大型PCIE缓冲区可能会提高性能。

操作系统

1.输入输出调度算法

Linux有四种IO调度算法：CFQ、截止期、预期和NOOP。CFQ是默认的IO调度算法。在完全随机访问的环境下，CFQ与deadline和NOOP的性能差异很小，但一旦出现大的连续IO，CFQ可能会造成小IO的响应延迟增加，因此数据库环境建议将其修改为Deadline算法，性能更加稳定。我们的环境统一使用截止日期算法。

IO调度算法都是基于磁盘设计的，所以减少磁头移动是最重要的考虑因素之一。但是，在使用Flash存储设备后，我们可以使用NOOP算法来代替头部移动。NOOP表示不操作，也就是说我不会做任何IO优化，我会根据请求完全按照FIFO处理IO。

减少预读：/sys/block/SDB/queue/read _ ahead _ KB，默认为128，调整为16。

增加队列：/sys/block/sdb/queue/NR _ requests，默认值为128，调整为512。

2.NUMA设置

单个实例，建议关闭NUMA。关闭NUMA有三种方法：

(1)硬件层，在BIOS中设置关机。

(2) OS内核，启动时设置numa=off。

(3)可以通过numactl命令将内存分配策略更改为alternate，也可以在BIOS中设置一些硬件。

单机多实例，请参考：http://www.hellodb.net/2011/06/mysql_multi_instance.html.

3.文件系统设置

我们使用XFS文件系统，XFS有两个设置：su(条带大小)和sw(stirpe宽度)。这两个参数应该根据硬件级别RAID来设置，比如RAID10为10个磁盘，条带大小为64K，XFS设置为su=64K，sw=10。

Xfmount参数：默认值、rw、noatime、nodiratime、noikeep、no barrier、allocsize=8m、attr2、largeio、inod

e64,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数据库优化的方案与实践是怎样的就分享到这里了，希望