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
上一篇: Spring MVC框架搭建扩展--多数据源配置(一)
下一篇: Spring MVC入门