测试范围
1.release、debug测试case全量回归
所有Case均要通过，即100%通过率，先测release后debug，正式提交代码前必须保证。
将代码mysql-test目录中testall.sh拷入编译目录mysql-test执行即可，当需要进行完整测试时要加--big-test
2.valgrind测试
自己新加的case必须要通过valgrind测试，防止有内存泄漏。
3.Rqg 测试
4.sysbench测试
(read-only、Rread-write、Write-only、Update-indx、Point-Select) 8、16、32、64、128、256、512并发，测试每个3分钟，作为性能基准，与前版本性能对比并列出。(可以一个脚本完成，数据量待定）
5.TPCC测试稳定性
OLTP 200 wearhouse 256并发2天稳定性测试
6.sysbench稳定性测试
256 并发32表* 100W 3天稳定性测试读写（Rread-write）（关binlog）
7.与前版本默认参数对比
版本打包
参考上一篇文章【MySQL内核系列】Centos7环境MySQL5.7源码编译教程
初始化安装目录
目录1：sysbench压测目录
目录2：tpcc目录
release、debug全量回归测试（--big-test），失败用例单独跑。
sysbench 压测：
sysbench测试(read-only、Rread-write、Write-only、Update-indx、Point-Select)，数据量：100W*16个表，8、16、32、64、128、256、512并发，测试每个3分钟，作为性能基准。
sysbench稳定性测试，256 并发32表* 100W 3天稳定性测试读写（Read-write）（关binlog）
sysbench 测试
下载sysbench 版本： GitHub - akopytov/sysbench: Scriptable database and system performance benchmark
安装编译略。
sysbench性能&稳定测试：
/data/test_tpcc/tpcc-mysql-master/sysbench_perf.sh > /data/test_tpcc/tpcc-mysql-master/sysbench_perf.log &
#!/bin/bash
mysql -uroot -h127.1 -P3310 -p123456 -e"shutdown"
sleep 20;
cd /data/tpcc
rm -rf /data/tpcc/data
tar -xf data_source.tar.gz
/data/oppo-advanced-MySQL-5.7.36-39-linux-x86_64_20200430/bin/mysqld --defaults-file=/data/my.cnf --basedir=/data/oppo-advanced-MySQL-5.7.36-39-linux-x86_64_20200430 --user=root &
sleep 10;
mysql -uroot -h127.1 -P3310 -p123456 -e"create database mydb"
mysql -uroot -h127.1 -P3310 -p123456 -e"set global max_prepared_stmt_count=200000"
echo "============sysbench性能测试开始======================"
echo "======开始时间：" && date
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_point_select.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=1 --tables=16 --threads=16 --time=120 --report-interval=30 prepare
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_point_select.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=1 --tables=16 --threads=4 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_point_select.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=1 --tables=16 --threads=16 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_point_select.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=1 --tables=16 --threads=64 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_point_select.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=1 --tables=16 --threads=256 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_point_select.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=1 --tables=16 --threads=1024 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_point_select.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=1 --tables=16 --threads=16 --time=120 --report-interval=30 cleanup
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=4 --time=120 --report-interval=30 prepare
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=4 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=16 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=64 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=256 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=1024 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=1 --tables=16 --threads=16 --time=120 --report-interval=30 cleanup
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_update_index.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=4 --time=120 --report-interval=30 prepare
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_update_index.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=4 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_update_index.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=16 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_update_index.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=64 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_update_index.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=256 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_update_index.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=1024 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=1 --tables=16 --threads=16 --time=120 --report-interval=30 cleanup
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_update_non_index.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=4 --time=120 --report-interval=30 prepare
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_update_non_index.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=4 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_update_non_index.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=16 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_update_non_index.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=64 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_update_non_index.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=256 --time=120 --report-interval=30 run
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_update_non_index.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=100000 --tables=16 --threads=1024 --time=120 --report-interval=30 run
echo "============sysbench性能测试结束======================"
echo "======结束时间：" && date
##稳定性
echo "==================sysbench稳定性测试开始，压测时间5天======================"
echo "======开始时间：" && date
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=200000 --tables=32 --threads=16 --time=120 --report-interval=30 cleanup
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=200000 --tables=32 --threads=16 --time=120 --report-interval=30 prepare
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=127.1 --mysql-port=3310 --mysql-db=mydb --mysql-user=root --mysql-password=123456 --table_size=200000 --tables=32 --threads=256 --time=432000 --report-interval=1800 run
echo "==================sysbench稳定性完成=========="
echo "======结束时间：" && date
TPCC测试
OLTP 200 wearhouse 256并发3天稳定性测试（占用空间会比较大）
下载代码：GitHub - Percona-Lab/tpcc-mysql
进行src目录执行make，会在上级目录生成 tpcc_load、tpcc_start 。
在tpcc目录下执行：
创建ＤＢ：
mysql -uroot -h127.1 -e"create database tpcc"
mysql -uroot -h127.1 -Dtpcc <create_table.sql
mysql -uroot -h127.1 -Dtpcc < add_fkey_idx.sql
./tpcc_load -uroot -h127.1 -d tpcc -w 1000 #1000warehouse
./tpcc_load -uroot -h127.1 -d tpcc -w 20 #20warehouse
load数据准备已经完成，直接cp一份即可。tpcc_data_1000warehouses、tpcc_data_20warehouses分别对应1000和20warehouse数据目录，复制并重命名为data目录用mysqld启动即可。
先测试tpcc性能数据，20warehouse，预热2分钟，运行20分钟：
./tpcc_start -uroot -h127.1 -P3306 -p123456 -d tpcc -w 20 -c64 -r120 -l1200 -i 60 -f tpcc_perf.log >> tpcc_caseX_perf.log 2>&1再测试tpcc稳定性数据，1000warehouse预热20分钟，运行72小时（259200秒）长度
./tpcc_start -uroot -h127.1 -P3306 -p123456 -d tpcc -w 1000 -c64 -r1200 -l259200 -i 1800 -f tpcc.log >> tpcc_caseX.log 2>&1参数含义
-h server_host: 服务器名
-P port : 端口号，默认为3306
-d database_name: 数据库名
-u mysql_user : 用户名
-p mysql_password : 密码
-w warehouses: 仓库的数量
-c connections : 线程数，默认为1
-r warmup_time : 热身时间，单位:s，默认为10s ,热身是为了将数据加载到内存。
-l running_time: 测试时间，单位:s，默认为20s
-i report_interval: 指定生成报告间隔时长
-f report_file: 测试结果输出文件
tpcc完整测试脚本
/data/test_tpcc/tpcc-mysql-master/tpcc_alltest.sh > /data/test_tpcc/tpcc-mysql-master/tpcc_all.log &
echo "============准备进行TPCC测试======================"
mysql -uroot -h127.1 -P3310 -p123456 -e"shutdown"
sleep 60;
cd /data/tpcc
rm -rf /data/tpcc/data
cp -r tpcc_data_20warehouses data
/data/oppo-advanced-MySQL-5.7.36-39-linux-x86_64_20200430/bin/mysqld --defaults-file=/data/my.cnf --basedir=/data/oppo-advanced-MySQL-5.7.36-39-linux-x86_64_20200430 --user=root &
sleep 20;
echo "============TPCC性能测试======================"
cd /data/test_tpcc/tpcc-mysql-master
mysql -uroot -h127.1 -P3310 -p123456 -e"set global max_prepared_stmt_count=200000"
./tpcc_start -uroot -h127.1 -P3310 -p123456 -d tpcc -w 20 -c64 -r120 -l1200 -i 60 -f tpcc_perf.log >> tpcc_caseX_perf.log 2>&1
echo "============TPCC性能测试完成======================"
echo "============准备进行TPCC稳定性测试，压测时间3天======================"
mysql -uroot -h127.1 -P3310 -p123456 -e"shutdown"
sleep 60;
cd /data/tpcc
rm -rf /data/tpcc/data
cp -r tpcc_data_1000warehouses data
/data/oppo-advanced-MySQL-5.7.36-39-linux-x86_64_20200430/bin/mysqld --defaults-file=/data/my.cnf --basedir=/data/oppo-advanced-MySQL-5.7.36-39-linux-x86_64_20200430 --user=root &
sleep 60;
echo "============TPCC稳定性测试开始======================"
echo "======开始时间：" && date
cd /data/test_tpcc/tpcc-mysql-master
mysql -uroot -h127.1 -P3310 -p123456 -e"set global max_prepared_stmt_count=200000"
./tpcc_start -uroot -h127.1 -P3310 -p123456 -d tpcc -w 1000 -c64 -r1200 -l259200 -i 1800 -f tpcc.log >> tpcc_caseX.log 2>&1
echo "============TPCC稳定性测试完成======================"
echo "======结束时间：" && date
echo "============全部测试完成======================"一致性校验SQL，所有语句都必须为空，主要是从机校验，当前未开主从：
select * from
(
select w.w_ytd as w_ytd , sum(d.d_ytd) as d_ytd, w.w_id from warehouse as w, district as d
where w.w_id = d.d_w_id group by w_id
) as chk
where chk.w_ytd != chk.d_ytd;
select d.D_W_ID, d.D_ID, d.D_NEXT_O_ID, o.max_o_id, no.max_no_o_id from
(select D_W_ID,D_ID,D_NEXT_O_ID from district) as d,
(select O_W_ID,O_D_ID,max(O_ID) as max_o_id from orders group by O_W_ID,O_D_ID) as o,
(select NO_W_ID,NO_D_ID,max(NO_O_ID) as max_no_o_id from new_orders group by NO_W_ID,NO_D_ID) as no
where
d.d_w_id=o.o_w_id and d.d_w_id=no.no_w_id
and d.d_id=o.o_d_id and d.d_id=no.no_d_id
and !(((d.D_NEXT_O_ID - 1) = o.max_o_id) and ((d.D_NEXT_O_ID - 1) = no.max_no_o_id));
select max_no_o_id, min_no_o_id, row_count from
(
select max(no_o_id) as max_no_o_id, min(no_o_id) as min_no_o_id, count(1) as row_count from new_orders as no group by no_w_id, no_d_id
) as chk
where row_count != (max_no_o_id - min_no_o_id + 1);
select o_w_id,o_d_id, ol_count, row_count from
(select o_w_id, o_d_id,sum(O_OL_CNT) as ol_count from orders /*where o_w_id = 1 and o_d_id = 1*/ group by o_w_id, o_d_id ) as o,
(select ol_w_id, ol_d_id, count(1) as row_count from order_line /*where ol_w_id = 1 and ol_d_id = 1*/ group by ol_w_id, ol_d_id) as ol
where o.o_w_id = ol.ol_w_id and o.o_d_id = ol.ol_d_id
and ol_count != row_count;
select o_w_id,o_d_id,o_id,O_CARRIER_ID,no_w_id from
(select o_w_id,o_d_id,o_id,O_CARRIER_ID from orders where O_CARRIER_ID is null) as ot1 left join new_orders as no
on ot1.o_w_id=no.no_w_id and ot1.o_d_id=no.no_d_id and ot1.o_id=no.no_o_id
where no_w_id is null limit 1;
select O_W_ID, O_D_ID, O_ID, O_OL_CNT, row_count from
(select O_W_ID, O_D_ID, O_ID, O_OL_CNT from orders) as o,
(select OL_W_ID, OL_D_ID, OL_O_ID, count(1) as row_count from order_line as ol group by OL_W_ID, OL_D_ID, OL_O_ID) ol_cnt
where
o.O_W_ID = ol_cnt.OL_W_ID and o.O_D_ID=ol_cnt.OL_D_ID and o.O_ID=ol_cnt.OL_O_ID
and O_OL_CNT != row_count;
select O_W_ID, O_D_ID, O_ID, O_CARRIER_ID, OL_DELIVERY_D
from orders as o, order_line as ol
where o.O_W_ID=ol.OL_W_ID and o.O_D_ID=ol.OL_D_ID and o.O_ID=ol.OL_O_ID /*(O_W_ID, O_D_ID, O_ID) = (OL_W_ID, OL_D_ID, OL_O_ID)*/
and OL_DELIVERY_D is null and O_CARRIER_ID is not null/*OL_DELIVERY_D is set to a null, O_CARRIER_ID set to a null value*/;
select W_ID, W_YTD, h_amount from
(select W_ID, W_YTD from warehouse) as w,
(select H_W_ID, sum(H_AMOUNT) as h_amount from history group by h_w_id) as h
where
W_ID = H_W_ID
and W_YTD! =h_amount;
select D_W_ID, D_ID, D_YTD, H_AMOUNT from
(select H_W_ID, H_D_ID, sum(H_AMOUNT) as H_AMOUNT from history group by H_W_ID, H_D_ID) as h,
district as d
where
D_W_ID = H_W_ID and D_ID = H_D_ID
and D_YTD != H_AMOUNT;
167
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
