mysql tpcc压测

数据运维 2024-03-29 剑圣无痕手机阅读

配置

1 台 IT5.4XLARGE64 (高IO型IT5, 16核64GB）

mysql安装

安装过程（略）

重要配置

innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size=32G
innodb_log_file_size = 2G

innodb_sort_buffer_size = 64M

max_connections = 5000
max_connect_errors = 1000000
table_open_cache = 2048
table_definition_cache = 2048
thread_stack = 2048K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M

TPCC 安装

tpcc-mysql 安装

# 安装MySQL TPCC工具 wget https://github.com/Percona-Lab/tpcc-mysql/archive/refs/heads/master.zip unzip master.zip cd src make 或 git clone https://github.com/percona/tpcc-mysql.git cd tpcc-mysql make

压测用户

create database tpccdb; create user 'tpcc'@'%' IDENTIFIED with mysql_native_password by 'tpcc@123'; grant all privileges on tpccdb.* to tpcc;

数据准备

建表索引

cd /root/tpcc-mysql-master mysql -hlocalhost -S /data/mysql/mysql.sock -utpcc -p'tpcc@123' tpccdb < create_table.sql mysql -hlocalhost -S /data/mysql/mysql.sock -utpcc -p'tpcc@123' tpccdb < add_fkey_idx.sql

加载数据

cd /root/tpcc-mysql-master ./tpcc_load -h 127.0.0.1 -P3306 -utpcc -p'tpcc@123' -d tpccdb -w 200

太慢了，使用load并行加载来替换

cd /root/tpcc-mysql-master sh load.sh tpccdb 200

mysql tpcc压测-每日运维

为了快速加载，关闭log-bin相关参数skip-log-bin=1

压测

未开binlog前提下测试

./tpcc_start -h127.0.0.1 -dtpccdb -utpcc -p'tpcc@123' -w200 -c32 -r10 -l 360 -i10 | tee /tmp/tpcc_report_`date +%F`.log

压测输出 mysql tpcc压测-每日运维

开binlog下测试

开启binlog, innodb_spin_wait_delay=3 原值5 innodb_sync_spin_loops=20 原值30

./tpcc_start -h127.0.0.1 -dtpccdb -utpcc -p'tpcc@123' -w200 -c32 -r10 -l 360 -i10 | tee /tmp/tpcc_report_`date +%F`.log

mysql tpcc压测-每日运维

nmon监控

mkdir nmon cd nmon wget http://sourceforge.net/projects/nmon/files/download/nmon_x86_12a.zip unzip nmon_x86_12a.zip chmod u+x nmon_x86_rhel45 yum install glibc.i686 libncurses.so.5 redhat-lsb -y cp nmon_x86_rhel45 /usr/local/bin/nmon

监控

nmon -s 1 -c 360 -f -m /tmp