探索InnoDB并行读

作者: 小知_知数堂 | 来源:发表于2019-12-25 10:26 被阅读0次

探索InnoDB并行读
GreatSQL重磅特性，InnoDB并行并行查询优化测试
MySQL存储引擎MyISAM 和 InnoDB的区别
MySQL优化系列10-MySQL的并行介绍
校招面试题mysql锁总结
InnoDB如何在RR隔离级别下避免幻读
2PC & GC
MySQL架构
mysql表级锁
mysql中引擎区别

导读：

作者：蒋乐兴 MySQL-DBA 目前在 github 上维护着两套 MySQL 开源工具 mysqltools & dbm(dbm-agent dbm-center)，对机器学习和程序化交易也有些心得。

一、innodbparallelread_threads

这个参数控制着聚集索引扫描时 innodb 层面的并发数，核心数无限的情况下并发数越大，返回结果的时间就越短

二、环境准备

我们准备用innodb 之前最不擅长的一个场景来测试这参数的功能，那这是一个什么场景呢？答案就是 select count(*) from t; 下面为表t准备80w行数据

1、建表

mtls-perf-bench --host=127.0.0.1  --user=root --password=dbma@0352 --port=3306  --ints=8  --floats=8  --varchars=4  --database=tempdb create

2、检查表结构

mysql>  select  @@version;
+-----------+
|  @@version  |
+-----------+
|  8.0.18  |
+-----------+
1 row in  set  (0.00 sec)
    
show create table tempdb.t;
    
CREATE TABLE `t`  (
 `id`  int(11) NOT NULL AUTO_INCREMENT,
 `i0`  int(11) NOT NULL,
 `i1`  int(11) NOT NULL,
 `i2`  int(11) NOT NULL,
 `i3`  int(11) NOT NULL,
 `i4`  int(11) NOT NULL,
 `i5`  int(11) NOT NULL,
 `i6`  int(11) NOT NULL,
 `i7`  int(11) NOT NULL,
 `c0` varchar(128) NOT NULL,
 `c1` varchar(128) NOT NULL,
 `c2` varchar(128) NOT NULL,
 `c3` varchar(128) NOT NULL,
 `f0`  float NOT NULL,
 `f1`  float NOT NULL,
 `f2`  float NOT NULL,
 `f3`  float NOT NULL,
 `f4`  float NOT NULL,
 `f5`  float NOT NULL,
 `f6`  float NOT NULL,
 `f7`  float NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

3、完全随机的插入 80w 行数据

mtls-perf-bench --host=127.0.0.1  --user=root --password=dbma@0352 --port=3306  --ints=8  --floats=8  --varchars=4  --database=tempdb --parallel=16  --rows=800000 insert

虚拟机　cpu:4核、mem:4G、innodbbufferpool_size:1G、t.ibd:324M

三、性能测试

1、对数据进行预热

--  执行  select count(*)  from tempdb.t
--  8  次

2、一个并发下的响应时间(1.04s)

mysql> show global variables like 'innodb_parallel_read_threads';
+------------------------------+-------+
|  Variable_name  |  Value  |
+------------------------------+-------+
| innodb_parallel_read_threads |  1  |
+------------------------------+-------+
1 row in  set  (0.01 sec)
    
mysql>  select count(*)  from tempdb.t;
+----------+
| count(*)  |
+----------+
|  800000  |
+----------+
1 row in  set  (1.04 sec)

3、二个并发下的响应时间(0.59s)

mysql> show global variables like 'innodb_parallel_read_threads';
+------------------------------+-------+
|  Variable_name  |  Value  |
+------------------------------+-------+
| innodb_parallel_read_threads |  2  |
+------------------------------+-------+
1 row in  set  (0.01 sec)
    
mysql>  select count(*)  from tempdb.t;
+----------+
| count(*)  |
+----------+
|  800000  |
+----------+
1 row in  set  (0.59 sec)

4、四个并发下的响应时间(0.55)

mysql> show global variables like 'innodb_parallel_read_threads';
+------------------------------+-------+
|  Variable_name  |  Value  |
+------------------------------+-------+
| innodb_parallel_read_threads |  4  |
+------------------------------+-------+
1 row in  set  (0.01 sec)
    
mysql>  select count(*)  from tempdb.t;
+----------+
| count(*)  |
+----------+
|  800000  |
+----------+
1 row in  set  (0.55 sec)

5、八个并发下的响应时间(0.29)

mysql> show global variables like 'innodb_parallel_read_threads';
+------------------------------+-------+
|  Variable_name  |  Value  |
+------------------------------+-------+
| innodb_parallel_read_threads |  8  |
+------------------------------+-------+
1 row in  set  (0.01 sec)
    
mysql>  select count(*)  from tempdb.t;
+----------+
| count(*)  |
+----------+
|  800000  |
+----------+
1 row in  set  (0.29 sec)

四、结论

提高 innodb 并行读线程数(innodb_parallel_read_threads) 在特定场景下可以明显的提升性能。

原文链接：https://www.sqlpy.com/blogs/books/1/chapters/7/articles/114