astream使能MySQL测试步骤

1 环境要求

1.1 硬件

要求服务端（server）和客户端（client）各一台。

1.2 软件

1.3 组网

2. Server端部署

2.1 安装mysql依赖包

yum install -y cmake doxygen bison ncurses-devel openssl-devel libtool tar rpcgen libtirpc-devel bison bc unzip git gcc-c++ libaio libaio-devel numactl

2.2 编译安装mysql

• 从官网下载下载源码包。

• 下载优化补丁: 细粒度锁优化特性补丁 、 NUMA调度补丁 、 无锁优化特性补丁。

• 编译mysql。编译前确保已安装libaio-devel包。

  tar zxvf mysql-boost-8.0.20.tar.gz
  cd mysql-8.0.20/
  patch -p1 < ../0001-SHARDED-LOCK-SYS.patch
  patch -p1 < ../0001-SCHED-AFFINITY.patch
  patch -p1 < ../0002-LOCK-FREE-TRX-SYS.patch
  cd cmake
  make clean
  cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-8.0.20  -DWITH_BOOST=../boost -DDOWNLOAD_BOOST=1
  make -j 64
  make install
  

2.3 配置mysql参数

为了使磁盘压力足够大，测试过程中采用mysql双实例跑法。其中实例1对应的配置文件为/etc/my-1.cnf，实例2对应的配置文件为/etc/my-2.cnf。

• /etc/my-1.cnf

[mysqld_safe]
log-error=/data/mysql-1/log/mysql.log
pid-file=/data/mysql-1/run/mysqld.pid

[client]
socket=/data/mysql-1/run/mysql.sock
default-character-set=utf8

[mysqld]
server-id=3306
#log-error=/data/mysql-1/log/mysql.log
#basedir=/usr/local/mysql
socket=/data/mysql-1/run/mysql.sock
tmpdir=/data/mysql-1/tmp
datadir=/data/mysql-1/data
default_authentication_plugin=mysql_native_password
port=3306
user=root
#innodb_page_size=4k

max_connections=2000
back_log=4000
performance_schema=OFF
max_prepared_stmt_count=128000
#transaction_isolation=READ-COMMITTED
#skip-grant-tables

#file
innodb_file_per_table
innodb_log_file_size=2048M
innodb_log_files_in_group=32
innodb_open_files=10000
table_open_cache_instances=64

#buffers
innodb_buffer_pool_size=150G # 根据系统内存大小可调整
innodb_buffer_pool_instances=16
innodb_log_buffer_size=2048M
#innodb_undo_log_truncate=OFF

#tune
default_time_zone=+8:00
#innodb_numa_interleave=1
thread_cache_size=2000
sync_binlog=1
innodb_flush_log_at_trx_commit=1
innodb_use_native_aio=1
innodb_spin_wait_delay=180
innodb_sync_spin_loops=25
innodb_flush_method=O_DIRECT
innodb_io_capacity=30000
innodb_io_capacity_max=40000
innodb_lru_scan_depth=9000
innodb_page_cleaners=16
#innodb_spin_wait_pause_multiplier=25

#perf special
innodb_flush_neighbors=0
innodb_write_io_threads=24
innodb_read_io_threads=16
innodb_purge_threads=32

sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES

#skip_log_bin
log-bin=mysql-bin # 开启mysql-bin
binlog_expire_logs_seconds=1800 # 根据需要生成的数据量能够维持长时间运行可调整
ssl=0
table_open_cache=30000
max_connect_errors=2000
innodb_adaptive_hash_index=0

mysqlx=0

• /etc/my-2.cnf

[mysqld_safe]
log-error=/data/mysql-2/log/mysql.log
pid-file=/data/mysql-2/run/mysqld.pid

[client]
socket=/data/mysql-2/run/mysql.sock
default-character-set=utf8

[mysqld]
server-id=3307
#log-error=/data/mysql-2/log/mysql.log
#basedir=/usr/local/mysql
socket=/data/mysql-2/run/mysql.sock
tmpdir=/data/mysql-2/tmp
datadir=/data/mysql-2/data
default_authentication_plugin=mysql_native_password
port=3307
user=root
#innodb_page_size=4k

max_connections=2000
back_log=4000
performance_schema=OFF
max_prepared_stmt_count=128000
#transaction_isolation=READ-COMMITTED
#skip-grant-tables

#file
innodb_file_per_table
innodb_log_file_size=2048M
innodb_log_files_in_group=32
innodb_open_files=10000
table_open_cache_instances=64

#buffers
innodb_buffer_pool_size=150G # 根据系统内存大小可调整
innodb_buffer_pool_instances=16
innodb_log_buffer_size=2048M
#innodb_undo_log_truncate=OFF

#tune
default_time_zone=+8:00
#innodb_numa_interleave=1
thread_cache_size=2000
sync_binlog=1
innodb_flush_log_at_trx_commit=1
innodb_use_native_aio=1
innodb_spin_wait_delay=180
innodb_sync_spin_loops=25
innodb_flush_method=O_DIRECT
innodb_io_capacity=30000
innodb_io_capacity_max=40000
innodb_lru_scan_depth=9000
innodb_page_cleaners=16
#innodb_spin_wait_pause_multiplier=25

#perf special
innodb_flush_neighbors=0
innodb_write_io_threads=24
innodb_read_io_threads=16
innodb_purge_threads=32

sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES

log-bin=mysql-bin
#skip_log_bin # 开启mysql-bin
binlog_expire_logs_seconds=1800 # 根据需要生成的数据量能够维持长时间运行可调整
ssl=0
table_open_cache=30000
max_connect_errors=2000
innodb_adaptive_hash_index=0

mysqlx=0

2.4 部署mysql

#!/bin/bash
systemctl stop firewalld
systemctl disable irqbalance
echo 3 > /proc/sys/vm/drop_caches
mysql=mysql-8.0.20
prepare_mysql_data()
{
	umount /dev/nvme0n1
	rm -rf /data
	mkfs.xfs /dev/nvme0n1 -f
	groupadd mysql
	useradd -g mysql mysql
	mkdir /data
	mount /dev/nvme0n1 /data
	mkdir -p /data/{mysql-1,mysql-2}
	mkdir -p /data/mysql-1/{data,run,share,tmp,log}
	mkdir -p /data/mysql-2/{data,run,share,tmp,log}
	chown -R mysql:mysql /data
	chown -R mysql:mysql /data/mysql-1
	chown -R mysql:mysql /data/mysql-2
	touch /data/mysql-1/log/mysql.log
	touch /data/mysql-2/log/mysql.log
	chown -R mysql:mysql /data/mysql-1/log/mysql.log
	chown -R mysql:mysql /data/mysql-2/log/mysql.log
}
init_mysql()
{
	/usr/local/$mysql/bin/mysqld --defaults-file=/etc/my.cnf --user=root --initialize
	/usr/local/$mysql/support-files/mysql.server start
	sed -i 's/#skip-grant-tables/skip-grant-tables/g' /etc/my.cnf
	/usr/local/$mysql/support-files/mysql.server restart
	/usr/local/$mysql/bin/mysql -u root -p123456 <<EOF
	use mysql;
	Select * from user where user='root' \G;
	update user set password_expired='N' where user='root';
	flush privileges;
	alter user 'root'@'localhost' identified by '123456';
	flush privileges;
	update user set host='%' where user='root';
	flush privileges;
	create database tpcc;
	quit
EOF
	sed -i 's/skip-grant-tables/#skip-grant-tables/g' /etc/my.cnf
	/usr/local/$mysql/support-files/mysql.server restart
}
prepare_mysql_data
init_mysql

3. client部署benchmarksql工具

3.1 benchmarksql安装

下载 benchmarksql工具。

#安装benchmarksql依赖包
yum install -y java

unzip benchmarksql5.0-for-mysql.zip
cd benchmarksql5.0-for-mysql/run
chmod +x *.sh

3.2 配置benchmarksql参数

打开配置文件benchmarksql5.0-for-mysql/run/props.conf，配置如下：

db=mysql
driver=com.mysql.cj.jdbc.Driver
conn=jdbc:mysql://192.168.1.10:3306/tpcc?useSSL=false&useServerPrepStmts=true&useConfigs=maxPerformance&rewriteBatchedStatements=true
user=root
password=123456
profile=/etc/my-1.cnf

warehouses=4050
loadWorkers=100
terminals=330
terminalWarehouseFixed=true
runMins=720

runTxnsPerTerminal=0
limitTxnsPerMin=1000000000
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4

当前目录下再复制一个props.conf文件，重命名为props-2.conf，作为mysql实例2对应的配置文件。

db=mysql
driver=com.mysql.cj.jdbc.Driver
conn=jdbc:mysql://192.168.1.10:3307/tpcc?useSSL=false&useServerPrepStmts=true&useConfigs=maxPerformance&rewriteBatchedStatements=true
user=root
password=123456
profile=/etc/my-2.cnf

warehouses=4050
loadWorkers=100
terminals=330
terminalWarehouseFixed=true
runMins=720

runTxnsPerTerminal=0
limitTxnsPerMin=1000000000
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4

其中，warehouses的值根据当前 NVMe SSD 磁盘规格进行设定，然后生成一份warehouses=4050的数据作为每个mysql实例的操作数据。总的原则是：拷贝两份生成的数据分别作为两个mysql实例的操作数据，同时两份数据的总大小占磁盘空间的60%-70%，以便再经过720min的长稳运行后，磁盘空间的利用率能够达到90%以上。

3.3 创建mysql测试数据

#启动服务
/usr/local/mysql-8.0.20/support-files/mysql.server start

#创建测试数据(完成测试数据创建后，建议对server端/data/mysql-1/data下数据进行备份,之后测试，数据从此拷贝即可)
./runDatabaseBuild.sh props.conf

#停止数据库
/usr/local/mysql-8.0.20/support-files/mysql.server stop

3.4 数据转储备用盘

创建mysql数据之后，我们保存该份数据到备用盘/dev/nvme1n1，假设挂载目录为/bak。

cp -r /data/mysql-1/data/* /bak 

4 配置执行环境

4.1 开启STEAL优化

服务端开启STEAL优化，最大化提高mysql测试时的CPU利用率，提高CPU效率。

在/etc/grub2-efi.cfg文件中，系统启动项添加参数sched_steal_node_limit=4，reboot重启生效。

重启后，设置开启STEAL模式即可。

echo STEAL > /sys/kernel/debug/sched_features

4.2 关闭测试影响项

#关闭irqbalance
systemctl stop irqbalance.service
systemctl disable irqbalance.service

#关闭防火墙
systemctl stop iptables
systemctl stop firewalld

4.3 网卡中断绑核

#服务端绑中断(根据环境替换网卡名称、绑核cpu核)
ethtool -L enp4s0 combined 6
irq1=`cat /proc/interrupts| grep -E enp4s0 | head -n5 | awk -F ':' '{print $1}'`
cpulist=(61 62 63 64 65 66) ## 自行根据环境调整为需要固定处理网卡中断请求的核
c=0
for irq in $irq1
do
echo ${cpulist[c]} "->" $irq
echo ${cpulist[c]} > /proc/irq/$irq/smp_affinity_list
let "c++"
done

4.4 安装nvme-cli工具

nvme-cli是一个用于监控和配置管理NVMe设备的命令行工具。可用于开启NVMe SSD多流功能以及通过log相关的命令获取控制器的各类日志记录等功能。

yum install nvme-cli

4.5 开启NVMe 磁盘的多流特性

• 查询NVMe SSD磁盘当前的多流使能情况。

  nvme dir-receive /dev/nvme0n1 -n 0x1 -D 0 -O 1 -H
  

  

  回显结果表示，当前NVMe SSD支持Stream Directive，即支持开启多流特性，当前的状态为关闭状态。

• 启用多流功能。

  modprobe -r nvme
  modprobe nvme-core streams=1
  modprobe nvme
  

• 再次查询NVMe SSD磁盘当前的多流使能情况。

  

  回显结果表示，当前NVMe SSD已开启多流特性。

4.6 mysql双实例数据准备

为了统一基线测试和多流测试的流程，每次测试前格式化磁盘，统一从/bak目录下拷贝两份数据到两个mysql实例的对应数据目录下。

prepare_mysql_data()
{
	umount /dev/nvme0n1
	rm -rf /data
	mkfs.xfs /dev/nvme0n1 -f
	mkdir /data
	mount /dev/nvme0n1 /data
	mkdir -p /data/{mysql-1,mysql-2}
	mkdir -p /data/mysql-1/{data,run,share,tmp,log}
	mkdir -p /data/mysql-2/{data,run,share,tmp,log}
	chown -R mysql:mysql /data
	chown -R mysql:mysql /data/mysql-1
	chown -R mysql:mysql /data/mysql-2
	touch /data/mysql-1/log/mysql.log
	touch /data/mysql-2/log/mysql.log
	chown -R mysql:mysql /data/mysql-1/log/mysql.log
	chown -R mysql:mysql /data/mysql-2/log/mysql.log
}

prepare_mysql_data()
# 格式化后，创建mysql双实例对应的数据目录，即可启动astream
astream -i /data/mysql-1/data /data/mysql-2/data -r rule1.txt rule2.txt#  ---->该步骤测试基线时请去掉
cp -r /bak/* /data/mysql-1/data
cp -r /bak/* /data/mysql-2/data

然后，查看待测磁盘/dev/nvme0n1的空间占用率情况(df -h)是否在60%-70%左右即可。

4.7 绑核启动mysql服务

#启动mysql双实例
numactl -C 0-60 -i 0-3  /usr/local/bin/mysqld --defaults-file=/etc/my-1.cnf &
numactl -C 67-127 -i 0-3  /usr/local/bin/mysqld --defaults-file=/etc/my-2.cnf &

4.8 设置定时任务

拷贝数据或生成数据成功后，在执行mysql测试前，为了衡量磁盘的写放大水平，在12小时(720mins)的测试过程中，每隔1小时利用定时器crontab执行计算磁盘WA的脚本calculate_wa.sh。

#!/bin/bash

source /etc/profile
source ~/.bash_profile

BASE_PATH=$(cd $(dirname $0);pwd)
diskName=$1

echo 0x`/usr/bin/nvme get-log /dev/${diskName}n1 -i 0xc0 -n 0xffffffff -l 800|grep "01c0:"|awk '{print $13$12$11$10$9$8$7$6}'` >> ${BASE_PATH}/host_tmp
echo 0x`/usr/bin/nvme get-log /dev/${diskName}n1 -i 0xc0 -n 0xffffffff -l 800|grep "01d0:"|awk '{print $9$8$7$6$5$4$3$2}'` >> ${BASE_PATH}/gc_tmp

# IO write counts,unit:4K #
hostWriteHexSectorTemp=`tail -1 ${BASE_PATH}/host_tmp`
# GC write counts,unit 4k #
gcWriteHexSectorTemp=`tail -1 ${BASE_PATH}/gc_tmp`
hostWriteDecSectorTemp=`printf "%llu" ${hostWriteHexSectorTemp}`
gcWriteDecSectorTemp=`printf "%llu" ${gcWriteHexSectorTemp}`
preHostValue=`tail -2 ${BASE_PATH}/host_tmp|head -1`
preGcValue=`tail -2 ${BASE_PATH}/gc_tmp|head -1`
preHostValue=`printf "%llu" ${preHostValue}`
preGcValue=`printf "%llu" ${preGcValue}`

# IO write counts for a period of time
hostWrittenSector=$(echo ${hostWriteDecSectorTemp}-${preHostValue} | bc -l)
# Gc write counts for a period of time
gcWrittenSector=$(echo ${gcWriteDecSectorTemp}-${preGcValue} | bc -l)
nandSector=$(echo ${hostWrittenSector}+${gcWrittenSector} | bc -l)

# unit from kB->MB
hostWrittenMB=$((${hostWrittenSector}/256))
nandWrittenMB=$((${nandSector}/256))

# compute the WA
WA=$(echo "scale=5;${nandSector}/${hostWrittenSector}" | bc)
echo $nandWrittenMB $hostWrittenMB $WA >> ${BASE_PATH}/result_WA.txt

可执行crontab -e，加入每隔1小时执行脚本命令的定时任务，命令如下：

0 */1 * * * bash  /root/calculate_wa.sh nvme0

若所测的NVMe磁盘盘符名为/dev/nvme0n1，则定时任务中脚本的参数传入nvme0即可。

4.9 启动mysql双实例测试

执行测试前，确保已使能NVMe SSD磁盘的多流特性。

客户端工具根目录下执行测试如下：

cd benchmarksql5.0-for-mysql
./runBenchmark.sh props.conf
./runBenchmark.sh props-2.conf

4.10 停止astream进程

本步骤测试基线时无需操作，若测试多流结束后，执行如下命令停止astream进程：

astream stop

5 测试结果

使用定时器脚本的结果，在脚本所在目录的result_WA.txt下输出。每次测试结束后，从中选择最近12条非0数据即可。

当磁盘有数据写入时，result_WA.txt中每一行有三个值，其意义如下：

• 1小时内磁盘实际写入的数据量。
• 1小时内主机提交的写入量。
• 当前的磁盘WA，根据附录中的公式即可算出每个小时内磁盘的WA水平。

当前实测，在使用astream情况下，mysql长稳运行后期，NVMe SSD磁盘稳定保持12%的WA下降幅度，即性能较前提升12%。

6 附录

写入放大（英语：Write amplification，简称WA）是闪存和固态硬盘（SSD）中一种不良的现象，即磁盘实际写入的数据量是写入数据量的多倍。其公式为：

$$ WA=\frac{磁盘实际写入的数据量}{主机提交的写入数据量} $$

一般来说，随着数据的存储以及磁盘的碎片化愈演愈烈，WA的值将越来越大，如果WA的值能够延迟升高，那么将有助于延长磁盘的使用寿命。