← Back to DevBytes
Docker July 07, 2026 12 min read

Docker Database Performance: Profiling and Optimization

Understanding Docker Database Performance Profiling

Docker database performance profiling is the systematic process of measuring, analyzing, and understanding how your containerized database instances consume resources and handle workloads. It encompasses monitoring CPU utilization, memory consumption, disk I/O patterns, network latency, and query execution characteristics within the containerized environment. Unlike bare-metal or VM-based databases, Docker adds an abstraction layer that introduces unique performance considerations—shared kernel resources, overlay filesystem overhead, and cgroup constraints that directly impact database throughput.

Profiling in this context means gathering granular metrics from both the container runtime and the database engine itself. You collect data from docker stats, cgroup metrics, database-specific performance views, and external monitoring tools to build a holistic picture of performance. Optimization then translates these insights into actionable changes: adjusting container resource limits, tuning database configuration parameters, restructuring volumes, or rewriting inefficient queries.

Key Metrics to Profile

When profiling a Dockerized database, focus on these critical dimensions:

Why Profiling and Optimization Matter in Docker

🚀 Deploy your AI agent in 10 minutes

Managed Hermes hosting. Zero DevOps. 100M tokens/mo included.

Try it free →

Databases are stateful, resource-intensive workloads that container orchestration platforms were not originally designed to handle. A poorly profiled database container can exhibit performance degradation that cascades across microservices. The most common pain points include:

Profiling catches these issues before they become production incidents. It also enables right-sizing—ensuring you don't over-provision expensive cloud resources while maintaining performance SLAs. For teams running PostgreSQL, MySQL, MongoDB, or Elasticsearch in Docker, systematic profiling is the difference between "it works" and "it works reliably at scale."

Setting Up a Profiling Environment

Let's walk through building a complete profiling stack using open-source tools. We'll profile a PostgreSQL container, but the techniques apply across database engines.

Step 1: Launch the Database with Instrumentation

# Create a dedicated volume to avoid overlay filesystem overhead
docker volume create pgdata

# Run PostgreSQL with resource limits and exposed metrics endpoint
docker run -d \
  --name pg-profiling \
  --memory="2g" \
  --cpus="2" \
  --mount source=pgdata,target=/var/lib/postgresql/data \
  -e POSTGRES_PASSWORD=securepass \
  -e POSTGRES_INITDB_ARGS="--data-checksums" \
  -p 5432:5432 \
  postgres:16 \
  -c shared_preload_libraries='pg_stat_statements' \
  -c track_activity_query_size=2048 \
  -c pg_stat_statements.track=all

# Verify the container is running with defined limits
docker inspect pg-profiling | jq '.[0].HostConfig.Memory, .[0].HostConfig.CpuShares'

Step 2: Enable Database-Side Profiling Extensions

Connect to the container and activate detailed query profiling:

# Enter the container
docker exec -it pg-profiling bash

# Inside the container, connect to PostgreSQL
psql -U postgres

-- Enable pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Reset statistics for a clean baseline
SELECT pg_stat_statements_reset();

-- Verify the extension is capturing queries
SELECT query_count, query FROM pg_stat_statements ORDER BY calls DESC LIMIT 5;

-- Enable slow query logging with auto_explain
ALTER SYSTEM SET log_min_duration_statement = 50; -- log queries > 50ms
ALTER SYSTEM SET auto_explain.log_min_duration = 50;
ALTER SYSTEM SET auto_explain.log_analyze = true;
ALTER SYSTEM SET auto_explain.log_buffers = true;

-- Reload configuration
SELECT pg_reload_conf();

Step 3: Collect Container-Level Metrics with cAdvisor and Prometheus

Deploy a lightweight monitoring stack alongside your database container:

# Create a docker-compose.yaml for the monitoring stack
cat < docker-compose.yaml
version: '3.8'
services:
  cadvisor:
    image: gcr.io/cadvisor/cadvisor:v0.47.2
    container_name: cadvisor
    volumes:
      - /:/rootfs:ro
      - /var/run:/var/run:ro
      - /sys:/sys:ro
      - /var/lib/docker/:/var/lib/docker:ro
    ports:
      - "8080:8080"
    privileged: true
  
  prometheus:
    image: prom/prometheus:v2.50.0
    container_name: prometheus
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    ports:
      - "9090:9090"
EOF

# Create Prometheus configuration to scrape cAdvisor
cat < prometheus.yml
global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'cadvisor'
    static_configs:
      - targets: ['cadvisor:8080']
  
  - job_name: 'postgresql'
    static_configs:
      - targets: ['pg-exporter:9187']
EOF

# Start the monitoring stack
docker compose up -d

Step 4: Generate Representative Load for Profiling

Use a benchmarking tool to create realistic database load:

# Run pgbench inside the PostgreSQL container
docker exec -it pg-profiling bash

# Initialize benchmark tables (scale factor 50 = ~1GB data)
pgbench -i -s 50 -U postgres postgres

# Run a mixed workload benchmark for 300 seconds
pgbench -c 20 -j 4 -T 300 -P 10 -U postgres postgres

# While benchmark runs, capture container stats from another terminal
# On the host machine:
watch -n 2 'docker stats --no-stream pg-profiling'

Analyzing Profiling Data

Container Resource Profiling

Use docker stats with structured output to capture time-series data:

# Collect 60 samples at 2-second intervals
for i in $(seq 1 60); do
  docker stats --no-stream --format "{{.Name}},{{.CPUPerc}},{{.MemUsage}},{{.MemPerc}},{{.BlockIO}},{{.PIDs}}" pg-profiling >> stats.csv
  sleep 2
done

# Analyze the CSV to detect patterns
cat stats.csv | awk -F',' '{print $2}' | sed 's/%//' | \
  sort -n | awk '{sum+=$1; count+=1} END {print "Avg CPU:", sum/count "%"}'

For deeper kernel-level profiling, inspect cgroup metrics directly:

# Find the container's cgroup path
CONTAINER_ID=$(docker inspect pg-profiling | jq -r '.[0].Id' | cut -c1-12)
CGROUP_PATH=$(find /sys/fs/cgroup -name "*${CONTAINER_ID}*" -type d | head -1)

# Check CPU throttling statistics
cat ${CGROUP_PATH}/cpu.stat
# Look for nr_throttled > 0 indicating CPU limit breaches

# Check memory pressure details  
cat ${CGROUP_PATH}/memory.stat | grep -E "pgfault|pgmajfault|swap"
# High pgmajfault values indicate major page faults and potential memory shortage

# Check I/O pressure
cat ${CGROUP_PATH}/io.stat
# High "cost" values indicate I/O contention

Database Query Profiling

Extract actionable query performance data from PostgreSQL's internal views:

-- Top 10 queries by total execution time
SELECT 
  queryid,
  LEFT(query, 100) AS query_preview,
  calls,
  total_exec_time / 1000 AS total_seconds,
  mean_exec_time AS avg_ms,
  rows,
  shared_blks_hit + shared_blks_read AS total_blocks,
  ROUND(shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 1) AS cache_hit_pct
FROM pg_stat_statements
WHERE queryid != 0
ORDER BY total_exec_time DESC
LIMIT 10;

-- Identify queries with poor buffer cache efficiency
SELECT 
  queryid,
  LEFT(query, 80) AS query_preview,
  calls,
  shared_blks_read,
  shared_blks_hit,
  CASE WHEN shared_blks_read > 0 
    THEN ROUND(shared_blks_hit::numeric / (shared_blks_hit + shared_blks_read) * 100, 1)
    ELSE 100 
  END AS hit_ratio
FROM pg_stat_statements
WHERE calls > 10 AND shared_blks_read > 1000
ORDER BY hit_ratio ASC;

-- Check for lock contention patterns
SELECT 
  blocked_locks.pid AS blocked_pid,
  blocking_locks.pid AS blocking_pid,
  blocked_activity.query AS blocked_query,
  blocking_activity.query AS blocking_query,
  blocked_locks.mode AS lock_mode,
  age(now(), blocked_activity.query_start) AS blocked_duration
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid
JOIN pg_locks blocking_locks ON blocked_locks.lock_type = blocking_locks.lock_type
  AND blocked_locks.database = blocking_locks.database
  AND blocked_locks.relation = blocking_locks.relation
  AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted
  AND blocking_locks.granted;

I/O Profiling with fio Inside the Container

Test filesystem performance characteristics specific to your volume configuration:

# Install fio inside the container
docker exec -it pg-profiling bash
apt-get update && apt-get install -y fio

# Run random read/write benchmark on the data directory
fio --name=pg-iops-test \
  --directory=/var/lib/postgresql/data \
  --size=1G \
  --rw=randrw \
  --bs=8k \
  --direct=1 \
  --numjobs=4 \
  --iodepth=32 \
  --runtime=60 \
  --time_based \
  --group_reporting \
  --output-format=json > /tmp/fio_results.json

# Parse results for latency percentiles
cat /tmp/fio_results.json | jq '.jobs[0].read.lat_ns.percentile | {
  p50: .["50.000000"]/1000000, 
  p95: .["95.000000"]/1000000, 
  p99: .["99.000000"]/1000000
}'

Optimization Strategies Based on Profiling Results

Optimization 1: Tune Database Configuration for Container Constraints

Based on profiling data showing memory pressure or cache inefficiency:

# Example: Optimize PostgreSQL for a 2GB container
docker exec -it pg-profiling bash

cat <> /var/lib/postgresql/data/postgresql.conf
# Derived from profiling: container has 2GB RAM, 2 CPUs
shared_buffers = '512MB'          # 25% of container memory
effective_cache_size = '1536MB'   # 75% of container memory
maintenance_work_mem = '128MB'    # For VACUUM operations
work_mem = '16MB'                 # Per-operation sort memory
wal_buffers = '16MB'             # Write-ahead log buffers
random_page_cost = 1.1           # Lower if SSD-backed volume detected
effective_io_concurrency = 200   # Higher for NVMe/SSD volumes
max_worker_processes = 4         # Match CPU count
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

# Checkpoint tuning to reduce I/O spikes
checkpoint_timeout = '15min'
max_wal_size = '2GB'
min_wal_size = '512MB'
checkpoint_completion_target = 0.9
EOF

# Restart PostgreSQL to apply changes
pg_ctl restart -D /var/lib/postgresql/data

Optimization 2: Switch Volume Driver for I/O-Bound Workloads

If profiling reveals high I/O latency, migrate from overlay2-backed volumes to local-persistent volumes or tmpfs for specific paths:

# Create a local-persistent volume with direct block device access
docker volume create \
  --driver local \
  --opt type=none \
  --opt device=/mnt/fast-ssd/pgdata \
  --opt o=bind \
  pg-fast-volume

# Alternatively, mount critical WAL directory on tmpfs for latency reduction
docker run -d \
  --name pg-optimized \
  --memory="4g" \
  --cpus="4" \
  --mount source=pg-fast-volume,target=/var/lib/postgresql/data \
  --mount type=tmpfs,destination=/var/lib/postgresql/data/pg_stat_tmp,tmpfs-size=256M \
  --mount type=tmpfs,destination=/var/lib/postgresql/data/pg_wal,tmpfs-size=512M \
  postgres:16 \
  -c shared_preload_libraries='pg_stat_statements'

# Verify I/O improvement with another fio run
docker exec -it pg-optimized fio --directory=/var/lib/postgresql/data \
  --name=verify-io --size=500M --rw=randread --bs=8k --direct=1 \
  --runtime=30 --time_based --output-format=json | \
  jq '.jobs[0].read.lat_ns.percentile["95.000000"]/1000000'

Optimization 3: Connection Pooling Layer

If profiling shows connection overhead dominating query latency:

# Deploy PgBouncer as a connection pooling sidecar
docker run -d \
  --name pgbouncer \
  --network container:pg-profiling \
  -e DB_USER=postgres \
  -e DB_PASSWORD=securepass \
  -e DB_HOST=localhost \
  -e DB_PORT=5432 \
  -e POOL_MODE=transaction \
  -e MAX_CLIENT_CONN=200 \
  -e DEFAULT_POOL_SIZE=25 \
  -e RESERVE_POOL_SIZE=5 \
  pgbouncer/pgbouncer:latest

# Benchmark with connection pooling
pgbench -c 50 -j 8 -T 120 -p 6432 -U postgres postgres

# Compare latency percentiles before and after pooling
# Query pg_stat_statements to verify reduced connection overhead
SELECT total_exec_time, calls, query 
FROM pg_stat_statements 
WHERE query LIKE '%connection%' OR query LIKE '%authentication%'
ORDER BY total_exec_time DESC;

Optimization 4: CPU Affinity and NUMA Awareness

For large database containers on multi-socket hosts, pin CPU cores to avoid NUMA node crossing:

# Pin the container to specific CPU cores on the same NUMA node
# First, identify NUMA topology
lscpu | grep "NUMA node"
numactl --hardware

# Run container pinned to NUMA node 0 CPUs (e.g., cores 0-7)
docker run -d \
  --name pg-numa-pinned \
  --cpuset-cpus="0-7" \
  --memory="8g" \
  --cpus="8" \
  --mount source=pgdata,target=/var/lib/postgresql/data \
  postgres:16

# Verify CPU assignment
docker inspect pg-numa-pinned | jq '.[0].HostConfig.CpusetCpus'

# Profile again to compare cross-NUMA memory access latency
# Use perf stat inside the container to measure memory access patterns
docker exec -it pg-numa-pinned bash -c \
  "perf stat -e 'cpu/mem-loads/,cpu/mem-stores/' -p 1 sleep 30"

Optimization 5: Prewarm Buffer Cache After Restart

Profiling often reveals slow performance after container restarts due to cold caches:

# Install and configure pg_prewarm extension
docker exec -it pg-profiling bash
psql -U postgres -c "CREATE EXTENSION pg_prewarm;"

# Create a prewarm script that runs on container startup
cat <<'EOF' > /usr/local/bin/prewarm-cache.sh
#!/bin/bash
# Wait for PostgreSQL to be ready
until pg_isready -U postgres; do sleep 1; done

# Prewarm buffer cache with recently accessed blocks
psql -U postgres -c "
  SELECT pg_prewarm(relation::regclass, 'buffer') 
  FROM (
    SELECT DISTINCT relation 
    FROM pg_stat_all_tables 
    WHERE schemaname NOT IN ('pg_catalog','information_schema')
    ORDER BY pg_relation_size(relation) DESC 
    LIMIT 20
  ) t;
"
EOF

chmod +x /usr/local/bin/prewarm-cache.sh

# Update Docker entrypoint to include prewarm (custom Dockerfile approach)
cat <<'DOCKERFILE' > Dockerfile.prewarm
FROM postgres:16
COPY prewarm-cache.sh /docker-entrypoint-initdb.d/99-prewarm.sh
RUN chmod +x /docker-entrypoint-initdb.d/99-prewarm.sh
DOCKERFILE

docker build -t postgres-prewarm:16 -f Dockerfile.prewarm .
docker run -d --name pg-prewarmed --memory="2g" --cpus="2" \
  --mount source=pgdata,target=/var/lib/postgresql/data \
  postgres-prewarm:16

Continuous Profiling Pipeline

Embed profiling into your CI/CD and production monitoring workflows:

# Script for automated profiling in CI pipelines
cat <<'SCRIPT' > profile-db.sh
#!/bin/bash
set -e

DB_CONTAINER="pg-profiling"
DURATION_SECONDS=300
OUTPUT_DIR="profiling-reports/$(date +%Y%m%d-%H%M%S)"
mkdir -p "$OUTPUT_DIR"

# 1. Capture container stats
docker stats --no-stream --format "{{.Name}},{{.CPUPerc}},{{.MemUsage}},{{.BlockIO}}" \
  "$DB_CONTAINER" > "$OUTPUT_DIR/container_stats.csv" &
STATS_PID=$!

# 2. Run benchmark workload
docker exec "$DB_CONTAINER" pgbench -c 10 -j 4 -T "$DURATION_SECONDS" -U postgres postgres \
  > "$OUTPUT_DIR/pgbench_results.txt" 2>&1

kill $STATS_PID 2>/dev/null || true

# 3. Snapshot pg_stat_statements
docker exec "$DB_CONTAINER" psql -U postgres -c "
  COPY (SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20) 
  TO STDOUT WITH CSV HEADER;
" > "$OUTPUT_DIR/top_queries.csv"

# 4. Snapshot lock activity
docker exec "$DB_CONTAINER" psql -U postgres -c "
  COPY (SELECT pid, state, wait_event_type, wait_event, query_start, left(query,200) 
  FROM pg_stat_activity WHERE state != 'idle' ORDER BY query_start) 
  TO STDOUT WITH CSV HEADER;
" > "$OUTPUT_DIR/active_sessions.csv"

# 5. Generate summary report
echo "=== Profiling Summary ===" > "$OUTPUT_DIR/summary.txt"
echo "Date: $(date)" >> "$OUTPUT_DIR/summary.txt"

# Extract average CPU from stats
AVG_CPU=$(awk -F',' '{gsub(/%/,"",$2); sum+=$2; count+=1} END {printf "%.1f", sum/count}' \
  "$OUTPUT_DIR/container_stats.csv")
echo "Average CPU Utilization: ${AVG_CPU}%" >> "$OUTPUT_DIR/summary.txt"

# Extract transactions per second from pgbench
TPS=$(grep "tps" "$OUTPUT_DIR/pgbench_results.txt" | tail -1 | \
  awk '{print $3}' | sed 's/[^0-9.]//g')
echo "Transactions Per Second: ${TPS}" >> "$OUTPUT_DIR/summary.txt"

# Check for CPU throttling
THROTTLED=$(docker inspect "$DB_CONTAINER" | \
  jq '.[0].State.CPUUsageStats.throttling_data.throttled_periods')
echo "CPU Throttled Periods: ${THROTTLED}" >> "$OUTPUT_DIR/summary.txt"

echo "Report saved to: $OUTPUT_DIR" >> "$OUTPUT_DIR/summary.txt"
cat "$OUTPUT_DIR/summary.txt"
SCRIPT

chmod +x profile-db.sh
./profile-db.sh

Best Practices for Docker Database Performance

Conclusion

Docker database performance profiling and optimization is not a one-time activity—it's an ongoing discipline that combines container runtime metrics with database engine internals. The profiling techniques covered here—from cgroup inspection and fio benchmarking to pg_stat_statements analysis and continuous pipeline automation—give you a complete toolkit for identifying bottlenecks before they impact users. The optimization strategies, including volume driver selection, configuration tuning, connection pooling, and CPU pinning, translate raw profiling data into measurable throughput gains. By embedding these practices into your development and operations workflows, you ensure that containerized databases perform predictably, scale efficiently, and remain cost-effective in production environments. Start profiling today: run docker stats, activate your database's performance views, and establish your baseline—every optimization journey begins with accurate measurement.

🚀 Need a reliable AI agent for your project?

Deploy Hermes Agent in 10 minutes. Managed hosting, zero DevOps.

Get Started — $23.99/mo
← Back to all articles