MySQL Tuning Guide

Regarding MySQL installation, an easy way is to install with a prebuild MySQL package.

For Ubuntu OS, installing commands are:

Copy
sudo apt update
sudo apt install mysql-server

For better performance, we suggest building from the source code. The following are instructions to download and build MySQL.

Copy
# Ubuntu packages 

<br>

sudo apt install pkg-config gcc g++  cmake libssl-dev libncurses-dev libntirpc-dev libudev-dev bison sysstat  -y

# download and compile mysql 
rm -rf build 
rm -rf BOOST 
rm -rf mysql-8.0.27.tar.gz 
rm -rf mysql-8.0.27 

echo "Installing mysql @ $MYSQL_INSTALL_DIR" 
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.27.tar.gz 
tar zxvf mysql-8.0.27.tar.gz
mkdir BOOST; cd BOOST; wget https://sourceforge.net/projects/boost/files/boost/1.73.0/boost_1_73_0.tar.gz ; cd ${CURDIR} 
mkdir build; cd build 

OPT_FLAGS="-g -O3 -fno-omit-frame-pointer -march=native -DNDEBUG" 

cmake ../mysql-8.0.27 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=../BOOST -DCMAKE_C_FLAGS_RELWITHDEBINFO="$OPT_FLAGS" -DCMAKE_CXX_FLAGS_RELWITHDEBINFO="$OPT_FLAGS" -DCMAKE_BUILD_TYPE=RelWithDebInfo  -DWITH_UNIT_TESTS=OFF -DCMAKE_INSTALL_PREFIX=$MYSQL_INSTALL_DIR 

make -j`nproc` 
sudo make install
#cleanup
cd ..
rm -rf build mysql-8.0.27.tar.gz mysql-8.0.27 BOOST

An XFS file system format is recommended for better IO performance. Below are instructions to create an XFS filesystem and mount it on local disk storage.

Copy
#format the data disk with xfs 
sudo apt install xfsprogs 
sudo mkfs.xfs /dev/DEVICE_NAME 

#mount the disk  
sudo mkdir -p /mnt/disks/MOUNT_DIR 
sudo mount -o discard,defaults /dev/DEVICE_NAME /mnt/disks/MOUNT_DIR

Besides local disk storage, the cloud provider also provides the file storage service. The below link describes instructions to setup and mount with OCI file storage:

https://blogs.oracle.com/cloud-infrastructure/post/mounting-oci-file-storage-and-other-nfs-shares-on-docker-containers

Contents of config.rc (variable definitions):

Copy
# --------------
# config.rc for MySQL server
# -------------- 

HOMEDIR=/home/USR
MYSQL_INSTALL_DIR=$HOMEDIR/installs/mysql-8.0.27
MYSQL_CLIENT=${MYSQL_INSTALL_DIR}/bin/mysql 

DATA_DIR="/mnt/disks/mysql_data"
PORT=3000 

INSTANCE=1
NUMACTL_SRV="" 

MYSQL_HOST=IP_ADDRESS
PASSWD='123456' 

Contents of mysql_conf.tmp

Copy
# --------------
# MySQL configuration file
# --------------
[mysqld]
tmpdir = %DATA_ROOT%/data%PORT%/tmp
socket = %DATA_ROOT%/data%PORT%/tmp/mysql.sock
log-error = %DATA_ROOT%/data%PORT%/tmp/error.log
pid-file = %DATA_ROOT%/data%PORT%/tmp/mysql.pid
datadir = %DATA_ROOT%/data%PORT%/dbs%PORT%
port = %PORT%
general_log = 0
skip_log_bin =1
performance_schema = false
max_prepared_stmt_count=1000000
skip-name-resolve
max_connections=3500
max_user_connections=3500
thread_cache_size=16384
max_connect_errors=3500
binlog_format=STATEMENT
innodb_buffer_pool_size=50GB 
innodb_buffer_pool_instances=8
innodb_file_per_table 
innodb_log_file_size=2G
innodb_log_files_in_group=3 
binlog_row_image=minimal
binlog_checksum=NONE
innodb_checksum_algorithm=none
table_open_cache_instances=64
innodb_open_files=4000 
innodb_use_native_aio=1
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=64M
innodb_thread_concurrency=128
innodb_max_dirty_pages_pct=25 
innodb_adaptive_flushing=1
innodb_read_io_threads=8
innodb_write_io_threads=40
innodb_purge_threads=8 
innodb_io_capacity=20000 
innodb_lru_scan_depth=20000 
#innodb_max_io_capacity=100000 
#symbolic-links=0 
 [mysqld_safe]
#
# include all files from the config directory 
# 
#!includedir /etc/my.cnf.d 

Make sure to set the innodb_buffer_pool_size to 80% of available memory.

a. Initialize MySQL server with mysql_init.sh. Note: this script reads the server IP address, paths from config.rc , and configures according to mysql_conf.tmp

Copy
# --------------
# Contents of mysql_init.sh 
# --------------
#!/bin/bash 
source ./config.rc
if [ ! -d $DATA_DIR ] ; then 

    mkdir -p $DATA_DIR 

fi 

echo never > /sys/kernel/mm/transparent_hugepage/enabled 

ninst=$INSTANCE 

for i in $(seq 1 $ninst) ; do 

     # prepare the conf file for each instance 

    mysql_port=$(($PORT + i)) 

    mysql_basedir=$DATA_DIR/data$mysql_port 

    mysql_datadir=$DATA_DIR/data$mysql_port/dbs$mysql_port 

    mysql_tmpdir=$DATA_DIR/data$mysql_port/tmp 

    rm -rf $mysql_basedir 

    mkdir -p $mysql_basedir $mysql_datadir $mysql_tmpdir 

    echo "Creating a mysqld instance at ${mysql_basedir}..." 

    # prepare the conf file for instance 

    sed -e 's/%PORT%/'$mysql_port'/g' \ 

            -e 's,%DATA_ROOT%,'$DATA_DIR',g' \ 

            mysql_conf.tmp > \ 

            $mysql_basedir/my.cnf 

    # initialize instance 

    $NUMACTL_SRV $MYSQL_INSTALL_DIR/bin/mysqld --defaults-file=$mysql_basedir/my.cnf --skip-grant-tables --user=root --initialize >> $mysql_basedir/mysql_install_db.log 2>&1 & 

    wait 

    echo "Starting mysqld instances on port $mysql_port..." 

    LD_PRELOAD=$JEMALLOC $NUMACTL_SRV $MYSQL_INSTALL_DIR/bin/mysqld --defaults-file=$mysql_basedir/my.cnf --user=root & 

done 

sleep 10 

for i in $(seq 1 $ninst) ; do 

     mysql_port=$(($PORT + i)) 

    mysql_basedir=$DATA_DIR/data$mysql_port 

    mysql_datadir=$DATA_DIR/data$mysql_port/dbs$mysql_port 

    mysql_tmpdir=$DATA_DIR/data$mysql_port/tmp 

    password_line=$(grep "A temporary password is generated" ${mysql_tmpdir}/error.log) 

    OLD_PASSWORD=${password_line##*: } 

    $MYSQL_CLIENT --socket=${mysql_tmpdir}/mysql.sock --connect-expired-password -uroot -p"${OLD_PASSWORD}" -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';" 

    $MYSQL_CLIENT --socket=${mysql_tmpdir}/mysql.sock -uroot -p'123456' -e "GRANT ALL ON *.* TO 'root'@'%';" 

    $MYSQL_CLIENT --socket=${mysql_tmpdir}/mysql.sock -uroot -p'123456' -e "create database sbtest" 

    $MYSQL_CLIENT --socket=${mysql_tmpdir}/mysql.sock -uroot -p'123456' -e "CREATE USER 'sbtest'@'%' IDENTIFIED WITH mysql_native_password BY '123456';" 

    $MYSQL_CLIENT --socket=${mysql_tmpdir}/mysql.sock -uroot -p'123456' -e "GRANT ALL ON *.* TO 'sbtest'@'%';" 

done 

for i in $(seq 1 $ninst) ; do 

    mysql_port=$(($PORT + i)) 

    mysql_tmpdir=$DATA_DIR/data$mysql_port/tmp 

    mysql_sock=$mysql_tmpdir/mysql.sock 

    echo "Shuting down the instance at port $mysql_port..." 

    $MYSQL_INSTALL_DIR/bin/mysqladmin --socket=$mysql_sock -uroot -p123456 shutdown

done

b. Start the MySQL server at the port requested in config.rc.

Copy
# --------------
# Contents of mysql_start.sh
# --------------
#!/bin/bash 

source ./config.rc 

echo never > /sys/kernel/mm/transparent_hugepage/enabled 

ninst=$INSTANCE 

for i in $(seq 1 $ninst) ; do 

    mysql_port=$(($PORT + i)) 

    mysql_basedir=$DATA_DIR/data$mysql_port 

    mysql_tmpdir=$DATA_DIR/data$mysql_port/tmp 

    # start the server instance 

    echo "Starting mysqld instances on port $mysql_port..." 

    LD_PRELOAD=$JEMALLOC $NUMACTL_SRV $MYSQL_INSTALL_DIR/bin/mysqld --defaults-file=$mysql_basedir/my.cnf  --user=root >>$mysql_basedir/mysqld_startup.log & 

    sleep 5 

done

Regarding Sysbench installation, an easy way on Ubuntu is using the command as: apt install Sysbench, although that doesn't have all the OLTP features.

The following are instructions to manually build and installation from Sysbench source code.

Sysbench is locally installed in user home directory on the client VM. Edit the install path in “./configure --prefix= “ to any path of user choice.

Copy
sudo apt -y install make automake libtool pkg-config libaio-dev wget unzip 
sudo apt -y install libmysqlclient-dev libssl-dev libmariadb-dev-compat libmariadb-dev 
sudo apt install libmariadb-dev-compat libmariadb-dev -y
wget https://github.com/akopytov/sysbench/archive/refs/heads/master.zip
unzip master
cd sysbench-master
./autogen.sh
./configure --prefix=/home/USER/installs/sysbench
make -j`nproc`
sudo make install 

The below file shows an example test setup for the MySQL server. Sysbench is set-up to run the oltp_point_select workload. More workloads can be added to the list. Edit the 'MYSQL_HOST_IP_ADDRESS’ to point to the MySQL host VM.

Copy
# --------------
# Contents of config.rc on the client VM
# -------------- 

<br>

tag="mysql_50GB_buffer_compress"
HOMEDIR=/home/USER 
SYSBENCH_INSTALL_DIR=${HOMEDIR}/installs/sysbench 

PORT=3000 
WARMTIME=1200 
RUNTIME=300 
TABLECNT=8 
TABLESIZ=10000000 
INSTANCE=1 
NUMACTL_CLI="" 

MYSQL_HOST='MYSQL_HOST_IP_ADDRESS’ 
PASSWD='123456'
THREAD_ARRY=(64 72) 
WORKLOADS=(oltp_point_select)

TABLE_COMPRESSION='on' 
PREWARM='yes'

Run mysql_prepare.sh script to load the database using point_select_workload

Copy
# -------------- 
# Contents of mysql_prepare.sh 
# -------------- 

#!/bin/bash 

source ./config.rc 

workload=oltp_point_select 

export LUA_PATH=$SYSBENCH_INSTALL_DIR/share/sysbench/?.lua 

export LD_LIBRARY_PATH=$MYSQL_INSTALL_DIR/lib 

ninst=$INSTANCE 

job_ids="" 

for i in $(seq 1 $ninst) ; do 

    mysql_port=$(($PORT + i)) 

    echo "Generate data for instance $mysql_port for benchmark..." 

    if [ "${TABLE_COMPRESSION}" == "on" ] 

    then 

        $SYSBENCH_INSTALL_DIR/bin/sysbench $SYSBENCH_INSTALL_DIR/share/sysbench/${workload}.lua --table-size=$TABLESIZ --tables=$TABLECNT --threads=$TABLECNT --mysql-user=sbtest --mysql-password=123456 --mysql-host=${MYSQL_HOST}  --mysql-port=$mysql_port --create_table_options="ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8"  prepare >> ./data_prepare.log 2>&1 & 

    else 

        $SYSBENCH_INSTALL_DIR/bin/sysbench $SYSBENCH_INSTALL_DIR/share/sysbench/${workload}.lua --table-size=$TABLESIZ --tables=$TABLECNT --threads=$TABLECNT --mysql-user=sbtest --mysql-password=123456 --mysql-host=${MYSQL_HOST}  --mysql-port=$mysql_port  prepare >> ./data_prepare.log 2>&1 &
    fi  

    job_ids="$job_ids $!" 

done 

echo "Waiting for the data generating..." 

wait $job_ids 

echo "Data Generation Done."

Run mysql_bench.sh script. The workloads and threads are defined in config.rc on the client VM mentioned in the previous step. The resulting summary will be in the folder name that is specified in the config.rc “tag” variable.

Copy
#!/bin/bash 

source ./config.rc 

DATASTAMP=`date +%Y_%m_%d_%H%M%S` 

ninst=$INSTANCE 

export LUA_PATH=$SYSBENCH_INSTALL_DIR/share/sysbench/?.lua 

export LD_LIBRARY_PATH=$MYSQL_INSTALL_DIR/lib 

ulimit -Hn 1048576 

ulimit -n 1048576 

ulimit -s unlimited 

#TODO: capture Ctlr-C and clean up the monitoring on the server 

clean_up() { 

    echo "Test Terminated." 

    ## kill sysbench 

    kill -9 `pidof sysbench` 

} 

trap clean_up SIGINT 

#warm the buffer bool using oltp_point_select 

if [ "$PREWARM" == "yes" ] 

then 

    job_ids="" 

    for i in $(seq 1 $ninst) ; do 

        mysql_port=$(($PORT + i)) 

        mysql_tmpdir=$DATA_DIR/data$mysql_port/tmp 

        mysql_sock=$mysql_tmpdir/mysql.sock 

       echo "Warming for instance $mysql_port warming..." 

        thread_num=64 

        $NUMACTL_CLI ${SYSBENCH_INSTALL_DIR}/bin/sysbench $SYSBENCH_INSTALL_DIR/share/sysbench/oltp_point_select.lua --table-size=$TABLESIZ --tables=$TABLECNT --mysql-user=sbtest --mysql-password=123456 --mysql-host=${MYSQL_HOST}  --mysql-db=sbtest  --db-driver=mysql --mysql-ignore-errors=1062 --threads=$thread_num --events=0 --time=$WARMTIME --report-interval=10 --mysql-port=$mysql_port --skip_trx run >> prewarm.log 2>&1 & 

        job_ids="$job_ids $!" 

    done 

    echo "Waiting for the data warming..." 

    wait $job_ids 

fi 

for workload in ${WORKLOADS[@]}; 

do 

    RESULT_DIR=`pwd`/${workload}_ins${ninst}_${tag}_${DATASTAMP} 

    if [ ! -d ${RESULT_DIR} ] 

    then 

        mkdir ${RESULT_DIR} 

        touch ${RESULT_DIR}/summary.txt 

        cp ./config.rc ${RESULT_DIR} 

    fi 

    printf "*** MySQL Instance = $ninst ***\n" >>$RESULT_DIR/summary.txt 

    #printf "%-10s %-10s %-10s %-10s %-20s %-20s %-20s %-20s %-20s %-20s %-20s\n" "thread" "TPS" "QPS" "resp_time" "resp_time_95%" "CPU_Usr" "CPU_Sys" "CPU_IO" "CPU_Util" "IO_R(MB/s)" "IO_W(MB/s)">>$RESULT_DIR/summary.txt 

    printf "%-10s %-10s %-10s %-10s %-20s\n" "thread" "TPS" "QPS" "resp_time" "resp_time_95%">>$RESULT_DIR/summary.txt 

    for thread_num in ${THREAD_ARRY[@]}; 

    do 

        echo " ==> Test with $thread_num client thread" 

        job_ids="" 

        for inst in $(seq 1 $ninst) ; do 

            mysql_port=$(($PORT + inst)) 

            LOG="mysql_${workload}_thread${thread_num}_instance$inst.log" 

            echo "----------------thread=$thread_num---------------------" >> $RESULT_DIR/$LOG 

            # Run Workloads 

            echo "  --Start test for instance $inst..." 

            $NUMACTL_CLI ${SYSBENCH_INSTALL_DIR}/bin/sysbench $SYSBENCH_INSTALL_DIR/share/sysbench/${workload}.lua --table-size=$TABLESIZ --tables=$TABLECNT --mysql-user=sbtest --mysql-password=123456 --mysql-host=${MYSQL_HOST}  --mysql-db=sbtest  --db-driver=mysql --mysql-ignore-errors=1062,1213 --threads=$thread_num --events=0 --time=$RUNTIME --warmup-time=100 --report-interval=10 --mysql-port=$mysql_port --skip_trx  run >> $RESULT_DIR/$LOG 2>&1 & 

            job_ids="$job_ids $!" 

        done 

        wait $job_ids 

        echo " <== All jod done for $thread_num client thread." 

        TPS=0 

        QPS=0 

        RESP_TIME=0 

        RESP_TIME_95=0 

        for inst in $(seq 1 $ninst) ; do 

            LOG="mysql_${workload}_thread${thread_num}_instance$inst.log" 

            echo "----------------END thread=$thread_num---------------------" >>  $RESULT_DIR/$LOG 

            ltps=`sed -n '/thread='"$thread_num"'[^0-9]/,/END thread='"$thread_num"'[^0-9]/p' $RESULT_DIR/$LOG | grep "transactions" | awk -F ':' '{print $2}'|awk -F '(' '{printf "%4.2f",$2}'` 

            TPS=`echo $TPS $ltps | awk '{printf "%4.2f", $1+$2}'` 

            lqps=`sed -n '/thread='"$thread_num"'[^0-9]/,/END thread='"$thread_num"'[^0-9]/p' $RESULT_DIR/$LOG | grep "queries:" | awk -F ':' '{print $2}'|awk -F '(' '{printf "%4.2f",$2}'` 

            QPS=`echo $QPS $lqps | awk '{printf "%4.2f", $1+$2}'` 

            lrt=`sed -n '/thread='"$thread_num"'[^0-9]/,/END thread='"$thread_num"'[^0-9]/p' $RESULT_DIR/$LOG | sed -n '/Latency (ms)/,/Threads fairness/p'| grep "avg" | awk -F ':' '{printf "%4.2f",$2}'` 

            RESP_TIME=`echo $RESP_TIME $lrt | awk '{printf "%4.2f", $1+$2}'` 

            lrt95=`sed -n '/thread='"$thread_num"'[^0-9]/,/END thread='"$thread_num"'[^0-9]/p' $RESULT_DIR/$LOG | sed -n '/Latency (ms)/,/Threads fairness/p'| grep "95th percentile" | awk -F ':' '{printf "%4.2f",$2}'` 

            RESP_TIME_95=`echo $RESP_TIME_95 $lrt95 | awk '{printf "%4.2f", $1+$2}'` 

        done 

        # Average the response time 

        RESP_TIME=`echo $RESP_TIME $ninst | awk '{printf "%4.2f", $1/$2}'` 

        RESP_TIME_95=`echo $RESP_TIME_95 $ninst | awk '{printf "%4.2f", $1/$2}'` 

        #printf "%-10s %-10s %-10s %-10s %-20s %-20s %-20s %-20s %-20s %-20s %-20s\n" "$thread_num" "$TPS" "$QPS" "$RESP_TIME" "$RESP_TIME_95" "$CPU_AVG_USR" "$CPU_AVG_SYS" "$CPU_AVG_IO" "$CPU_AVG_UTIL" "$IO_R" "$IO_W" >>$RESULT_DIR/summary.txt 

        printf "%-10s %-10s %-10s %-10s %-20s\n" "$thread_num" "$TPS" "$QPS" "$RESP_TIME" "$RESP_TIME_95" >>$RESULT_DIR/summary.txt 

        # Sleep for clean up 

        sleep 5 

    done 

    echo -e "\n" >>$RESULT_DIR/summary.txt 

done

Copy
# -------------- 
# Contents of mysql_install_pgo_profile.sh 
# --------------  

# Ubuntu packages 

 sudo apt install pkg-config gcc g++  cmake libssl-dev libncurses-dev libntirpc-dev libudev-dev bison sysstat  -y  

# download and compile mysql 
rm -rf build 
rm -rf BOOST 
rm -rf mysql-8.0.27.tar.gz 
rm -rf mysql-8.0.27 

MYSQL_INSTALL_DIR=/usr/local/mysql-8.0.27_pgo_profile  

echo "Installing mysql @ $MYSQL_INSTALL_DIR" 
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.27.tar.gz 
tar zxvf mysql-8.0.27.tar.gz 

mkdir BOOST; cd BOOST; wget https://sourceforge.net/projects/boost/files/boost/1.73.0/boost_1_73_0.tar.gz ; cd ${CURDIR} 
mkdir build; cd build

#create profile directory 
rm -rf /tmp/pgo_dir 
mkdir /tmp/pgo_dir 

#build with profile-generate 

OPT_FLAGS="-g -O3 -fno-omit-frame-pointer -march=native -DNDEBUG -fprofile-generate -fprofile-dir=/tmp/pgo_dir" 

cmake ../mysql-8.0.27 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=../BOOST -DCMAKE_C_FLAGS_RELWITHDEBINFO="$OPT_FLAGS" -DCMAKE_CXX_FLAGS_RELWITHDEBINFO="$OPT_FLAGS" -DCMAKE_BUILD_TYPE=RelWithDebInfo  -DWITH_UNIT_TESTS=OFF -DCMAKE_INSTALL_PREFIX=$MYSQL_INSTALL_DIR 
make -j`nproc` 
sudo make install 
#cleanup  
cd .. 
rm -rf build mysql-8.0.27.tar.gz mysql-8.0.27 BOOST

Execute mysql_install_pgo_.profile.sh.

Folder /tmp/pgo_dir is created as a pgo profile storage directory. MySQL binaries are built for profile generating, installed at /usr/local/mysql-8.0.27_pgo_profile

a. Training with MTR innodb suite cases? Skip the failure cases in skip file list

Copy
# --------------
# Contents of mtr_pgo_training.sh 
# --------------  

cd /usr/local/mysql-8.0.27_pgo_profile/mysql-test 
rm -rf skiplist  
#create skipfile 
cat << EOF > skiplist 
innodb.log_corruption : WL#90000 Skip due to failure 
innodb.log_corruption_1 : WL#90000 Skip due to failure 
innodb.tablespace_portability : WL#90000 Skip due to failure 
innodb.alter_kill : WL#90000 Skip due to failure 
innodb.log_file_size_1 : WL#90000 Skip due to failure 
innodb.alter_rename_existing_xtra : WL#90000 Skip due to failure 
innodb.bootstrap : WL#90000 Skip due to failure 
innodb.innodb_page_size_func : WL#90000 Skip due to failure
innodb.innodb_read_only-2 : WL#90000 Skip due to failure 
innodb.log_encrypt_6 : WL#90000 Skip due to failure 
innodb.discarded_partition_upgrade_from_57 : WL#90000 Skip due to failure 
innodb.log_file_system : WL#90000 Skip due to failure 
innodb.temporary_table : WL#90000 Skip due to failure 
EOF 
#run mtr with innodb suite and skip failure case in the skiplist 
 ./mtr --suite=innodb --skip-test-list=skiplist 

Execute script mtr_pgo_trainning.sh to train with MTR innodb suite. Profile information is generated under /tmp/pgo_dir.

Copy
# -------------- 
# Contents of mysql_install_pgo_use.sh 
# -------------- 

# Ubuntu packages 

 sudo apt install pkg-config gcc g++  cmake libssl-dev libncurses-dev libntirpc-dev libudev-dev bison sysstat  -y  

# download and compile mysql 
rm -rf build 
rm -rf BOOST

rm -rf mysql-8.0.27.tar.gz 
rm -rf mysql-8.0.27 

MYSQL_INSTALL_DIR=/usr/local/mysql-8.0.27_pgo_use 

echo "Installing mysql @ $MYSQL_INSTALL_DIR" 
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.27.tar.gz 
tar zxvf mysql-8.0.27.tar.gz 

mkdir BOOST; cd BOOST; wget https://sourceforge.net/projects/boost/files/boost/1.73.0/boost_1_73_0.tar.gz ; cd ${CURDIR} 
mkdir build; cd build 


#build with profile-use 

OPT_FLAGS="-g -O3 -fno-omit-frame-pointer -march=armv8.2-a -DNDEBUG -fprofile-use -fprofile-dir=/tmp/pgo_dir/ -fprofile-correction -Wno-error=missing-profile"  

cmake ../mysql-8.0.27 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=../BOOST -DCMAKE_C_FLAGS_RELWITHDEBINFO="$OPT_FLAGS" -DCMAKE_CXX_FLAGS_RELWITHDEBINFO="$OPT_FLAGS" -DCMAKE_BUILD_TYPE=RelWithDebInfo  -DWITH_UNIT_TESTS=OFF -DCMAKE_INSTALL_PREFIX=$MYSQL_INSTALL_DIR 

make -j`nproc` 
sudo make install 
#cleanup  
cd .. 
rm -rf build mysql-8.0.27.tar.gz mysql-8.0.27 BOOST 

Execute the script MySQL_install_pgo_use.sh, which uses the PGO profile to build optimized binaries installed at /usr/local/mysql-8.0.27_pgo_use.

This MySQL build can be used for performance testing.