MySQL Forums
Forum List  »  Performance

Re: MySQL became too slowly when using O_DIRECT
Posted by: Jerry Lin
Date: June 08, 2018 04:00AM

Hi ,

My server is Ubuntu 16.04 and MySQL 5.7.22
This server is a VM that has 2 cores and 8G ram.
This server has a SATA and a SSD disk.

I run MySQL on SSD ,the tps of o_direct and fsync almost the same ,but I run MySQL on SATA ,the tps will get big gap.


I used sysbench 0.4.12 to run my test.
My test script as below:
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

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

If you need more information ,please tell me what kind of information you should know ,thank you.

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.