MySQL Forums
Forum List  »  Performance

MySQL became too slowly when using O_DIRECT
Posted by: Jerry Lin
Date: June 07, 2018 04:30AM

Hi ,
In my lab ,I setting the innodb_flush_method variable by O_DIRECT and fsync ,and test benchmark using sysbench.
These two value with very big different result in my lab.
If I set innodb_flush_method was default(fsync) ,the tps is 1591.87 ;but if I set it was O_DIRECT ,I only could get 63.16 .
Using the O_DIRECT is so slowly. I don't know why reason to cause this big different ?
Thanks.

My server is a VM with 2 cores and 8G memory ,the disk is SATA.

My configuration as below:
[mysqld]
skip-slave-start
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /var/lib/mysql/data
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
character-set-server = utf8mb4
skip_name_resolve = 1
log-error = /var/lib/mysql/error.log



server-id = 83
#log-bin = /var/lib/mysql/binlog/mydbsrv4-bin
relay_log=/var/lib/mysql/relaylog/mydbsrv4-relay
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum=NONE
#slave-parallel-workers = 2
#slave-preserve-commit-order = 1
#slave-parallel-type = LOGICAL_CLOCK

innodb_io_capacity=600
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 8
#innodb_flush_method=O_DIRECT
innodb_flush_method=fsync

innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2




Below are my sysnench log:

root@s201:~# sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --mysql-host=10.0.1.3 --mysql-port=3306 --mysql-db=sysbench --oltp-table-size=20000000 --mysql-user=dba --mysql-password=dba --max-time=30 --max-requests=0 --num-threads=16 run
sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

OLTP test statistics:
queries performed:
read: 26754
write: 9555
other: 3822
total: 40131
transactions: 1911 (63.16 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 36309 (1200.00 per sec.)
other operations: 3822 (126.32 per sec.)

Test execution summary:
total time: 30.2574s
total number of events: 1911
total time taken by event execution: 481.6728
per-request statistics:
min: 5.22ms
avg: 252.05ms
max: 1735.32ms
approx. 95 percentile: 623.14ms

Threads fairness:
events (avg/stddev): 119.4375/6.01
execution time (avg/stddev): 30.1046/0.06

---------------------------------------------
roots201:~# sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --mysql-host=10.0.1.3 --mysql-port=3306 --mysql-db=sysbench --oltp-table-size=20000000 --mysql-user=dba --mysql-password=dba --max-time=30 --max-requests=0 --num-threads=16 run
sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

OLTP test statistics:
queries performed:
read: 668752
write: 238819
other: 95529
total: 1003100
transactions: 47761 (1591.87 per sec.)
deadlocks: 7 (0.23 per sec.)
read/write requests: 907571 (30249.27 per sec.)
other operations: 95529 (3183.97 per sec.)

Test execution summary:
total time: 30.0031s
total number of events: 47761
total time taken by event execution: 479.7711
per-request statistics:
min: 1.52ms
avg: 10.05ms
max: 826.75ms
approx. 95 percentile: 32.31ms

Threads fairness:
events (avg/stddev): 2985.0625/57.20
execution time (avg/stddev): 29.9857/0.00

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.