欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

innodb_io_capacity、innodb_io_capacity_max 的影响

程序员文章站 2022-07-15 13:26:43
...

os: centos 7.4
db: mysql 8.0.19

innodb_io_capacity、innodb_io_capacity_max 控制的是 innodb 刷脏页的能力。

过小会导致 mysql 刷脏页能力不足,影响性能。
过大会让 mysql 认为 io 能力很强,会造成 io 尖峰。

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# 
# yum list installed |grep -i mysql
mysql-community-client.x86_64              8.0.19-1.el7                @mysql80-community
mysql-community-common.x86_64              8.0.19-1.el7                @mysql80-community
mysql-community-devel.x86_64               8.0.19-1.el7                @mysql80-community
mysql-community-libs.x86_64                8.0.19-1.el7                @mysql80-community
mysql-community-libs-compat.x86_64         8.0.19-1.el7                @mysql80-community
mysql-community-server.x86_64              8.0.19-1.el7                @mysql80-community
mysql-community-test.x86_64                8.0.19-1.el7                @mysql80-community
mysql80-community-release.noarch           el7-3                       installed

# mysql

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

创建基础表

mysql> use test;

mysql> create table tmp_t0 (
	c0 bigint not null,
	c1 varchar(100) not null,
	c2 varchar(100) not null,
	primary key (c0)
)
engine=innodb
default charset=utf8mb4
collate=utf8mb4_bin;

mysql> delimiter $$
create procedure insert_into_tmp_t0(
   in start_num int(10),
   in max_num   int(10)
)
begin  
   declare i int default 0;   
   set autocommit = 0;    
   repeat  
      set i = i + 1;  
      insert into tmp_t0 (c0,c1,c2) 
	       values (start_num+i,md5(start_num+i),md5(start_num+i));  
      until i = max_num
   end repeat;  
   commit;  
 end$$
delimiter ;

mysql> call insert_into_tmp_t0(0,1000000);

mysql> create table tmp_t100 as select * from tmp_t0 where 1=2;

mysql> alter table tmp_t100 add primary key (c0);


mysql> create table tmp_t2000 as select * from tmp_t0 where 1=2;

mysql> alter table tmp_t2000 add primary key (c0);

innodb_io_capacity 默认值

# mysql

mysql> show global variables like '%io_cap%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 200   |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+
2 rows in set (0.00 sec)

innodb_io_capacity=100

mysql> set global innodb_io_capacity=100;
set global innodb_io_capacity_max=200;

mysql> show global variables like '%io_cap%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 100   |
| innodb_io_capacity_max | 200   |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> truncate table tmp_t100;

mysql> insert into tmp_t100 select * from tmp_t0;
Query OK, 1000000 rows affected (18.76 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

innodb_io_capacity=2000

mysql> set global innodb_io_capacity=2000;
set global innodb_io_capacity_max=4000;

mysql> show global variables like '%io_cap%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 2000  |
| innodb_io_capacity_max | 4000  |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> truncate table tmp_t2000;

mysql> insert into tmp_t2000 select * from tmp_t0;

Query OK, 1000000 rows affected (19.48 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

参考:
https://dev.mysql.com/doc/refman/8.0/en/innodb-configuring-io-capacity.html
https://www.cnblogs.com/glon/p/6484912.html

相关标签: # mysql parameter