Database Benchmarking with pgbench and mysqlslap

Database performance validation requires tools specifically designed to generate realistic transactional workloads and measure system response. pgbench for PostgreSQL and mysqlslap for MySQL provide industry-standard methods for database benchmarking, enabling capacity planning and performance optimization. This guide covers deploying both tools for comprehensive database performance analysis.

Table of Contents

  1. pgbench for PostgreSQL
  2. pgbench Workload Configuration
  3. mysqlslap for MySQL
  4. Custom SQL Benchmarking
  5. Results Interpretation
  6. Performance Tuning Validation
  7. Comparative Benchmarking
  8. Conclusion

pgbench for PostgreSQL

pgbench Installation and Setup

# pgbench comes with PostgreSQL
psql --version

# Initialize pgbench database
createdb pgbench

# Generate test data (scale factor 10 = 100,000 rows)
pgbench -i -s 10 pgbench

# Parameters:
# -i: Initialize (prepare test tables)
# -s: Scale factor (determines data volume)

# Check generated tables
psql pgbench -c "SELECT schemaname, tablename FROM pg_tables WHERE schemaname='public';"

# View table sizes
psql pgbench -c "SELECT tablename, pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) FROM pg_tables WHERE schemaname='public';"

Basic pgbench Test

# Run basic OLTP benchmark (10 seconds)
pgbench -r pgbench

# Standard output shows:
# TPS (Transactions Per Second)
# Average latency
# 90th percentile latency

# Extended test parameters
pgbench -c 10 -j 4 -T 60 pgbench

# Parameters explained:
# -c 10: 10 concurrent clients
# -j 4: 4 threads (one per connection)
# -T 60: 60-second test duration

# View transaction rates
pgbench -r -R 1000 pgbench  # Rate-limited to 1000 TPS

pgbench Scaling Tests

# Test various client counts
for clients in 1 5 10 20 50; do
  echo "=== $clients clients ==="
  pgbench -c $clients -j $((clients/2)) -T 30 pgbench
done

# Test with larger dataset
pgbench -i -s 100 pgbench  # 1,000,000 rows

pgbench -c 20 -j 4 -T 60 pgbench

# Monitor during test
# In parallel terminal:
watch -n 1 'psql pgbench -c "SELECT count_est FROM pgbench_stats;"'

pgbench Workload Configuration

Custom pgbench Workload

# Create custom SQL script
cat > ~/pgbench_custom.sql <<'EOF'
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)

BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
COMMIT;
EOF

# Run custom workload
pgbench -f ~/pgbench_custom.sql -c 10 -j 4 -T 60 pgbench

Read-Heavy Workload

# Create read-only test script
cat > ~/pgbench_readonly.sql <<'EOF'
\set aid random(1, 100000 * :scale)
BEGIN;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
SELECT * FROM pgbench_branches WHERE bid = :aid % 100;
COMMIT;
EOF

# Run read workload
pgbench -f ~/pgbench_readonly.sql -c 20 -j 4 -T 60 -r pgbench

mysqlslap for MySQL

mysqlslap Installation and Setup

# mysqlslap comes with MySQL client tools
mysql --version

# Basic auto-generated test
mysqlslap --auto-generate-sql --concurrency=10 --number-of-queries=1000

# Connect to specific MySQL server
mysqlslap -h localhost -u root -p --auto-generate-sql --concurrency=10

# Parameters:
# --auto-generate-sql: Generate random SQL
# --concurrency: Number of clients
# --number-of-queries: Total queries to run

mysqlslap Workload Generation

# Generate comprehensive workload
mysqlslap \
  --auto-generate-sql \
  --auto-generate-sql-load-type=mixed \
  --concurrency=5,10,15 \
  --number-of-queries=5000 \
  --engine=InnoDB \
  -u root -p

# Workload types:
# read: SELECT queries only
# write: INSERT/UPDATE/DELETE
# key: UPDATE with WHERE clause
# mixed: Combination of all types

# Preserve test data between runs
mysqlslap \
  --create-schema=testdb \
  --auto-generate-sql \
  --preserve-schema-on-disconnect \
  --concurrency=10 \
  --number-of-queries=10000 \
  -u root -p

Custom mysqlslap Queries

# Create custom SQL script
cat > ~/mysql_test.sql <<'EOF'
SELECT * FROM test_table WHERE id = @id;
INSERT INTO test_table (name, value) VALUES ('test', RAND());
UPDATE test_table SET value = RAND() WHERE id = @id;
DELETE FROM test_table WHERE id < 100;
EOF

# Run custom test
mysqlslap \
  --concurrency=10 \
  --iterations=10 \
  --query=~/mysql_test.sql \
  --create-schema=testdb \
  -u root -p

# Multiple query files
mysqlslap \
  --concurrency=10 \
  --query=~/read_queries.sql \
  --query=~/write_queries.sql \
  -u root -p

Custom SQL Benchmarking

Database-Specific Optimization Testing

# PostgreSQL: Test query plan changes
cat > test_query_performance.sql <<'EOF'
EXPLAIN ANALYZE
SELECT * FROM large_table WHERE indexed_column = 'value';

-- After creating index
CREATE INDEX idx_indexed_column ON large_table(indexed_column);

EXPLAIN ANALYZE
SELECT * FROM large_table WHERE indexed_column = 'value';
EOF

# Run before and after index creation
pgbench -f test_query_performance.sql pgbench

# MySQL: Test with different engines
cat > test_engine_performance.sql <<'EOF'
ALTER TABLE test_table ENGINE=InnoDB;
-- Run benchmark
-- Then:
ALTER TABLE test_table ENGINE=MyISAM;
-- Run again for comparison
EOF

Results Interpretation

Understanding TPS Metrics

# pgbench output example:
# transaction type: TPC-B (sort of)
# scaling factor: 10
# number of clients: 10
# number of threads: 4
# number of transactions skipped: 0
# number of transactions processed: 6000
# number of failed transactions: 0
# latency average = 1.667 ms
# latency stddev = 2.551 ms
# latency min = 0.495 ms
# latency max = 45.829 ms
# latency p50 = 1.285 ms
# latency p95 = 3.456 ms
# latency p99 = 7.890 ms
# tps = 599.88 (including connections establishing)
# tps = 600.05 (excluding connections establishing)

# Interpretation:
# Higher TPS = better throughput
# Lower latency = better responsiveness
# Consistent latency = stable performance

Latency Analysis

# Create script to analyze latency progression
cat > analyze_latency_trend.sh <<'EOF'
#!/bin/bash
echo "time,avg_latency,p95_latency,p99_latency" > latency_trend.csv

for iteration in {1..10}; do
  sleep 60  # Let system stabilize
  result=$(pgbench -c 20 -j 4 -T 60 pgbench 2>&1)
  
  avg=$(echo "$result" | grep "average" | awk '{print $NF}')
  p95=$(echo "$result" | grep "p95" | awk '{print $NF}')
  p99=$(echo "$result" | grep "p99" | awk '{print $NF}')
  
  echo "$(date +%H:%M:%S),$avg,$p95,$p99" >> latency_trend.csv
done

# View results
column -t -s, latency_trend.csv
EOF

chmod +x analyze_latency_trend.sh
./analyze_latency_trend.sh

Performance Tuning Validation

Before/After Tuning Comparison

# Baseline test
pgbench -c 20 -j 4 -T 60 -r pgbench | tee baseline.txt

# Apply tuning (adjust postgresql.conf)
# shared_buffers = 256MB
# effective_cache_size = 4GB
# work_mem = 20MB

# Reload config
sudo systemctl restart postgresql

# Post-tuning test
pgbench -c 20 -j 4 -T 60 -r pgbench | tee after_tuning.txt

# Compare results
echo "=== TPS Comparison ==="
grep "tps =" baseline.txt
grep "tps =" after_tuning.txt

echo "=== Latency Comparison ==="
grep "latency average" baseline.txt
grep "latency average" after_tuning.txt

Configuration Impact Testing

# Test different cache settings
for shared_buffers in "64MB" "256MB" "1GB"; do
  # Update postgresql.conf
  sudo sed -i "s/^shared_buffers = .*/shared_buffers = $shared_buffers/" /etc/postgresql/*/main/postgresql.conf
  
  # Restart PostgreSQL
  sudo systemctl restart postgresql
  
  # Run test
  echo "=== Testing shared_buffers=$shared_buffers ==="
  pgbench -c 10 -j 4 -T 60 pgbench | grep "tps ="
done

Comparative Benchmarking

Database Comparison

# Compare PostgreSQL vs MySQL performance
echo "=== PostgreSQL Benchmark ==="
pgbench -i -s 50 pgbench
pgbench -c 20 -j 4 -T 60 -r pgbench

echo "=== MySQL Benchmark ==="
mysql -u root -p -e "CREATE DATABASE mysqlslap_test;"

mysqlslap \
  --auto-generate-sql \
  --concurrency=20 \
  --number-of-queries=100000 \
  --engine=InnoDB \
  -u root -p

Version Comparison

# Test different PostgreSQL versions
# Install multiple versions:
# PostgreSQL 12, 13, 14, 15

# For each version:
# Initialize cluster, run benchmark

echo "=== PostgreSQL 14 ==="
/usr/lib/postgresql/14/bin/pgbench -i -s 50 pgbench_14
/usr/lib/postgresql/14/bin/pgbench -c 20 -j 4 -T 60 pgbench_14 | grep "tps ="

echo "=== PostgreSQL 15 ==="
/usr/lib/postgresql/15/bin/pgbench -i -s 50 pgbench_15
/usr/lib/postgresql/15/bin/pgbench -c 20 -j 4 -T 60 pgbench_15 | grep "tps ="

Conclusion

pgbench and mysqlslap provide essential capabilities for database performance validation and optimization. By establishing baseline metrics before tuning, organizations quantify improvement from configuration changes and hardware upgrades. Understanding latency percentiles and transaction throughput enables informed decisions about capacity planning. Regular benchmarking detects performance degradation from aging hardware, fragmentation, or suboptimal configurations. Whether validating new database infrastructure, evaluating major version upgrades, or proving the value of tuning efforts, database benchmarking tools remain indispensable for database engineering excellence.