starrocks-tpch-benchmark-job.yaml

apiVersion: batch/v1
kind: Job
metadata:
  name: tpch-benchmark-1tb
  namespace: starrocks
  labels:
    app: tpch-benchmark
    scale: "1tb"
    benchmark: "tpch"
spec:
  activeDeadlineSeconds: 7200  # 2 hours timeout
  template:
    spec:
      containers:
      - name: benchmark-runner
        image: public.ecr.aws/amazonlinux/amazonlinux:2
        command: ["/bin/bash"]
        args: 
        - "-c"
        - |
          set -e
          echo "=========================================="
          echo "TPC-H 1TB Benchmark for StarRocks (Robust)"
          echo "Scale Factor: 1000 (1TB dataset)"
          echo "Running all 22 TPC-H queries with timeout handling"
          echo "=========================================="
          
          echo "Installing required packages..."
          yum install -y mysql bc perl time wget unzip procps-ng timeout
          
          echo "Testing StarRocks connection..."
          FE_HOST="starrocks-karpenter-shared-nothing-fe-service.starrocks.svc.cluster.local"
          
          # Test connection with retry logic
          for i in {1..5}; do
            if mysql -h $FE_HOST -P 9030 -u root -e "USE tpch; SHOW TABLES;" 2>/dev/null; then
              echo "✓ Successfully connected to StarRocks"
              break
            else
              echo "⚠ Connection attempt $i failed, retrying in 10 seconds..."
              sleep 10
              if [ $i -eq 5 ]; then
                echo "✗ Failed to connect to StarRocks after 5 attempts"
                exit 1
              fi
            fi
          done
          
          # Verify 1TB dataset is loaded
          echo "Verifying 1TB TPC-H dataset..."
          LINEITEM_COUNT=$(mysql -h $FE_HOST -P 9030 -u root -e "USE tpch; SELECT COUNT(*) FROM lineitem;" -s -N)
          echo "LINEITEM table row count: $LINEITEM_COUNT"
          
          # For SF1000 (1TB), lineitem should have ~6 billion rows
          if [ "$LINEITEM_COUNT" -lt 5000000000 ]; then
            echo "⚠ WARNING: LINEITEM row count ($LINEITEM_COUNT) seems low for 1TB dataset"
            echo "Expected: ~6 billion rows for SF1000"
          else
            echo "✓ Dataset size verification passed"
          fi
          
          # Download and setup tpch-poc-1.0 for benchmark queries
          echo "Setting up TPC-H benchmark tools..."
          cd /tmp
          wget -q https://starrocks-public.oss-cn-zhangjiakou.aliyuncs.com/tpch-poc-1.0.zip
          unzip -q tpch-poc-1.0.zip
          cd tpch-poc-1.0
          
          # Create configuration file for StarRocks connection
          echo "Creating StarRocks configuration..."
          cat > conf/starrocks.conf << EOF
          [starrocks]
          # for mysql cmd
          mysql_host: $FE_HOST
          mysql_port: 9030
          mysql_user: root
          mysql_password: 
          database: tpch
          EOF
          
          echo ""
          echo "=========================================="
          echo "Starting TPC-H 1TB Benchmark"
          echo "Scale Factor: 1000 (1TB dataset)"
          echo "Running all 22 TPC-H queries"
          echo "Job timeout: 2 hours total"
          echo "=========================================="
          echo ""
          
          # Record start time
          START_TIME=$(date +%s)
          echo "Benchmark started at: $(date)"
          
          # Create results file
          echo -e "Query\tTime(ms)\tTime(sec)\tStatus" > /tmp/results.csv
          
          # Function to run a single query
          run_query() {
              local query_num=$1
              local query_file="sql/tpch/query/tpch/q$(printf "%02d" $query_num).sql"
              
              if [ ! -f "$query_file" ]; then
                  echo "⚠ Query file $query_file not found, skipping Q$query_num"
                  echo -e "Q$query_num\t0\t0\tSKIPPED" >> /tmp/results.csv
                  return
              fi
              
              echo "Running Query $query_num..."
              
              # Read the query from file
              local query=$(cat "$query_file")
              
              # Single warm-up run
              echo "  Warm-up run..."
              if ! mysql -h $FE_HOST -P 9030 -u root tpch -e "$query" > /dev/null 2>&1; then
                  echo "⚠ Q$query_num warm-up failed, skipping"
                  echo -e "Q$query_num\t0\t0\tFAILED" >> /tmp/results.csv
                  return
              fi
              
              # Single measurement run
              echo "  Measurement run..."
              local start=$(date +%s%3N)
              if mysql -h $FE_HOST -P 9030 -u root tpch -e "$query" > /dev/null 2>&1; then
                  local end=$(date +%s%3N)
                  local duration=$((end - start))
                  local duration_sec=$(echo "scale=3; $duration/1000" | bc)
                  
                  echo -e "Q$query_num\t$duration\t$duration_sec\tSUCCESS" >> /tmp/results.csv
                  echo "✓ Q$query_num completed: ${duration}ms (${duration_sec}s)"
              else
                  echo "⚠ Q$query_num failed"
                  echo -e "Q$query_num\t0\t0\tFAILED" >> /tmp/results.csv
              fi
              echo ""
          }
          
          # Run all 22 TPC-H queries
          for query_num in {1..22}; do
              run_query $query_num
          done
          
          # Calculate total time
          END_TIME=$(date +%s)
          TOTAL_BENCHMARK_TIME=$((END_TIME - START_TIME))
          
          echo ""
          echo "=========================================="
          echo "TPC-H 1TB Benchmark Results"
          echo "=========================================="
          
          # Display the results
          echo "Complete Results:"
          echo "================="
          cat /tmp/results.csv | column -t
          echo ""
          
          # Calculate summary statistics
          SUCCESSFUL_QUERIES=$(awk -F'\t' 'NR>1 && $4=="SUCCESS" {count++} END {print count+0}' /tmp/results.csv)
          FAILED_QUERIES=$(awk -F'\t' 'NR>1 && $4=="FAILED" {count++} END {print count+0}' /tmp/results.csv)
          SKIPPED_QUERIES=$(awk -F'\t' 'NR>1 && $4=="SKIPPED" {count++} END {print count+0}' /tmp/results.csv)
          
          TOTAL_QUERY_TIME=$(awk -F'\t' 'NR>1 && $4=="SUCCESS" {sum+=$2} END {print sum+0}' /tmp/results.csv)
          
          echo "Performance Summary:"
          echo "==================="
          echo "Successful queries: $SUCCESSFUL_QUERIES/22"
          echo "Failed queries: $FAILED_QUERIES"
          echo "Skipped queries: $SKIPPED_QUERIES"
          echo "Total successful query time: ${TOTAL_QUERY_TIME} ms"
          echo "Total successful query time: $(echo "scale=2; $TOTAL_QUERY_TIME/1000" | bc) seconds"
          if [ "$SUCCESSFUL_QUERIES" -gt 0 ]; then
              AVG_QUERY_TIME=$(echo "scale=2; $TOTAL_QUERY_TIME/$SUCCESSFUL_QUERIES" | bc)
              echo "Average time per successful query: ${AVG_QUERY_TIME} ms"
          fi
          echo "Total benchmark duration: ${TOTAL_BENCHMARK_TIME} seconds"
          echo "Benchmark completed at: $(date)"
          
          # Show successful queries sorted by time
          echo ""
          echo "Successful Queries (sorted by time):"
          echo "===================================="
          awk -F'\t' 'NR>1 && $4=="SUCCESS" {print $1 "\t" $2 "ms\t" $3 "s"}' /tmp/results.csv | sort -k2 -nr | column -t
          
          # Save results with timestamp
          TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
          cp /tmp/results.csv "/tmp/tpch_1tb_benchmark_results_${TIMESTAMP}.csv"
          echo ""
          echo "Results saved to: /tmp/tpch_1tb_benchmark_results_${TIMESTAMP}.csv"
          
          # Show final system resources
          echo ""
          echo "=========================================="
          echo "System Resources After Benchmark"
          echo "=========================================="
          echo "Memory usage:"
          if command -v free >/dev/null 2>&1; then
              free -h
          else
              cat /proc/meminfo | head -3
          fi
          
          echo ""
          echo "=========================================="
          echo "TPC-H 1TB Benchmark Completed!"
          echo "Scale Factor: 1000 (1TB)"
          echo "Successful Queries: $SUCCESSFUL_QUERIES/22"
          echo "Total Time: ${TOTAL_BENCHMARK_TIME} seconds"
          echo "=========================================="
          
          # Exit successfully even if some queries failed
          exit 0
          
        resources:
          requests:
            cpu: "2"
            memory: "4Gi"
          limits:
            cpu: "4"
            memory: "8Gi"
      restartPolicy: Never
      nodeSelector:
        kubernetes.io/arch: amd64
  backoffLimit: 1