Tuning the XFS file System

网络上非常多的关于数据库文件系统调优的文章，里面的秘籍是不是都是真的，还是瞎折腾？Don’t Trust it, Test it！

其中一个来自Seagate的文档"https://www.seagate.com/files/www-content/support-content/solid-state-flash-storage/accelerator-cards/_shared/masters/seagate-ssds-linux-and-mysql-tpc-c-optimizations-application-note.pdf?pdhname=WOLF"提到:

1. "Deploying an XFS file system with a 4KB block size resulted in an improvement of 5% to 15% in overall performance."

2. "When considering mount options, you have several options that can be applied to increase performance of the
Seagate SSD."
For XFS:
  nobarrier,discard,noatime,attr2,delaylog,inode64,noquota 

那么就来实测一下以上两点文件系统设置对MySQL的TPC-C的性能是否有提高。

1. 环境配置

创建两个文件系统, 一个使用默认参数，一个使用"4KB block size".

## 创建PV
# pvcreate --dataalignment 4M /dev/fioc
# pvs /dev/fioc -o+pe_start
  PV         VG Fmt  Attr PSize PFree 1st PE 
  /dev/fioc     lvm2 ---  2.91t 2.91t   4.00m
## 创建VG
# vgcreate datavg1 /dev/fioc

##创建LV
# lvcreate -l 50%vg -n datalv1 datavg1
# lvcreate -l 50%vg -n datalv2 datavg1

##  创建XFS文件系统
# 创建一个默认参数的文件XFS文件系统
# mkfs.xfs /dev/datavg1/datalv1
Discarding blocks...Done.
meta-data=/dev/datavg1/datalv1   isize=512    agcount=4, agsize=97656064 blks
         =                       sectsz=512   attr=2, projid32bit=1 *###--> sector_size = 512 bytes (default)*
         =                       crc=1        finobt=0, sparse=0
data     =                       bsize=4096   blocks=390624256, imaxpct=5
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal log           bsize=4096   blocks=190734, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0

## 再根据文档介绍的参数"-s size=4096"创建一个文件系统
# mkfs.xfs -s size=4096 /dev/datavg1/datalv2
Discarding blocks...Done.
meta-data=/dev/datavg1/datalv2   isize=512    agcount=4, agsize=97656064 blks
         =                       sectsz=4096  attr=2, projid32bit=1  *###--> sector_size=4 KB*
         =                       crc=1        finobt=0, sparse=0
data     =                       bsize=4096   blocks=390624256, imaxpct=5
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal log           bsize=4096   blocks=190734, version=2
         =                       sectsz=4096  sunit=1 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0

## 使用默认文件系统选项，挂载文件系统
# mkdir -p /data{1,2}
# mount /dev/datavg1/datalv1 /data1 ###-> /data1使用的时默认参数的文件系统;
# mount /dev/datavg1/datalv2 /data2  ###-> /data2使用的是"-s size=4096"创建的文件系统；
# mkdir -p /data{1,2}/mysql/tpcc
# chown -R mysql.mysql /data{1,2}/mysql/

# 使用文档推荐的mount选项挂载文件系统/data2
# mount -o nobarrier,discard,noatime,attr2,delaylog,inode64,noquota /dev/datavg1/datalv2  /data2
# mount |grep /data
/dev/mapper/datavg1-datalv1 on /data1 type xfs (rw,relatime,attr2,inode64,noquota)
/dev/mapper/datavg1-datalv2 on /data2 type xfs (rw,noatime,attr2,discard,nobarrier,inode64,noquota)

使用tpcc-dbcp分别进行测试, 数据库和测试参数:

# ./tpcc.sh -d mysql-ds-1 -r 180 -w 1000 -t 64 -m 600 run

2. 测试结果

	TpmC	TPmC增幅%	New-Order平均响应时间
/data1 (默认方式创建)	137091.00	100%	43.86 ms
/data2 (指定"-s size=4096"选项)	141050.80	102.8%	42.34 ms
/data2 (nobarrier,discard,noatime,attr2,delaylog,inode64,noquota)	144693.70	105.5%	41.04 ms

3. 配置信息

	DataBase Server	Load Generation Server(Client)
CPU	Intel® Xeon® CPU E7-4820 v2 @ 2.00GHz Core(s) 16 Thread/CPU(s) 32	Intel® Xeon® CPU E7-4830 v3 @ 2.10GHz Core(s) 42 Thread/CPU(s) 96
Memory	64 GB	256 GB
Storage	SANDISK FUSION-IOMEMORY SX350-3200
Database	Percona MySQL Server 8.0.22-13

MySQL配置

[mysqld]
basedir                        = /usr/local/mysql
datadir                        = /data2/mysql/tpcc/data
port                           = 3306
user                           = mysql
socket                         = /data2/mysql/tpcc/mysql.sock
pid-file                       = /data2/mysql/tpcc/mysqld.pid
tmpdir                         = /data2/mysql/tpcc/tmp
character-set-server           = utf8mb4
default_time_zone              = "+8:00"
default_authentication_plugin  = mysql_native_password
default-storage-engine         = InnoDB
skip-name-resolve              = ON
lower_case_table_names         = 1
back_log                       = 512
transaction-isolation          = READ-COMMITTED
large-pages

log_timestamps                 = SYSTEM 
log-error                      = /data2/mysql/tpcc/logs/error.log
log-output                     = file
general-log-file               = /data2/mysql/tpcc/logs/general.log

slow_query_log                 = ON
log-queries-not-using-indexes  = ON
log_throttle_queries_not_using_indexes = 40
long_query_time                = 2
slow-query-log-file            = /data2/mysql/tpcc/logs/slow.log 
log-slow-admin-statements      = 1
log_slow_slave_statements      = 0

open-files-limit               = 65535
thread-cache-size              = 64
max-allowed-packet             = 64M
max-connect-errors             = 1000
max-connections                = 4000
table_open_cache_instances     = 16
read_rnd_buffer_size           = 4M
join_buffer_size               = 4M
sort_buffer_size               = 4M

innodb-data-file-path          = ibdata1:500M:autoextend
innodb_temp_data_file_path     = ibtmp1:128M:autoextend:max:32G
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 2000M
innodb-buffer-pool-size        = 40000M
innodb_buffer_pool_instances   = 20
innodb_page_cleaners           = 20
innodb_flush_neighbors         = 0
innodb_page_size               = 4K
loose-innodb_checksum_algorithm = strict_crc32
innodb_write_io_threads        = 4
innodb_read_io_threads         = 16
innodb_io_capacity             = 40000
innodb_io_capacity_max         = 80000
innodb_parallel_read_threads   = 16

disable-log-bin
#log-bin                        = /data2/mysql/tpcc/binlog/blog
log-bin-index                  = /data2/mysql/tpcc/binlog/blog.index
binlog-format                  = row
max-binlog-size                = 1G
sync-binlog                    = 1
binlog_cache_size              = 4M
binlog-expire-logs-seconds     = 1468800