Test Procedure for astream-Enabled MySQL

1. Environment Requirements

1.1 Hardware

A server machine and a client machine are required.

1.2 Software

1.3 Networking

2. Deployment on the Server

2.1 Installing MySQL Dependency Packages

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 Compiling and Installing MySQL

• Download the source package from the official website.

• Download the optimization patches for fine-grained locking, NUMA scheduling, and lock-free tuning.

• Compile MySQL. Ensure that the libaio-devel package has been installed in advance.

  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 Configuring MySQL Parameters

To produce enough drive load, two MySQL instances run simultaneously during the test. The configuration file of instance 1 is /etc/my-1.cnf, and the configuration file of instance 2 is /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 # Adjust the value based on the system memory size.
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 # Enable mysql-bin.
binlog_expire_logs_seconds=1800 # Set a value so that the generated data volume meets the requirement for long-time running.
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 # Adjust the value based on the system memory size.
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 # Enable mysql-bin.
binlog_expire_logs_seconds=1800 # Set a value so that the generated data volume meets the requirement for long-time running.
ssl=0
table_open_cache=30000
max_connect_errors=2000
innodb_adaptive_hash_index=0

mysqlx=0

2.4 Deploying 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. BenchmarkSQL Deployment on the Client

3.1 Installing BenchmarkSQL

Download BenchmarkSQL.

#Install the BenchmarkSQL dependency package.
yum install -y java

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

3.2 Configuring BenchmarkSQL Parameters

Configure the configuration file benchmarksql5.0-for-mysql/run/props.conf as follows:

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

Copy the props.conf file in the directory as props-2.conf, which is used as the configuration file of MySQL instance 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

The value of warehouses is set based on the current NVMe SSD specifications. 4050 warehouses of data is generated as the operation data of each MySQL instance. The general principle is as follows: Two copies of generated data are used as the operation data of the two MySQL instances. The total size of the two copies of data accounts for 60% to 70% of the drive space. In this way, the drive space usage can reach over 90% after the system runs stably for 720 minutes.

3.3 Creating MySQL Test Data

# Start the service.
/usr/local/mysql-8.0.20/support-files/mysql.server start

# Create test data. (After the test data is created, you are advised to back up the data in the **/data/mysql-1/data** directory on the server and copy the data from this directory.)
./runDatabaseBuild.sh props.conf

# Stop the database service.
/usr/local/mysql-8.0.20/support-files/mysql.server stop

3.4 Backing Up Data to the Standby Drive

After data for MySQL is created, back up the data to the standby drive /dev/nvme1n1. Assume that the drive is mounted to /bak:

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

4 Execution Environment Configuration

4.1 Enabling STEAL Optimization

Enable STEAL optimization on the server to maximize the CPU usage during the MySQL test and improve the CPU efficiency.

In the /etc/grub2-efi.cfg file, add the sched_steal_node_limit=4 parameter to the system boot options and reboot the system for the modification to take effect.

After the restart, enable the STEAL mode.

echo STEAL > /sys/kernel/debug/sched_features

4.2 Stopping Items That Affect the Test

# Stop irqbalance.
systemctl stop irqbalance.service
systemctl disable irqbalance.service

# Stop the firewall.
systemctl stop iptables
systemctl stop firewalld

4.3 Configuring NIC Interrupt-Core Binding

# Bind the interrupts on the server. (Replace the NIC name and CPU cores to be bound based on the environment.)
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) ## Set the cores for handling NIC interrupts based on the environment.
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 Installing the nvme-cli Tool

nvme-cli is a command-line tool used to monitor, configure, and manage NVMe devices. nvme-cli can be used to enable the NVMe SSD multi-stream feature and obtain controller logs through log commands.

yum install nvme-cli

4.5 Enabling the NVMe Multi-Stream Feature

• Run the following command to check the multi-stream feature status of the NVMe SSD:

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

  

  The command output indicates that the NVMe SSD supports Stream Directive, that is, the multi-stream feature, which is currently disabled.

• Enable the multi-stream function.

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

• Check the multi-stream feature status of the NVMe SSD again.

  

  The command output indicates that the multi-stream feature has been enabled for the NVMe SSD.

4.6 Preparing Data for the MySQL Instances

To unify the baseline test and multi-stream test processes, format the drive before each test and copy two copies of data from the /bak directory to the corresponding data directories of the two MySQL instances.

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()
# After formatting, create the data directories of the two MySQL instances, and start astream.
astream -i /data/mysql-1/data /data/mysql-2/data -r rule1.txt rule2.txt# ----&gt. Delete this step when testing the baseline version.
cp -r /bak/* /data/mysql-1/data
cp -r /bak/* /data/mysql-2/data

Run the df -h command to check whether the drive space usage of the /dev/nvme0n1 drive is about 60% to 70%.

4.7 Starting and Binding the MySQL Services

#Start two MySQL instances.
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 Setting a Scheduled Task

After data is successfully copied or generated, to measure the write amplification factor (WAF) of the drive before the MySQL test, use the crontab timer to execute the calculate_wa.sh script (see the following) for calculating the drive WAF every hour during the 12-hour test.

#!/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

You can run the crontab -e command to add a scheduled task for executing the script command every hour. The command is as follows:

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

If the device name of the tested NVMe drive is /dev/nvme0n1, pass nvme0 to the script as the parameter of the scheduled task.

4.9 Testing the MySQL Instances

Before the test, ensure that the multi-stream feature of the NVMe SSD has been enabled.

Enter the root directory of the tool on the client and start the test:

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

4.10 Stopping the astream Process

You do not need to perform this step after the baseline test. After the multi-stream test is complete, run the following command to stop the astream process:

astream stop

5 Test Results

The result generated by the scheduled script is in the result_WA.txt file in the directory where the script is located. After each test is complete, select the latest 12 non-zero data records in the file.

When data is written to the drive, three values are written to a line in the result_WA.txt file, including:

• Amount of data that is actually written to the drive within one hour.
• Write volume submitted by the host within one hour.
• Current drive WA. You can calculate the drive WAF in each hour using the formula in the appendix.

According to the current test results, when astream is used and MySQL runs stably for a long time, the WAF of NVMe SSDs decreases by 12%, that is, the performance is improved by 12%.

6 Appendix

Write amplification (WA) is an undesirable phenomenon associated with flash memory and SSDs where the actual amount of information physically written to the drive is a multiple of the logical amount intended to be written. The formula for calculating the write amplification factor (WAF) is as follows:

$$ WAF=\frac{\text{Actual data volume written to the drive}}{\text{Data volume submitted by the host}} $$

Generally, as data storage and drive fragmentation become more and more severe, the WAF increases. If the WAF increase can be the delayed, the service life of the drive can be prolonged.