#!/bin/bash # ============================================================================= # INDEX PREFETCH ADVERSARIAL BENCHMARK RUNNER # ============================================================================= # Usage: # ./run_benchmarks.sh setup # Create test tables # ./run_benchmarks.sh run baseline # Run benchmarks, save as baseline # ./run_benchmarks.sh run patched # Run benchmarks, save as patched # ./run_benchmarks.sh compare # Compare baseline vs patched # ./run_benchmarks.sh pgbench baseline # Run pgbench tests as baseline # ./run_benchmarks.sh pgbench patched # Run pgbench tests as patched # ./run_benchmarks.sh cold baseline # Run with cold cache # ============================================================================= set -e SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)" RESULTS_DIR="${SCRIPT_DIR}/results" DB="${PGDATABASE:-postgres}" PGBENCH_TIME="${PGBENCH_TIME:-60}" PGBENCH_CLIENTS="${PGBENCH_CLIENTS:-4}" mkdir -p "${RESULTS_DIR}" drop_caches() { echo "Skip dropping OS caches (requires sudo)..." } restart_postgres() { echo "Restarting PostgreSQL to clear shared buffers..." pg_ctl restart -D "${PGDATA}" -w -m fast 2>/dev/null || \ echo "Warning: Could not restart PostgreSQL automatically" } case "$1" in setup) echo "Setting up adversarial benchmark tables..." psql -d "${DB}" -f "${SCRIPT_DIR}/adversarial_benchmarks.sql" 2>&1 | \ tee "${RESULTS_DIR}/setup.log" echo "Setup complete. Tables created in database '${DB}'" ;; run) TAG="${2:-$(date +%Y%m%d_%H%M%S)}" OUTFILE="${RESULTS_DIR}/benchmark_${TAG}.log" echo "Running benchmarks, saving to ${OUTFILE}..." echo "=== Benchmark run: ${TAG} ===" > "${OUTFILE}" echo "Date: $(date)" >> "${OUTFILE}" echo "PostgreSQL version:" >> "${OUTFILE}" psql -d "${DB}" -c "SELECT version();" >> "${OUTFILE}" 2>&1 echo "" >> "${OUTFILE}" # Run the main benchmark queries psql -d "${DB}" -f "${SCRIPT_DIR}/adversarial_benchmarks.sql" >> "${OUTFILE}" 2>&1 echo "Benchmark complete. Results saved to ${OUTFILE}" ;; cold) TAG="${2:-cold_$(date +%Y%m%d_%H%M%S)}" OUTFILE="${RESULTS_DIR}/benchmark_${TAG}.log" echo "Running COLD CACHE benchmarks..." echo "This will restart PostgreSQL and drop OS caches." # Drop OS caches drop_caches # Restart PostgreSQL to clear shared buffers restart_postgres sleep 2 echo "Running benchmarks with cold cache, saving to ${OUTFILE}..." echo "=== COLD CACHE Benchmark run: ${TAG} ===" > "${OUTFILE}" echo "Date: $(date)" >> "${OUTFILE}" psql -d "${DB}" -c "SELECT version();" >> "${OUTFILE}" 2>&1 echo "" >> "${OUTFILE}" psql -d "${DB}" -f "${SCRIPT_DIR}/adversarial_benchmarks.sql" >> "${OUTFILE}" 2>&1 echo "Cold cache benchmark complete. Results saved to ${OUTFILE}" ;; pgbench) TAG="${2:-$(date +%Y%m%d_%H%M%S)}" echo "Running pgbench adversarial tests..." # Test 1: Short query overhead echo "=== Short Query Overhead Test ===" OUTFILE="${RESULTS_DIR}/pgbench_short_${TAG}.log" echo "Running pgbench short queries for ${PGBENCH_TIME}s..." pgbench -d "${DB}" \ -f "${SCRIPT_DIR}/pgbench_short_queries.sql" \ -c "${PGBENCH_CLIENTS}" -j 2 \ -T "${PGBENCH_TIME}" \ -P 10 \ 2>&1 | tee "${OUTFILE}" # Test 2: Mixed hot/cold pattern echo "" echo "=== Mixed Access Pattern Test ===" OUTFILE="${RESULTS_DIR}/pgbench_mixed_${TAG}.log" echo "Running pgbench mixed pattern for ${PGBENCH_TIME}s..." pgbench -d "${DB}" \ -f "${SCRIPT_DIR}/pgbench_mixed_pattern.sql" \ -c "${PGBENCH_CLIENTS}" -j 2 \ -T "${PGBENCH_TIME}" \ -P 10 \ 2>&1 | tee "${OUTFILE}" # Test 3: LIMIT waste echo "" echo "=== LIMIT Waste Test ===" OUTFILE="${RESULTS_DIR}/pgbench_limit_${TAG}.log" echo "Running pgbench limit waste for ${PGBENCH_TIME}s..." pgbench -d "${DB}" \ -f "${SCRIPT_DIR}/pgbench_limit_waste.sql" \ -c "${PGBENCH_CLIENTS}" -j 2 \ -T "${PGBENCH_TIME}" \ -P 10 \ 2>&1 | tee "${OUTFILE}" echo "" echo "pgbench tests complete. Results in ${RESULTS_DIR}/pgbench_*_${TAG}.log" ;; compare) if [ ! -f "${RESULTS_DIR}/benchmark_baseline.log" ] || \ [ ! -f "${RESULTS_DIR}/benchmark_patched.log" ]; then echo "Error: Need both baseline and patched results." echo "Run: $0 run baseline" echo " $0 run patched" exit 1 fi echo "=== COMPARISON: baseline vs patched ===" echo "" # Extract timing information echo "--- Execution Times (ms) ---" echo "Query | Baseline | Patched | Delta" echo "------|----------|---------|------" # Simple grep for "Time:" lines and compare paste <(grep -E "Time:|Execution Time:" "${RESULTS_DIR}/benchmark_baseline.log" | head -20) \ <(grep -E "Time:|Execution Time:" "${RESULTS_DIR}/benchmark_patched.log" | head -20) | \ awk -F'\t' '{print NR, $1, $2}' echo "" echo "--- Buffer Statistics ---" echo "Baseline shared hits:" grep -o "shared hit=[0-9]*" "${RESULTS_DIR}/benchmark_baseline.log" | \ awk -F= '{sum+=$2} END {print " Total:", sum}' echo "Patched shared hits:" grep -o "shared hit=[0-9]*" "${RESULTS_DIR}/benchmark_patched.log" | \ awk -F= '{sum+=$2} END {print " Total:", sum}' echo "" echo "Baseline shared reads:" grep -o "shared read=[0-9]*" "${RESULTS_DIR}/benchmark_baseline.log" | \ awk -F= '{sum+=$2} END {print " Total:", sum}' echo "Patched shared reads:" grep -o "shared read=[0-9]*" "${RESULTS_DIR}/benchmark_patched.log" | \ awk -F= '{sum+=$2} END {print " Total:", sum}' echo "" echo "For detailed analysis, compare the full log files manually." ;; *) echo "Usage: $0 {setup|run|cold|pgbench|compare} [tag]" echo "" echo "Commands:" echo " setup Create test tables in database" echo " run Run benchmarks, save with tag (e.g., 'baseline', 'patched')" echo " cold Run with cold cache (restarts PG, drops OS cache)" echo " pgbench Run pgbench stress tests" echo " compare Compare baseline vs patched results" echo "" echo "Environment variables:" echo " PGDATABASE Database to use (default: postgres)" echo " PGDATA Data directory for pg_ctl (for cold cache tests)" echo " PGBENCH_TIME pgbench duration in seconds (default: 60)" echo " PGBENCH_CLIENTS pgbench client count (default: 4)" exit 1 ;; esac