Files
mtusi2026lab2/scripts/track2_indexes.py
2026-10-16 05:27:13 +03:00

415 lines
22 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
#!/usr/bin/env python3
"""Track 2, часть 1: план запроса истории и стоимость записи.
Варианты индексов на messages (кроме PK):
none — только messages_pkey (id)
C — (chat_id, created_at), без id
A — (chat_id, created_at, id), индекс из миграции 001
A+C — оба (только в бенче записи: цена лишнего индекса)
Чтение: запросы приложения (MessageRepo::list_page) на чатах small/medium/
large/burst: Q1 — первая страница, Q2 — keyset-страница из середины чата,
Q3 — OFFSET на той же глубине (контраст). EXPLAIN (ANALYZE, BUFFERS),
1 прогрев + N замеров, медиана Execution Time.
Запись: pgbench, однострочные INSERT в отдельных транзакциях (как POST
/messages), раунды с ротацией порядка вариантов; CHECKPOINT и VACUUM перед
каждым прогоном; метрики TPS, средняя latency и байты WAL на вставку.
В конце индексы возвращаются к состоянию миграций (только A).
Результат: evidence/track2_indexes.md.
python3 scripts/track2_indexes.py
python3 scripts/track2_indexes.py --write-seconds 20 --write-rounds 5
"""
import argparse
import json
import re
from track2_common import (ENV, MESSAGE_COLS, PERF_USER_ID, Pg, dataset_lines,
environment_lines, median, pick_chats, write_evidence)
db = Pg.compose()
INDEXES = {
"A": ("messages_chat_keyset_idx", "(chat_id, created_at, id)"),
"C": ("track2_chat_created_idx", "(chat_id, created_at)"),
}
READ_VARIANTS = [("none", []), ("C", ["C"]), ("A", ["A"])]
WRITE_VARIANTS = [("none", []), ("C", ["C"]), ("A", ["A"]), ("A+C", ["A", "C"])]
CHAT_ORDER = ["small", "medium", "large", "burst"]
QUERIES = ["Q1", "Q2", "Q3"]
QUERY_TITLE = {
"Q1": "первая страница",
"Q2": "keyset из середины",
"Q3": "OFFSET из середины",
}
PAGE = 51 # limit + 1, как в приложении
BENCH_SQL = (
"INSERT INTO messages (id, chat_id, sender_id, text, created_at) "
"VALUES (gen_random_uuid(), ':chat', ':sender', 'track2-bench', "
"date_trunc('second', clock_timestamp()));\n"
)
def set_indexes(keys):
sql = []
for key, (name, _) in INDEXES.items():
if key not in keys:
sql.append(f"DROP INDEX IF EXISTS {name};")
for key in keys:
name, cols = INDEXES[key]
sql.append(f"CREATE INDEX IF NOT EXISTS {name} ON messages {cols};")
sql.append("ANALYZE messages;")
db.psql("\n".join(sql))
def index_size(key):
return int(db.scalar(f"SELECT pg_relation_size('{INDEXES[key][0]}');"))
# ---------- чтение ----------
def build_queries(chat_id, size):
depth = size // 2
ts, mid_id = db.rows(f"""
SELECT created_at::text, id FROM messages WHERE chat_id = '{chat_id}'
ORDER BY created_at, id OFFSET {depth} LIMIT 1;
""")[0]
base = f"SELECT {MESSAGE_COLS} FROM messages WHERE chat_id = '{chat_id}'"
order = "ORDER BY created_at ASC, id ASC"
return {
"Q1": f"{base} {order} LIMIT {PAGE}",
"Q2": f"{base} AND (created_at, id) > ('{ts}'::timestamptz, '{mid_id}'::uuid) "
f"{order} LIMIT {PAGE}",
"Q3": f"{base} {order} OFFSET {depth} LIMIT {PAGE}",
}
def walk(node):
yield node
for child in node.get("Plans", []):
yield from walk(child)
def summarize(plan_json):
top = plan_json[0]
plan = top["Plan"]
nodes, rows_read = [], 0
for n in walk(plan):
label = n["Node Type"]
if n.get("Index Name"):
label += f" ({n['Index Name']})"
nodes.append(label)
if not n.get("Plans"):
# Для параллельных узлов EXPLAIN показывает среднее на один loop.
per_loop = n.get("Actual Rows", 0) + n.get("Rows Removed by Filter", 0)
rows_read += round(per_loop * n.get("Actual Loops", 1))
return {
"ms": top["Execution Time"],
"buffers": plan.get("Shared Hit Blocks", 0) + plan.get("Shared Read Blocks", 0),
"rows_read": rows_read,
"plan": " → ".join(nodes),
}
def measure_read(sql, reps):
explain = f"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {sql};"
db.psql(explain) # прогрев
runs = [summarize(json.loads(db.psql(explain))) for _ in range(reps)]
result = runs[-1]
result["ms"] = median([r["ms"] for r in runs])
return result
def text_plan(sql):
return db.psql(f"EXPLAIN (ANALYZE, BUFFERS) {sql};", tuples=False).strip()
def read_phase(chats, reps):
results, plans, sizes = {}, {}, {}
queries = {label: build_queries(c[0], c[2]) for label, c in chats}
for variant, keys in READ_VARIANTS:
print(f"\n[чтение] вариант {variant}")
set_indexes(keys)
for key in keys:
sizes[key] = index_size(key)
for label in CHAT_ORDER:
for q in QUERIES:
r = measure_read(queries[label][q], reps)
results[(label, q, variant)] = r
print(f" {label:6} {q}: {r['ms']:8.3f} ms, buffers={r['buffers']:5}, "
f"rows_read={r['rows_read']:6} {r['plan']}")
for label, q in (("large", "Q2"), ("burst", "Q2"), ("small", "Q1")):
plans[(label, q, variant)] = text_plan(queries[label][q])
return results, plans, sizes
# ---------- запись ----------
def install_bench_script():
db.run(["sh", "-c", "cat > /tmp/track2_insert.sql"], input=BENCH_SQL)
def pgbench_once(chat_id, seconds, clients):
db.psql(f"""
DELETE FROM messages WHERE text = 'track2-bench';
VACUUM messages;
CHECKPOINT;
""")
lsn0 = db.scalar("SELECT pg_current_wal_lsn();")
out = db.run(["pgbench", "-n", "-U", ENV["POSTGRES_USER"], "-d", ENV["POSTGRES_DB"],
"-f", "/tmp/track2_insert.sql",
"-D", f"chat={chat_id}", "-D", f"sender={PERF_USER_ID}",
"-c", str(clients), "-j", str(clients), "-T", str(seconds)]).stdout
wal = int(db.scalar(f"SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '{lsn0}');"))
tx = int(re.search(r"actually processed: (\d+)", out).group(1))
tps = float(re.search(r"tps = ([\d.]+)", out).group(1))
lat = float(re.search(r"latency average = ([\d.]+) ms", out).group(1))
return {"tps": tps, "lat": lat, "wal_per_tx": wal / tx, "tx": tx}
def write_phase(chat_id, seconds, rounds, clients):
install_bench_script()
runs = {v: [] for v, _ in WRITE_VARIANTS}
db.psql("ALTER TABLE messages SET (autovacuum_enabled = false);")
try:
for rnd in range(rounds):
shift = rnd % len(WRITE_VARIANTS)
order = WRITE_VARIANTS[shift:] + WRITE_VARIANTS[:shift]
print(f"\n[запись] раунд {rnd + 1}/{rounds}: {' → '.join(v for v, _ in order)}")
for variant, keys in order:
set_indexes(keys)
r = pgbench_once(chat_id, seconds, clients)
runs[variant].append(r)
print(f" {variant:5} tps={r['tps']:8.1f} lat={r['lat']:.3f} ms "
f"WAL={r['wal_per_tx']:.0f} B/insert ({r['tx']} tx)")
finally:
db.psql("DELETE FROM messages WHERE text = 'track2-bench';")
db.psql("ALTER TABLE messages RESET (autovacuum_enabled); VACUUM ANALYZE messages;")
return runs
# ---------- отчёт ----------
def fmt_ms(v):
return f"{v:.3f}" if v < 10 else f"{v:.1f}"
def conclusions(chats, R, W, sizes):
size_of = {label: c[2] for label, c in chats}
out = []
# H1: индекс A даёт стоимость страницы, не зависящую от размера и глубины.
a_cells = [R[(l, q, "A")] for l in ("small", "medium", "large", "burst") for q in ("Q1", "Q2")]
a_rows = max(c["rows_read"] for c in a_cells)
a_buf = max(c["buffers"] for c in a_cells)
a_ms = max(c["ms"] for c in a_cells)
ok = a_rows <= PAGE
sorted_cells = [f"{l} {q}" for l in CHAT_ORDER for q in ("Q1", "Q2")
if "Sort" in R[(l, q, "A")]["plan"]]
sort_note = (
"Узла Sort нет ни в одном плане: порядок `(created_at, id)` берётся из индекса."
if not sorted_cells else
f"Sort встречается только в {', '.join(sorted_cells)}: для чата из {size_of['small']} "
f"сообщений планировщику дешевле взять все строки чата Bitmap-сканом по тому же индексу "
f"и отсортировать их в памяти; строк всё равно не больше размера чата."
)
out.append(
f"**H1 (индекс A, стоимость страницы постоянна) — {'подтверждена' if ok else 'НЕ подтверждена'}.** "
f"Для Q1 и Q2 на всех чатах (от {size_of['small']} до {size_of['large']} сообщений, "
f"включая середину чата и чат-всплеск) читается не более {a_rows} строк и {a_buf} буферов, "
f"время ≤ {fmt_ms(a_ms)} ms. {sort_note}"
)
# Без индекса.
n_small, n_large = R[("small", "Q1", "none")], R[("large", "Q1", "none")]
a_small = R[("small", "Q1", "A")]
out.append(
f"**Без вторичного индекса** стоимость определяется размером всей таблицы, а не чата: "
f"первая страница чата из {size_of['small']} сообщений читает {n_small['buffers']} буферов "
f"({fmt_ms(n_small['ms'])} ms), чата из {size_of['large']} — {n_large['buffers']} буферов "
f"({fmt_ms(n_large['ms'])} ms). Для маленького чата это в "
f"{n_small['ms'] / a_small['ms']:.0f}× медленнее индекса A. "
f"Узел: `{n_small['plan']}`."
)
# H2: C ломается на одинаковых created_at.
cb, ab = R[("burst", "Q2", "C")], R[("burst", "Q2", "A")]
cl, al = R[("large", "Q2", "C")], R[("large", "Q2", "A")]
ok = cb["rows_read"] >= 10 * max(ab["rows_read"], 1)
out.append(
f"**H2 (индекс C без id деградирует на одинаковых created_at) — "
f"{'подтверждена' if ok else 'НЕ подтверждена'}.** "
f"На обычном большом чате C почти не отличается от A: Q2 {fmt_ms(cl['ms'])} против "
f"{fmt_ms(al['ms'])} ms, потому что совпадений по секунде мало и Incremental Sort "
f"сортирует крошечные группы. В чате-всплеске ({size_of['burst']} сообщений в одну секунду) "
f"условие по id становится Filter, и C читает {cb['rows_read']} строк / {cb['buffers']} "
f"буферов за {fmt_ms(cb['ms'])} ms, а A — {ab['rows_read']} строк / {ab['buffers']} буферов "
f"за {fmt_ms(ab['ms'])} ms ({cb['ms'] / ab['ms']:.0f}×). Приложение хранит время с точностью "
f"до секунды, поэтому такой всплеск реалистичен при нагрузке: `id` в индексе обязателен."
)
# OFFSET.
q3, q2 = R[("large", "Q3", "A")], R[("large", "Q2", "A")]
out.append(
f"**OFFSET против keyset** на середине чата из {size_of['large']} сообщений, индекс A: "
f"OFFSET читает {q3['rows_read']} строк и {q3['buffers']} буферов ({fmt_ms(q3['ms'])} ms), "
f"keyset — {q2['rows_read']} строк и {q2['buffers']} буферов ({fmt_ms(q2['ms'])} ms). "
f"Стоимость OFFSET растёт линейно с глубиной, поэтому приложение использует курсор."
)
# Запись.
if W:
def stats(v):
tps = [r["tps"] for r in W[v]]
return median(tps), min(tps), max(tps), median([r["wal_per_tx"] for r in W[v]])
base_tps, base_min, base_max, base_wal = stats("none")
lines = []
for v in ("C", "A", "A+C"):
tps, mn, mx, wal = stats(v)
overlap = mn <= base_max and mx >= base_min
lines.append(
f"{v}: WAL {wal - base_wal:+.0f} B/вставку ({(wal / base_wal - 1) * 100:+.0f}%), "
f"TPS {(tps / base_tps - 1) * 100:+.1f}%"
+ (" (в пределах разброса прогонов)" if overlap else "")
)
out.append(
"**H3 (каждый вторичный индекс удорожает запись) — по WAL подтверждена.** "
f"База без вторичных индексов: {base_wal:.0f} B WAL на вставку, медиана {base_tps:.0f} TPS. "
+ "; ".join(lines) + ". "
"Объём WAL детерминирован и растёт с каждым индексом; TPS на локальном диске и "
"4 клиентах ограничен fsync при COMMIT, поэтому разница по TPS меньше и частично "
"тонет в шуме — это ограничение замера, а не отсутствие цены."
)
sa, sc = sizes.get("A"), sizes.get("C")
if sa and sc:
out.append(
f"**Размер:** A = {sa / 1024 / 1024:.1f} MiB, C = {sc / 1024 / 1024:.1f} MiB: "
f"`id` в ключе добавляет {(sa / sc - 1) * 100:.0f}% к размеру индекса."
)
out.append(
"**Решение:** оставить только A `(chat_id, created_at, id)`. Он один обслуживает и "
"первую, и любую следующую страницу за константное число буферов независимо от "
"размера чата и совпадений времени. C экономит немного места, но ломается на всплесках; "
"A+C платит за второй индекс при каждой записи без выигрыша для чтения."
)
return out
def render(chats, R, plans, W, sizes, args, env, dataset):
md = ["# Track 2. План запроса истории и стоимость записи", "",
"Сгенерировано `python3 scripts/track2_indexes.py "
f"--read-reps {args.read_reps} --write-seconds {args.write_seconds} "
f"--write-rounds {args.write_rounds} --clients {args.clients}`.", "",
"## Окружение и данные", "", *env, *dataset, "",
"Выбранные чаты:", "",
"| Метка | Чат | Сообщений |", "|---|---|---|"]
for label, (cid, title, n) in chats:
md.append(f"| {label} | `{title}` | {n} |")
md += ["",
"## Гипотезы", "",
"- **H1.** С индексом A `(chat_id, created_at, id)` страница истории читает ≈limit строк "
"независимо от размера чата и глубины курсора; без индекса читается вся таблица.",
"- **H2.** Индекс C `(chat_id, created_at)` без `id` эквивалентен A, пока `created_at` "
"уникален, но деградирует, когда много сообщений приходится на одну секунду.",
"- **H3.** Каждый вторичный индекс увеличивает стоимость INSERT (WAL, latency).", "",
"## Методика", "",
"- Запросы — те же, что в `MessageRepo::list_page`: `LIMIT 51` (limit+1), "
"порядок `(created_at, id)`. Курсор Q2 и OFFSET Q3 указывают на середину чата.",
f"- Для каждого варианта индексы пересоздаются, затем `ANALYZE`. "
f"`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)`: 1 прогрев + {args.read_reps} замеров, "
"медиана Execution Time; buffers = shared hit + read последнего замера; "
"rows_read — строки, прочитанные листовым узлом скана (включая отброшенные Filter).",
f"- Запись: `pgbench -c {args.clients} -j {args.clients} -T {args.write_seconds}`, "
"одна вставка на транзакцию в чат large, `created_at` с точностью до секунды как в "
f"приложении. {args.write_rounds} раунда с циклическим сдвигом порядка вариантов; "
"перед прогоном `DELETE` тестовых строк, `VACUUM`, `CHECKPOINT` (чтобы full-page writes "
"были в равных условиях); autovacuum на `messages` выключен на время бенча. "
"WAL = `pg_wal_lsn_diff` за прогон / число транзакций.", "",
"## Результаты: чтение", "",
"Время в ms (медиана) / buffers / rows_read.", "",
"| Чат | Запрос | none | C | A |", "|---|---|---|---|---|"]
for label in CHAT_ORDER:
for q in QUERIES:
cells = []
for v, _ in READ_VARIANTS:
r = R[(label, q, v)]
cells.append(f"{fmt_ms(r['ms'])} / {r['buffers']} / {r['rows_read']}")
md.append(f"| {label} | {q} {QUERY_TITLE[q]} | " + " | ".join(cells) + " |")
md += ["", "Узлы планов:", "", "| Чат | Запрос | Вариант | План |", "|---|---|---|---|"]
for label in CHAT_ORDER:
for q in QUERIES:
for v, _ in READ_VARIANTS:
md.append(f"| {label} | {q} | {v} | `{R[(label, q, v)]['plan']}` |")
if W:
md += ["", "## Результаты: запись", "",
"| Вариант | TPS медиана (min–max) | latency avg, ms | WAL, B/вставку | Прогоны TPS |",
"|---|---|---|---|---|"]
for v, _ in WRITE_VARIANTS:
tps = [r["tps"] for r in W[v]]
md.append(
f"| {v} | {median(tps):.0f} ({min(tps):.0f}–{max(tps):.0f}) | "
f"{median([r['lat'] for r in W[v]]):.3f} | "
f"{median([r['wal_per_tx'] for r in W[v]]):.0f} | "
f"{', '.join(f'{t:.0f}' for t in tps)} |")
md += ["", "Размеры индексов:", "", "| Индекс | Ключ | Размер |", "|---|---|---|"]
pkey = int(db.scalar("SELECT pg_relation_size('messages_pkey');"))
md.append(f"| messages_pkey | (id) | {pkey / 1048576:.1f} MiB |")
for key in ("C", "A"):
if key in sizes:
md.append(f"| {INDEXES[key][0]} | {INDEXES[key][1]} | {sizes[key] / 1048576:.1f} MiB |")
md += ["", "## Выводы", ""]
for c in conclusions(chats, R, W, sizes):
md += [c, ""]
md += ["## Ограничения", "",
"- Один хост, тёплый кэш (данные помещаются в shared_buffers/page cache): "
"время отражает CPU и число буферов, а не холодное чтение с диска.",
"- Литералы в запросах вместо параметров: приложение через libpqxx может получить "
"generic plan; для этих запросов выбор индекса одинаков, но это не проверялось.",
"- Данные синтетические, один отправитель, короткие тексты; распределение времени "
"внутри чата равномерное в окне.",
"- pgbench мерит БД без HTTP и Argon2; TPS ограничен fsync при COMMIT на локальном "
"диске, поэтому разница по TPS меньше разницы по WAL.",
"",
"## Ключевые планы (EXPLAIN ANALYZE, BUFFERS)", ""]
for (label, q, v), text in plans.items():
md += [f"### {label} {q} — {v}", "", "```", text, "```", ""]
return "\n".join(md)
def main():
p = argparse.ArgumentParser(description=__doc__.split("\n")[0])
p.add_argument("--read-reps", type=int, default=7)
p.add_argument("--write-seconds", type=int, default=15)
p.add_argument("--write-rounds", type=int, default=3)
p.add_argument("--clients", type=int, default=4)
p.add_argument("--skip-write", action="store_true")
args = p.parse_args()
chats = pick_chats(db)
env, dataset = environment_lines(db), dataset_lines(db)
print("\n".join(env + dataset))
for label, (_, title, n) in chats:
print(f" {label}: {title} ({n})")
try:
R, plans, sizes = read_phase(chats, args.read_reps)
W = None
if not args.skip_write:
large_id = dict(chats)["large"][0]
W = write_phase(large_id, args.write_seconds, args.write_rounds, args.clients)
finally:
set_indexes(["A"])
print("\nиндексы возвращены к состоянию миграций: messages_pkey + messages_chat_keyset_idx")
write_evidence("track2_indexes.md", render(chats, R, plans, W, sizes, args, env, dataset))
if __name__ == "__main__":
main()