load_test.py - master - nice_jam/ClickHouseVSPostgres

load_test.py
72 строки2 KB
Filippenko Pavel
code refactoring
28 июн 2025, 18:16
28 июн 2025, 18:169af234e
import time
import matplotlib.pyplot as plt
import clickhouse_connect
import psycopg2
from datetime import datetime, timedelta

# Connections
ch = clickhouse_connect.get_client(
    host='localhost',
    username='ch_user',
    password='ch_pass',
    database='default'
)

pg = psycopg2.connect(host='localhost', 
                      dbname='postgres', 
                      user='postgres', 
                      password='1234')
pg_cur = pg.cursor()

results = {"ClickHouse": [], "PostgreSQL": []}
labels = ["Single Read", "Range Read", "Aggregated Stats"]

# Time range
end = datetime.now()
start = end - timedelta(minutes=10)

# --- ClickHouse ---
# 1. Read a single record
t0 = time.time()
ch.command("SELECT * FROM system_metrics ORDER BY timestamp DESC LIMIT 1")
results["ClickHouse"].append(time.time() - t0)

# 2. Range read
t0 = time.time()
ch.command("SELECT * FROM system_metrics WHERE timestamp BETWEEN %s AND %s", (start, end))
results["ClickHouse"].append(time.time() - t0)

# 3. Aggregation
t0 = time.time()
ch.command("SELECT avg(cpu_usage), max(gpu_usage), min(ram_usage) FROM system_metrics")
results["ClickHouse"].append(time.time() - t0)

# --- PostgreSQL ---
# 1. Read a single record
t0 = time.time()
pg_cur.execute("SELECT * FROM system_metrics ORDER BY timestamp DESC LIMIT 1")
pg_cur.fetchall()
results["PostgreSQL"].append(time.time() - t0)

# 2. Range read
t0 = time.time()
pg_cur.execute("SELECT * FROM system_metrics WHERE timestamp BETWEEN %s AND %s", (start, end))
pg_cur.fetchall()
results["PostgreSQL"].append(time.time() - t0)

# 3. Aggregation
t0 = time.time()
pg_cur.execute("SELECT avg(cpu_usage), max(gpu_usage), min(ram_usage) FROM system_metrics")
pg_cur.fetchall()
results["PostgreSQL"].append(time.time() - t0)

# --- Plot ---
x = range(len(labels))
plt.bar([i - 0.15 for i in x], results["ClickHouse"], width=0.3, label="ClickHouse")
plt.bar([i + 0.15 for i in x], results["PostgreSQL"], width=0.3, label="PostgreSQL")
plt.xticks(x, labels)
plt.ylabel("Time (s)")
plt.title("Query Performance Comparison")
plt.legend()
plt.tight_layout()
plt.show()