mysqld BOLT优化及性能测试步骤

前置条件

bash

/data # 目录
python 3.7+
clang 12.0.0
gcc 10.2.0
llvm-bolt # from https://github.com/angelica-moreira/BOLT

在进行下面的程序前，请先终止mysqld线程

bash

# 1. 使用mysqladmin
cd /data/mysql/mysql-{compiler}/usr/local/mysql/ # {compiler}为使用的编译器
bin/mysqladmin shutdown -p{password} # {password}为mysql的密码
# 2. 使用killall
killall mysqld

安装python虚拟环境与所需依赖

将以下依赖复制至环境中，保存并命名为requirements.txt

txt

absl-py==1.3.0
astunparse==1.6.3
cachetools==5.2.0
certifi==2022.9.24
charset-normalizer==2.1.1
flatbuffers==22.9.24
gast==0.4.0
google-auth==2.12.0
google-auth-oauthlib==0.4.6
google-pasta==0.2.0
grpcio==1.49.1
h5py==3.7.0
idna==3.4
joblib==1.2.0
keras==2.10.0
Keras-Preprocessing==1.1.2
libclang==14.0.6
Markdown==3.4.1
MarkupSafe==2.1.1
numpy==1.23.4
oauthlib==3.2.1
opt-einsum==3.3.0
packaging==21.3
pandas==1.5.0
protobuf==3.19.6
pyasn1==0.4.8
pyasn1-modules==0.2.8
pyparsing==3.0.9
python-dateutil==2.8.2
pytz==2022.4
requests==2.28.1
requests-oauthlib==1.3.1
rsa==4.9
scikit-learn==1.1.2
scipy==1.9.2
six==1.16.0
sklearn==0.0
tensorboard==2.10.1
tensorboard-data-server==0.6.1
tensorboard-plugin-wit==1.8.1
tensorflow==2.10.0
tensorflow-estimator==2.10.0
tensorflow-io-gcs-filesystem==0.27.0
termcolor==2.0.1
threadpoolctl==3.1.0
typing_extensions==4.4.0
urllib3==1.26.12
Werkzeug==2.2.2
wrapt==1.14.1

bash

python3 -m venv vespa-env
source vespa-env/bin/activate
pip install -r requirements.txt

生成csv、预处理数据并生成prob.pdata

bash

cd /data/mysql/mysql-{compiler}
llvm-bolt usr/local/mysql/bin/mysqld -gen-features
VESPA_SCRIPTS_HOME=/data/llvm/tools/llvm-bolt/utils/vespa_scripts/
python3 ${VESPA_SCRIPTS_HOME}/preprocessing.py . 1 train
python3 ${VESPA_SCRIPTS_HOME}/generate_probfiles.py /data/llvm/tools/llvm-bolt/utils/models train/summarized_data .
deactivate

llvm-bolt编译并替换原有的mysqld

bash

llvm-bolt usr/local/mysql/bin/mysqld -o usr/local/mysql/bin/mysqld.bolt \
                         -infer-global-counts -heuristic-based=wularus \
                         -reorder-blocks=cache+ -split-functions=3 -split-all-cold -dyno-stats \
                         -icf=1 -reorder-functions=hfsort -split-eh -use-gnu-stack
cd usr/local/mysql
mv bin/mysqld bin/mysqld.old
cp bin/mysqld.bolt bin/mysqld
nohup numactl -N 0 bin/mysqld_safe \
    --user=mysql \
    --datadir={datadir} \ # {data-dir}为存放数据的路径
    --defaults-file={defaults-file} \ # {defaults-file}为存放的my.cnf
    --basedir={basedir} \
    --core-file-size=unlimited \
    --open_files_limit=102400 \
    --skip-grant-tables > cdb_para_start.log 2>&1 &

测试脚本

bash

#!/bin/bash

script_root=/usr/local/share/sysbench

for script_name in oltp_read_write.lua oltp_write_only.lua oltp_read_only.lua oltp_insert.lua oltp_update_index.lua oltp_update_non_index.lua
do
    script=${script_root}/${script_name}
    echo "************ Current script: ${script_name} ************"
    for THREADS in 1 8 16 32 128 
    do
        for i in 1 2 3
        do
            echo "****** loop ${i} ******"
            # 如果在启动mysqld时设置了socket 请在以下命令中设置--mysql-socket这一参数
            numactl -C 8-15 /data/usr/local/bin/sysbench ${script} --mysql-db=sbtest --mysql-user=root --mysql-password=root --table_size=1000000 --tables=1 --report-interval=10 --db-driver=mysql --threads=${THREADS} prepare
            numactl -C 8-15 /data/usr/local/bin/sysbench ${script} --mysql-db=sbtest --mysql-user=root --mysql-password=root --table_size=1000000 --tables=1 --report-interval=10 --db-driver=mysql --threads=${THREADS} --time=60 --warmup-time=30 run
            numactl -C 8-15 /data/usr/local/bin/sysbench ${script} --mysql-db=sbtest --mysql-user=root --mysql-password=root --table_size=1000000 --tables=1 --report-interval=10 --db-driver=mysql --threads=${THREADS} cleanup
        done
    done
done

mysqld BOLT优化及性能测试步骤 #

mysqld BOLT优化及性能测试步骤