MySQL 8.0 InnoDB压缩行格式性能测试

妖狐艹你老母 2023-02-18 03:28 4阅读 0赞

--------------------

> InnoDB compressed好吃吗？  
> 不，它有点硌牙。

1. 背景信息1. 测试环境2. 进行测试2.1 所有数据可以加载到buffer pool中2.1.1 数据压缩率2.1.2 TPS相差值2.1.3 平均延迟差值 avg Latency (ms)2.1.4 99%延迟差值 99th percentile Latency (ms)2.2 数据量超过内存ibp容量2.2.1 数据压缩率2.2.2 TPS相差值2.2.3 平均延迟差值 avg Latency (ms)2.2.4 99%延迟差值 99th percentile Latency (ms)3. 总结延伸阅读

# 1. 背景信息 #

多年前我对InnoDB表压缩格式做了个简单的测试，得到的结论大概是：

> InnoDB采用compressed行格式后，OLTP整体性能大约为原来的1/10，压缩率约为50%。

按照这个结论，压缩行格式不建议用在TPS较高的OLTP场景，如果有类似的业务需要，可以考虑用TokuDB或RocksDB引擎。

尝试过用TokuDB当做Zabbix的后端数据库，效果还不错，详情见 [迁移Zabbix数据库到TokuDB][Zabbix_TokuDB]。

不过，TokuDB现在已经基本被Percona抛弃了，还有这类业务需求时，可以考虑改用RocksDB引擎，可以参考这篇文章 [MyRocks引擎：入坑须知][MyRocks]。

随着MySQL 8.0.20的发布，我又重燃了对compressed行格式的兴趣，今日就此再做了个简单测试。

# 1. 测试环境 #

本次测试的服务器配置是腾讯云"标准型S5"型CVM主机，具体配置是：

<table> 
 <thead> 
  <tr> 
   <th>配置项</th> 
   <th>参数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CPU</td> 
   <td>4 Core（Intel(R) Xeon(R) Platinum 8255C CPU @ 2.50GHz）</td> 
  </tr> 
  <tr> 
   <td>内存</td> 
   <td>16GB</td> 
  </tr> 
  <tr> 
   <td>数据盘</td> 
   <td>500GB SSD云硬盘（理论最大随机IOPS值 16800，实际上最高也只能跑到10000不到）</td> 
  </tr> 
 </tbody> 
</table>

my.cnf中InnoDB相关配置参数（其余采用默认设置）

innodb_flush_log_at_trx_commit=1
    innodb_buffer_pool_size=8G
    innodb_log_file_size = 2G

MySQL选用最新的8.0.20版本：

Server version:        8.0.20 MySQL Community Server - GPL

# 2. 进行测试 #

本次测试计划分为两种模式  
a) 所有数据可以加载到buffer pool中  
b) 数据量超过内存ibp容量  
针对上述两种模式再分别对dynamic、compressed行格式的区别。

## 2.1 所有数据可以加载到buffer pool中 ##

相应的sysbench参数如下：

TBLCNT=50 #共50个表
    DURING=900 #一次压测900秒（5分钟）
    ROWS=100000 #每个表10万行数据
    MAXREQ=5000000 #每个线程执行500万次请求

### 2.1.1 数据压缩率 ###

<table> 
 <thead> 
  <tr> 
   <th>未压缩格式（KB）</th> 
   <th>压缩格式（KB）</th> 
   <th>压缩率(1-压缩格式/未压缩格式)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1638456</td> 
   <td>1218588</td> 
   <td>25.63%</td> 
  </tr> 
 </tbody> 
</table>

### 2.1.2 TPS相差值 ###

![format_png][]

数值说明：这表示 未压缩格式 相对于 压缩格式的提升比例，例如上图中第一列的 71.11%，表示 **在OLTP模式下，并发256线程压测时，未压缩行格式的TPS相对于压缩行格式增加71.11%**，下同。

### 2.1.3 平均延迟差值 avg Latency (ms) ###

![format_png 1][]

### 2.1.4 99%延迟差值 99th percentile Latency (ms) ###

![format_png 2][]

根据测试结果的几点结论：  
a) 当数据都能放在buffer pool中的时候，是否采用压缩格式对于读的业务场景影响很小。  
b) 当数据都能放在buffer pool中的时候，混合OLTP业务场景或者以更新为主的业务场景中，Dynamic行格式明显要比Compressed行格式的性能更好。  
综上，当数据量比较小的时候，并且读多写少的业务场景中，可以考虑使用Compressed行格式。而如果是写多读少的业务场景，则最好使用Dynamic行格式。

## 2.2 数据量超过内存ibp容量 ##

sysbench参数调整ROWS，其余不变。

ROWS=5000000 #每个表500万行数据

### 2.2.1 数据压缩率 ###

<table> 
 <thead> 
  <tr> 
   <th>未压缩格式（KB）</th> 
   <th>压缩格式（KB）</th> 
   <th>压缩率(1-压缩格式/未压缩格式)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>59596904</td> 
   <td>40210556</td> 
   <td>34.03%</td> 
  </tr> 
 </tbody> 
</table>

### 2.2.2 TPS相差值 ###

![format_png 3][]

### 2.2.3 平均延迟差值 avg Latency (ms) ###

![format_png 4][]

### 2.2.4 99%延迟差值 99th percentile Latency (ms) ###

![format_png 5][]

根据测试结果的几点结论：  
a) 当数据无法全部放在buffer pool中的时候，如果是读多写少的业务场景，则用Compressed行格式性能更高。  
b) 当数据无法全部放在buffer pool中的时候，如果是写多读少的业务场景，则用Dynamic行格式性能更高。  
综上，当数据量比较小的时候，并且读多写少的业务场景中，可以考虑使用压缩行格式。

# 3. 总结 #

根据上面的测试结果来看，如果是下面几种业务场景，则可以考虑使用InnoDB表想要使用compressed行格式：  
a) 对压缩比需求不是特别高，本案中，只压缩了 25% ~ 34% 数据量，优势不大。  
b) 数据量无法全部加载到buffer pool中的时候，读多写少的业务场景。

本案中，测试条件存在几点不足：  
a) 服务器配置不算高。  
b) 测试持续时长不够，只有15分钟。  
c) 测试表和实际业务预计相差比较大，实际业务环境中，可能文本类型列会多一些，这样压缩比也会高一些。

综合来看，类似下面的业务场景，可以考虑使用compressed格式：  
a) 数据量较大，且文本数据较多。  
b) 磁盘比较紧张。  
c) 读多写少。

最后，最好还是自己再亲自测试下比较靠谱哈。

# 延伸阅读 #

*  15.9 InnoDB Table and Page Compression, https://dev.mysql.com/doc/refman/8.0/en/innodb-compression.html

enjoy MySQL :)

全文完。

由叶老师主讲的知数堂「MySQL优化课」课程早已升级到MySQL 8.0版本了，现在上车刚刚好，扫码开启MySQL 8.0的修行之旅吧。

*  ![format_png 6][]
    
    --------------------
    
    另外，叶老师在腾讯课堂《**MySQL性能优化**》精编版第一期已完结，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。
    
    下面是自动拼团的二维码直接享受组团价
    
    ![format_png 7][]

[Zabbix_TokuDB]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=2f485118183fd80e2c1cfdb7a94d30b169b0af9052a2d7e8c6fbe35278e23c2c0b56ccd87d6c&idx=3&mid=207407986&scene=21&sn=933f3115d60fe41d5eee93a1195393ff#wechat_redirect
[MyRocks]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b5dd68a4cd4c0f21985a729fb49dd43801b3daa9b461500b54f967c0b469ee815c0f8f59f&idx=1&mid=2653931452&scene=21&sn=64243c1b68757235e3c3fc3b6bc81fa8#wechat_redirect
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUdzZpYjV3UEdZR2Q5dkNuTFJoR3pZUDc2MVdZaERlUGljd3R3NnlNMnRWdHUzaWNrSmRNaWE2M2ljWGljbFo4d1ZYZU5Ic3FYNEJRR2szY1lpY3cvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUdzZpYjV3UEdZR2Q5dkNuTFJoR3pZUDdEU05Fa1NlRkp2dWliaWM0TW85VVZiV2xoeGphckdueUFuUGpqczN6Q25NNFFqbFhneklob2ppYVEvNjQw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUdzZpYjV3UEdZR2Q5dkNuTFJoR3pZUDdRNlZaTXpLcHcxZDU4ZWt0c2lidmhiTElOUDFxc1JmR0l5bzU2TEhlcHZXNVcyOUpMM0JWNFlRLzY0MA?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUdzZpYjV3UEdZR2Q5dkNuTFJoR3pZUDdKVmhjUFgyZnE2NmZaUllVSkRvV3NJaWI2aDlUYkJ4bXAzM3p6YWlheTlaam9UY0lsS3YzMWJUQS82NDA?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUdzZpYjV3UEdZR2Q5dkNuTFJoR3pZUDdzcnlzaFZCbVpNandoUDNSV1BWQkpoVlVyTXNvaWJuN2ZwZzlzYVVDRXpHNVhYZDRYalB2VGRBLzY0MA?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUdzZpYjV3UEdZR2Q5dkNuTFJoR3pZUDc2eEdXNTVlbWtGMEM0QmdTTlB0Szdmbll5UjlVUW1oT3hDRkxEeTBJcDdpYmZFeDU1aDV3STFnLzY0MA?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUeEx5ajlRdWJEY0hQeUlwemgwaWNXSVpnTm96aFo3TmxDbW53MkU1ekpqUzZTTjE0TENaR0JvUW04WGliVHkzZDFtaWFqYWlhQVhTcTFTVVEvNjQw?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9udHM1Mm5IaGVUek5xdmliY3IwZmJDQkU3MXBoMzF5aWJ5TzNFOVJONnlLd0NYd1R6cVZmc1ZxM2RxUUFpYWRoUDlGSkVXOUluNlk0Mnl4ZTVUUWVuMm9BUS82NDA?x-oss-process=image/format,png