Benchmark: AWS Graviton5 vs. Intel Xeon 6th Gen for PostgreSQL 17 OLTP Workloads

# pgbench_runner.py: Automated PostgreSQL 17 OLTP benchmark runner for Graviton5 vs Xeon 6th Gen
# Author: Senior Engineer (15yr open-source contributor, InfoQ/ACM Queue writer)
# Requirements: boto3>=1.34.0, paramiko>=3.4.0, python>=3.11
import boto3
import paramiko
import time
import csv
import os
import sys
from datetime import datetime

# ----------------------------------------------------------------------
# Configuration - update these values before running
# ----------------------------------------------------------------------
AWS_REGION = \"us-east-1\"
AMI_ID_GRAVITON = \"ami-0a1b2c3d4e5f6g7h8\"  # Amazon Linux 2023 ARM64 AMI
AMI_ID_XEON = \"ami-1a2b3c4d5e6f7g8h9\"       # Amazon Linux 2023 x86-64 AMI
INSTANCE_TYPE_GRAVITON = \"r8g.16xlarge\"
INSTANCE_TYPE_XEON = \"r7i.16xlarge\"
KEY_NAME = \"pg-bench-key\"
SECURITY_GROUP_ID = \"sg-1234567890abcdef0\"
PG_VERSION = \"17.0\"
PG_BENCH_SCALE = 1000  # 100GB database
BENCH_DURATION = 1800  # 30 minutes per run
ITERATIONS = 3
RESULTS_FILE = f\"pgbench_results_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv\"

# ----------------------------------------------------------------------
# EC2 Helper Functions
# ----------------------------------------------------------------------
def launch_instance(ec2_client, instance_type, ami_id):
    \"\"\"Launch an EC2 instance with specified parameters, return instance ID or exit on failure\"\"\"
    try:
        response = ec2_client.run_instances(
            ImageId=ami_id,
            InstanceType=instance_type,
            KeyName=KEY_NAME,
            SecurityGroupIds=[SECURITY_GROUP_ID],
            MinCount=1,
            MaxCount=1,
            TagSpecifications=[{
                \"ResourceType\": \"instance\",
                \"Tags\": [{\"Key\": \"Name\", \"Value\": f\"pg-bench-{instance_type}\"}]
            }]
        )
        instance_id = response[\"Instances\"][0][\"InstanceId\"]
        print(f\"[INFO] Launched instance {instance_id} ({instance_type})\")
        return instance_id
    except Exception as e:
        print(f\"[ERROR] Failed to launch instance {instance_type}: {str(e)}\")
        sys.exit(1)

def wait_for_instance_running(ec2_client, instance_id):
    \"\"\"Wait for instance to reach running state, return public IP\"\"\"
    try:
        waiter = ec2_client.get_waiter(\"instance_running\")
        waiter.wait(InstanceIds=[instance_id])
        response = ec2_client.describe_instances(InstanceIds=[instance_id])
        public_ip = response[\"Reservations\"][0][\"Instances\"][0][\"PublicIpAddress\"]
        print(f\"[INFO] Instance {instance_id} running at {public_ip}\")
        return public_ip
    except Exception as e:
        print(f\"[ERROR] Failed to wait for instance {instance_id}: {str(e)}\")
        sys.exit(1)

def get_ssh_client(public_ip, key_path=\"/home/ec2-user/.ssh/id_rsa\"):
    \"\"\"Initialize SSH client and connect to instance, return client or exit on failure\"\"\"
    try:
        ssh = paramiko.SSHClient()
        ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        ssh.connect(
            hostname=public_ip,
            username=\"ec2-user\",
            key_filename=key_path,
            timeout=30
        )
        print(f\"[INFO] SSH connected to {public_ip}\")
        return ssh
    except Exception as e:
        print(f\"[ERROR] SSH connection to {public_ip} failed: {str(e)}\")
        sys.exit(1)

# ----------------------------------------------------------------------
# PostgreSQL Setup and Benchmark Functions
# ----------------------------------------------------------------------
def setup_postgres(ssh_client, instance_type):
    \"\"\"Install and configure PostgreSQL 17 on target instance, return True on success\"\"\"
    arch = \"aarch64\" if \"graviton\" in instance_type.lower() else \"x86_64\"
    commands = [
        \"sudo dnf update -y\",
        f\"sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-{arch}/pgdg-redhat-repo-latest.noarch.rpm\",
        \"sudo dnf install -y postgresql17-server postgresql17-contrib\",
        \"sudo /usr/pgsql-17/bin/postgresql-17-setup initdb\",
        \"sudo systemctl enable --now postgresql-17\",
        \"sudo sed -i \\\"s/ident/md5/g\\\" /var/lib/pgsql/17/data/pg_hba.conf\",
        \"sudo systemctl restart postgresql-17\"
    ]
    for cmd in commands:
        stdin, stdout, stderr = ssh_client.exec_command(cmd)
        exit_code = stdout.channel.recv_exit_status()
        if exit_code != 0:
            print(f\"[ERROR] Command failed: {cmd}\\nStderr: {stderr.read().decode()}\")
            return False
    print(\"[INFO] PostgreSQL 17 installed and configured successfully\")
    return True

def run_pgbench(ssh_client, instance_type, iteration):
    \"\"\"Run pgbench read-heavy workload, return TPS or None on failure\"\"\"
    cmd = f\"pgbench -h localhost -U postgres -s {PG_BENCH_SCALE} -T {BENCH_DURATION} -r -j 64 -c 64\"
    stdin, stdout, stderr = ssh_client.exec_command(cmd)
    exit_code = stdout.channel.recv_exit_status()
    if exit_code != 0:
        print(f\"[ERROR] pgbench failed on iteration {iteration}: {stderr.read().decode()}\")
        return None
    output = stdout.read().decode()
    # Parse TPS from pgbench output
    for line in output.split(\"\\n\"):
        if \"tps\" in line.lower():
            tps = float(line.split(\" \")[2])
            print(f\"[INFO] Iteration {iteration} TPS: {tps}\")
            return tps
    print(f\"[ERROR] Could not parse TPS from pgbench output\")
    return None

# ----------------------------------------------------------------------
# Main Execution
# ----------------------------------------------------------------------
if __name__ == \"__main__\":
    ec2 = boto3.client(\"ec2\", region_name=AWS_REGION)
    # Launch both instances
    graviton_id = launch_instance(ec2, INSTANCE_TYPE_GRAVITON, AMI_ID_GRAVITON)
    xeon_id = launch_instance(ec2, INSTANCE_TYPE_XEON, AMI_ID_XEON)
    # Wait for instances to start
    graviton_ip = wait_for_instance_running(ec2, graviton_id)
    xeon_ip = wait_for_instance_running(ec2, xeon_id)
    # Setup PostgreSQL on both
    graviton_ssh = get_ssh_client(graviton_ip)
    xeon_ssh = get_ssh_client(xeon_ip)
    if not setup_postgres(graviton_ssh, INSTANCE_TYPE_GRAVITON):
        sys.exit(1)
    if not setup_postgres(xeon_ssh, INSTANCE_TYPE_XEON):
        sys.exit(1)
    # Run benchmarks and collect results
    results = []
    for i in range(ITERATIONS):
        print(f\"\\n[INFO] Starting iteration {i+1}/{ITERATIONS}\")
        g_tps = run_pgbench(graviton_ssh, INSTANCE_TYPE_GRAVITON, i+1)
        x_tps = run_pgbench(xeon_ssh, INSTANCE_TYPE_XEON, i+1)
        if g_tps and x_tps:
            results.append({\"iteration\": i+1, \"graviton_tps\": g_tps, \"xeon_tps\": x_tps})
    # Write results to CSV
    with open(RESULTS_FILE, \"w\", newline=\"\") as f:
        writer = csv.DictWriter(f, fieldnames=[\"iteration\", \"graviton_tps\", \"xeon_tps\"])
        writer.writeheader()
        writer.writerows(results)
    print(f\"\\n[INFO] Results written to {RESULTS_FILE}\")
    # Terminate instances
    ec2.terminate_instances(InstanceIds=[graviton_id, xeon_id])
    print(\"[INFO] Instances terminated\")

# postgres_tuner.py: Auto-tune PostgreSQL 17 for Graviton5 or Xeon 6th Gen
# Author: Senior Engineer (15yr exp)
# Requirements: python>=3.11, psycopg2>=2.9.9
import os
import sys
import platform
import subprocess
from pathlib import Path

# ----------------------------------------------------------------------
# Configuration Templates
# ----------------------------------------------------------------------
GRAVITON5_SETTINGS = {
    \"shared_buffers\": \"128GB\",
    \"work_mem\": \"16MB\",
    \"maintenance_work_mem\": \"2GB\",
    \"vacuum_cost_limit\": \"2000\",
    \"jit\": \"on\",
    \"jit_above_cost\": \"1000\",
    \"huge_pages\": \"on\",
    \"max_connections\": \"500\"
}

XEON6_SETTINGS = {
    \"shared_buffers\": \"96GB\",
    \"work_mem\": \"12MB\",
    \"maintenance_work_mem\": \"1GB\",
    \"vacuum_cost_limit\": \"1500\",
    \"jit\": \"on\",
    \"jit_above_cost\": \"500\",
    \"huge_pages\": \"on\",
    \"max_connections\": \"500\"
}

# ----------------------------------------------------------------------
# Helper Functions
# ----------------------------------------------------------------------
def detect_architecture():
    \"\"\"Detect if running on Graviton5 (ARM Neoverse V2) or Xeon 6th Gen (x86 Sapphire Rapids)\"\"\"
    machine = platform.machine()
    if machine == \"aarch64\":
        # Check for Neoverse V2 via /proc/cpuinfo
        with open(\"/proc/cpuinfo\", \"r\") as f:
            cpuinfo = f.read()
        if \"Neoverse-V2\" in cpuinfo:
            return \"graviton5\"
        else:
            print(f\"[WARNING] ARM64 detected but not Neoverse V2: {cpuinfo[:100]}\")
            return \"unknown_arm\"
    elif machine == \"x86_64\":
        # Check for Sapphire Rapids via lscpu
        try:
            lscpu = subprocess.check_output([\"lscpu\"]).decode()
            if \"Sapphire Rapids\" in lscpu or \"Xeon Scalable 6th Gen\" in lscpu:
                return \"xeon6\"
            else:
                print(f\"[WARNING] x86-64 detected but not Xeon 6th Gen: {lscpu[:100]}\")
                return \"unknown_x86\"
        except Exception as e:
            print(f\"[ERROR] Failed to run lscpu: {str(e)}\")
            return \"unknown\"
    else:
        print(f\"[ERROR] Unsupported architecture: {machine}\")
        return \"unknown\"

def get_postgres_config_path():
    \"\"\"Find PostgreSQL 17 config file path, return None if not found\"\"\"
    possible_paths = [
        Path(\"/var/lib/pgsql/17/data/postgresql.conf\"),
        Path(\"/etc/postgresql/17/main/postgresql.conf\")
    ]
    for path in possible_paths:
        if path.exists():
            return path
    print(\"[ERROR] Could not find postgresql.conf for PostgreSQL 17\")
    return None

def apply_settings(config_path, settings):
    \"\"\"Apply settings to postgresql.conf, backup original first\"\"\"
    backup_path = config_path.with_suffix(\".conf.bak\")
    # Backup original config
    if not backup_path.exists():
        try:
            import shutil
            shutil.copy(config_path, backup_path)
            print(f\"[INFO] Backed up config to {backup_path}\")
        except Exception as e:
            print(f\"[ERROR] Failed to backup config: {str(e)}\")
            return False
    # Apply settings
    with open(config_path, \"a\") as f:
        f.write(\"\\n# --- Auto-tuned settings for OLTP workloads ---\\n\")
        for key, value in settings.items():
            f.write(f\"{key} = {value}\\n\")
    print(f\"[INFO] Applied {len(settings)} settings to {config_path}\")
    return True

def restart_postgres():
    \"\"\"Restart PostgreSQL 17 service, return True on success\"\"\"
    try:
        subprocess.check_call([\"sudo\", \"systemctl\", \"restart\", \"postgresql-17\"])
        print(\"[INFO] PostgreSQL 17 restarted successfully\")
        return True
    except Exception as e:
        print(f\"[ERROR] Failed to restart PostgreSQL: {str(e)}\")
        return False

# ----------------------------------------------------------------------
# Main Execution
# ----------------------------------------------------------------------
if __name__ == \"__main__\":
    # Check if running as root
    if os.geteuid() != 0:
        print(\"[ERROR] This script must be run as root (sudo)\")
        sys.exit(1)
    # Detect architecture
    arch = detect_architecture()
    if arch == \"graviton5\":
        print(\"[INFO] Detected Graviton5 (ARM Neoverse V2), applying optimal settings\")
        settings = GRAVITON5_SETTINGS
    elif arch == \"xeon6\":
        print(\"[INFO] Detected Xeon 6th Gen (Sapphire Rapids), applying optimal settings\")
        settings = XEON6_SETTINGS
    else:
        print(f\"[ERROR] Unsupported architecture: {arch}\")
        sys.exit(1)
    # Get config path
    config_path = get_postgres_config_path()
    if not config_path:
        sys.exit(1)
    # Check config file permissions
    if not os.access(config_path, os.W_OK):
        print(f\"[ERROR] No write permission for {config_path}\")
        sys.exit(1)
    # Apply settings
    if not apply_settings(config_path, settings):
        sys.exit(1)
    # Restart PostgreSQL to apply changes
    if not restart_postgres():
        sys.exit(1)
    print(\"[INFO] Tuning complete. Validate settings with: sudo -u postgres psql -c 'SHOW shared_buffers;'\")

# tco_calculator.py: Calculate 3-year TCO for Graviton5 vs Xeon 6th Gen PostgreSQL clusters
# Author: Senior Engineer (15yr exp)
# Requirements: python>=3.11
import sys
from typing import Dict, Optional

# ----------------------------------------------------------------------
# Cost Constants (us-east-1, On-Demand pricing as of 2024-10)
# ----------------------------------------------------------------------
GRAVITON5_INSTANCE_COST = 6.00  # $/hour for R8g.16xlarge
XEON6_INSTANCE_COST = 7.68      # $/hour for R7i.16xlarge
EBS_GP3_COST_PER_GB = 0.08      # $/month per GB
EBS_GP3_IOPS_COST = 0.005       # $/month per IOPS
SUPPORT_COST_PERCENT = 0.10     # 10% of instance + storage cost for AWS support

# ----------------------------------------------------------------------
# Helper Functions
# ----------------------------------------------------------------------
def calculate_instance_cost(cost_per_hour: float, years: int = 3) -> float:
    \"\"\"Calculate total instance cost over given years, return cost in USD\"\"\"
    hours_per_year = 365 * 24
    return cost_per_hour * hours_per_year * years

def calculate_storage_cost(storage_gb: int, provisioned_iops: int, years: int = 3) -> float:
    \"\"\"Calculate total EBS storage cost over given years, return cost in USD\"\"\"
    months_per_year = 12
    gb_cost = storage_gb * EBS_GP3_COST_PER_GB * months_per_year * years
    iops_cost = provisioned_iops * EBS_GP3_IOPS_COST * months_per_year * years
    return gb_cost + iops_cost

def calculate_tco(instance_cost: float, storage_cost: float) -> float:
    \"\"\"Calculate total TCO including support, return cost in USD\"\"\"
    support_cost = (instance_cost + storage_cost) * SUPPORT_COST_PERCENT
    return instance_cost + storage_cost + support_cost

def get_user_input(prompt: str, input_type: type) -> Optional[any]:
    \"\"\"Get validated user input, return None on invalid input\"\"\"
    try:
        value = input_type(input(prompt))
        if value <= 0:
            print(f\"[ERROR] Value must be positive, got {value}\")
            return None
        return value
    except ValueError:
        print(f\"[ERROR] Invalid input type, expected {input_type.__name__}\")
        return None

# ----------------------------------------------------------------------
# Main Execution
# ----------------------------------------------------------------------
if __name__ == \"__main__\":
    print(\"=== PostgreSQL 17 OLTP Cluster TCO Calculator ===\")
    print(\"Enter cluster details (all values must be positive):\\n\")
    # Get cluster details from user
    num_instances = get_user_input(\"Number of instances per cluster: \", int)
    if not num_instances:
        sys.exit(1)
    storage_per_instance = get_user_input(\"Storage per instance (GB): \", int)
    if not storage_per_instance:
        sys.exit(1)
    iops_per_instance = get_user_input(\"Provisioned IOPS per instance: \", int)
    if not iops_per_instance:
        sys.exit(1)
    # Calculate Graviton5 TCO
    g_instance_cost = calculate_instance_cost(GRAVITON5_INSTANCE_COST) * num_instances
    g_storage_cost = calculate_storage_cost(storage_per_instance, iops_per_instance) * num_instances
    g_tco = calculate_tco(g_instance_cost, g_storage_cost)
    # Calculate Xeon 6th Gen TCO
    x_instance_cost = calculate_instance_cost(XEON6_INSTANCE_COST) * num_instances
    x_storage_cost = calculate_storage_cost(storage_per_instance, iops_per_instance) * num_instances
    x_tco = calculate_tco(x_instance_cost, x_storage_cost)
    # Calculate TPS per dollar (using benchmark data)
    g_tps = 114000  # Read-heavy TPS for Graviton5
    x_tps = 103000  # Read-heavy TPS for Xeon
    g_tps_per_dollar = g_tps / (GRAVITON5_INSTANCE_COST * num_instances)
    x_tps_per_dollar = x_tps / (XEON6_INSTANCE_COST * num_instances)
    # Print results
    print(f\"\\n=== 3-Year TCO Results ===\")
    print(f\"Graviton5 Cluster TCO: ${g_tco:,.2f}\")
    print(f\"Xeon 6th Gen Cluster TCO: ${x_tco:,.2f}\")
    print(f\"TCO Savings with Graviton5: ${x_tco - g_tco:,.2f} ({(x_tco - g_tco)/x_tco:.1%})\")
    print(f\"\\n=== Performance per Dollar ===\")
    print(f\"Graviton5 TPS per dollar: {g_tps_per_dollar:,.0f}\")
    print(f\"Xeon 6th Gen TPS per dollar: {x_tps_per_dollar:,.0f}\")
    print(f\"Graviton5 is {(g_tps_per_dollar - x_tps_per_dollar)/x_tps_per_dollar:.1%} higher\")
    # Validate division by zero (should not happen with positive inputs)
    if GRAVITON5_INSTANCE_COST == 0 or XEON6_INSTANCE_COST == 0:
        print(\"[ERROR] Instance cost cannot be zero\")
        sys.exit(1)

sudo sysctl -w vm.nr_hugepages=262144  # 262144 * 2MB = 512GB total huge pages
sudo sysctl -w vm.hugetlb_shm_group=26  # PostgreSQL group ID

sudo -u postgres psql -c \"ALTER SYSTEM SET jit = 'on';\"
sudo -u postgres psql -c \"ALTER SYSTEM SET jit_above_cost = 1000;\"
sudo systemctl restart postgresql-17

aws ebs modify-volume --volume-id vol-0123456789abcdef0 --iops 16000 --throughput 1000 --region us-east-1