# lab  benchmark test how MySQL used IO  on Linux

**URL:** <https://forums.percona.com/t/lab-benchmark-test-how-mysql-used-io-on-linux/6595>\
**Category:** Other MySQL® Questions\
**Created:** [September 26, 2018, 10:35am UTC](https://forums.percona.com/t/lab-benchmark-test-how-mysql-used-io-on-linux/6595 "2018-09-26T10:35:21Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![evgenyfr](https://avatars.discourse-cdn.com/v4/letter/e/ba8739/32.png) [@evgenyfr](https://forums.percona.com/u/evgenyfr)\
**Post date:** [September 26, 2018, 10:35am UTC](https://forums.percona.com/t/lab-benchmark-test-how-mysql-used-io-on-linux/6595/1 "2018-09-26T10:35:21Z")

</div>

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 .

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [September 26, 2018, 10:59am UTC](https://forums.percona.com/t/lab-benchmark-test-how-mysql-used-io-on-linux/6595/2 "2018-09-26T10:59:53Z")

</div>

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.