## モジュール 3: `parse-tshark` を用いたワークロードのキャプチャと `sql-replay` でのリプレイ
### 手順 1. `parse-tshark` SQL ワークロードキャプチャ用に MySQL を準備します
+ ここをクリック - [ターミナル 3](ssh://monitor1-ssh#3) で、MySQLサーバーの`SSL`機能とスロークエリログをオフにします。
+ MySQLサーバーノードに接続します。プロンプトが表示された場合は、`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 サーバーに接続します。
```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 |
...
+-------------------------------------+----------+
```
+ `slow_query_log`と`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)
```
+ OSのコマンドラインに終了します。
```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)
```
+ OSコマンドラインを監視して終了します。
```sql
EXIT
```
```output
bye
```
### 手順 3. ネットワークデータパケットのキャプチャを開始し、`sql-replay` フォーマットに変換する
+ MySQLサーバーノードに接続します。プロンプトが表示された場合は、`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サーバーノードに接続します。
```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 OSプロンプトに退出します。
```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を押してレポートサービスを終了します。