## Module 3: Capture Workloads By `parse-tshark` And Replay Them By `sql-replay`
### Step 1. Prepare MySQL for `parse-tshark` SQL workloads capture
+ Click here - [Terminal 3](ssh://monitor1-ssh#3), turn off the `SSL` feature and slow query log of MySQL server.
+ Connect to MySQL server node. If prompted, answer `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 ~]$
```
+ Turn off `SSL` feature, then `tshark` can parse SQL text from the network data packet.
```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
```
+ Connect to MySQL server as database user `root`.
```bash
mysql -h localhost -P 3306 -u root -pq1w2e3R4_ --connect-expired-password
```
```output
mysql>
```
+ Verify the SSL feature is disabled.
```sql
SHOW VARIABLES LIKE '%ssl%';
```
```output
+-------------------------------------+----------+
| Variable_name | Value |
+-------------------------------------+----------+
...
| have_openssl | DISABLED |
| have_ssl | DISABLED |
...
+-------------------------------------+----------+
```
+ Set up parameters related to slow log and max_execution_time.
```sql
SET GLOBAL slow_query_log = off;
SET GLOBAL max_execution_time = 60000;
```
```sql
EXIT
```
+ Verify the parameters settings are correct.
```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
```
+ Return to the TiUP control machine.
```bash
exit
```
```output
logout
Connection to 10.90.1.204 closed.
```
### Step 2. Prepare TPC-H testing
+ Prepare the initial data on 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
```
+ Verify the data volume.
```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)
```
+ Exit to OS command line.
```sql
EXIT
```
```output
bye
```
+ Prepare the initial data on 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
```
+ Verify the data volume.
```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)
```
+ Exit to monitor OS command line.
```sql
EXIT
```
```output
bye
```
### Step 3. Start the network data packets capture and convert to `sql-replay` format
+ Connect to MySQL server node. If prompted, answer `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 ~]$
```
+ Start to capture the network data packet of MySQL protocal. This command captures packets based on port 3306 and network card(`eth0`), capturing for 1 hour as an example (each file is about 2000MB, generating up to 200 files).
```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'
```
+ Click here - [Terminal 4](ssh://monitor1-ssh#4).
+ Connect to MySQL server node.
```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 ~]$
```
+ Obtaining User and database information during packet capture
```bash
cd ~/parse-tshark
./parse-tshark -mode getmysql -dbinfo "test:q1w2e3R4_@tcp(${HOST_MYSQL1_PRIVATE_IP}:3306)/information_schema" -output host.ini
```
### Step 4. Run the TPC-H testing
+ Click here - [Terminal 5](ssh://monitor1-ssh#5), connect to monitor, and run the TPC-H testing.
```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
```
### Step 5. Stop the workload capture and parse the network traffic for SQL operations
+ Switch to [Terminal 3](ssh://monitor1-ssh#3) and [Terminal 4](ssh://monitor1-ssh#4), stop the threads by press Ctrl+C.
+ On [Terminal 3](ssh://monitor1-ssh#3), parse the network packet.
```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
```
+ Exit to monitor1_address OS prompt.
```bash
exit
```
### Step 6. Replay the workload on TiDB
+ Copy `tshark.out` file, and replay it.
```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
```
### Step 7. Generate the replay report
+ Load replay result.
```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
```
+ Generate the replay report.
```bash
sql-replay -mode report -db "root@tcp(${HOST_DB1_PRIVATE_IP}:4000)/test" -replay-name tshark
```
```output
Server is running on port :8081
```
+ Access the report: [http://monitor1_address:8081/](http://monitor1_address:8081/)
+ After viewing the report, terminate the reporting service by press Ctrl+C in the command line.