lab benchmark to check how MySQL used IO of Linux on single select (tested full scan and index scan )
a very interesting paradox is observed.
when we do not set the parameters, we use the maximum of the disk’s capabilities at the single query.
Initially it was planned to check how well the mysql is able to use the disk’s capabilities,
but it was found that if we use innodb_flush_method = O_DIRECT to save server memory,
then the mysql does not use the maximum disk capabilities for a single query.
Also from my test, I do not see an big improvement in performance on ext4 with the default mount settings.
have checked on other servers and also this situation is observed.
Server VM CentOS 7.4 64 Bit configuration
Tested on SSD disk.
FILESYSTEM TYPY XFS and EXT4
MOUNT option default :
/dev/mapper/mysql–ssd-lv_mysql–ssd on /mysqlssd type xfs (rw,relatime,seclabel,attr2,inode64,noquota)
/dev/mapper/mysql_ssd_ext4-lv_mysql_ssd_ext4 on /mysqlssdext4 type ext4 (rw,relatime,seclabel,data=ordered)
Attached File parameter of MYSQL 5.7.22
DDl of table and initial data of table attached :
CREATE TABLE hierarchy (
hierarchy_string varchar(256) NOT NULL,
TSN int(10) NOT NULL,
Parent_TSN int(10) DEFAULT NULL,
level int(10) NOT NULL,
ChildrenCount int(10) NOT NULL,
PRIMARY KEY (hierarchy_string),
KEY hierarchy_string (hierarchy_string)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE ITIS.hierarchy_nopk (
hierarchy_string varchar(256) NOT NULL,
TSN int(10) NOT NULL,
Parent_TSN int(10),
level int(10) NOT NULL,
ChildrenCount int(10) NOT NULL
) ENGINE=innodb DEFAULT CHARSET=utf8;
LOAD DATA LOCAL INFILE ‘hierarchy_part’ INTO TABLE hierarchy FIELDS TERMINATED BY ‘|’ LINES TERMINATED BY ‘\n’;
Rows multiplied by following insert :
insert into ITIS.hierarchy SELECT CONCAT(hierarchy_string,‘-re8-’, (@cnt := @cnt + 1)) as hierarchy_string,TSN,Parent_TSN,level,ChildrenCoun t FROM ITIS.hierarchy CROSS JOIN (SELECT @cnt := 98) AS dummy;
insert into into ITIS.hierarchy_nopk select * from ITIS.hierarchy;
insert into into ITIS.hierarchy_nopk select * from ITIS.hierarchy_nopk;
Ready database copied to ext4 filesystem.
all print screen attached .