MySQL技术内幕(InnoDB存储引擎概述)学习笔记05-性能调优

选择合适的CPU

首先需要弄清楚当前数据库的应用类型。一般而言,可分为两大类 : OLTP(Online Transaction Processing)和OLAP(Online Analytical Processing,在线分析处理)。这是两种截然不同的数据库应用。OLAP多用在数据仓库或数据集市中,一般需要执行复杂的SQL语句来进行查询;OLTP多用在日常的事务处理应用中国,如银行交易,在线商品交易,Blog,网络游戏等。相对于OLAP,数据库的容量较小。

InnoDB一般应用于OLTP的数据库应用,这种应用的特点如下

  • 用户操作的并发量大
  • 事务处理的时间一般比较短
  • 查询的语句较为简单,一般走索引
  • 复杂的查询少

可以看出,OLTP应用本身对CPU的要求不是很高,因为复杂的查询可能需要执行比较,排序,连接等非常耗CPU的操作,这些操作在OLTP的数据库应用中较少发生。因此,可以说OLAP是CPU密集型的操作,而OLTP是IO密集型的操作,建议在采购设备时,将更多的注意力放在提高IO的配置上。

此外,为了获得更多内存的支持,用户采购的CPU必须支持64位,否则无法支持64位操作系统的安装.

从InnoDB的设计架构上看,其主要的后台操作都是在一个单独的master thread中完成的,因此并不能很好地支持多核应用。当然,开源社区已经通过多种办法来改变这种局面,而InnoDB 1.0版本在各种测试环境下已经显示出对多核CPU的处理性能有了极大的提高,而InnoDB 1.2版本又支持多个purge线程,以及将刷新操作从master thread中分离出来。因此,如果用户的CPU支持多核,InnoDB的版本应该选择1.1或更高版本。另外,如果CPU是多核的,可以通过修改参数innodb_read_io_threads和innodb_write_io_threads来增大IO的线程,这样也能更充分有效地利用CPU的多核性能。

在当前的MySQL数据库版本中,一条SQL查询语句只能在一个CPU中工作,并不支持多核CPU的处理。OLTP的数据库应用一般操作都很简单,因此对于OLTP的应用影响不是很大。但是,多个CPU或多核CPU对处理大并发量的请求还是会有帮助。

内存的重要性

内存的大小最能直接反映数据库的性能。通过之前的学习,已经了解到InnDB即缓存数据,又缓存索引,并且将他们缓存于一个很大的缓冲池中,即InnoDB Buffer Pool。因此,内存的大小直接影响了数据库的性能。

判断内存是否达到了瓶颈

参数说明
innodb_buffer_pool_reads标识从物理磁盘读取页的次数
innodb_buffer_pool_read_ahead预读的次数
innodb_buffer_pool_read_ahead_evivted预读的页,但是没有被读取就从缓冲池中被替换的页的数量,一般用来判断预读的效率
innodb_buffer_pool_read_requests从缓冲池中读取页的次数
innodb_data_read总共读入的字节数
innodb_data_reads发起读取请求的次数,每次可能需要读取多个页

缓冲命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_ahead + innodb_buffer_pool_reads)
命中率越高,性能越高

平均每次读取的字节数 = innodb_data_read / innodb_data_reads

注意,即使缓冲池的大小已经大于数据库文件的大小,也并不意味着没有磁盘操作。数据库的缓冲池只是用来存放热点的区域,后台的线程还负责将脏页异步的写入到磁盘。此外,每次事务提交时还需要将日志写入到重做日志中。

硬盘对数据库性能的影响

传统机械硬盘

机械硬盘有两个重要的指标 : 寻道时间和转速。传统机械硬盘最大的问题在于读写磁头,读写磁头的设计使硬盘可以不再像磁带一样,只能进行顺序访问,而是可以随机访问。但是,机械硬盘的访问需要耗费长时间的磁头旋转和定位来查找,因此顺序访问的速度远远高于随机访问。传统关系型数据库的很多设计也是在尽量充分地利用顺序访问的特性。

通常来说,可以将多块机械硬盘组成RAID来提高数据库的性能,也可以将数据文件分布在不同硬盘上来达到访问负载的均衡。

固态硬盘

固态硬盘,更准确地说是基于闪存的固态硬盘,是近几年出现的一种新的存储设备,其内部由闪存(Flash Memory)组成。因为闪存的低延迟性,低功耗,以及防震性,闪存设备已经在移动设备上得到了广泛的应用。

不同于传统的机械硬盘,闪存是一个完全的的电子设备,没有传统机械硬盘的读写磁头。因此,固态硬盘不需要像传统机械硬盘一样,需要耗费大量时间的磁头旋转和定位来查找数据,所以固态硬盘可以一致的随机访问时间。

另一方面,闪存中的数据是不可以更新的,只能通过扇区(sctor)的覆盖重写,而在覆盖重写之前,需要执行非常耗时的擦除操作(erase)操作。擦除操作不能在所含数据的扇区上完成,而需要在删除整个被称为删除块的基础上完成,这个擦除块的尺寸大于扇区的大小,通常为128KB或256KB。此外,每个擦除块有擦写次数的限制。已经有一些算法来解决这个问题。但是对于数据库应用,需要认真考虑固态硬盘在写入方面存在的问题。

因为存在上述写入方面的问题,闪存的读写速度是非对称的。读取速度要远快于写入的速度,因此对于固态硬盘在数据库中的应用,应该好好利用其读取的性能,避免过度的写入操作。

由于闪存是一个完全的电子设备,没有读写磁头等移动部件,因此固态硬盘有着较低的访问延时。当主机发布一个读写请求时,固态硬盘的控制机会把I/O命令从逻辑地址映射成实际的物理地址,写操作还需要修改相应的映射表信息。算上这些额外的开销,固态硬盘的访问延时一般小于0.1ms左右。

对于固态硬盘在InnoDB中的优化,可以增加innodb_io_capacity变量的值达到充分利用固态硬盘带来的高IOPS特效。不过这需要用户根据自己的应用进行针对性的调整。在InnoDB 1.2版本中,可以选择关闭邻接页刷新,同样会为数据库的性能带来一定效果的提升。

合理的设置RAID

RAID类型

RAID(Redundant Array of Independent Disks,独立磁盘冗余数组)的基本思想就是把多个相对便宜的硬盘组合起来,成为一个磁盘数组,使性能达到升值超过一个价格昂贵,容量巨大的硬盘。由于将多个硬盘组合成为一个逻辑扇区,RAI看起来就像一个单独的硬盘或逻辑存储单元,因此操作系统只会把他当做一个硬盘。

RAID的作用是 :

  • 增加数据集成度
  • 增加容错功能
  • 增加处理量或容量

根据不同磁盘的组合方式,常见的RAID组合方式可分为RAID 0,RAID 1,RAID 5,RAID 10和RAID 50等。

RAID 0: 将多个磁盘合并成一个大的磁盘,不会有冗余,并行I/O,速度最快。 RAID 0亦称为带区集,他将多个磁盘并列起来,使之成为一个大磁盘。在存放数据时,其将数据按磁盘的个数进行分段,同时将这些数据写进这些盘中。所以,在所有级别中,RAID 0的速度是最快的。但是RAID 0没有冗余功能,如果一个磁盘(物理)损坏,则所有的数据都将丢失。理论上,多磁盘的性能等于 : 单一磁盘效能 * 磁盘数,但实际上受限于总线I/O瓶颈及其他因素的影响,RAID效能会随边际递减。也就是说,假设一个磁盘的效能是50MB/s,两个磁盘的RAID 0效能约为96MB/s,三个磁盘的RAID 0也许就是130MB/s而不是150MB/s了。

RAID 1 : 两组以上的N个磁盘相互作为镜像,在一些多线程操作系统中能有很好的读取速度,但写入速度略有降低。除非拥有相同数据的主磁盘和镜像同时损坏,否则只要有一个磁盘正常即可维持运作,可靠性最高。RAID 1就是镜像,其原理为在主硬盘上存放数据的同时也在镜像硬盘上写相同的数据。当主硬盘(物理)损坏时,镜像硬盘则代替主硬盘的工作。因为镜像硬盘做数据备份,所以RAID 1在所有的RAID级别上来说是最好的。但是,无论使用多少磁盘作为RAID 1,仅算·一个磁盘容量,是所有RAID级别中使用率最低的级别。

RAID 5 : 是一种存储性能,数据安全和存储成本兼顾的存储解决方案。它使用的是Disk Striping(硬盘分区)技术。RAID 5至少需要三个硬盘,RAID 5不对存储的数据进行备份,而是将数据和相对应的奇偶校验信息存储于不同的磁盘上。当RAID 5的一个磁盘数据发生损坏后,利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。RAID 5可以理解为是RAID 0和RAID 1的折中方案。RAID 5可以为系统提供数据安全保障,但保障程度比镜像低而磁盘利用率比镜像高。RAID 5具有和RAID 0相近似的数据读取速度,只是多了一个奇偶校验信息,写入数据的速度相当慢,若使用Write Back可以让性能改善不少,同时,由于多个数据对应一个奇偶校验信息,RAID 5的磁盘空间利用率要比RAID 1高,存储成本相对较低。

RAID 10和RAID 01 : RAID 10是先镜像再分区数据,将所有硬盘分为两组,视为RAID 0的最低组合,然后将这两组各自视为RAID 1运作。RAID 1-有着不错的读取速度,而且拥有比RAID 0更高的数据保护性。RAID 01则与RAID 10的程序相反,先分区再将数据镜射到两组硬盘。RAID 01将所有的硬盘分为两组,变成RAID 1的最低组合,而将两组硬盘各自视为RAID 0运作。RAID 01比RAID 10有着更快的读写速度,不过也多了一些会让整个磁盘组停止运转的几率,因为只要同一组的硬盘全部损毁,RAID 01就会停止运作,而RAID 10可以在牺牲RAID 0的优势下正常运作。RAID 10巧妙的利用了RAID 0的速度及RAID 1的安全(保护)两种特性,他的缺点的是需要更多的磁盘,因为至少必须有四个以上的偶数磁盘才能使用。

RAID 50 : RAID 50也被称为镜像阵列条带,由至少六块磁盘组成,像RAID 0一样,数据被分区成条带,在同一时间内向多块磁盘写入;像RAID 5一样,也是以数据的校验位来保证数据的安全,且检验条带分布在各个磁盘上,其目的在于提高RAID 50的读写性能。

对于数据库应用来说,RAID 10是最好的选择,它同时兼顾了RAID 1和RAID 0的特性,但是当一个磁盘失效时,性能可能会受到很大的影响,因为条带(strip)会成为瓶颈

RAID Write Back功能

RAID Write Back功能是指RAID控制器能够将写入的数据放入自身的缓存中,并把他们安排到后面再执行。这样做的好处是,不用等待物理磁盘实际写入的完成,因此写入变得更快了。对于数据库来说,这显得十分重要。例如,对重做日志的写入,在将sync_binlog设为1的情况下二进制日志的写入,脏页的刷新等性能都能得到明显的提升。

但是,当操作系统或数据库宕机时,Write Back功能可能会破坏数据库的数据,这是由于已经写入的数据可能还在RAID卡的缓存中,数据可能没有完全写入磁盘,而这时故障发生了。为了解决这个问题,目前大部分的硬件RAID卡都提供了电池备份单元(BBU,Battery Backup Unit),因此可以放心的开启Write Back的功能

操作系统的选择

Linux是MySQL数据库服务器最常使用的操作系统。与其他操作系统不同的是Linux拥有众多的发行版本,每个用户的偏好可能不尽相同。然而在将Linux作为数据库服务器时需要考虑更多的是操作系统的稳定性,而不是新特性。

除了Linux操作系统外,FreeBSD也是另一个常见的优秀操作系统。之前版本的FreeBSD对MySQL数据库支持不是很好,需要选择单独的线程库进行手动编译。新版的直接安全即可。

Solaris也是非常不错的操作系统,之前是基于SPARC硬件的操作系统,现在已经移植到了X86平台上。Solaris是高性能,高可靠性的操作系统,
。。。略。。。

后面的内容不是这次的目标,就略过了。。。

标签: mysql

添加新评论