# SQL 工作负载重放 ## 概述 在从 MySQL 迁移到 TiDB 的过程中，通常需要验证应用程序 SQL 的兼容性和性能。TiDB 真实流量重放工具可用于将从 MySQL 慢查询或数据包捕获中获取的流量文件重新应用到 TiDB 集群。该工具可以完成针对兼容性或性能的真实流量验证，提高数据库迁移的成功率。 ![80%](https://lab-static.pingcap.com/quick-demo/demo-302-1-en.png) ### 实验时长 + 完成此练习大约需要 90 分钟。 ### `sql-replay` 的核心功能 + 日志捕获和解析 + `MySQL slow log` 文件或由 `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：为 SQL 工作负载重放设置 MySQL 和 TiDB + 设置上游 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 server 节点。如果有提示，回答 `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 server。 ```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; ``` + 退出监控操作系统命令行。 ```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 环境。 + 准备重放目标数据库，并创建 `replay_info` 表以记录 SQL 的执行信息。 ```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 工作负载 ```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 server 的 `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 server 节点。如果有提示，回答 `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 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-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 server 节点。 ```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 的操作系统提示符。 ```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终止报告线程。 ## 实验结束