forked from mtusi2026/mtusi2026lab2
147 lines
9.1 KiB
Bash
Executable File
147 lines
9.1 KiB
Bash
Executable File
#!/bin/sh
|
||
# Трек 2: план запроса, стоимость индексов и backup/restore. Изолированный стенд: свои контейнеры, сеть и тома track2-*;
|
||
# стенд compose (messenger-lab2) не затрагивается. Фазы: up | indexes | backup | down. Сырые результаты — в evidence/track2/.
|
||
# sh scripts/track2/run.sh up # контейнер PostgreSQL, миграции приложения, 115 000 сообщений
|
||
# sh scripts/track2/run.sh indexes # сравнение вариантов индекса: планы, размер, pgbench записи/чтения
|
||
# sh scripts/track2/run.sh backup # pg_dump -> восстановление в новое хранилище -> сверка
|
||
# sh scripts/track2/run.sh down # удалить всё
|
||
# Требуются Docker, .NET SDK (dotnet tool restore уже выполнен), образ messenger-api:lab2 (docker compose build) для фазы backup.
|
||
set -eu
|
||
cd "$(dirname "$0")/../.."
|
||
export MSYS_NO_PATHCONV=1 # Git Bash на Windows не должен переписывать пути внутри docker exec
|
||
|
||
OUT=evidence/track2
|
||
NET=track2net
|
||
SRC=track2-src
|
||
RST=track2-restore
|
||
IMG=$(grep -o 'postgres:17@sha256:[0-9a-f]*' compose.yaml | head -1)
|
||
WORK=scripts/track2/.work # относительный путь: его понимают и dotnet.exe, и POSIX-оболочка
|
||
mkdir -p "$OUT" "$WORK"
|
||
|
||
. scripts/track2/backup.sh
|
||
|
||
src_psql() { docker exec -i "$SRC" psql -U postgres -d messenger -v ON_ERROR_STOP=1 "$@"; }
|
||
scalar() { src_psql -At -c "$1"; }
|
||
|
||
wait_ready() { # $1 — контейнер
|
||
for _ in $(seq 1 60); do
|
||
docker exec "$1" pg_isready -U postgres -d messenger >/dev/null 2>&1 && return 0
|
||
sleep 1
|
||
done
|
||
echo "контейнер $1 не готов" >&2; exit 1
|
||
}
|
||
|
||
phase_up() {
|
||
[ -f "$WORK/pw" ] || LC_ALL=C tr -dc 'A-Za-z0-9' < /dev/urandom | head -c 24 > "$WORK/pw"
|
||
docker network inspect "$NET" >/dev/null 2>&1 || docker network create "$NET" >/dev/null
|
||
docker volume create track2_src >/dev/null
|
||
docker run -d --name "$SRC" --network "$NET" -e POSTGRES_PASSWORD="$(cat "$WORK/pw")" -e POSTGRES_DB=messenger \
|
||
-v track2_src:/var/lib/postgresql/data "$IMG" >/dev/null
|
||
wait_ready "$SRC"; sleep 2; wait_ready "$SRC"
|
||
# Схема — те же миграции EF Core, что применяет приложение.
|
||
dotnet tool run dotnet-ef migrations script --project scr/Messenger.Api -o "$WORK/schema.sql" >/dev/null
|
||
src_psql -q < "$WORK/schema.sql"
|
||
echo "seed..."; src_psql -q < scripts/track2/seed.sql
|
||
src_psql -c "SELECT (SELECT count(*) FROM messages) AS messages, (SELECT count(*) FROM chats) AS chats, (SELECT max(c) FROM (SELECT count(*) c FROM messages GROUP BY chat_id) s) AS biggest_chat" \
|
||
| tee "$OUT/seed-summary.txt"
|
||
}
|
||
|
||
# Варианты индекса истории: имя | определение.
|
||
create_variant() {
|
||
case "$1" in
|
||
none) : ;;
|
||
hist) src_psql -q -c "CREATE INDEX ix_t2_hist ON messages (chat_id, created_at, id)" ;;
|
||
nosort) src_psql -q -c "CREATE INDEX ix_t2_nosort ON messages (chat_id, created_at)" ;;
|
||
covering) src_psql -q -c "CREATE INDEX ix_t2_covering ON messages (chat_id, created_at, id) INCLUDE (sender_id, text)" ;;
|
||
esac
|
||
}
|
||
|
||
bench_lines() { # tps и средняя задержка из вывода pgbench
|
||
grep -E "^(tps|latency average|number of transactions actually processed)" | tr '\n' ' '; echo
|
||
}
|
||
|
||
phase_indexes() {
|
||
big=$(scalar "SELECT big FROM bench.params"); medium=$(scalar "SELECT medium FROM bench.params")
|
||
small=$(scalar "SELECT small FROM bench.params"); sender=$(scalar "SELECT sender FROM bench.params")
|
||
ncur=$(scalar "SELECT count(*) FROM bench.cursors")
|
||
mid_t=$(scalar "SELECT t FROM bench.cursors WHERE n = (SELECT count(*)/2 FROM bench.cursors)")
|
||
mid_id=$(scalar "SELECT id FROM bench.cursors WHERE n = (SELECT count(*)/2 FROM bench.cursors)")
|
||
end_t=$(scalar "SELECT t FROM bench.cursors WHERE n = (SELECT max(n) FROM bench.cursors)")
|
||
end_id=$(scalar "SELECT id FROM bench.cursors WHERE n = (SELECT max(n) FROM bench.cursors)")
|
||
docker cp scripts/track2/write.pgbench "$SRC":/tmp/write.pgbench
|
||
docker cp scripts/track2/read.pgbench "$SRC":/tmp/read.pgbench
|
||
: > "$OUT/variants-summary.txt"
|
||
|
||
for v in none hist nosort covering; do
|
||
echo "=== variant: $v"
|
||
# Чистое состояние: удалить строки прошлых прогонов записи, пересобрать таблицу, оставить только индексы варианта.
|
||
src_psql -q -c "DELETE FROM messages WHERE text LIKE 'bench %'"
|
||
src_psql -q -c "VACUUM FULL messages"
|
||
src_psql -q -c "DROP INDEX IF EXISTS ix_messages_chat_history, ix_t2_hist, ix_t2_nosort, ix_t2_covering"
|
||
create_variant "$v"
|
||
src_psql -q -c "ANALYZE messages"
|
||
size=$(scalar "SELECT coalesce((SELECT pg_size_pretty(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relname = 'ix_t2_$v'), '-')")
|
||
|
||
# Планы снимаются на втором (прогретом) запуске: первый только заполняет shared buffers.
|
||
for pass in 1 2; do
|
||
src_psql -q -v big="$big" -v small="$small" -v mid_t="$mid_t" -v mid_id="$mid_id" -v end_t="$end_t" -v end_id="$end_id" \
|
||
-f - < scripts/track2/plans.sql > "$OUT/plans-$v.txt"
|
||
done
|
||
|
||
# Индекс с INCLUDE (text) ограничен размером строки B-дерева (~2700 байт): проверяем сообщение максимальной длины 4000 символов.
|
||
limit_msg=$(src_psql -At -c "BEGIN; INSERT INTO messages (id, chat_id, sender_id, text, created_at) SELECT gen_random_uuid(), '$small', '$sender', (SELECT string_agg(chr(33 + (random()*93)::int), '') FROM generate_series(1,4000)), now(); ROLLBACK;" 2>&1 | grep -E "ERROR|INSERT" | head -1 || true)
|
||
|
||
echo "read:" > "$WORK/bench.tmp"
|
||
for i in 1 2 3; do
|
||
docker exec "$SRC" pgbench -U postgres -d messenger -n -c 4 -j 2 -T 10 -f /tmp/read.pgbench \
|
||
-D big="'$big'" -D ncur="$ncur" 2>&1 | bench_lines >> "$WORK/bench.tmp"
|
||
done
|
||
echo "write:" >> "$WORK/bench.tmp"
|
||
lsn0=$(scalar "SELECT pg_current_wal_lsn()")
|
||
for i in 1 2 3; do
|
||
docker exec "$SRC" pgbench -U postgres -d messenger -n -c 4 -j 2 -T 10 -f /tmp/write.pgbench \
|
||
-D big="'$big'" -D medium="'$medium'" -D sender="'$sender'" 2>&1 | bench_lines >> "$WORK/bench.tmp"
|
||
done
|
||
# Размер индекса после нагрузки записи — до bulk-вставки: её откат оставляет мёртвые записи в индексе.
|
||
idx_after=$(scalar "SELECT coalesce((SELECT pg_size_pretty(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relname = 'ix_t2_$v'), '-')")
|
||
wal_bytes=$(scalar "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '$lsn0')")
|
||
wal_rows=$(scalar "SELECT count(*) FROM messages WHERE text LIKE 'bench %'")
|
||
wal_per_row=$(awk "BEGIN { printf \"%.0f\", $wal_bytes / $wal_rows }")
|
||
# Bulk-вставка 100 000 строк в большой чат одним оператором (без fsync на каждую строку): чистая стоимость поддержки индекса.
|
||
bulk=$(src_psql -At -c "BEGIN; EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF) INSERT INTO messages (id, chat_id, sender_id, text, created_at) SELECT gen_random_uuid(), '$big', '$sender', 'bulk ' || g, timestamptz '2027-01-01' + g * interval '1 second' FROM generate_series(1, 100000) g; ROLLBACK;" | grep -E "Execution Time|WAL:" | tr '
|
||
' ' ')
|
||
{
|
||
echo "### $v"
|
||
echo "index_size_before_writes: $size ; index_size_after_writes: $idx_after"
|
||
echo "insert_4000_chars: ${limit_msg:-ok}"
|
||
echo "wal_bytes_per_inserted_row (pgbench, 1 строка = 1 транзакция): $wal_per_row"
|
||
echo "bulk_insert_100000_rows: $bulk"
|
||
cat "$WORK/bench.tmp"
|
||
echo
|
||
} | tee -a "$OUT/variants-summary.txt"
|
||
cp "$WORK/bench.tmp" "$OUT/pgbench-$v.txt"
|
||
done
|
||
|
||
# Вернуть канонический индекс приложения (как в миграции), убрать строки нагрузки.
|
||
src_psql -q -c "DELETE FROM messages WHERE text LIKE 'bench %'"
|
||
src_psql -q -c "VACUUM FULL messages"
|
||
src_psql -q -c "DROP INDEX IF EXISTS ix_t2_hist, ix_t2_nosort, ix_t2_covering"
|
||
src_psql -q -c "CREATE INDEX ix_messages_chat_history ON messages (chat_id, created_at, id)"
|
||
src_psql -q -c "ANALYZE"
|
||
}
|
||
|
||
phase_down() {
|
||
docker rm -f track2-api-src track2-api-restore "$RST" "$SRC" >/dev/null 2>&1 || true
|
||
docker volume rm track2_src track2_restore >/dev/null 2>&1 || true
|
||
docker network rm "$NET" >/dev/null 2>&1 || true
|
||
rm -rf "$WORK"
|
||
}
|
||
|
||
case "${1:-}" in
|
||
up) phase_up ;;
|
||
indexes) phase_indexes ;;
|
||
backup) phase_backup ;;
|
||
down) phase_down ;;
|
||
*) echo "usage: $0 up|indexes|backup|down" >&2; exit 2 ;;
|
||
esac
|