MySQL技术内幕InnoDB存储引擎学习笔记第九章性能调优

╰+哭是因爲堅強的太久メ 2022-11-03 15:27 32阅读 0赞

数据库的应用分两类，OLTP（Online Transaction Processing，在线事务处理）和OLAP（Online Analytical Processing，在线分析处理）。OLAP一般用在数据仓库（一种关系数据库模式，其中保存了来自一个或者多个源数据库的历史数据和元数据）或数据集市（是数据仓库的一个访问层，用于从数据仓库抽取相关的数据给用户）中，一般需要复杂的SQL语句来进行查询；OLTP多用在日常事务处理应用中，数据库容量相对于OLAP较小。

InnoDB引擎一般用于OLTP的数据库应用，这种应用特点如下：  
1.用户操作的并发量大。  
2.事务处理时间一般比较短。  
3.查询的语句较为简单，一般都走索引。  
4.复杂的查询较少。

从以上特点可以看出，OLTP的数据库应用对CPU要求不高，因为只有复杂的查询才可能执行比较、排序、连接等耗CPU的工作。可以说OLAP是CPU密集型操作，OLTP是IO密集型操作（采购时将更多注意力放在提高IO配置上）。

为了获得更多内存的支持，CPU必须支持64位应用，否则无法支持64位操作系统的安装。

从InnoDB引擎的设计架构上看，主要的后台操作都是在一个单独的MASTER THREAD中完成的，不能很好地支持多核的应用，新的InnoDB Plugin版本对多核CPU的处理性能有了极大提高，也可以通过修改参数innodb\_read\_io\_threads和innodb\_write\_io\_threads来增大IO线程，这样也能更充分利用CPU多核性能。

当前版本，一条SQL语句只能在一个CPU工作，由于OLTP的数据库应用的操作一般很简单，对OLTP应用影响不大，但多个CPU或多核CPU对大并发量的请求非常有帮助。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70]  
上述测试中的数据文件和索引总大小为18GB，随着缓冲池的增大，测试结果TPS（Transaction Per Second）会线性增长，当缓冲池增大到20GB，数据库性能有了极大提高，这是因为缓冲池大小已经大于数据文件本身大小，对数据文件的操作都可以在内存中进行，此时的性能是最优的，再调大缓冲池不能再提高数据库性能。

判断数据库内存是否达到瓶颈：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 2]  
参数含义：  
1.Innodb\_buffer\_pool\_reads：从物理磁盘读取页的次数。  
2.Innodb\_buffer\_pool\_read\_ahead：预读的次数。  
3.Innodb\_buffer\_pool\_read\_ahead\_evicted：预读的没有被读取就从缓冲池中被替换掉的页的数量，一般用来判断预读的效率。  
4.Innodb\_buffer\_pool\_read\_requests：向缓冲池的读请求次数。  
5.Innodb\_data\_read：总共读入的字节数。  
6.Innodb\_data\_reads：发起读取请求的次数，每次读取可能需要读多个页。

根据以上参数可计算缓冲池命中率（通常应保证在99%以上）：  
`(innodb_buffer_pool_read_requests) / (Innodb_buffer_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)`  
平均每次读取字节数：  
`innodb_data_read / innodb_data_reads`

即使缓冲池大小已经大于数据库大小，也并不意味着没有磁盘操作，后台的master线程还负责将脏页异步地写入磁盘，每次事务提交时还需立即写入重做日志文件。

服务器领域一般使用SAS或SATA接口的机械硬盘，机械硬盘有两个重要指标：寻道时间和转速。机械硬盘随机访问需要耗费长时间的磁头旋转和定位来查找，而顺序访问的速度远高于随机访问。数据库的很多特性也在尽量充分利用顺序访问的特性。可将多块机械硬盘组成RAID提高数据库性能，也可将数据文件分布在不同硬盘上达到负载均衡。

固态硬盘内部由闪存组成，闪存具有低延时性、低功耗和防震性。闪存是一个完全的电子设备，没有传统机械硬盘的读写磁头，固态硬盘可以提供一致的随机访问时间。闪存中的数据是不可更新的，只能通过扇区的覆盖重写，在覆盖重写之前，需要执行非常耗时的擦除操作，擦除操作不能在扇区上完成，而是要在擦除块上完成，擦除块通常为128KB，但每个擦除块有擦写次数的限制，现在有一些算法可以解决这个问题。由于存在写入方面的问题，固态的读取速度高于写入速度，应好好利用其读取性能，避免过多的写入操作。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 3]  
对于固态硬盘在InnoDB引擎中的优化，可以增加innodb\_io\_capacity参数（InnoDB后台任务每秒可用的I/O操作数(IOPS)）的值。

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

根据不同磁盘组合方式，常见的RAID组合方式：  
1.RAID 0：将多个磁盘合并成大磁盘，不会有冗余，并行IO，速度最快。RAID 0也称为带区集。存放数据时，将数据按磁盘个数进行分段，同时将这些数据写入相应盘中。理论上，多磁盘效能等于`单一磁盘效能 * 磁盘数`，但实际上受限于总线IO瓶颈和其他因素影响，RAID效能会随边际递减。  
2.RAID 1：两组以上的N个磁盘相互作为镜像，在多线程操作系统中有很好的读取速度，但写入速度略低。除非拥有相同数据的主磁盘与镜像同时损坏，否则只要一个磁盘正常，即可维持运作，可靠性高。镜像原理为，在主硬盘上存放数据的同时也在镜像硬盘上写一样的数据。无论用多少组磁盘，RAID 1仅将其算为一组磁盘的容量，磁盘利用率低。  
3.RAID 5：是兼顾存储性能、数据安全、存储成本的解决方案，它使用的是Disk Striping（硬盘分区）技术，RAID 5至少需要三个硬盘（两个磁盘存数据，一个磁盘存两个磁盘的奇偶校验信息），它不对存储的数据进行备份，而是把数据和相对应的奇偶校验信息存储到组成RAID 5的各个磁盘上，并且将数据和相对应的奇偶校验信息存储到不同磁盘上，当RAID 5的一个磁盘数据发生损坏后，可利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据（假设有三份数据A1=00000111、A2=00000101以及A3=00000000，三份数据进行异或得到结果 Ap=00000010，如果此时A2出现故障不能访问，可以通过`A1 XOR A3 XOR Ap = 00000101`计算得到A2的值，原理为任意两个相同数异或的结果为0，而0与任何数异或是这个数本身，且异或有交换律）。RAID 5可以理解为RAID 0和RAID 1的折中方案，RAID 5的安全保障能力比镜像低，而磁盘空间利用率比镜像高。RAID 5读取速度接近RAID 0，但由于多了奇偶校验信息，所以写入速度相当慢，使用Write Back可以提升性能。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 4]

4.RAID 10和RAID 01：RAID 10是先做镜像，再做条带：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 5]  
RAID 01是先做条带，再做镜像：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 6]  
RAID 10的安全性更高，假设Disk0损坏，RAID 10在Disk1也同时损坏时才会损失数据，简单计算损坏率为1/3；而对于RAID 01，它是先进行RAID 0，因此Disk0和Disk1是一个逻辑磁盘，相当于两个都坏了，此时如果Disk2或Disk3之一损坏（相当于Disk2和Disk3整体被损坏），数据就会损坏，简单计算损坏率为2/3。这两种方法缺点是需要较多硬盘，至少要有四个以上偶数块硬盘时才能使用。  
（5）RAID 50：也称为镜像阵列条带，至少需要六块硬盘，其结构如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 7]  
RAID Write Back指RAID控制器将要写入的数据放到自身的缓存中，并把它们的实际写入安排到后面执行，这样不用等待物理磁盘的实际写入完成，性能明显提升。此方法可以使数据库的重做日志的写入、将sync\_binlog设为1时二进制日志的写入、脏页的刷新等操作的性能都会有明显提升。

如果系统发生意外，Write Back可能会还没写入故障就发生了，对此，大部分硬件RAID卡都提供了电池备份单元（BBU，Battery Backup Unit），因此可放心开启此功能。

如果没有启用Write Back，RAID卡设置中显示的是Write Through，它没有缓冲写入，因此写入性能可能差，但它是最安全的写入。

即使开启了Write Back，RAID卡可能也会启用Write Through，因为安全使用Write Back的前提是RAID卡有电池备份单元，为确保电池的有效性，RAID卡会定期检查电池状态，并在电量不足时对其充电，充电期间会将Write Back功能切换为最安全的Write Through。

向数据库插入20W条数据（通过20W次事务插入）测试Write Back和Write Through的性能差异：  
![在这里插入图片描述][20210305161157721.png]  
在Write Through模式下，通过将参数innodb\_flush\_log\_at\_trx\_commit设为0也能提高性能，此时重做日志的写入不是在每次事务提交时，而是在后台master线程每秒自动刷新的时候，减少了物理磁盘写入请求，提高了执行速度。

RAID卡可在服务器启动时进入一个类似BIOS的配置界面对其进行各种设置，很多厂商也开发了各种操作系统下的软件进行RAID卡的配置。

各种文件系统在性能上差距不大，但提供的功能需要关注，如ZFS可支持快照，这样就不用LVM快照了。

基准测试工具可用来对数据库或操作系统调优后的性能进行对比，除MySQL本身提供的优秀工具外， 还有更优秀、更常用的sysbench和mysql-tpcc。

sysbench是一个模块化、跨平台、多线程基准测试工具，主要包括以下测试方式：  
1.CPU性能。  
2.磁盘IO性能。  
3.调度程序性能。  
4.内存分配及传输速度。  
5.POSIX线程性能。  
6.数据库OLTP基准测试。

TPC（Transaction Processing Performance Council，事务处理性能协会）是评价大型数据库系统软硬件性能的非盈利组织。TPC-C是TPC协会制定的，用来测试典型的复杂OLTP系统的性能（学术界和业界普遍采用）。

TPC-C用3NF（第三范式）模拟一家仓库销售供应商公司，包含了九个基本关系：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 8]  
TPC-C性能度量单位是tpmC（transaction per minute，C表示TPC的C基准测试），该值越大，事务处理的性能越高。

tpcc-mysql是开源的TPC-C测试工具，完全遵守TPC-C标准。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70]: /images/20221023/4c1b983efbf2425bb2ce6807e3927b6b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 1]: /images/20221023/2ba65d1c5279409c97a8fb4fff11b653.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 2]: /images/20221023/2c475f356529467ab378f392a1003f14.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 3]: /images/20221023/3e3b0c8e410e4bd496421863cdb8410f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 4]: /images/20221023/e2b63ef997ae40e89ea58549e03c5d23.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 5]: /images/20221023/394bbceda7034b90ab4c64dc3c7a9e63.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 6]: /images/20221023/a98cae3afceb49f082c5b1ffa175fb58.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 7]: /images/20221023/7ee9a250922b4bd8943f429419a2ddd5.png
[20210305161157721.png]: /images/20221023/e24ffe67e5584b56a8f561143035da5d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 8]: /images/20221023/1cb4f104e20143d99211e5944e40ed8c.png