shell脚本：数据库压测全流程1-生成随机数据

qq_22799793

已于 2023-04-21 18:05:02 修改

阅读量1.5k

点赞数

分类专栏： mysql压测文章标签：数据库 mysql 服务器

于 2023-04-21 17:21:19 首次发布

本文链接：https://blog.csdn.net/qq_22799793/article/details/130292724

版权

mysql压测专栏收录该内容

1 篇文章 0 订阅

订阅专栏

数据库压测的意义

- 确定数据库的性能瓶颈，找出优化的方向和重点。

- 验证数据库的可靠性和稳定性，避免出现数据丢失、损坏等问题。

- 模拟实际业务场景，评估数据库的处理能力和承载能力。

- 为数据库的容量规划提供数据支持，避免过度或不足的资源配置。

- 提高数据库管理员和开发人员的技能水平，增强团队的协作和沟通能力。

压测方案的必要性

- 在产品上线前进行数据库压测，可以在生产环境中模拟高并发、大数据量等情况，发现并解决潜在的性能问题，提高产品的稳定性和可靠性。

- 通过压测可以了解数据库的极限承受能力，为后续的业务发展和数据库架构优化提供数据支持。

- 压测可以发现数据库在高并发、大数据量等情况下的瓶颈，为优化数据库性能提供方向和参考。

## 2. 压测准备

数据库环境准备

- 确认数据库版本和类型，例如 mysql 5.7

- 根据业务需求创建相应的数据库和表结构

- 准备测试数据，可以使用数据生成工具或者手动**数据

- 确认数据库连接方式和参数，例如连接池大小、最大连接数等

- 确认数据库参数设置，例如缓存大小、索引等

- 确认数据库的硬件配置，例如 cpu、内存、磁盘等

- 确认数据库的网络配置，例如网络带宽、延迟等

- 确认数据库的安全配置，例如用户权限、访问控制等

压测方案

压测场景设计


场景名称	场景描述	压测目的
登录场景	模拟用户登录操作	测试系统登录并发量和响应时间
注册场景	模拟用户注册操作	测试系统注册并发量和响应时间
下单场景	模拟用户下单操作	测试系统下单并发量和响应时间
支付场景	模拟用户支付操作	测试系统支付并发量和响应时间

压测流程规划

压测指标的设定- 并发数：100

事务数：10000
响应时间：小于1秒
CPU使用率：小于80%
内存使用率：小于80%
磁盘读写速率：小于100MB/s
网络带宽：小于1Gbps

表格语法实例：

压测指标	设定值
并发数	100
事务数	10000
响应时间	小于1秒
CPU使用率	小于80%
内存使用率	小于80%
磁盘读写速率	小于100MB/s
网络带宽	小于1Gbps

压测过程

1.部署需要压测的数据库(单点,主从,PXC,MHA,MGR)

2.安装压测工具sysbench

3.通过sysbench写入大量随机数据

4.利用脚本模拟应用写入大量测试数据

5.测试不同应用场景(只读，只写，混合)

4. 压测执行

压测前的准备工作

确定压测的目标和指标
准备测试数据和测试脚本
确认测试环境已经搭建完成
关闭不必要的系统服务，释放资源
清理数据库，保证测试环境的干净
确保测试人员已经熟悉测试流程和工具
确认测试人员已经掌握故障排查和处理的方法
确认测试人员已经掌握测试数据的生成和处理方法
确认测试人员已经掌握测试报告的生成和分析方法

压测执行过程中的监控与记录

监控数据库的连接数和响应时间，可以使用工具如MySQL自带的performance_schema和sys库。
使用工具如pt-stalk定期收集数据库的状态信息，以便后续分析。
记录压测过程中的各项指标，如QPS、TPS、响应时间、错误率等，可以使用工具如Grafana和Prometheus进行可视化展示。
在压测结束后，进行数据分析和优化，结合上述监控和记录的数据，找出性能瓶颈并进行优化。
对于异常情况，及时进行处理和记录，以便后续分析。可以使用工具如ELK进行日志分析和检索。

压测后的数据分析与总结- 压测后的数据分析与总结：

通过分析压测结果，发现数据库的QPS在高峰期达到了5000，超过了我们的预期目标。
但同时也发现了一些潜在的问题，例如在并发量达到一定程度时，部分查询语句的响应时间明显增加，需要进一步优化。
经过总结，我们制定了一些优化方案，并计划在下一次压测中验证效果。同时，我们也发现了一些性能较好的查询语句，可以在后续的开发中进行推广和应用。

5. 压测报告

压测报告的撰写

压测报告的撰写：
- 采用表格的形式呈现数据，包括但不限于以下内容：
  
  指标压测前压测中压测后
  QPS 100 200 180
  响应时间 50ms 80ms 60ms
  错误率 0% 1% 0.5%
- 对数据进行分析和总结，提出问题和建议，包括但不限于以下内容：
  - 压测结果是否符合预期；
  - 压测中出现的问题及其原因；
  - 对系统的改进建议。

指标	压测前	压测中	压测后
QPS	100	200	180
响应时间	50ms	80ms	60ms
错误率	0%	1%	0.5%

报告中应包含的内容

压测环境信息表格：

环境	配置
操作系统	CentOS 7.4
CPU	Intel® Xeon® CPU E5-2680 v4 @ 2.40GHz
内存	32GB
数据库	MySQL 5.7.21
网络	1Gb/s

压测结果表格：

压测项	压测结果
吞吐量	1000/s
响应时间	50ms
错误率	0%

压测分析报告：

根据以上压测结果分析，系统在当前配置下能够承受1000/s的吞吐量，响应时间在可接受范围内，错误率为0%，系统运行稳定。建议在增加负载前，对系统进行进一步优化。

报告的呈现方式- 报告的呈现方式：

表格：可以使用表格的形式呈现压测结果，方便查看各项指标的数值和趋势。

时间	QPS	响应时间	错误率
10:00	1000	50ms	0.2%
10:01	980	55ms	0.3%
10:02	1020	45ms	0.1%

图表：可以使用折线图、柱状图等形式呈现压测结果，直观展示各项指标的变化趋势。

文字说明：可以在报告中加入文字说明，对压测结果进行解读和分析，方便理解各项指标的含义和影响因素。

根据压测结果可以看出，系统在高并发下QPS有所下降，响应时间和错误率有所上升，需要进一步优化系统性能和稳定性。

6. 压测方案的优化

压测过程中发现的问题：
1. 数据库连接数过高，导致数据库崩溃
2. 网络带宽不足，导致响应时间过长
3. SQL语句执行效率低下，需要优化
4. 数据库缓存命中率低，需要增加缓存
5. 硬件资源不足，需要增加服务器数量或升级硬件配置
6. 数据库锁竞争激烈，需要优化事务处理方式
7. 数据库索引使用不当，需要重新设计索引策略
8. 数据库表结构设计不合理，需要重新设计表结构
9. 数据库备份恢复策略不完善，需要优化备份恢复方案。

优化方案的制定

通过增加数据库的缓存大小，减少读写磁盘的次数，提高响应速度。
通过分库分表的方式，减少单个数据库的负载，提高并发处理能力。
通过优化SQL语句，减少不必要的查询和数据传输，提高数据库性能。
通过使用索引，加快数据的检索速度。
通过使用数据库连接池，减少数据库连接的创建和销毁，提高数据库的并发处理能力。
通过使用负载均衡技术，将压力分散到多个数据库节点上，提高整个系统的性能和稳定性。

表格示例：

优化方案	具体措施
增加缓存	使用Redis作为缓存数据库，设置缓存时间和缓存大小
分库分表	使用分布式数据库，将数据分散到多个节点上
优化SQL语句	使用索引，避免全表扫描
连接池	使用连接池管理数据库连接
负载均衡	使用负载均衡技术将请求分散到多个数据库节点上

优化效果的评估- 通过增大并发数，观察响应时间是否有所下降，以及下降的幅度。

通过增加机器数量，观察响应时间是否有所下降，以及下降的幅度。
通过优化SQL语句，观察响应时间是否有所下降，以及下降的幅度。
通过使用缓存机制，观察响应时间是否有所下降，以及下降的幅度。
通过使用负载均衡，观察响应时间是否有所下降，以及下降的幅度。
通过使用分库分表，观察响应时间是否有所下降，以及下降的幅度。

7. 结论与建议

压测结果的总结

压测结果的总结：

根据压测数据的分析，我们可以得出以下结论和建议：

结论/建议	说明
数据库的QPS	经过压测，数据库的QPS能够达到每秒1000次，满足了业务需求。
响应时间	数据库的响应时间在95%的情况下能够控制在1秒以内，但在高峰期会出现短暂的延迟。建议在高峰期增加服务器资源。
并发数	在当前的服务器资源下，数据库的并发数能够达到600，但在高峰期可能会出现瓶颈。建议增加服务器资源。
索引优化	经过压测，发现有些查询语句的执行效率较低，建议对相应的字段增加索引优化。

对数据库性能优化的建议

建议1：优化SQL语句，尽量减少全表扫描和索引扫描。
建议2：合理设计数据库表结构，避免冗余字段和多余的表连接操作。
建议3：使用缓存技术，减少数据库的访问次数。
建议4：定期清理数据库中的无用数据和过期数据，减少数据库的负担。
建议5：使用数据库连接池技术，避免频繁的连接和断开数据库。
建议6：使用分库分表技术，将数据分散到多个数据库中，提高数据库的并发处理能力。
建议7：使用数据库读写分离技术，将读和写操作分离到不同的数据库服务器上，提高数据库的性能和稳定性。
建议8：对于高并发场景，可以考虑使用缓存中间件，如Redis等，减轻数据库的压力。

附录：数据生成脚本（可根据现有业务生成符合表结构的测试数据）

执行chmod +x save_data.sh

执行sh save_data.sh按要求输入对应信息即可生成测试数据sql文件，默认地址在/data目录下

脚本内容如下，已测试

#!/bin/bash

# 定义随机字符串函数
function random_string() {
    cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w ${1:-16} | head -n 1
}

# 定义随机浮点数函数
function random_float() {
    awk -v min=${1:-1} -v max=${2:-10} 'BEGIN{srand(); printf "%.2f", min+rand()*(max-min)}'
}

# 定义随机整数函数
function random_int() {
    awk -v min=${1:-1} -v max=${2:-100} 'BEGIN{srand(); printf "%d", int(min+rand()*(max-min+1))}'
}

# 用户输入表名和插入记录数
read -p "请输入要插入的表名（多个表用逗号分隔）：" tables
read -p "请输入插入记录数：" rows

# 链接mysql
read -p "请输入库名：" db
read -p "请输入mysql用户名：" mysql_user
read -s -p "请输入mysql密码：" mysql_password
echo ""
read -p "请输入mysql主机：" mysql_host
read -p "请输入mysql端口号：" mysql_port
current_time=$(date +%Y%m%d)
sql_filename="/data/${db}_${tables}_${rows}_${current_time}.sql"
# 遍历多个表
for table in $(echo ${tables} | sed "s/,/ /g")
do

    # 根据表名查询表结构
    columns=""
    column_info=$(mysql -u${mysql_user} -p${mysql_password} -h${mysql_host} -P${mysql_port} $db -e "desc ${table}" 2>/dev/null)

    if [ $? -ne 0 ]
    then
        # 查询失败，退出
        echo "查询表 ${table} 结构失败"
        exit 1
    fi

    column_list=$(echo "${column_info}" | grep -vE "(Field|----)" | awk '{print $1","$2}'| sed -n '2,$p')
    #pri_column=$(echo "${column_info}"  | grep -vE "(Field|----)" | awk '{print $3}')
    columns="${column_list}"
    # 生成插入数据的语句
    insert_sql="insert into ${table} ("

    # 将字段列表以逗号分隔，并且判断最后一个字段是否需要添加逗号，若最后一个字段不需要逗号，则需要删除最后一个逗号
    insert_sql+=$(echo "${columns}" | awk -v ORS= -F ',' '{if(NR==1){printf("%s,",$1)}else if(NR==NF){printf("%s,",$1)}else{printf("%s",$1)}}')
 echo $insert_sql
    insert_sql+=") values "

    # 遍历插入数据
    for i in $(seq 1 ${rows})
    do
        row_values=""

        # 根据表结构生成随机数据
        #for column in $(echo ${columns} | sed "s/,/ /g")
        for column in ${columns}
        do
            column_name=$(echo $column | awk -F ',' '{print $1}')
            column_type=$(echo $column | awk -F ',' '{print $2}' |awk -F " "  '{print $1}')

            # 根据字段类型生成随机数据
            case $column_type in
                *char*|*text*)
                    row_values+=",'$(random_string 16)'" ;;
                *float*|*double*|*decimal*)
                    row_values+=",$(random_float)" ;;
                *int*|*bigint*|*tinyint*|*smallint*|*mediumint*)
                    row_values+=",$(random_int)" ;;
                *)
                    echo "未知的字段类型: ${column_type}" ;;
            esac
        done
        # 去掉开头多余的逗号
        row_values=$(echo ${row_values} | sed "s/^,//")

        # 每1000条记录执行一次插入
        if [ $((i%1000)) -eq 0 ]
        then
            insert_sql+="(${row_values}); "

            echo ${insert_sql}

            # 执行插入
            mysql -u${mysql_user} -p${mysql_password} -h${mysql_host} -P${mysql_port} $db -e "${insert_sql}" 2>/dev/null

            if [ $? -ne 0 ]
            then
                # 插入失败，退出
                echo "插入数据失败"
                exit 1
            fi

            # 重新生成插入数据的sql语句
            insert_sql="insert into ${table} ("
            insert_sql+=$(echo "${columns}" | awk -v ORS= -F ',' '{if(NR==1){printf("%s,",$1)}else if(NR==NF){printf("%s,",$1)}else{printf("%s",$1)}}')
            insert_sql+=") values "
        else
            insert_sql+="(${row_values}),"
        fi
    done
echo "use $db;" >>$sql_filename
echo $insert_sql >> $sql_filename
sed -i "/^insert/s/,$/;/" $sql_filename