run_benchmarks.sh
text/x-sh
#!/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 <tag> Run benchmarks, save with tag (e.g., 'baseline', 'patched')"
echo " cold <tag> Run with cold cache (restarts PG, drops OS cache)"
echo " pgbench <tag> 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