## 模块 3：使用 `parse-tshark` 捕获工作负载并通过 `sql-replay` 重放
### 步骤 1. 为 `parse-tshark` 捕获 SQL 工作负载准备 MySQL
+ 点击这里 - [终端 3](ssh://monitor1-ssh#3) 打开一个新的终端，关闭 MySQL server 的 `SSL` 功能和慢查询日志。
+ 连接到 MySQL server 节点。如果有提示，回答 `yes`。
```bash
ssh -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem rocky@${HOST_MYSQL1_PRIVATE_IP}
```
```output
Last login: Thu Aug 1 11:58:47 2024 from 10.90.4.90
[rocky@ip-10-90-1-235 ~]$
```
+ 关闭 `SSL` 功能，这样 `tshark` 就可以从网络数据包中解析 SQL 文本。
```bash
sudo su -
```
```bash
echo 'skip_ssl' >> /etc/my.cnf
cat /etc/my.cnf
```
```output
...
skip_ssl
```
```bash
sudo service mysqld restart
```
```output
Redirecting to /bin/systemctl restart mysqld.service
```
+ 以数据库用户 `root` 连接到 MySQL server。
```bash
mysql -h localhost -P 3306 -u root -pq1w2e3R4_ --connect-expired-password
```
```output
mysql>
```
+ 验证 SSL 功能已禁用。
```sql
SHOW VARIABLES LIKE '%ssl%';
```
```output
+-------------------------------------+----------+
| Variable_name | Value |
+-------------------------------------+----------+
...
| have_openssl | DISABLED |
| have_ssl | DISABLED |
...
+-------------------------------------+----------+
```
+ 设置与慢日志和 max_execution_time 相关的参数。
```sql
SET GLOBAL slow_query_log = off;
SET GLOBAL max_execution_time = 60000;
```
```sql
EXIT
```
+ 验证参数设置是否正确。
```bash
mysql -h localhost -P 3306 -u root -pq1w2e3R4_ --connect-expired-password
```
```bash
SHOW VARIABLES like 'slow_query_log';
SHOW VARIABLES like 'max_execution_time';
```
```output
mysql> SHOW VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | 0 |
+----------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'max_execution_time';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_execution_time | 0 |
+--------------------+-------+
1 row in set (0.00 sec)
```
```sql
EXIT
```
+ 返回 TiUP 控制机。
```bash
exit
```
```output
logout
Connection to 10.90.1.204 closed.
```
### 步骤 2. 准备 TPC - H 测试
+ 在 MySQL 上准备初始数据。
```bash
~/.tiup/bin/tiup bench tpch \
-H ${HOST_MYSQL1_PRIVATE_IP} \
-P 3306 \
-U test \
-p q1w2e3R4_ \
--sf=1 \
-T 30 prepare
```
```output
Starting component bench: /home/ec2-user/.tiup/components/bench/v1.12.0/tiup-bench tpch -H 10.90.1.125 -P 3306 -U test -p q1w2e3R4_ --sf=1 -T 30 prepare
creating nation
creating region
creating part
creating supplier
creating partsupp
creating customer
creating orders
creating lineitem
generating nation table
generate nation table done
generating region table
generate region table done
generating customers table
generate customers table done
generating suppliers table
generate suppliers table done
generating part/partsupplier tables
generate part/partsupplier tables done
generating orders/lineitem tables
generate orders/lineitem tables done
Finished
```
+ 检查数据量。
```bash
mysql -h ${HOST_MYSQL1_PRIVATE_IP} -P 3306 -u test -pq1w2e3R4_ --connect-expired-password
```
```output
mysql>
```
```sql
USE test;
SELECT COUNT(1) FROM nation;
SELECT COUNT(1) FROM region;
SELECT COUNT(1) FROM part;
SELECT COUNT(1) FROM supplier;
SELECT COUNT(1) FROM partsupp;
SELECT COUNT(1) FROM customer;
SELECT COUNT(1) FROM orders;
SELECT COUNT(1) FROM lineitem;
```
```output
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT COUNT(1) FROM nation;
+----------+
| count(1) |
+----------+
| 25 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(1) FROM region;
+----------+
| count(1) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(1) FROM part;
+----------+
| count(1) |
+----------+
| 200000 |
+----------+
1 row in set (0.05 sec)
mysql> SELECT COUNT(1) FROM supplier;
+----------+
| count(1) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(1) FROM partsupp;
+----------+
| count(1) |
+----------+
| 800000 |
+----------+
1 row in set (0.14 sec)
mysql> SELECT COUNT(1) FROM customer;
+----------+
| count(1) |
+----------+
| 150000 |
+----------+
1 row in set (0.08 sec)
mysql> SELECT COUNT(1) FROM orders;
+----------+
| count(1) |
+----------+
| 1500000 |
+----------+
1 row in set (0.25 sec)
mysql> SELECT COUNT(1) FROM lineitem;
+----------+
| count(1) |
+----------+
| 6001215 |
+----------+
1 row in set (1.24 sec)
```
+ 退出到操作系统命令行。
```sql
EXIT
```
```output
bye
```
+ 在 TiDB 上准备初始数据。
```bash
~/.tiup/bin/tiup bench tpch \
-H ${HOST_DB1_PRIVATE_IP} \
-P 4000 \
-U root \
--sf=1 \
-T 30 prepare
```
```output
Starting component bench: /home/ec2-user/.tiup/components/bench/v1.12.0/tiup-bench tpch -H 10.90.2.218 -P 4000 -U root --sf=1 -T 30 prepare
creating nation
creating region
creating part
creating supplier
creating partsupp
creating customer
creating orders
creating lineitem
generating nation table
generate nation table done
generating region table
generate region table done
generating customers table
generate customers table done
generating suppliers table
generate suppliers table done
generating part/partsupplier tables
generate part/partsupplier tables done
generating orders/lineitem tables
generate orders/lineitem tables done
Finished
```
+ 检查数据量。
```bash
mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root --connect-expired-password
```
```output
mysql>
```
```sql
USE test;
SELECT COUNT(1) FROM nation;
SELECT COUNT(1) FROM region;
SELECT COUNT(1) FROM part;
SELECT COUNT(1) FROM supplier;
SELECT COUNT(1) FROM partsupp;
SELECT COUNT(1) FROM customer;
SELECT COUNT(1) FROM orders;
SELECT COUNT(1) FROM lineitem;
```
```output
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT COUNT(1) FROM nation;
+----------+
| count(1) |
+----------+
| 25 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(1) FROM region;
+----------+
| count(1) |
+----------+
| 5 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT COUNT(1) FROM part;
+----------+
| count(1) |
+----------+
| 200000 |
+----------+
1 row in set (0.17 sec)
mysql> SELECT COUNT(1) FROM supplier;
+----------+
| count(1) |
+----------+
| 10000 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT COUNT(1) FROM partsupp;
+----------+
| count(1) |
+----------+
| 800000 |
+----------+
1 row in set (0.45 sec)
mysql> SELECT COUNT(1) FROM customer;
+----------+
| count(1) |
+----------+
| 150000 |
+----------+
1 row in set (0.15 sec)
mysql> SELECT COUNT(1) FROM orders;
+----------+
| count(1) |
+----------+
| 1500000 |
+----------+
1 row in set (0.25 sec)
mysql> SELECT COUNT(1) FROM lineitem;
+----------+
| count(1) |
+----------+
| 6001215 |
+----------+
1 row in set (1.28 sec)
```
+ 退出到监控操作系统命令行。
```sql
EXIT
```
```output
bye
```
### 步骤 3. 开始捕获网络数据包并转换为 `sql-replay` 格式
+ 连接到 MySQL server 节点。如果有提示，回答 `yes`。
```bash
ssh -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem rocky@${HOST_MYSQL1_PRIVATE_IP}
```
```output
Last login: Thu Aug 1 11:58:47 2024 from 10.90.4.90
[rocky@ip-10-90-1-235 ~]$
```
+ 开始捕获 MySQL 协议的网络数据包。此命令基于端口 3306 和网卡（`eth0`）捕获数据包，以捕获 1 小时为例（每个文件约 2000MB，最多生成 200 个文件）。
```bash
cd ~/parse-tshark
tshark -i eth0 -f "tcp port 3306" -a duration:3600 -b filesize:2000000 -b files:200 -w ./ts.pcap
```
```output
Capturing on 'eth0'
```
+ 点击这里 - [终端 4](ssh://monitor1-ssh#4) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。
+ 连接到 MySQL server 节点。
```bash
ssh -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem rocky@${HOST_MYSQL1_PRIVATE_IP}
```
```output
Last login: Thu Aug 1 11:58:47 2024 from 10.90.4.90
[rocky@ip-10-90-1-235 ~]$
```
+ 在数据包捕获期间获取用户和数据库信息。
```bash
cd ~/parse-tshark
./parse-tshark -mode getmysql -dbinfo "test:q1w2e3R4_@tcp(${HOST_MYSQL1_PRIVATE_IP}:3306)/information_schema" -output host.ini
```
### 步骤 4. 运行 TPC - H 测试
+ 点击这里 - [终端 5](ssh://monitor1-ssh#5) 打开一个新的终端，连接到监控机，并运行 TPC - H 测试。
```bash
~/.tiup/bin/tiup bench tpch \
-H ${HOST_MYSQL1_PRIVATE_IP} \
-P 3306 \
-U test \
-p q1w2e3R4_ \
--ignore-error \
--count=22 \
--sf=1 \
--check=true run
```
```output
Starting component bench: /home/ec2-user/.tiup/components/bench/v1.12.0/tiup-bench tpch -H 10.90.1.125 -P 3306 -U test -p q1w2e3R4_ --count=22 --ignore-error --sf=1 --check=true run
[Current] Q1: 26.81s
[Current] Q2: 0.64s
[Current] Q3: 9.70s
[Current] Q4: 1.98s
[Current] Q5: 4.60s
[Current] Q6: 5.60s
[Current] Q7: 16.27s
[Current] Q8: 12.99s
[2024-10-22 02:32:26] execute run failed, err check q9 failed expect 175 rows, got 0 rows
[Current] Q9: 60.03s
[Current] Q10: 4.80s
[Current] Q11: 3.72s
[Current] Q12: 7.82s
[Current] Q13: 4.26s
[Current] Q14: 6.68s
[Current] Q15: 13.05s
[Current] Q16: 0.91s
[2024-10-22 02:34:07] execute run failed, err check q17 failed expect 1 rows, got 0 rows
[Current] Q17: 60.03s
[Current] Q18: 7.01s
[Current] Q19: 12.45s
[2024-10-22 02:35:26] execute run failed, err check q20 failed expect 184 rows, got 0 rows
[Current] Q20: 60.03s
Finished
[Summary] Q1: 26.81s
[Summary] Q10: 4.80s
[Summary] Q11: 3.72s
[Summary] Q12: 7.82s
[Summary] Q13: 4.26s
[Summary] Q14: 6.68s
[Summary] Q15: 13.05s
[Summary] Q16: 0.91s
[Summary] Q17: 60.03s
[Summary] Q18: 7.01s
[Summary] Q19: 12.45s
[Summary] Q2: 0.64s
[Summary] Q20: 60.03s
[Summary] Q21: 14.13s
[Summary] Q22: 2.11s
[Summary] Q3: 9.70s
[Summary] Q4: 1.98s
[Summary] Q5: 4.60s
[Summary] Q6: 5.60s
[Summary] Q7: 16.27s
[Summary] Q8: 12.99s
[Summary] Q9: 60.03s
```
### 步骤 5. 停止工作负载捕获并解析网络数据包以获取 SQL 操作
+ 切换到[终端 3](ssh://monitor1-ssh#3)和[终端 4](ssh://monitor1-ssh#4)，按 Ctrl+C 停止线程。
+ 切换到[终端 3](ssh://monitor1-ssh#3) ，解析网络数据包。
```bash
for i in `ls -rth ts*.pcap`
do
sudo tshark -r $i -Y "mysql.query or ( tcp.srcport==3306)" -d tcp.port==3306,mysql -o tcp.calculate_timestamps:true -T fields -e tcp.stream -e tcp.len -e tcp.time_delta -e ip.src -e tcp.srcport -e ip.dst -e tcp.dstport -e frame.time_epoch -e mysql.query -E separator='|' >> tshark.log
done
```
```output
Running as user "root" and group "root". This could be dangerous.
```
```bash
./parse-tshark \
-mode parse2file \
-parsemode 1 \
-tsharkfile ./tshark.log \
-hostfile ./host.ini \
-replayfile ./tshark.out \
-defaultuser user_null \
-defaultdb db_null
```
+ 退出到 monitor1_address 的操作系统提示符。
```bash
exit
```
### 步骤 6. 在 TiDB 上重放工作负载
+ 复制 `tshark.out` 文件并重放。
```bash
scp -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem rocky@${HOST_MYSQL1_PRIVATE_IP}:/home/rocky/parse-tshark/tshark.out ~/sql-replay/tshark.out.tmp
sed -e ':a' -e 'N' -e '$!ba' -e 's/\\\\n/ /g' -e 's/\\\\t/ /g' ~/sql-replay/tshark.out.tmp > ~/sql-replay/tshark.out
```
```output
tshark.out 100% 31KB 22.8MB/s 00:00
```
```bash
cd ~/sql-replay
sql-replay -mode replay -db "root@tcp(${HOST_DB1_PRIVATE_IP}:4000)/test" -speed 100 -slow-out tshark.out -replay-out ./replay_out/tshark -username all -sqltype all -dbname all
```
```output
Filter Rule: Source user - all, Source database - all, Source SQL type - all, Replay speed: 100.000000
[2024-10-22 14:01:18.333] Parameters read successfully, starting data parsing
[2024-10-22 14:01:18.347] Data parsing completed, Data parsing time: 13.893043ms, Starting SQL replay
[2024-10-22 14:01:30.629] SQL replay completed, SQL replay time: 12.282532809s
```
### 步骤 7. 生成重放报告
+ 加载重放结果。
```bash
sql-replay -mode load -db "root@tcp(${HOST_DB1_PRIVATE_IP}:4000)/test" -out-dir ./replay_out -replay-name tshark -table replay_info
```
```output
load batchsize: 1000, load workers: 4
[2024-10-22 14:02:15.665] Begin create table - REPLAY_INFO
[2024-10-22 14:02:15.674] Completed processing file: tshark.3518596272
[2024-10-22 14:02:15.676] Completed processing file: tshark.1069663518
[2024-10-22 14:02:15.678] Completed processing file: tshark.out
```
+ 生成重放报告。
```bash
sql-replay -mode report -db "root@tcp(${HOST_DB1_PRIVATE_IP}:4000)/test" -replay-name tshark
```
```output
Server is running on port :8081
```
+ 访问报告： [http://monitor1_address:8081/](http://monitor1_address:8081/)
+ 查看报告后，在命令行中按Ctrl+C终止报告线程。