个人介绍：

姚艳庆  G云首席数据库架构师

主题介绍：

让MySQL在SSD上获得更优性能 ，介绍MySQL 如何能够根据写入原理进行系统调整、数据库配置来最大限度的发挥SSD性能

目录

SSD特性

磁盘挂载

磁盘IO子系统

InnoDB体系结构

参数配置

多表并发在SSD上的性能

总结

内容分享

1、SSD特性

SSD的物理组成

    •        闪存+主控+缓存+PCB+接口
    •        没有机械部件，数据读写都是电子讯号，不存在马达转速这样的瓶颈因素
    •        小文件随机性能较高
    •        SSD的随机读取延迟只有零点几毫秒，而7200RPM的随机读取延迟有7毫秒左右，5400RPM硬盘更是高达9毫秒之多
    •       支持掉电保护

 

::__IHACKLOG_REMOTE_IMAGE_AUTODOWN_BLOCK__::0

2、磁盘挂载的优化

Raid卡的Cache Policy配置

使用单颗Intel 3510时，单线程在WriteBack、WriteThrough两种策略下的测试结果

如下图：

::__IHACKLOG_REMOTE_IMAGE_AUTODOWN_BLOCK__::1

Nobarrier :

文件系统会在数据提交时强制底层设备刷新cache，避免数据丢失，称之为write barriers，由于通过文件系统进行刷新会有较多功能损耗，而数据库服务器底层存储设备基本都会采用具有掉电保护功能的RAID卡，固态硬盘也有自身的掉电保护机制，保证数据不会丢失，因此无需开启write barriers

Noatime:

当文件被创建，修改和访问时，Linux系统会记录这些时间信息。

当系统的读文件操作频繁时，对于数据库这种多文件构成的系统来说会增加大量的IO操作，而这类信息对用户来说意义并不大，因此应避免

针对挂载参数进行的测试结果如下：

::__IHACKLOG_REMOTE_IMAGE_AUTODOWN_BLOCK__::2

挂载参数带来的性能提升达到了20%以上

3、磁盘IO子系统

磁盘IO经常会成为系统的一个瓶颈，特别是对于运行数据库的系统而言。数据从磁盘读取到内存，在到CPU缓存和寄存器，然后进行处理，最后写回磁盘，中间要经过很多的过程，下图是一个以write为例的 Linux 磁盘IO子系统的架构

::__IHACKLOG_REMOTE_IMAGE_AUTODOWN_BLOCK__::3

目前磁盘的调度算法有如下四种

1、Anticipatory:

适用于个人PC，单磁盘系统；

2、CFQ(Complete Fair Queuing)：

默认的IO调度算法，完全公平的排队调度算法

3、Deadline:

按照截止期限来循环在各个IO队列中进行调度

4、NOOP:

简单的FIFO队列进行调度

由于SSD在对待顺 序IO和随机IO没有什么区别。所以它不需要对临近的IO进行合并。避免了合并操作对CPU的使用。

数据库采用SSD时，通过测试数据对比来看，采用Noop是较为合理的

4、InnoDB体系结构

如下图：

::__IHACKLOG_REMOTE_IMAGE_AUTODOWN_BLOCK__::4

Master Thread

核心后台线程，主要负责将缓冲池的数据异步刷新到磁盘。例如脏页的刷新，插入缓冲的合并，undo 页的回收等

Purge Thread

事务提交后。该事务相关的undolog可能不再需要。Purge Thread就是用来回收不需要的undo页的

PageCleanerThread

负责脏页的刷新操作。减轻master thread的工作以及对于用户查询线程的阻塞

内存缓冲池

对于数据库中页的修改操作，首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。这就意味着不是每次缓冲池中的页修改时触发刷新回磁盘，而是通过checkpoint技术刷新回磁盘

缓冲池的大小由 innodb_buffer_pool_size进行控制

允许多个缓冲池实例

这样通过hash到不同缓冲池实例来减少锁的竞争

由inodb_buffer_pool_instance进行控制

缓冲池是一个很大的内存区域，数据库通过LRU算法进行管理,LRU列表中加入的midpoint位置。新读取到的页，并不是直接放到lru列表的首部，而是midpoint位置。默认情况下，在lru列表长度的5/8处

由innodb_old_blocks_pct控制

两次写（Double Write)

l带给 Innodb存储引擎的是数据页的可靠性。l避免partial page write

Double write由两部分组成:

l内存中的 double write bufferl物理磁盘上的共享表空间中的连续128个页，大小和内存中（2M）一致

对缓冲池中的页进行刷新时，并不直接写磁盘，而是memcpy到 double writebuffer. 之后通过 double writebuffer 分两次，每次顺序写入共享表空间1M数据，然后马上调用fsync同步磁盘

由参数innodb_doublewrite进行控制

5、参数配置

刷新邻近页

当刷新一个脏页时，Innodb存储引擎会通过检测该页所在区的所有页，如果是脏页，会一起进行刷新

由参数innodb_flush_neighbors来进行控制

Check Point技术

为了避免数据丢失的问题，事物数据库系统一般都采用了write aheadlog策略。即事物提交时，先写重做日志，再修改页

由于数据修改先在缓冲池中进行，而由于缓冲池大小有限，那么当缓冲池刷新到磁盘时就需要CheckPoint技术来保证数据库的刷新

数据库宕机后重启时，不需要重做所有的日志了。因为 Check Point之前的页都已经刷新 到磁盘，数据库只需对Check Point后的重做日志进行恢复即可。

innodb_io_capacity

这个参数据控制Innodb checkpoint时的IO能力，一般可以按一块SAS 15000转的磁盘200个计算，6块盘的SAS做的Raid10这个值可以配到600即可。针对SSD需要根据线上的情况进行选择，下面是在单块SSD的情况下配置1000、4000两个值产生的性能差距

::__IHACKLOG_REMOTE_IMAGE_AUTODOWN_BLOCK__::5

注：实际测试中1000与4000的值有比较明显的差距，因此只列出了这两个值执行效率的差异对比

innodb_log_file_size

innodb_log_file_size在数据库高写入压力的线上系统也是会有一定影响

测试结果如下图

::__IHACKLOG_REMOTE_IMAGE_AUTODOWN_BLOCK__::6

innodb_flush_log_at_trx_commit

如果innodb_flush_log_at_trx_commit设置为0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。

如果innodb_flush_log_at_trx_commit设置为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.

如果innodb_flush_log_at_trx_commit设置为2，每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作

innodb_old_blocks_pct

缓存池可以被认为一条长LRU链表，该链表又分为2个子链表，一个子链表存放old pages(里面存放的是长时间未被访问的数据页)，另一个子链接存放new pages（里面存放的是最近被访问的数据页面）

::__IHACKLOG_REMOTE_IMAGE_AUTODOWN_BLOCK__::7

当系统为Update为主的应用时，尽量放大innodb_old_blocks_pct会获得较高的性能

将innodb_old_blocks_pct分别配置为5、90两个值获得如下结果

::__IHACKLOG_REMOTE_IMAGE_AUTODOWN_BLOCK__::8

innodb_max_dirty_pages_pct

通过 show engine innodb status \G来分析

观察Modified pages的较稳定值，通过调节innodb_max_dirty_pages_pct使Modified pages保持在(buffer poolsize)* innodb_max_dirty_pages_pct以下,可以获得较好的性能，在本测试环境中配置值为70.

innodb_adaptive_flushing_lwm

通过设置innodb_adaptive_flushing_lwm =0来关闭pre-flushing功能

innodb_lru_scan_depth

Innodb引擎有一个Page_cleaner线程用来扫描寻找dirty pages来进行flush 动作，这个后台操作每秒执行一次，在普通的IO压力下可增加此参数的值，如果属于写压力较大的的系统，可适当减小次参数的值

innodb_purge_threads

控制Purge Thread的数量，用来加快undo页的回收。同时由于Purge Thread需要离散地读取undo页，这样也能更进一步利用磁盘的随机读取性能

6、多表并发在SSD上的性能

针对SSD的随机写入与顺序写入,如果写入很小（就是说比簇大小要小），随机写入将比顺序写入慢。如果写入是按簇大小对齐，随机写入将使用所有可用层级上的内部并行，并显示出和随机写入相同的性能，根据SSD的此项特性，应尽量将应用进行多表并发写入，而避免单表写入的瓶颈效应

::__IHACKLOG_REMOTE_IMAGE_AUTODOWN_BLOCK__::9

很多并发的小写入请求将比单一的小写入请求提供更好的吞吐量。因此如果I/O比较小并不能整合到一起，最好是使用多线程。

单一的大写入请求和很多小并发写入请求相比，表现出相同的吞吐量，但会导致延迟。单一的大写入比并发写入在响应时间上表现的更好。因此，只要可能，最好使用大写入。

7、总结

更高效的SSD数据库性能应采取如下措施：

在SSD磁盘的读写性能低于Raid卡性能时，使用WB反之则需要采用WT策略

磁盘挂在参数调整:

nobarrier，noatime

系统 I/O 调度算法：

采用不需要寻道的块设备如SSD的Noop算法

分析 I/O 情况，动态调整innodb_io_capacity和innodb_max_dirty_pages_pct、innodb_purge_threads

数据库参数配置可根据线上压力调节以下参数获得较好性能:

innodb_flush_log_at_trx_commit

innodb_io_capacity

innodb_max_dirty_pages_pct

innodb_log_file_size

innodb_old_blocks_pct

innodb_adaptive_flushing_lwm

对数据库采用多表并发写，能够提升数据库的写入性能

数据库表控制在一定数量级，避免大表带来的性能损耗

QA

1、ssd现在成本比SAS高出多少，这个有对比吗？

答 : 目前SSD的价格已经比较亲民，对应单位IOPS，价格基本可以和SAS持平甚至更为便宜，只是在单位磁盘空间上性价比还略高。

2、ssd是怎么跟网络匹配的，iops上来了，io是否又会成为新的瓶颈？

答 : 数据库是一个高IO系统，iops对比网络请求来说要高出多个数量级，大部分情况下网络是不会成为瓶颈的。

3、多表并发写的话，会不会涉及到多表关联的问题，需不需要考虑考虑一定程度的同步，那么这样的话会不会增加软件的复杂度呢？

答 : 对于互联网应用来说，一般是通过数据冗余来解决多表联合查询，这样可以通过空间换取速度以及数据库锁带来的性能问题，复杂度是会增加，在高压力的情况下需要在冗余、复杂度与数据库承载之间进行取舍

4、有测试过pg和mysql的差距吗？

答 : 这个目前还没做过 ，目前公司有个别游戏在使用，后续会进行研究对比。

5、如何定位当前系统的总磁盘IO还是根据理论值？

答 : 理论值只是一个参考值，还是要根据线上使用监控工具去具体获取才真实，IO处理的数据量的不同，还要与磁盘的吞吐来结合判断。

6、除了生产环境使用SSD盘和调整参数来提高MySQL的性能之后，对应应用表设计有什么要求？

答 : 数据表的设计还是需要向前面提到的，通过一定数据冗余也就是反范式的方式进行设计，并且要尽量控制表的大小在一个百万级以下，这样可以加少数据过度搜索及数据库锁带来的额外性能损耗。。

7、怎么根据系统负载去调InnoDB性能参数,进一步提高Currenty ？

答 : 这个要配合当前系统状况 ，看应用是哪一种类型，比如是Update为主还是Insert 为主，这个例如前面 innodb_old_blocks_pct 的示例。

8、请问mysql的高可用架构用的是什么？MMM？MHA？

答 : 高可用建议还是采用MHA，MMM由于维护的成本较高，且需要配置自增等参数，容易带来很多维护上的难点。

9、有没有好的测试工具推荐？评测主机iops和定位SQL瓶颈的工具，然后根据怎么样的两者数值设置InnoDB参数？

答 : 在linux下一般使用fio，windows是iometer，定位mysql可以使用innotop等工具。

10、请问，当发现磁盘io负载高时，有什么办法尽快定位到是什么语句导致？

答 : 磁盘IO，可以通过slow日志来发现，可以将慢查询时间缩短，不断定位，还有可以通过log_queries_not_using_indexes等记录未使用索引的语句等

11、SQL是否需要一定需要SSD支持？SSD支撑优势在哪，是不是选取多STAT或是SCSI等普通盘跑 ？

答 : ssd的优势在于超高的IO吞吐能力，但是对应的空间性价比还是较低，在类似日志类的数据库、对于响应请求要求不高的就无需采用ssd，对于支付一类对于延迟有较高敏感度的应用那就应该采用ssd了，多磁盘混跑也是一个比较好的模式，可以极大的扩展单台数据库的极限承载。

12、可不可以介绍下你的测试办法？图中的数据是怎么获取的？有什么注意地方没有？

答 : 使用sysbench 进行测试来获取，测试过程需要进行环境清理，避免缓存记忆有其他负载时进行。

13、对于一个表记录大于默认4K，是否建议开启hugepage来提高其性能？

答 : 一般来说不建议，目前系统磁盘对于4K是有比较好的适应的。

14、SSD目前存储量上去没有

答 : 目前SSD已经可以达到TB级别。

15、对数据库监控一般重点关注那些点？常用那些工具？

答 :  重点关注服务器的系统负载如 : cpu利用率、load值、iops、磁盘的idld、awai、iowait等。数据库关注: 并发连接数、锁的数量、日志刷新情况、慢查询等，工具上可以采用系统上的top 、vmstat 、iostat、ifstat 、mysql可以使用innotop等。

图形化工具可以通过zabbix、cacti等开源工具进行监控，Percona附带的功能较多，优化方面做得也比较好，建议采用下。

16、有相关文档吗？不是分享内容的，是实战。

答 :  以后有机会可以再次与大家在实战方面进行交流分享，谢谢。

17、如果压力大的话，mysql主从同步这里有没有什么好的办法处理？

答 : 目前5.6 以上已经有并发同步的功能，5.7上的并发更加完善，如果同步压力较大建议选择。

18、請問老師:目前三星有單顆最大容量的 SSD：15TB還有其他推薦的廠牌嗎？

答 :  这个不太清楚 ...

19、问个和ssd无关的数据库问题，老姚应该percona的那个工具进行主从数据对对比吧？这个工具很耗时不？几百g的数据库需要多久才可以完成？这个操作会影响数据使用不？是否有风险？

答 : 只是说数据校验，不会对数据进行变更，校验肯定会带来磁盘IO、cpu计算上的压力的，还需在业务低峰期谨慎使用。

发表评论

邮箱地址不会被公开。 必填项已用*标注