性能优化不是一项简单的工作,但也不是复杂的难事,关键在于对 InnoDB存储引擎特性的了解。如果之前各章的内容读者已经完全理解并掌握了,那就应该基本掌握了如何使 InnoDB存储引擎更好地工作。本章将从以下几个方面集中讲解 InnodB存储引擎的性能问题:

• 选择合适的CPU
• 内存的重要性
• 硬盘对数据库性能的影响
• 合理地设置RAID
• 操作系统的选择也很重要
• 不同文件系统对数据库的影响
• 选择合适的基准测试工具

1、选择合适的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位操作系统的安装。因此,为新的应用选择64位的CPU是必要的前提。现在4核的CPU已经非常普遍,如今 Intel和AMD又相继推出了8核的CPU,将来

随着操作系统的升级我们还可能看到128核的CPU,这都需要数据库更好地对其提供支持。

从 InnoDB存储引擎的设计架构上来看,其主要的后台操作都是在一个单独的master thread中完成的,因此并不能很好地支持多核的应用。当然,开源社区已经通过多种方法来改变这种局面,而InnoDB1.0版本在各种测试下已经显示出对多核CPU的处理性能的支持有了极大的提高,而 InnoDB1.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对处理大并发量的请求还是会有帮助。

2、内存的重要性

内存的大小是最能直接反映数据库的性能。通过之前各个章节的介绍,已经了解到InnoDB存储引擎既缓存数据,又缓存索引,并且将它们缓存于一个很大的缓冲池中,即InnoDB Buffer Pool因此,内存的大小直接影响了数据库的性能。 Percona公司的CTO Vadin对此做了一次测试,以此反映内存的重要性,结果如图9-1所示。

在上述测试中,数据和索引总大小为18GB,然后将缓冲池的大小分别设为2GB、4GB、6GB、8GB、10GB、12GB、14GB、16GB、18GB、20GB、22GB,再进行 sysbench的测试。可以发现,随着缓冲池的增大,测试结果TPS(Transaction Per Second)会线性增长。当缓冲池增大到20GB和2GB时,数据库的性能有了极大的提高,因为这时缓冲池的大小已经大于数据文件本身的大小,所有对数据文件的操作都可以在内存中进行。因此这时的性能应该是最优的,再调大缓冲池并不能再提高数据库的性能所以,应该在开发应用前预估“活跃”数据库的大小是多少,并以此确定数据库服务器内存的大小。当然,要使用更多的内存还必须使用64位的操作系统。

如何判断当前数据库的内存是否已经达到瓶颈了呢?可以通过查看当前服务器的状态,比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率,通常 InnoDB存储引擎的缓冲池的命中率不应该小于99%,如:

mysql> show global status like 'innodb%read%';
+---------------------------------------+---------------+
| Variable_name                         | Value         |
+---------------------------------------+---------------+
| Innodb_buffer_pool_read_ahead_rnd     | 0             |
| Innodb_buffer_pool_read_ahead         | 4180          |
| Innodb_buffer_pool_read_ahead_evicted | 0             |
| Innodb_buffer_pool_read_requests      | 472889481455  |
| Innodb_buffer_pool_reads              | 33048         |
| Innodb_data_pending_reads             | 0             |
| Innodb_data_read                      | 1330352128    |
| Innodb_data_reads                     | 76350         |
| Innodb_master_thread_active_loops     | 922124        |
| Innodb_master_thread_idle_loops       | 8245265       |
| Innodb_pages_read                     | 81193         |
| Innodb_rows_read                      | 1329913665393 |
| Innodb_read_views_memory              | 3832          |
+---------------------------------------+---------------+
13 rows in set (0.01 sec)复制代码

当前服务器的状态参数

可以通过以下公式计算InnoDB缓存池的命中率:

(1-innodb_buffer_pool_reads/inodb_buffer_pool_read_request)*100复制代码

如果命中率太低，则应考虑扩充内存，增加innodb_buffer_pool_size的值。

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

3、硬盘对数据库性能的影响

3.1、传统机械硬盘

当前大多数数据库使用的都是传统的机械硬盘。机械硬盘的技术目前已非常成熟,在服务器领域一般使用SAS或SATA接口的硬盘。服务器机械硬盘开始向小型化转型,目前大部分使用25寸的SAS机械硬盘。

机械硬盘有两个重要的指标:一个是寻道时间,另一个是转速。当前服务器机械硬盘的寻道时间已经能够达到3ms,转速为15000RM(rotate per minute)。传统机械硬盘最大的问题在于读写磁头,读写磁头的设计使硬盘可以不再像磁带一样,只能进行顺序

访问,而是可以随机访问。但是,机械硬盘的访问需要耗费长时间的磁头旋转和定位来查找,因此顺序访问的速度要远高于随机访问。传统关系数据库的很多设计也都是在尽量充分地利用顺序访问的特性。

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

3.2、固态硬盘

固态硬盘,更准确地说是基于闪存的固态硬盘,是近几年出现的一种新的存储设备,其内部由闪存( Flash Memory)组成。因为闪存的低延迟性、低功耗,以及防震性,闪存设备已在移动设备上得到了广泛的应用。企业级应用一般使用固态硬盘,通过并联多块闪存来进一步提高数据传输的吞吐量。传统的存储服务提供商EMC公司已经开始提供基于闪存的固态硬盘的TB级别存储解决方案。数据库厂商 Oracle公司最近也开始提供绑定固态硬盘的 Exadata服务器。

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

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

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

图9-2显示了一个双通道的固态硬盘架构,通过支持4路的闪存交叉存储来降低固态硬盘的访问延时,同时增大并发的读写操作。通过进一步增加通道的数量,固态硬盘的性能可以线性地提高,例如我们常见的 Intel X25M固态硬盘就是10通道的固态硬盘。

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

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

此外,还可以使用 InnoSQL开发的L2 Cache解决方案,该解决方案可以充分利用固态硬盘的超高速随机读取性能,在内存缓冲池和传统存储层之间建立一层基于闪存固态硬盘的二级缓冲池,以此来扩充缓冲池的容量,提高数据库的性能。与基于磁盘的固态硬盘 Cache类似的解决方案还有 Facebook Flash Cache和 bcache,只不过它们是基于通用文件系统的,对 InnoDB存储引擎本身的优化较少。

4、合理地设置RAID

4.1RAID类型

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

RAID的作用是

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

根据不同磁盘的组合方式,常见的RAID组合方式可分为RAID0、RAID1、RAID5、RAID10和RAID50等。

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

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

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

RAID10和RAD01:RAID10是先镜像再分区数据,将所有硬盘分为两组,视为RAID0的最低组合,然后将这两组各自视为RAID1运作。

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

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

对于数据库应用来说,RAID10是最好的选择,它同时兼顾了RAID1和RAID0的特性。但是,当一个磁盘失效时,性能可能会受到很大的影响,因为条带(strip)会成为瓶颈。我曾在生产环境下遇到过的情况是,两台负载基本相同的数据库,一台正常的服务器磁盘IO负载为20%左右,而另一台服务器IO负载却高达90%。

4.2、RAID Write Back功能

RAID Write Back功能是指RAID控制器能够将写入的数据放入自身的缓存中,并把它们安排到后面再执行。这样做的好处是,不用等待物理磁盘实际写入的完成,因此写入变得更快了。对于数据库来说,这显得十分重要。例如,对重做日志的写人,在将sync_binlog设为1的情况下二进制日志的写入、脏页的刷新等都可以使性能得到明显的提升但是,当操作系统或数据库关机时, Write Back功能可能会破坏数据库的数据。这是由于已经写人的数据库可能还在RAID卡的缓存中,数据可能并没有完全写入磁盘,而这时故障发生了。为了解决这个问题,目前大部分的硬件RAID卡都提供了电池备份单元(BBU, Battery Backup Unit),因此可以放心地开启 Write Back的功能。不过我发现每台服务器的出厂设置都不相同,应该将RAID设置要求告知服务器提供商,开启一些认为需要的参数。

如果没有启用 Write Back功能,那么在RAID卡设置中显示的就是 Write Through。

Write Through没有缓冲写入,因此写入性能可能不是很好,但它却是最安全的写入即使用户开启了 Write Back功能,RAID卡也可能只是在 Write Through模式下工作。

这是因为安全使用 Write back的前提是RAID卡有电池备份单元。为了确保电池的有效性,RAID卡会定期检查电池状态,并在电池电量不足时对其进行充电,在充电的这段时间内会将 Write Back功能切换为最为安全的 Write Through。

用户可以在没有电池备份单元的情况下强制启用 Write Back功能,也可以在电池充电时强制使用 Write Back功能,只是写入是不安全的。用户应该非常确信这点,否则不应该在没有电池备份单元的情况下启用 Write back可以通过插入20W的记录来比较 Write back和 Write Through的性能差异:

首先创建一个向表t插入20W记录的存储过程,并在 Write Back和 Write Through的设置下分别进行测试,最终测试结果如表9-2所示。

由于批量插入不是在一个事务中完成的,而是直接用命令CALP来运行的,因此数据库实际执行了20W次的事务。很明显可以看到,在 Write Back模式下执行时间只需要43秒,而在 Write Through模式下执行时间需要31分钟,大约有40多倍的差距。

当然,在 Write Through模式下,通过将参数 innodb_flush_log_at_trx_commit设置为0也可以提高执行存储过程P的性能,这时只需要68秒了。因为,在此设置下,重做日志的写入不是发生在每次事务提交时,而是发生在后台 master线程每秒钟自动刷新的时候,因此减少了物理磁盘的写入请求,所以执行速度也可以有明显的提高。

4.3、RAID配置工具

对RAID卡进行配置可以在服务器启动时进入一个类似于BIOS的配置界面,然后再对其进行各种设置。此外,很多厂商都开发了各种操作系统下的软件对RAID进行配置,如果用户使用的是LSI公司生产提供的RAID卡,则可以使用 MegaCLI工具来进行配置。

MegaCLI为多个操作系统提供了支持,对 Windows操作系统还提供了GU界面的配置环境,因此相对来说比较简单。这里主要介绍命令行下 MegaCLI的使用,在Windows下同样可以使用命令 MegaCLI.exe。

使用 MegaCL查看RAID卡的信息：

MegaLI还可以用来查看当前物理磁盘的信息,如:

通过上面的结果可以发现当前开启了RAID卡的 Write Back功能,并且当BBU有问题时或在充电时禁用 Write Back功能。此外,这里还显示了不需要启用RAID卡的预读功能,写入方式为直接写入通过下面的命令可以对当前的写入策略进行调整:

特别需要注意地是,当RAID卡的写人策略从 Write Back切换为 Write Through时,该更改立即生效。然而从 Write Through切换为 Write Back时,必须重启服务器才能使其生效。

5、操作系统的选择

Linux是 MySQL数据库服务器中最常使用的操作系统。与其他操作系统不同的是Linux有着众多的发行版本,每个用户的偏好可能不尽相同。然而在将 Linux操作系统作为数据库服务器时需要考虑更多的是操作系统的稳定性,而不是新特性除了 Linux操作系统外, FreeBSD也是另一个常见的优秀操作系统。之前版本的FreeBSD对 MySQL数据库支持得不是很好,需要选择单独的线程库进行手动编译但是新版本的 FreeBSD对 MySQL数据库的支持已经好了很多,直接下载二进制安装包即可。

Solaris也是非常不错的操作系统,之前是基于 SPARC硬件的操作系统,现在已经移植到了X86平台上。 Solaris是高性能、高可靠性的操作系统,同时其提供的ZFS文件系统非常适合 MySQL的数据库应用。如果需要,用户可以尝试它的开源版本open Solaris。

Windows操作系统在 MySQL数据库应用中也非常普及。也有公司喜欢在开发环境下使用 Windows版本的 MySQL数据库,而在正式生产环境下选择使用 Linux操作系统。这本身没有什么问题,但问题通常存在于文件系统大小写敏感对应用程序的影响。

在 Windows操作系统下表名不区分大小写,而 Linux操作系统却是大小写敏感的,这点在开发阶段需要特别注意。

4G内存在当前已经非常普遍了,即使是桌面用户也开始使用8G的内存。为了可以更好地使用大于4G的内存容量,用户必须使用64位的操作系统,上述介绍的这些操作系统都提供了64位的版本。此外,使用64位的操作系统还必须使用64位的软件。这听上去像是句废话,但是我曾多次看到32位的 MySQL数据库安装在64位的系统上,导致不能充分发挥64位操作系统的内存寻址能力。

6、不同的文件系统对数据库性能的影响

每个操作系统都默认支持一种文件系统并推荐用户使用,如 Windows默认支持NTFS, Solaris默认支持ZFS。而对于 Linux这样的操作系统,不同发行版本默认支持的文件系统各不相同,有的默认支持EXT3,有的是 ReiserFS,有的是EXT4,有的是XFS。

虽然不同特性的文件系统有很多,但是在实际使用过程中从未感觉到文件系统的性能差异有多大。网上有多个关于XFS文件系统的“神话”,认为其是多么地适合数据库应用,性能较之EXT3有极大的提升。但是在实际测试和使用后发现,它的性能和EXT3在整体上没有大的差距。因此,DBA首先应该把更多的注意力放到数据库上,而不是纠结于文件系统。

文件系统可提供的功能也许是DBA需要关注的,例如ZFS文件系统本身就可以支持快照,因此就不需要IM这样的逻辑卷管理工具。此外,可能还需要知道 mount的参数,这些参数在每个文件系统中可能有所不同。