Бази даних: експлуатація й масштабування
20 питань · ~20 хв · Версія v3.0
Увійдіть, щоб продовжити
Резервні копії й відновлення на момент часу, реплікація й перемикання, оновлення версій, налаштування пам'яті, моніторинг і блокування, масштабування - питання від middle до lead.
- За спробу
- 20
- У пулі
- 42
- Проходжень
- 0
- Середній бал
- -
- Пройшли на 70%+
- -
Питання для підготовки
27 питаньНайпростіший спосіб - логічний дамп:
# PostgreSQL: власний стиснений формат, зручний для вибіркового відновлення
pg_dump -Fc -d app > app.dump
pg_restore -d app_restored app.dump
# MySQL
mysqldump --single-transaction --routines app > app.sql
pg_dump бере узгоджений знімок і не блокує роботу застосунку. Для MySQL на InnoDB той самий ефект дає --single-transaction.
Бекап, який ніхто не пробував відновити, - не бекап. Мінімум:
- Регулярно відновлювати копію на окремому сервері й перевіряти, що застосунок з нею працює. Автоматично, за розкладом.
- Зберігати копії не там, де база: інший сервер, інший дата-центр, об'єктне сховище. Пожежа чи видалений акаунт не мають забрати і базу, і бекапи.
- Кілька поколінь: щоденні за тиждень, щотижневі за місяць. Помилку в даних часто помічають не одразу.
- Моніторинг: сповіщення, якщо бекап не створився чи раптом став набагато меншим.
Правило 3-2-1: три копії, на двох різних носіях, одна - поза основним майданчиком.
Обмеження дампу: відновлення - лише на момент створення копії. Усе, що сталося після, втрачено. Для відновлення на довільний момент потрібне безперервне архівування журналу (WAL, binlog).
Докладніше в документації: Резервне копіювання і відновлення
Кожне з'єднання з PostgreSQL - це окремий процес сервера з власною пам'яттю. Сотні з'єднань споживають багато ресурсів, а встановлення нового з'єднання помітно повільне. Тим часом PHP-FPM з 200 воркерами на кількох серверах легко відкриває тисячу з'єднань, більшість з яких простоює.
PgBouncer стоїть між застосунком і базою: тисячі клієнтських з'єднань він обслуговує невеликим пулом серверних.
Режими:
- Session pooling - серверне з'єднання видається клієнту на всю сесію. Безпечно, але виграш малий.
- Transaction pooling - з'єднання видається лише на час транзакції (або окремого запиту поза транзакцією). Головна економія - і головні сюрпризи.
- Statement pooling - на окремий запит; транзакції з кількох запитів неможливі.
Що ламається в transaction pooling: усе, що прив'язане до сесії, бо наступний запит може піти іншим серверним з'єднанням:
SET(часовий пояс,search_path,statement_timeout) безLOCAL- налаштування «перетече» до іншого клієнта.- Сесійні advisory-блокування,
LISTEN/NOTIFY, тимчасові таблиці. - Підготовлені запити (prepared statements) - PgBouncer підтримує їх на рівні протоколу лише з версії 1.21 і з налаштуванням
max_prepared_statements. До того PDO з емуляцією вимкненою отримував помилки на кшталтprepared statement does not exist.
Альтернативи й доповнення: керовані пули (RDS Proxy, Supavisor у Supabase), довгоживучі процеси (Octane), що тримають сталі з'єднання, і просто обмеження кількості PHP-воркерів під реальну ємність бази.
Які індекси є:
-- у psql
\d orders
-- або запитом
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';
Чи використовуються - статистика pg_stat_user_indexes, яку PostgreSQL збирає з моменту останнього скидання статистики:
SELECT relname AS table,
indexrelname AS index,
idx_scan AS scans,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;
idx_scan = 0 означає, що індекс жодного разу не використали для пошуку. Такий індекс лише сповільнює запис і займає місце.
Перш ніж видаляти «невикористаний» індекс:
- Перевірити термін статистики: якщо її скинули вчора, щомісячний звіт ще не встиг скористатися індексом.
- Перевірити всі репліки: статистика збирається окремо на кожному сервері. Індекс може не використовуватися на primary, але бути критичним для звітів, що йдуть на репліку.
- Унікальні індекси й первинні ключі не видаляють, навіть з
idx_scan = 0: вони забезпечують цілісність, а не швидкість. - Видаляти через
DROP INDEX CONCURRENTLY, щоб не блокувати таблицю.
Окремо корисно: seq_scan і seq_tup_read у pg_stat_user_tables показують таблиці, які часто читаються повністю, - кандидати на новий індекс.
shared_buffers - власний кеш сторінок даних PostgreSQL у спільній пам'яті. Кожне читання спершу шукає сторінку тут; якщо її немає - читає з файлу.
Подвійне кешування. PostgreSQL читає файли через звичайну файлову систему, тож сторінки, яких немає в shared_buffers, часто вже лежать у кеші ОС (page cache). Тому:
shared_buffersне має забирати всю пам'ять - решту ефективно використовує ОС;- типова рекомендація документації - близько 25% оперативної пам'яті на виділеному сервері бази (більше дає менший виграш, бо дублюється з кешем ОС);
effective_cache_size- підказка планувальнику, скільки кешу всього (shared_buffers + кеш ОС), зазвичай 50-75% пам'яті. Пам'ять він не виділяє, лише робить індексні плани «дешевшими» в оцінках.
# сервер з 32 ГБ, лише під PostgreSQL
shared_buffers = 8GB
effective_cache_size = 24GB
Значення за замовчуванням - лише 128 МБ - розраховане на запуск будь-де, а не на продуктивність. На сервері для продакшену його майже завжди треба змінити (і перезапустити PostgreSQL).
Як оцінити, чи вистачає кешу:
SELECT round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_percent
FROM pg_stat_database
WHERE datname = current_database();
Для OLTP-навантаження зазвичай очікують 99%+. Але blks_read рахує читання повз shared_buffers, а ці сторінки могли прийти з кешу ОС - низький відсоток не завжди означає читання з диска. Точніше - EXPLAIN (ANALYZE, BUFFERS) для конкретних запитів і метрики вводу-виводу ОС.
Ще пов'язані параметри: huge_pages (для великих shared_buffers зменшує накладні витрати пам'яті), wal_buffers. У керованих базах (RDS, Cloud SQL) розумні значення вже виставлені під розмір інстанса.
Повільний запит - не лише той, що виконується 5 секунд один раз. Часто більше шкодить запит на 20 мс, який виконується мільйон разів на день. Тому дивляться на сумарний час.
PostgreSQL - pg_stat_statements. Розширення збирає статистику по кожному нормалізованому запиту (параметри замінено на $1): кількість викликів, сумарний і середній час, прочитані блоки.
-- shared_preload_libraries = 'pg_stat_statements' у postgresql.conf, потім:
CREATE EXTENSION pg_stat_statements;
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Доповнення:
log_min_duration_statement = 500- записувати в лог усі запити довші за 500 мс, з параметрами.auto_explain- автоматично логувати плани повільних запитів. Без цього план на проді, де дані й статистика інші, ніж локально, часто не відтворити.pg_stat_activity- що виконується просто зараз, хто кого чекає.
MySQL: slow query log (long_query_time), performance_schema і sys.statement_analysis - аналог pg_stat_statements.
На рівні застосунку APM (Sentry Performance, New Relic, Telescope локально) пов'язує запит з місцем у коді, яке його породило. Часто проблема - не сам запит, а N+1: сто швидких запитів там, де мав бути один.
Порядок дій: знайти топ за сумарним часом → EXPLAIN (ANALYZE, BUFFERS) на реальних параметрах → індекс, переписаний запит чи кеш → порівняти статистику після змін.
Спершу почитати
Прочитати - ще не значить знати
20 питань, по одному на екран, ~20 хв. Після завершення - розбір кожної помилки з посиланням на питання.