Not the answer you need?
Register and ask your own question!

lab benchmark test how MySQL used IO on Linux

evgenyfrevgenyfr EntrantCurrent User Role Beginner
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 .

Comments

  • PeterPeter Percona CEO Percona Moderator Role
    How much memory do you have on the box and what was buffer pool size ?

    Typically buffered IO is faster when buffer pool size is not large enough.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.