# Test Compare Cluster Performance go test -v -run TestComparePerformance -timeout=120s -args \ -prod=1379661944646416054 \ -prodStart=2025-11-11T13:58:00Z \ -prodEnd=2025-11-11T23:08:01Z \ -replay=10255290801445290516 \ -replayStart=2025-11-23T21:00:00Z \ -replayEnd=2025-11-24T06:10:01Z go test -v -run TestComparePerformance -timeout=120s -args -prod=1379661944646414653 -prodStart=2025-11-06T22:07:00Z -prodEnd=2025-11-07T14:37:00Z -replay=10434003241707502157 -replayStart=2025-11-20T09:30:00Z -replayEnd=2025-11-21T02:00:00Z # Test Compare Cluster Slow Query scl enable gcc-toolset-11 "bash -c 'CGO_ENABLED=1 go test -v -run TestClusterDiff2 -args \ -clusterA=10434003241707502157 \ -aStart=2025-11-20T08:00:00Z \ -aEnd=2025-11-21T08:00:00Z \ -clusterB=1379661944646414653 \ -bStart=2025-11-06T23:20:12Z \ -bEnd=2025-11-07T23:20:12Z \ -aAlias=replay \ -bAlias=prod'" # TiDB S3 Slow Query Log Analysis System ## Project Overview This project is a TiDB slow query log analysis tool developed in Go, supporting S3 bucket log file downloads, local file processing, SQLite database storage, intelligent analysis, and Feishu integrated report pushing. The project adopts a modular design, supports high-concurrency processing and streaming data processing, and can effectively identify and analyze slow query issues in TiDB clusters to help users optimize database performance. ## Core Features ### 1. Multi-mode Operation Support - **Local S3 Mode** (default): Process local S3 slow query log files and generate analysis reports - **Download Mode**: Download log files from S3 to local storage - **Document Generation Mode**: Directly query SQLite database and generate Feishu document reports ### 2. S3 Integration - Support AWS S3 bucket file downloads - Stream processing of large files to avoid memory overflow - Concurrent download and processing for improved efficiency - Support for multiple file format processing ### 3. Database Operations - SQLite database storage for slow query records - GORM integration with modern ORM operations support - Batch insert optimization for improved write performance - WAL mode support for concurrent read/write ### 4. Intelligent Analysis - Multiple query pattern analysis (such as TableFullScan, SQLPlanChanged, etc.) - Execution plan parsing and optimization suggestions - Statistics collection and aggregation - Top 10 slow query identification - New slow query Digest identification ### 5. Feishu Integration - Automatically generate analysis reports and push to Feishu documents - Support card message notifications - Include S3 processing statistics - Configurable template ID and version ## Project Structure ``` pasefile/ ├── main.go # Main program entry ├── configs/ │ ├── config-dev.yaml # Development environment configuration file │ ├── config-prod.yaml # Production environment configuration file │ └── cluster.yaml # Organization and cluster configuration file ├── pkg/ │ ├── common/ # Common functionality │ ├── config/ # Configuration management │ ├── database/ # Database operations │ ├── dto/ # Data transfer objects │ ├── entity/ # Data entities │ ├── feishu/ # Feishu integration │ ├── log/ # Log management │ ├── parser/ # Log parsing │ ├── query/ # Query analysis │ ├── s3/ # S3 client and log processing │ └── utils/ # Utility functions └── scripts/ # Script files ``` ## Installation and Configuration ### 1. Environment Requirements - Go 1.19+ - SQLite3 - AWS S3 access permissions ### 2. Dependency Installation ```bash go mod tidy ``` ### 3. Configuration File Setup #### configs/config-dev.yaml ```yaml env: "dev" day: "20250701" feishu: appID: "your_app_id" appSecret: "your_app_secret" documentUrl: "your_document_url" documentID: "your_document_id" receiveID: "your_receive_id" cardTemplate: templateID: "your_template_id" templateVersionName: "your_template_version" s3: access_key_id: "your_access_key" secret_access_key: "your_secret_key" session_token: "your_session_token" region: "us-east-1" bucket: "your-bucket-name" prefix: "your/prefix/path" parser: batch_size: 1500 ``` #### configs/cluster.yaml ```yaml orgs: - orgName: "Your Organization" orgId: "your_org_id" receiveID: "your_receive_id" documentUrl: "your_document_url" documentId: "your_document_id" afterBlockId: "your_after_block_id" clusters: - clusterId: "your_cluster_id" clusterName: "your_cluster_name" alias: "your_alias" projectId: "your_project_id" region: "your_region" provider: "your_provider" ``` ## Usage ### 1. Basic Usage #### Process Local S3 Files (Default Mode) ```bash # Use date from configuration file ./pasefile # Specify a specific date ./pasefile -day=20250701 ``` #### Download S3 Files Only ```bash ./pasefile -mode=download -day=20250701 ``` ### 2. Programmatic Usage ```go package main import ( "pasefile/pkg/config" "pasefile/pkg/feishu" "pasefile/pkg/query" "pasefile/pkg/s3" log2 "pasefile/pkg/log" "go.uber.org/zap" ) func main() { // 1. Initialize configuration if err := config.LoadConfig(); err != nil { log2.PlainError("Failed to load config", zap.Error(err)) return } // 2. Process each organization for _, org := range config.OrgsConfig.Orgs { // 3. Process S3 log files s3Stats, err := s3.ProcessLocalS3LogsWithStatistics(org) if err != nil { log2.PlainError("Failed to process S3 logs", zap.Error(err)) continue } // 4. Query analysis querySqlLite, err := query.NewQuerySqlLite(org) if err != nil { log2.PlainError("Failed to create query", zap.Error(err)) continue } defer querySqlLite.Close() finalOutput, err := querySqlLite.RunS3SlowQueryAnalysis(s3Stats) if err != nil { log2.PlainError("Failed to run analysis", zap.Error(err)) continue } // 5. Push Feishu report querySqlLite.PushSummaryToFeishuWithS3Stats(*finalOutput, s3Stats, org) // 6. Send Feishu card message feishu.SendMessageCard(*finalOutput, org.ReceiveID) } } ``` ## Data Structures ### S3Statistics ```go type S3Statistics struct { SlowQueryCount int `json:"slow_query_count"` SlowQueryPatternCount int `json:"slow_query_pattern_count"` SlowQueryAnalyzeCount int `json:"slow_query_analyze_count"` SlowQueryDDLCount int `json:"slow_query_ddl_count"` SlowQueryTiflashCount int `json:"slow_query_tiflash_count"` SlowQueryForceIndexCount int `json:"slow_query_force_index_count"` } ``` ### FinalOutput ```go type FinalOutput struct { Summary Summary `json:"summary"` QueryResults []QueryResult `json:"results"` DocLink string `json:"doc_link"` } ``` ### QueryResult ```go type QueryResult struct { QueryName string `json:"slow_query_pattern"` Level string `json:"level"` TotalCount PatternCountResult `json:"count_result"` Top10 []TopResult `json:"top_10"` } ``` ## Database Table Structure ### SQLite Table Structure -- "20250701" definition CREATE TABLE `20250701` (`Formatted_Timestamp` varchar(64), `Cluster_Id` varchar(128), `Query` text, `Query_time` REAL, `Plan` text, `Plan_digest` varchar(64), `Binary_plan` text, `Binary_plan_parsed` json, `DB` varchar(64), `Succ` boolean, `Pattern_Name` varchar(64), `Source_file` varchar(128), `Digest` varchar(128), `Index_names` varchar(128), `Mem_max` integer, `Stats` varchar(128), `Warnings` varchar(128), `Conn_ID` integer ); CREATE INDEX idx_20250701_digest ON `20250701` (`Digest`); CREATE INDEX idx_20250701_pattern_name ON `20250701` (`Pattern_Name`); CREATE INDEX idx_20250701_mem_max ON `20250701` (`Mem_max`); CREATE INDEX idx_20250701_query_time ON `20250701` (`Query_time`); CREATE INDEX idx_20250701_pattern_digest ON `20250701` (`Pattern_Name`, `Digest`); CREATE INDEX idx_20250701_cluster_digest_plan ON `20250701` (`Cluster_Id`, `Digest`, `Plan`); ``` ### MySQL Table Structure #### daily_summary Table ```sql CREATE TABLE `daily_summary` ( `id` INT NOT NULL AUTO_INCREMENT, `report_date` DATE NOT NULL COMMENT 'Report statistics date', `total_slow_queries` INT NOT NULL COMMENT 'Total number of slow queries', `unique_digests` INT NOT NULL COMMENT 'Total number of unique slow query digests', `critical_count` INT DEFAULT 0 COMMENT 'Total number of Critical level queries', `major_count` INT DEFAULT 0 COMMENT 'Total number of Major level queries' `details_json` JSON DEFAULT NULL COMMENT 'Other classification details, such as TableFullScan count', `created_at` datetime(3) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_report_date` (`report_date`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'Daily slow query analysis summary table'; ``` #### daily_stats Table ```sql CREATE TABLE `daily_stats` ( `id` BIGINT NOT NULL AUTO_INCREMENT, `report_date` DATE NOT NULL COMMENT 'Statistics date', `sql_timestamp` DOUBLE NOT NULL COMMENT 'SQL execution time', `pattern_name` VARCHAR(255) DEFAULT NULL COMMENT 'Slow query pattern, such as TableFullScan', `level` VARCHAR(50) DEFAULT NULL COMMENT 'Severity level, such as Critical, Major', `query` TEXT COMMENT 'Formatted SQL query statement', `digest` VARCHAR(512) NOT NULL COMMENT 'SQL fingerprint, linked to digest_details table', `total_count` INT NOT NULL COMMENT 'Total occurrences of this slow query on the day' `avg_query_time` DOUBLE DEFAULT NULL COMMENT 'Average query time for the day (seconds)', `avg_mem_max` DOUBLE DEFAULT NULL COMMENT 'Average maximum memory for the day (bytes)', `max_mem` BIGINT DEFAULT NULL COMMENT 'Maximum memory for the day (bytes)', `created_at` datetime(3) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_date_digest` (`sql_timestamp`, `digest`), KEY `idx_digest` (`digest`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Daily slow query statistics data table'; ``` ## Performance Optimization ### 1. Concurrent Processing - Use worker pool pattern to process large numbers of files - Automatically adjust concurrency based on CPU core count - SQLite WAL mode supports concurrent read/write ### Processing Statistics - Total slow query count - Pattern matching query count - DDL statement count - Analysis statement count - Processing efficiency statistics ### Output Files - `output/parse.json`: Processing statistics - Database files: `../db-file/{orgId}/{day}.db` - Log files: `../logs/` ## Main Process 1. **Initialize Configuration**: Load configuration files and organization information 2. **Download S3 Files**: Download slow query log files from S3 bucket 3. **Process Local Files**: Parse log files and store in SQLite database 4. **Query Analysis**: Execute predefined queries to analyze slow query patterns 5. **Generate Reports**: Aggregate analysis results and generate reports 6. **Push to Feishu**: Push reports to Feishu documents and group chats # Feishu API ## Feishu chat_id wetech：oc_f2dae01fdee204a8b75f5ed5f4808fb5 // bingo：oc_d544427c8d3fa5c7eb923ffa2050063c //bingoplus kernel support swat team ue: oc_1e9a771840c771816271a057cb088ae6 // 【Internal】UE 技术沟通群 https://open.feishu.cn/document/server-docs/group/chat/list?appId=cli_a8c5690eb1b6500c ## Get All Document Blocks https://open.feishu.cn/api-explorer/cli_a8c5690eb1b6500c?apiName=list&from=op_doc&project=docx&resource=document.block&version=v1 ## Create Block https://open.feishu.cn/api-explorer/cli_a8c5690eb1b6500c?apiName=create&from=op_doc&project=docx&resource=document.block.children&version=v1 ## Card Template ### Card Toolkit https://open.feishu.cn/cardkit/editor?cardId=AAqIt09BLX3VJ ## Send Card Message https://open.feishu.cn/api-explorer/cli_a8c5690eb1b6500c?apiName=create&from=op_doc&project=im&resource=message&version=v1 request { "receive_id": "oc_271b5e3f25d764f1fc7fa591d10dd963", "msg_type": "interactive", "content": "{\"type\":\"template\",\"data\":{\"template_id\":\"AAqIt09BLX3VJ\",\"template_version_name\":\"1.0.4\",\"template_variable\":{\"orgName\":\"wetech\",\"day\":\"2025-07-02\",\"critical_table_raw_array\":[{\"customer_name\":\"HugeResourceUsage\",\"customer_scale\":\"3\",\"customer_arr\":\"4\"},{\"customer_name\":\"SQLPlanChanged\",\"customer_scale\":\"33\",\"customer_arr\":\"2\"},{\"customer_name\":\"Failed\",\"customer_scale\":\"3\",\"customer_arr\":\"6\"}],\"major_table_raw_array\":[{\"customer_name\":\"HugeResourceUsage\",\"customer_scale\":\"3\",\"customer_arr\":\"4\"},{\"customer_name\":\"SQLPlanChanged\",\"customer_scale\":\"33\",\"customer_arr\":\"2\"},{\"customer_name\":\"Failed\",\"customer_scale\":\"3\",\"customer_arr\":\"6\"}]}}}" } ### Configuration Variables https://open.feishu.cn/document/feishu-cards/feishu-card-cardkit/configure-card-variables#f66593dd # AWS CLI S3 File Download aws s3 cp s3://o11y-prod-shared-us-east-1/diagnosis/data/1379661944646416027/slowlog-merged/1h/ ./slow_d/ \ --recursive \ --exclude "*" \ --include "20250612*" \ --profile clinic-prod # Configure Crontab ## Start nohup crond -s > /home/ec2-user/healthck_check/logs/crond.log 2>&1 & ## Edit Crontab chmod +x run_once.sh crontab -e 0 5 * * * /home/ec2-user/healthck_check/run_slow_query.sh >> /home/ec2-user/healthck_check/logs/run_slow_query.log 2>&1 0,30 * * * * /home/ec2-user/healthck_check/run_check_tikv.sh >> /home/ec2-user/healthck_check/logs/run_check_tikv.log 2>&1 ## View Crontab crontab -l ps aux | grep crond # Configure slowquery-proxy service 创建 slowquery-proxy.service 文件 sudo vim /etc/systemd/system/slowquery-proxy.service 1️⃣让 systemd 识别新服务 sudo systemctl daemon-reexec sudo systemctl daemon-reload 2️⃣ 启动服务 sudo systemctl start slowquery-proxy 3️⃣ 查看状态（非常关键） sudo systemctl status slowquery-proxy 4️⃣ 设置开机自启 sudo systemctl enable slowquery-proxy 详细教程：https://pingcap.feishu.cn/wiki/XCr1wfq7si57vTkpDe2cKOZqnZb 验证自动拉起是否生效 手动 kill 进程 ps aux | grep uvicorn kill -9