# SQL ワークロードの再生 ## 概要 MySQL から TiDB への移行時には、アプリケーションの SQL の互換性とパフォーマンスを確認することがよく必要になります。TiDB Real Traffic Replay Tool は、MySQL のスロークエリやパケットキャプチャから得られたトラフィックファイルを TiDB クラスタに再適用するために使用できます。このツールは、互換性やパフォーマンスのための実際のトラフィック検証を完了し、データベース移行の成功率を向上させます。 ![80%](https://lab-static.pingcap.com/quick-demo/demo-302-1-en.png) ### 所要時間 + この演習は約 90 分程度かかります。 ### `sql-replay` の基本機能 + ログのキャプチャと解析 + `MySQL スローログ`ファイルまたは`parse-tshark captures`によって生成されたファイルには、SQL実行時間とSQLテキストが含まれています。このツールはこれらのログを解析し、SQLステートメント、実行時間、実行コンテキストやクエリを実行したユーザーなどの関連情報を抽出することができます。 + クエリの再生 + 抽出されたSQLクエリをTiDBクラスタの制御された環境で再生します。 + パフォーマンス測定と分析 + 再生中に、TiDBクラスタでのSQLクエリの実行時間が取得されます。 + これらのメトリクスはベンチマークや以前の実行と比較され、パフォーマンスの低下や改善を特定するのに役立ちます。特定のクエリやデータベース設定が問題を引き起こす可能性がある箇所を特定するのに役立ちます。 + レポート生成 + 最後に、sql-replayはクエリの互換性とパフォーマンスに関する洞察を提供するレポートを生成します。これらのレポートは、データベース管理者や開発者がシステムに対するさまざまなクエリの影響を理解し、情報に基づいた最適化の決定を行うのに役立ちます。 ### タスク + 上流のMySQLと下流のTiDB環境をセットアップします。 + `sql-replay`ツールを使ってSQLをキャプチャしてリプレイします。 + `parse-tshark`ツールでSQLをキャプチャし、それを`sql-replay`ツールでリプレイします。 > [!TIP] > 演習環境を起動するには、**開始**をクリックしてください。 > 環境の準備が整ったら、下部のツールバーで **次のモジュール** を選択して **モジュール 1** に進みます。 > そして、ステップバイステップの演習ガイドに従って、ハンズオン環境に接続し、タスクを開始してください。 ## モジュール 1: MySQL と TiDB のセットアップによる SQL ワークロードのリプレイ + 上流のMySQLと下流のTiDB環境をセットアップし、`sql-replay`と`tshark`ツールをインストールします。 ### 手順 1. Linux マシンにログインする + ここをクリック - [ターミナル 1](ssh://monitor1-ssh#1) で、ハンズオン演習用に割り当てられた仮想マシンに接続された新しいターミナルを開きます。 + ログイン後に表示されるバナー： ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ### 手順 2. `sql-replay` のインストール + `sql-replay` ツールをインストールします。 ```bash mkdir ~/replay cd ~/replay wget https://github.com/Bowen-Tang/sql-replay/releases/download/v0.3.3/0.3.3.zip unzip 0.3.3.zip ``` ```output --2024-08-06 09:07:09-- https://github.com/Bowen-Tang/sql-replay/releases/download/0.3.3/0.3.3.zip Resolving github.com (github.com)... 140.82.116.3 Connecting to github.com (github.com)|140.82.116.3|:443... connected. HTTP request sent, awaiting response... 302 Found ... Resolving objects.githubusercontent.com (objects.githubusercontent.com)... 185.199.110.133, 185.199.109.133, 185.199.108.133, ... Connecting to objects.githubusercontent.com (objects.githubusercontent.com)|185.199.110.133|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 7015215 (6.7M) Saving to: ‘0.3.3.zip’ 100%======================================================================================================================================================================> 7,015,215 --.-K/s in 0.06s 2024-08-06 09:07:09 (119 MB/s) - ‘0.3.3.zip’ saved [7015215/7015215] $ unzip 0.3.3.zip Archive: 0.3.3.zip inflating: sql-replay ``` + ファイルを保存するためのディレクトリを作成します。 ```bash mkdir ~/sql-replay cd ~/sql-replay ``` ### 手順 3. `parse-tshark` をインストールします + MySQLサーバーノードに接続します。プロンプトが表示された場合は、`yes` と答えてください。 ```bash ssh -i /home/ec2-user/.ssh/pe-class-key-oregon.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 ~]$ ``` + `wireshark` と `parse-tshark` ツールをインストールします。 ```bash sudo yum install -y wireshark ``` ```output ... Complete! ``` + ユーザー `rocky` を `wireshark` ユーザーグループに追加し、`dump` パスへのアクセス権限を付与します。 ```bash sudo usermod -a -G wireshark rocky sudo chmod +x /usr/bin/dumpcap mkdir ~/parse-tshark cd ~/parse-tshark wget https://github.com/Bowen-Tang/parse-tshark/releases/download/0.1.2/parse-tshark-v0.1.2.zip unzip parse-tshark-v0.1.2.zip export PATH=/home/rocky/parse-tshark:$PATH ``` ```output [ec2-user@ip-10-90-4-205 replay]$ mkdir ~/parse-tshark [ec2-user@ip-10-90-4-205 replay]$ cd ~/parse-tshark [ec2-user@ip-10-90-4-205 parse-tshark]$ wget https://github.com/Bowen-Tang/parse-tshark/releases/download/0.1.2/parse-tshark-v0.1.2.zip --2024-08-06 09:11:36-- https://github.com/Bowen-Tang/parse-tshark/releases/download/0.1.2/parse-tshark-v0.1.2.zip Resolving github.com (github.com)... 140.82.116.3 Connecting to github.com (github.com)|140.82.116.3|:443... connected. HTTP request sent, awaiting response... 302 Found ... Resolving objects.githubusercontent.com (objects.githubusercontent.com)... 185.199.109.133, 185.199.110.133, 185.199.111.133, ... Connecting to objects.githubusercontent.com (objects.githubusercontent.com)|185.199.109.133|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 5162087 (4.9M) [application/octet-stream] Saving to: ‘parse-tshark-v0.1.2.zip’ 100%======================================================================================================================================================================> 5,162,087 10.1MB/s in 0.5s 2024-08-06 09:11:37 (10.1 MB/s) - ‘parse-tshark-v0.1.2.zip’ saved [5162087/5162087] $ unzip parse-tshark-v0.1.2.zip Archive: parse-tshark-v0.1.2.zip inflating: parse-tshark ``` ### 手順 4. SQL ワークロードキャプチャ用に MySQL を準備する + データベースユーザー `root` としてMySQLサーバーに接続します。 ```bash mysql -h localhost -P 3306 -u root -pq1w2e3R4_ --connect-expired-password ``` ```output mysql> ``` + データベースユーザー `test` を作成します。 ```sql DROP USER IF EXISTS 'test'@'%'; CREATE USER 'test'@'%' IDENTIFIED BY 'q1w2e3R4_'; GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES; ``` + MySQLでスローログのパラメータを設定します。 ```sql SET GLOBAL slow_query_log = on; SET GLOBAL slow_query_log_file='/var/lib/mysql/local-slow.log'; SET GLOBAL long_query_time = 0; ``` ```sql EXIT ``` ```bash mysql -h localhost -P 3306 -u root -pq1w2e3R4_ --connect-expired-password ``` + パラメータが正しいことを確認します。 ```sql SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'slow_query_log_file'; SHOW VARIABLES LIKE 'long_query_time'; ``` ```output mysql> SHOW VARIABLES LIKE 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | ON | +----------------+-------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'slow_query_log_file'; +---------------------+----------------------------------------+ | Variable_name | Value | +---------------------+----------------------------------------+ | slow_query_log_file | /var/lib/mysql/local-slow.log | +---------------------+----------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 0.000000 | +-----------------+----------+ 1 row in set (0.00 sec) ``` + `sbtest` データベースを作成します。 ```sql DROP DATABASE IF EXISTS sbtest; CREATE DATABASE sbtest; ``` + OS コマンドラインを監視するために退出します。 ```sql EXIT ``` ```output Bye ``` + スロークエリログファイルに対する読み取り権限を追加します。 ```bash sudo chmod -R +r /var/lib/mysql/local-slow.log ``` + Linuxの踏み台サーバーに戻ります。 ```bash exit ``` ```output logout Connection to 10.90.1.130 closed. ``` ### 手順 5. SQL ワークロードのリプレイのための TiDB の準備 + 下流のTiDB環境をセットアップします。 + リプレイ対象のデータベースを準備し、SQLの実行情報を記録するための`replay_info`テーブルを作成します。 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -uroot ``` ```output mysql> ``` ```sql DROP DATABASE IF EXISTS sbtest; CREATE DATABASE sbtest; ``` ```output Query OK, 0 rows affected (0.11 sec) ``` + `sql-replay` と `parse-tshark` ツールをインストールし、SQLワークロードを再生するためのソースおよびターゲットのデータベースのセットアップを完了しました。次のモジュールに進んでください。 ## モジュール 2: `sql-replay` によるワークロードのキャプチャと再生 ### 手順 1. `sysbench` を実行し、MySQL からスロークエリを生成します。 + ここをクリック - [ターミナル 2](ssh://monitor1-ssh#2) で、ハンズオン演習用に割り当てられた仮想マシンに接続された新しいターミナルを開きます。 + ログイン後に表示されるバナー： ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` + 作業ディレクトリに変更します。 ```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-oregon.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 ワークロードを再生する + 指定された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 を押してレポートスレッドを終了します。 ## モジュール 3: `parse-tshark` を用いたワークロードのキャプチャと `sql-replay` でのリプレイ ### 手順 1. `parse-tshark` SQL ワークロードキャプチャ用に MySQL を準備します + ここをクリック - [ターミナル 3](ssh://monitor1-ssh#3) で、MySQLサーバーの`SSL`機能とスロークエリログをオフにします。 + ログイン後に表示されるバナー： ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` + MySQLサーバーノードに接続します。プロンプトが表示された場合は、`yes`と答えてください。 ```bash ssh -i /home/ec2-user/.ssh/pe-class-key-oregon.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-oregon.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) で、ハンズオン演習用に割り当てられた仮想マシンに接続された新しいターミナルを開きます。 + ログイン後に表示されるバナー： ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` + MySQLサーバーノードに接続します。 ```bash ssh -i /home/ec2-user/.ssh/pe-class-key-oregon.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テストを実行します。 + ログイン後に表示されるバナー： ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ```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-oregon.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を押してレポートサービスを終了します。 ## 演習終了 ```hbsptform { "ui": { "titleLevel": 3 }, "portalId": "4466002", "formId": "62bfb32a-a60a-4f9d-b2dc-1107a93db568", "region": "na1", "sfdcCampaignId": "701RC00000NdFlAYAV" } ```