## 模块 2：使用 `sql-replay` 捕获并重放工作负载 ### 步骤 1. 运行 `sysbench` 并从 MySQL 生成慢查询 + 点击这里 - [终端 2](ssh://monitor1-ssh#2) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。 + 切换工作目录。 ```bash cd ~/sql-replay ``` + 设置 `sysbench` 配置。 ```bash cat << EOF > sysbench_config mysql-host=${HOST_MYSQL1_PRIVATE_IP} mysql-port=3306 mysql-user=test mysql-password=q1w2e3R4_ mysql-db=sbtest time=5 threads=2 report-interval=1 EOF cat sysbench_config ``` ```output mysql-host=10.90.1.186 mysql-port=3306 mysql-user=test mysql-password=q1w2e3R4_ mysql-db=sbtest time=5 threads=2 report-interval=1 ``` + 运行 `sysbench` 测试。 ```bash sysbench \ --config-file=sysbench_config oltp_point_select \ --tables=6 \ --table-size=10000 prepare sysbench \ --config-file=sysbench_config oltp_read_only \ --tables=6 \ --table-size=10000 \ --db-ps-mode=auto \ --rand-type=uniform run ``` ```output sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) Creating table 'sbtest1'... Inserting 10000 records into 'sbtest1' Creating a secondary index on 'sbtest1'... Creating table 'sbtest2'... Inserting 10000 records into 'sbtest2' Creating a secondary index on 'sbtest2'... Creating table 'sbtest3'... Inserting 10000 records into 'sbtest3' Creating a secondary index on 'sbtest3'... Creating table 'sbtest4'... Inserting 10000 records into 'sbtest4' Creating a secondary index on 'sbtest4'... Creating table 'sbtest5'... Inserting 10000 records into 'sbtest5' Creating a secondary index on 'sbtest5'... Creating table 'sbtest6'... Inserting 10000 records into 'sbtest6' Creating a secondary index on 'sbtest6'... ``` ```output sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 1 Report intermediate results every 1 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 1s ] thds: 2 tps: 567.08 qps: 9080.34 (r/w/o: 7944.17/0.00/1136.17) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00 [ 2s ] thds: 2 tps: 572.13 qps: 9159.12 (r/w/o: 8015.85/0.00/1143.26) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00 [ 3s ] thds: 2 tps: 549.00 qps: 8786.00 (r/w/o: 7687.00/0.00/1099.00) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00 [ 4s ] thds: 2 tps: 565.00 qps: 9042.03 (r/w/o: 7912.03/0.00/1130.00) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00 [ 5s ] thds: 2 tps: 564.00 qps: 9025.95 (r/w/o: 7897.96/0.00/1127.99) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 39480 write: 0 other: 5640 total: 45120 transactions: 2820 (563.47 per sec.) queries: 45120 (9015.46 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 5.0034s total number of events: 2820 Latency (ms): min: 3.12 avg: 3.55 max: 48.57 95th percentile: 3.82 sum: 9998.20 Threads fairness: events (avg/stddev): 1410.0000/11.00 execution time (avg/stddev): 4.9991/0.00 ``` ### 步骤 2. 解析慢查询日志 + 将 MySQL 慢查询日志文件复制到 monitor1_address。 ```bash scp -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem rocky@${HOST_MYSQL1_PRIVATE_IP}:/var/lib/mysql/local-slow.log ~/sql-replay cd ~/sql-replay sql-replay -mode parse -slow-in local-slow.log -slow-out sysbench-traffic.json ``` ```output local-slow.log 100% 19MB 241.7MB/s 00:00 ``` ### 步骤 3. 针对目标 TiDB 重放 SQL 工作负载 ```bash mkdir ~/sql-replay/replay_out sql-replay \ -mode replay \ -db "root@tcp(${HOST_DB1_PRIVATE_IP}:4000)/sbtest" \ -speed 100 \ -slow-out sysbench-traffic.json \ -replay-out ./replay_out/sysbench \ -username all \ -sqltype all \ -dbname all ``` ```output Filter Rule: Source user - all, Source database - all, Source SQL type - all, Replay speed: 100.000000 [2024-09-03 01:36:19.440] Parameters read successfully, starting data parsing [2024-09-03 01:36:19.693] Data parsing completed, Data parsing time: 252.338636ms, Starting SQL replay [2024-09-03 01:36:57.467] SQL replay completed, SQL replay time: 37.774048651s ``` + 加载重放结果。 ```bash sql-replay \ -mode load \ -db "root@tcp(${HOST_DB1_PRIVATE_IP}:4000)/test" \ -out-dir ./replay_out \ -replay-name sysbench \ -table replay_info ``` ```output load batchsize: 1000, load workers: 4 [2024-09-03 01:37:43.077] Begin create table - REPLAY_INFO [2024-09-03 01:37:43.397] Completed processing file: sysbench.9 [2024-09-03 01:37:43.515] Completed processing file: sysbench.11 [2024-09-03 01:37:43.519] Completed processing file: sysbench.10 [2024-09-03 01:37:44.838] Completed processing file: sysbench.12 [2024-09-03 01:37:44.923] Completed processing file: sysbench.13 ``` + 生成重放报告。 ```bash sql-replay \ -mode report \ -db "root@tcp(${HOST_DB1_PRIVATE_IP}:4000)/test" \ -replay-name sysbench ``` ```output [2024-09-03 01:38:18.078] Begin execute query [2024-09-03 01:38:19.132] Server is running on port :8081 ``` + 访问报告： [http://monitor1_address:8081/](http://monitor1_address:8081/)。以下是一个报告示例： ![80%](https://lab-static.pingcap.com/quick-demo/demo-302-2-en.png) + 查看报告后，在命令行中按 Ctrl+C 终止报告线程。