Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Бази даних: експлуатація й масштабування

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-воркерів під реальну ємність бази.

Докладніше в документації: Можливості PgBouncer

Які індекси є:

-- у 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) розумні значення вже виставлені під розмір інстанса.

Докладніше в документації: shared_buffers

Повільний запит - не лише той, що виконується 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) на реальних параметрах → індекс, переписаний запит чи кеш → порівняти статистику після змін.

Докладніше в документації: pg_stat_statements

Прочитати - ще не значить знати

20 питань, по одному на екран, ~20 хв. Після завершення - розбір кожної помилки з посиланням на питання.