# 1. 安装Sysbench
# Ubuntu/Debian
apt-get update
apt-get install sysbench

# CentOS/RHEL
yum install sysbench

# 2. 安装TiDB Bench
# 克隆TiDB Bench仓库
git clone https://github.com/pingcap/tidb-bench.git
cd tidb-bench

# 安装依赖
pip install -r requirements.txt

# 3. 安装TPCC
# 克隆TPCC仓库
git clone https://github.com/pingcap/tpcc.git
cd tpcc

# 编译
go build -o tidb-tpcc .

# 4. 配置测试工具
# Sysbench配置
# 创建测试脚本
cat > sysbench_test.sh << 'EOF' #!/bin/bash # sysbench_test.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn sysbench --db-driver=mysql --mysql-host=192.168.1.100 --mysql-port=4000 --mysql-user=root --mysql-password=password --mysql-db=fgedudb --table_size=100000 --tables=10 --threads=16 --time=300 --report-interval=10 oltp_read_write prepare sysbench --db-driver=mysql --mysql-host=192.168.1.100 --mysql-port=4000 --mysql-user=root --mysql-password=password --mysql-db=fgedudb --table_size=100000 --tables=10 --threads=16 --time=300 --report-interval=10 oltp_read_write run sysbench --db-driver=mysql --mysql-host=192.168.1.100 --mysql-port=4000 --mysql-user=root --mysql-password=password --mysql-db=fgedudb --table_size=100000 --tables=10 oltp_read_write cleanup EOF chmod +x sysbench_test.sh

# 1. 读密集型测试场景
# Sysbench读密集型测试
sysbench –db-driver=mysql –mysql-host=192.168.1.100 –mysql-port=4000 –mysql-user=root –mysql-password=password –mysql-db=fgedudb –table_size=100000 –tables=10 –threads=32 –time=600 –report-interval=10 oltp_read_only run

# 2. 写密集型测试场景
# Sysbench写密集型测试
sysbench –db-driver=mysql –mysql-host=192.168.1.100 –mysql-port=4000 –mysql-user=root –mysql-password=password –mysql-db=fgedudb –table_size=100000 –tables=10 –threads=32 –time=600 –report-interval=10 oltp_write_only run

# 3. 混合读写测试场景
# Sysbench混合读写测试
sysbench –db-driver=mysql –mysql-host=192.168.1.100 –mysql-port=4000 –mysql-user=root –mysql-password=password –mysql-db=fgedudb –table_size=100000 –tables=10 –threads=32 –time=600 –report-interval=10 oltp_read_write run

# 4. 事务测试场景
# TPCC测试
./tidb-tpcc –host=192.168.1.100 –port=4000 –user=root –password=password –db=fgedudb prepare –warehouses=10

./tidb-tpcc –host=192.168.1.100 –port=4000 –user=root –password=password –db=fgedudb run –warehouses=10 –threads=32 –time=600

./tidb-tpcc –host=192.168.1.100 –port=4000 –user=root –password=password –db=fgedudb cleanup –warehouses=10

# 1. 执行测试
# 运行Sysbench测试
./sysbench_test.sh

# 2. 监控系统状态
# 监控TiDB状态风哥提示：
mysql -h 192.168.1.100 -P 4000 -u root -ppassword -e “SHOW GLOBAL STATUS;”

# 监控TiKV状态
curl http://192.168.1.101:20160/metrics | grep tikv_server_thread_pool

# 监控PD状态
curl http://192.168.1.100:2379/metrics | grep pd_server

# 3. 监控系统资源
# 监控CPU和内存使用
top

# 监控磁盘IO
iostat -x 1

# 监控网络流量
tcpdump -i eth0 -n port 4000

# 4. 收集测试结果
# 保存测试结果到文件
sysbench –db-driver=mysql –mysql-host=192.168.1.100 –mysql-port=4000 –mysql-user=root –mysql-password=password –mysql-db=fgedudb –table_size=100000 –tables=10 –threads=32 –time=600 –report-interval=10 oltp_read_write run > test_results.txt

# 5. 分析测试结果
# 查看QPS和延迟
cat test_results.txt | grep “queries”
cat test_results.txt | grep “latency”

# 1. 问题描述：需要评估TiDB在高并发读写场景下的性能

# 2. 解决方案：
# 1. 准备测试环境
# 搭建3节点TiDB集群
# 192.168.1.100: TiDB + PD
# 192.168.1.101: TiKV
# 192.168.1.102: TiKV
# 192.168.1.103: TiKV

# 2. 安装Sysbench
apt-get install sysbench

# 3. 准备测试数据
sysbench –db-driver=mysql –mysql-host=192.168.1.100 –mysql-port=4000 –mysql-user=root –mysql-password=password –mysql-db=fgedudb –table_size=1000000 –tables=10 –threads=16 oltp_read_write prepare

# 4. 执行测试
# 测试不同并发数下的性能
for threads in 16 32 64 128; do
echo “Testing with $threads threads…”
sysbench –db-driver=mysql –mysql-host=192.168.1.100 –mysql-port=4000 –mysql-user=root –mysql-password=password –mysql-db=fgedudb –table_size=1000000 –tables=10 –threads=$threads –time=300 –report-interval=10 oltp_read_write run > test_${threads}_threads.txt
echo “Test completed. Results saved to test_${threads}_threads.txt”
done

# 5. 分析测试结果
# 查看QPS和延迟
for threads in 16 32 64 128; do
echo “Results for $threads threads:”
cat test_${threads}_threads.txt | grep “queries per second”
cat test_${threads}_threads.txt | grep “avg:” | head -1
done

# 6. 效果：通过测试了解TiDB在不同并发下的性能表现，为生产环境配置提供参考

# 1. 问题描述：需要全面评估TiDB的性能

# 2. 解决方案：
# 1. 克隆TiDB Bench仓库
git clone https://github.com/pingcap/tidb-bench.git
cd tidb-bench

# 2. 安装依赖
pip install -r requirements.txt

# 3. 配置测试
# 修改config.yaml
cat > config.yaml << 'EOF' # Global variables mysql_host: 192.168.1.100 mysql_port: 4000 mysql_user: root mysql_password: password mysql_db: fgedudb # Test cases testcases: - name: oltp_read_write type: sysbench params: table_size: 1000000 tables: 10 threads: [16, 32, 64] time: 300学习交流加群风哥QQ113257174 report_interval: 10 - name: oltp_read_only type: sysbench params: table_size: 1000000 tables: 10 threads: [16, 32, 64] time: 300 report_interval: 10 EOF # 4. 执行测试 python run.py --config config.yaml # 5. 查看测试结果 ls -la results/ # 6. 效果：通过TiDB Bench执行多种测试场景，全面评估TiDB的性能

# 1. 问题描述：需要测试特定业务场景的性能

# 2. 解决方案：
# 1. 编写测试脚本
cat > custom_benchmark.py << 'EOF' #!/usr/bin/env python3 # custom_benchmark.py # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn import time import threading import pymysql from concurrent.futures import ThreadPoolExecutor # 数据库配置 DB_CONFIG = { 'host': '192.168.1.100', 'port': 4000, 'user': 'root', 'password': 'password', 'database': 'fgedudb', 'charset': 'utf8mb4' } # 测试SQL TEST_SQLS = [ "SELECT * FROM fgedudb.fgedu_users WHERE id = %s", "INSERT INTO fgedudb.fgedu_logs (user_id, action, created_at) VALUES (%s, %s, NOW())", "UPDATE fgedudb.fgedu_users SET last_login = NOW() WHERE id = %s", "DELETE FROM fgedudb.fgedu_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)" ] # 测试结果 results = [] # 测试函数 def test_query(thread_id): start_time = time.time() try: conn = pymysql.connect(**DB_CONFIG) cursor = conn.cursor() for i in range(100): # 随机执行不同的SQL sql_index = i % len(TEST_SQLS) sql = TEST_SQLS[sql_index] if sql_index == 0: # SELECT cursor.execute(sql, (i % 1000 + 1,)) cursor.fetchall() elif sql_index == 1: # INSERT cursor.execute(sql, (i % 1000 + 1, f'Action {i}')) conn.commit() elif sql_index == 2: # UPDATE cursor.execute(sql, (i % 1000 + 1,)) conn.commit() elif sql_index == 3: # DELETE cursor.execute(sql) conn.commit() cursor.close() conn.close() elapsed_time = time.time() - start_time results.append(elapsed_time) print(f"Thread {thread_id} completed in {elapsed_time:.2f} seconds") except Exception as e: print(f"Thread {thread_id} failed: {e}") # 主函数 def main(): # 并发线程数 threads = 32 print(f"Starting benchmark with {threads} threads...") start_time = time.time() # 使用线程池执行测试 with ThreadPoolExecutor(max_workers=threads) as executor: executor.map(test_query, range(threads)) total_time = time.time() - start_time avg_time = sum(results) / len(results) qps = (threads * 100) / total_time print(f"\nBenchmark completed in {total_time:.2f} seconds") print(f"Average thread time: {avg_time:.2f} seconds") print(f"QPS: {qps:.2f}") if __name__ == "__main__": main() EOF # 2. 执行测试 python custom_benchmark.py # 3. 效果：通过自定义测试脚本，模拟特定业务场景的压力，评估TiDB在该场景下的性能

更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 压力测试最佳实践

• 设计合理的测试场景：模拟实际业务场景
• 使用真实的测试数据：确保测试结果的准确性
• 逐步增加负载：从低负载开始，逐步增加到目标负载
• 监控系统状态：实时监控系统的各项指标
• 分析测试结果：详细分析测试数据，找出性能瓶颈
• 对比不同配置：测试不同配置下的性能表现
• 定期进行测试：定期测试系统性能，发现性能变化

5.2 常见问题与解决方案

• 测试环境与生产环境差异：确保测试环境与生产环境相似
• 测试数据不足：生成足够的测试数据，模拟真实场景
• 测试工具配置不当：正确配置测试工具参数
• 系统资源限制：确保测试环境有足够的资源
• 测试结果不稳定：多次测试取平均值，减少误差

5.3 未来发展趋势

• 智能化测试：利用AI技术自动生成测试场景和分析测试结果
• 云原生测试：与云平台深度集成的测试工具
• 实时测试：实时监控和测试系统性能
• 分布式测试：使用分布式测试工具提高测试效率
• 容器化测试：在容器环境中进行测试，提高测试的可重复性

from tidb视频:www.itpux.com