MySQL-性能调优

性能调优

• 选择合适的CPU
• 内存的重要性
• 硬盘对数据库性能的影响
• • 传统机械硬盘
  • 固态硬盘
• 合理地设置RAID
• • RAID 类型
  • • RAID 0
    • RAID 1
    • RAID 5
    • RAID 10 和 RAID 01
    • RAID 50
    • 总结
  • RAID Write Back功能
  • RAID配置工具(待完善)
• 操作系统的选择
• 不同的文件系统对数据库性能的影响
• 选择合适的基准测试工具
• • sysbench(待完善)
  • mysql-tpcc(待完善)

将从以下几个方面集中讲解InnoDB存储引擎的性能问题：

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

选择合适的CPU

当前数据库的应用类型一般可分为两大类：OLTP（Online Transaction Processing，在线事务处理）和OLAP（Online Analytical Processing，在线分析处理）

OLAP多用在数据仓库或数据集市中，一般需要执行复杂的SQL语句来进行查询

OLTP多用在日常的事物处理应用中，如银行交易、在线商品交易、Blog、网络游戏等应用。相对于OLAP，数据库的容量较小

InnoDB存储引擎一般都应用于OLTP的数据库应用，这种应用的特点如下：

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

OLTP的数据库应用本身对CPU的要求并不是很高，因为复杂的查询可能需要执行比较、排序、连接等非常耗CPU的操作，这些操作在OLTP的数据库应用中较少发生

可以说OLAP是CPU密集型的操作，而OLTP是IO密集型的操作

从InnoDB存储引擎的设计架构上来看，其主要的后台操作都是在一个单独的master thread中完成的，因此并不能很好地支持多核的应用

InnoDB1.0版本在各种测试下已经显示出对多核CPU的处理性能的支持有了极大的提高

InnoDB1.2版本又支持多个purge线程，以及将刷新操作从master thread中分离出来

因此，若CPU支持多核，InnoDB的版本应该选择1.1或更高版本

另外，还可以通过修改参数

innodb_read_io_threads

和

innodb_write_io_threads

来增大IO的线程，这样也能更充分有效地利用CPU的多核性能

在当前的MySQL数据库版本中，一条SQL查询语句只能在一个CPU中工作，并不支持多CPU的处理

OLTP的数据库应用操作一般都很简单，因此对OLTP应用的影响并不是很大

但多个CPU或多核CPU对处理大并发量的请求还是会有帮助

内存的重要性

内存的大小是最能直接反映数据库的性能

InnoDB存储引擎 既 缓存数据，又 缓存索引，并且将它们缓存于一个很大的缓冲池中，即InnoDB Buffer Pool。因此，内存的大小直接影响了数据库的性能

如何判断当前数据库的内存是否已经达到瓶颈了？

可以通过查看当前服务器的状态，比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率，通常InnoDB存储引擎的缓冲池的命中率不应该小于99%

SHOW GLOBAL STATUS LIKE 'innodb%read%'

Variable_name,Value
"Innodb_buffer_pool_read_ahead_rnd","0"
"Innodb_buffer_pool_read_ahead","0"
"Innodb_buffer_pool_read_ahead_evicted","0"
"Innodb_buffer_pool_read_requests","188769811"
"Innodb_buffer_pool_reads","1182"
"Innodb_data_pending_reads","0"
"Innodb_data_read","19436544"
"Innodb_data_reads","1207"
"Innodb_pages_read","1182"
"Innodb_rows_read","140634457"
"Innodb_ready_for_write_lsn","333002131"

           

具体含义如下：

以下公式可以计算各种对缓冲池的操作

即使缓冲池的大小已经大于数据库文件的大小，这也并不意味着没有磁盘操作

数据库的缓冲池 只是一个 用来存放 热点的区域，后台的线程 还负责 将脏页 异步地 写入到磁盘

此外，每次事务提交时 还需要 将日志 写入 重做日志文件

硬盘对数据库性能的影响

传统机械硬盘

机械硬盘有两个重要的指标：一个是寻道时间，另一个是转速

当前服务器机械硬盘的寻道时间已经能够达到3ms，转速为15 000RPM（rotate per minute）

传统机械硬盘最大的问题在于读写磁头，读写磁头的设计 使硬盘 可以不再像 磁带一样，只能进行顺序访问，而是可以随机访问

机械硬盘的访问 需要耗费 长时间的磁头旋转和定位 来查找，因此 顺序访问的速度 要远高于 随机访问

传统关系数据库的很多设计也都是在尽量充分地利用顺序访问的特性

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

固态硬盘

固态硬盘，更准确地说是基于闪存的固态硬盘，内部由闪存（Flash Memory）组成

企业级应用一般使用固态硬盘，通过并联多块闪存来进一步提高数据传输的吞吐量

不同于传统的机械硬盘，闪存是一个完全的电子设备，没有传统机械硬盘的读写磁头

固态硬盘不需要像传统机械硬盘一样，需要耗费大量时间的磁头旋转和定位来查找数据，所以固态硬盘可以提供一致的随机访问时间

闪存中的数据是不可以更新的，只能通过扇区（sector）的覆盖重写，而在覆盖重写之前，需要执行非常耗时的擦除（erase）操作

擦除操作 不能在 所含数据的扇区上 完成，而 需要在 删除整个 被称为 擦除块的基础上 完成，这个擦除块的尺寸 大于 扇区的大小，通常为128KB或者256KB

每个擦除块 有擦写次数 的 限制，对于数据库应用，需要认真考虑固态硬盘在写入方面存在的问题

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

由于闪存是一个完全的电子设备，没有读写磁头等移动部件，因此固态硬盘有着较低的访问延时

当发布一个读写请求时，固态硬盘的控制器 会把 I/O命令 从逻辑地址 映射成 实际的物理地址，写操作 还需要 修改相应的映射表信息。算上这些额外的开销，固态硬盘的访问延时一般小于0.1ms左右

对于固态硬盘在InnoDB存储引擎中的优化，可以增加

innodb_io_capacity

变量的值 达到 充分利用 固态硬盘带来的高IOPS特性

在InnoSQL及InnoDB1.2版本中，可以选择 关闭 邻接页的刷新，同样可以为数据库的性能带来一定效果的提升

还可以使用InnoSQL开发的L2 Cache解决方案，该解决方案 可以 充分利用 固态硬盘的超高速随机读取性能，在 内存缓冲池 和 传统存储层 之间 建立一层 基于闪存固态硬盘 的 二级缓冲池，以此来扩充缓冲池的容量，提高数据库的性能

与基于磁盘的固态硬盘Cache类似的解决方案还有Facebook Flash Cache和bcache，只不过它们是基于通用文件系统的，对InnoDB存储引擎本身的优化较少

合理地设置RAID

RAID 类型

RAID（Redundant Array of Independent Disks，独立磁盘冗余数组）的基本思想就是把多个相对便宜的硬盘组合起来，成为一个磁盘数组，使性能达到甚至超过一个价格昂贵、容量巨大的硬盘

由于将多个硬盘组合成为一个逻辑扇区，RAID看起来就像一个单独的硬盘或逻辑存储单元，因此操作系统只会把它当作一个硬盘。

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

RAID 5是一种存储性能、数据安全和存储成本兼顾的存储解决方案

它使用的是Disk Striping（硬盘分区）技术

RAID 5至少需要三个硬盘，RAID 5不对存储的数据进行备份，而是把 数据 和 相对应的奇偶校验信息 存储到 组成RAID 5的各个磁盘上，并且奇偶校验信息和相对应的数据分别存储于不同的磁盘上

当RAID 5的一个磁盘数据发生损坏后，利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据

RAID 5可以理解为是RAID 0和RAID 1的折中方案

RAID 5可以为系统提供数据安全保障，但保障程度要比镜像低 而 磁盘空间利用率要比镜像高

RAID 5具有和RAID 0相近似的数据读取速度，只是多了一个奇偶校验信息，写入数据的速度相当慢，若使用Write Back可以让性能改善不少

同时，由于多个数据对应一个奇偶校验信息，RAID 5的磁盘空间利用率要比RAID 1高，存储成本相对较低

• 问题：如果存储了奇偶校验信息得disk坏了怎么办？

RAID 10 和 RAID 01

RAID 10是 先镜像 再分区 数据，将所有硬盘分为两组，视为RAID 0的最低组合，然后将这两组各自视为RAID 1运作

RAID 10有着不错的读取速度，而且拥有比RAID 0更高的数据保护性

RAID 10巧妙地利用了RAID 0的速度 及 RAID 1的安全（保护）两种特性，它的缺点是需要较多的硬盘，因为至少必须拥有四个以上的偶数硬盘才能使用

RAID 01则与RAID 10的程序相反，先 分区 再将 数据 镜射到 两组硬盘

RAID 01将所有的硬盘分为两组，变成RAID 1的最低组合，而将两组硬盘各自视为RAID 0运作

RAID 01比RAID 10有着更快的读写速度，不过也多了一些会让整个硬盘组停止运转的几率，因为只要同一组的硬盘全部损毁，RAID 01就会停止运作，而RAID 10可以在牺牲RAID 0的优势下正常运作

RAID 50

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

总结

对于数据库应用来说，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的功能

如果没有启用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

RAID配置工具(待完善)

操作系统的选择

Linux是MySQL数据库服务器中最常使用的操作系统

与其他操作系统不同的是Linux有着众多的发行版本，每个用户的偏好可能不尽相同

在将Linux操作系统作为数据库服务器时需要考虑更多的是操作系统的稳定性，而不是新特性

除了Linux操作系统外，FreeBSD也是另一个常见的优秀操作系统

之前版本的FreeBSD对MySQL数据库支持得不是很好，需要选择单独的线程库进行手动编译，但是新版本的FreeBSD对MySQL数据库的支持已经好了很多，直接下载二进制安装包即可

Solaris也是非常不错的操作系统，之前是基于SPARC硬件的操作系统，现在已经移植到了X86平台上

Solaris是高性能、高可靠性的操作系统，同时其提供的ZFS文件系统非常适合MySQL的数据库应用。如果需要，用户可以尝试它的开源版本Open Solaris

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

每个操作系统都默认支持一种文件系统并推荐用户使用，虽然不同特性的文件系统有很多，但是在实际使用过程中从未感觉到文件系统的性能差异有多大

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

选择合适的基准测试工具

sysbench(待完善)

sysbench是一个模块化的、跨平台的多线程基准测试工具，主要用于测试各种不同系统参数下的数据库负载情况。它主要包括以下几种测试方式：

• CPU性能
• 磁盘IO性能
• 调度程序性能
• 内存分配及传输速度
• POSIX线程性能
• 数据库OLTP基准测试

sysbench的官网地址是：http://sysbench.sourceforge.net，可以从该地址下载最新版本的sysbench工具，然后进行编译和安装

mysql-tpcc(待完善)

TPC（Transaction Processing Performance Council，事务处理性能协会）是一个 用来 评价 大型数据库系统软硬件性能的非盈利组织

TPC-C是TPC协会制定的，用来测试典型的复杂OLTP（在线事务处理）系统的性能

TPC-C用3NF（第三范式） 虚拟实现了 一家仓库销售供应商公司，拥有一批分布在不同地方的仓库和地区分公司

当公司业务扩大时，将建立新的仓库和地区分公司

通常每个仓库供货覆盖10家地区分公司，每个地区分公司服务3000名客户

公司共有100 000种商品，分别储存在各个仓库中

该系统包含了库存管理、销售、分发产品、付款、订单查询等一系列操作，一共包含了9个基本关系，如下：

TPC-C的性能度量单位是tpmC，tpm是transaction per minute的缩写，C代表TPC的C基准测试

该值越大，代表事务处理的性能越高。

tpcc-mysql是开源的TPC-C测试工具，该测试工具完全遵守TPC-C的标准。其官方网站为：https://code.launchpad.net/percona-dev/perconatools/tpcc-mysql

tpcc-mysql由以下两个工具组成：

• tpcc_load：根据仓库数量，生成9张表中的数据
• tpcc_start：根据不同选项进行TPC-C测试