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

73次阅读
没有评论

共计 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 数据库优化的方案与实践是怎样的就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-18发表,共计3417字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)