--- title: "SQL Workloads Replay" version: "v2" status: "GA" description: "This hands-on lab demonstrates how to replay a SQL workload using TiDB Data Migration and sql-replay utility. Specifically, we will cover how to capture a SQL workload from a MySQL database, replay the SQL workload on a TiDB cluster, and analyze the performance of the TiDB cluster. This lab is a practical workshop that allows you to learn how to replay a SQL workload using TiDB Data Migration and analyze the performance of a TiDB cluster." --- # SQL Workloads Replay ## Overview During the migration from MySQL to TiDB, it is often necessary to verify the compatibility and performance of applications' SQLs. The TiDB Real Traffic Replay Tool can be used to reapply traffic files obtained from MySQL slow queries or packet captures to the TiDB cluster. This tool can complete real traffic validation for compatibility or performance, enhancing the success rate of database migration. ![80%](https://lab-static.pingcap.com/quick-demo/demo-302-1-en.png) ### Duration + This lab should take you approximately 90 minutes to complete. ### The core features of `sql-replay` + Log capture and parsing + `MySQL slow log` files or files generated by `parse-tshark captures` include SQL execution times and SQL text. The tool can parse these logs to extract relevant information such as SQL statements, execution times, and possibly other metadata like execution context or the user executing the query. + Query replay + Replay the extracted SQL queries to the TiDB cluster in a controlled environment. + Performance measurement and analysis + During replay, the execution times of SQL queries in the TiDB cluster are obtained. + These metrics are compared with benchmarks or previous runs to identify performance degradation or improvements, helping pinpoint specific queries or database settings that may cause issues. + Report generation + Finally, sql-replay generates reports providing insights into query compatibility and performance. These reports help database administrators and developers understand the impact of different queries on the system and make informed optimization decisions. ### Tasks + Setup upstream MySQL and downstream TiDB environment. + Capture and replay SQLs by `sql-replay` tool. + Capture SQLs by `parse-tshark` tool and replay them by `sql-replay` tool. > [!TIP] > Click **Start** to launch the lab environment. > Once the environment is ready, select **Next** on the bottom toolbar to advance to **Module 1**. > And, follow the step-by-step lab guide to connect to the hands-on environment and begin your tasks. ## Module 1: Set Up MySQL And TiDB For SQL Workloads Replay + Setup upstream MySQL and downstream TiDB environment, and install `sql-replay` and `tshark` tools. ### Step 1. Log into a Linux machine + Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab. + The banner after you logged in: ```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/ ``` ### Step 2. Install `sql-replay` + Install `sql-replay` tool. ```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 ``` + Create a directory for saving files. ```bash mkdir ~/sql-replay cd ~/sql-replay ``` ### Step 3. Install `parse-tshark` + Connect to MySQL server node. If prompted, answer `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 ~]$ ``` + Install `wireshark` and `parse-tshark` tools. ```bash sudo yum install -y wireshark ``` ```output ... Complete! ``` + Add user `rocky` to `wireshark` user group and grant `dump` path access permission. ```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 ``` ### Step 4. Prepare MySQL for SQL workloads capture + Connect to MySQL server as database user `root`. ```bash mysql -h localhost -P 3306 -u root -pq1w2e3R4_ --connect-expired-password ``` ```output mysql> ``` + Create database user `test`. ```sql DROP USER IF EXISTS 'test'@'%'; CREATE USER 'test'@'%' IDENTIFIED BY 'q1w2e3R4_'; GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES; ``` + Set up the slow log parameters in 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 ``` + Verify the parameters are correct. ```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) ``` + Create `sbtest` database ```sql DROP DATABASE IF EXISTS sbtest; CREATE DATABASE sbtest; ``` + Exit to monitor OS command line. ```sql EXIT ``` ```output Bye ``` + Add read privilege on slow query log files. ```bash sudo chmod -R +r /var/lib/mysql/local-slow.log ``` + Return to the Linux bastion server. ```bash exit ``` ```output logout Connection to 10.90.1.130 closed. ``` ### Step 5. Prepare TiDB for SQL workloads replay + Set up downstream TiDB environment. + Prepare replay target database, and create `replay_info` table for recording SQLs' execution 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) ``` + You have installed `sql-replay` and `parse-tshark` tools, and finished set up the source and target database for replaying SQL workloads. Proceed to the next module. ## Module 2: Capture And Replay Workloads By `sql-replay` ### Step 1. Run `sysbench` and generate slow queries from MySQL + Click here - [Terminal 2](ssh://monitor1-ssh#2) to open a new terminal connected to the virtual machine assigned for your hands-on lab. + The banner after you logged in: ```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/ ``` + Change into the working directory. ```bash cd ~/sql-replay ``` + Set up `sysbench` configuration. ```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 ``` + Run `sysbench` testing. ```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 ``` ### Step 2. Parse slow query log + Copy the MySQL slow query log file to 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 ``` ### Step 3. Replay SQL workload against the target TiDB ```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 ``` + Load the replay result. ```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 ``` + Generate the replay report. ```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 ``` + Access the report: [http://monitor1_address:8081/](http://monitor1_address:8081/). Here is a report example: ![80%](https://lab-static.pingcap.com/quick-demo/demo-302-2-en.png) + After viewing the report, terminate the report thread by press Ctrl+C in the command line. ## 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. + The banner after you logged in: ```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/ ``` + Connect to MySQL server node. If prompted, answer `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 ~]$ ``` + 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-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 ~]$ ``` + 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). + The banner after you logged in: ```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/ ``` + Connect to MySQL server node. ```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 ~]$ ``` + 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. + The banner after you logged in: ```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 ``` ### 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-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 ``` ### 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. ## Conslusion ```hbsptform { "ui": { "titleLevel": 3 }, "portalId": "4466002", "formId": "89fa8368-eba1-4b84-b91e-eb9eb6613ddf", "region": "na1", "sfdcCampaignId": "701RC00000VqCODYA3" } ```