Концепции настройки БД — блокировки, кэш, индексы, контрольная точка, память

Обзор — что делает этот экран

Сервисы ▸ База данных напрямую подключается к зарегистрированным коллекторам БД (access=database) и показывает актуальные показатели нагрузки и структуры. Вместо простого перечисления чисел экран прикрепляет к каждому ключевому показателю (ожидание блокировки, коэффициент попаданий в кэш, использование индексов, контрольная точка, «грязные» страницы) специально спроектированную обучающую подсказку при наведении, чтобы вы узнали, «почему это значение плохое и как это исправить», прямо там, где вы его видите.

Эта статья кратко излагает концепции, которым учат такие подсказки. Полный словарь хранится в единственном месте — в самих подсказках (resources/lang/{ko,en}/dbmon.php, ключ edu), поэтому в реальной ситуации наведите указатель на показатель и прочитайте подробности, относящиеся к его контексту.

Экран — это монитор (что плохо прямо сейчас), подсказка — обучение причинам (почему и как), а фактический снимок конфигурации находится в параметрах экземпляра ([param] · nginx / my.cnf / sysctl). Читайте всё три вместе, чтобы пройти путь диагностика → доказательство → действие.

Когда использовать

Карточка БД показывает Degraded или Down, и вы хотите понять причину (насыщение соединений, высокая параллельность, очередь запросов, растущая доля медленных запросов).
Оценка коэффициента попаданий в кэш или использования индексов низкая, и вы хотите знать первопричину и рычаги улучшения (размер буферного пула, добавление индексов, покрывающие индексы).
Ожидание блокировки строки резко выросло, и нужно сузить круг: какой запрос или шаблон создаёт конкуренцию.
В подробностях управления памятью видно, как кривые «грязных» страниц и контрольной точки подбираются к зоне риска, и вы хотите понять направление настройки (увеличить буферный пул или redo-лог).
Введение в работу для DBA / DevOps — изучение внутреннего устройства InnoDB (блокировки, буферный пул, redo) на реальных данных.

Устройство экрана

Где прикрепляются обучающие подсказки:

Расположение	Показатель	Чему учит подсказка
Низ карточки справа · значок `Cache`	Коэффициент попаданий в кэш (буферный пул)	Формула расчёта + 4 способа его повысить
Низ карточки справа · значок `Index`	Использование индексов	Формула расчёта + 5 способов увеличить попадания по индексу
Нагрузка параллельности · `lock`	Среднее ожидание блокировки строки (мс)	7 причин роста ожидания блокировки (по частоте)
Заголовок / подробности памяти · `Checkpoint … % of redo`	Возраст контрольной точки	Что означает давление на redo-лог и как читать его рост и спад
Подробности (сворачиваемые) · Управление памятью	«Грязные» страницы · временной ряд контрольной точки	Наложение порогов LWM/MAX + пометки эпизодов сброса

Сам корпус карточки обобщает две оси нагрузки:

Нагрузка параллельности — насыщение соединений (кольцевой индикатор) · Running (threads_running) · Backlog (waiting) · lock.
Пропускная способность — спарклайн Total QPS · Slow rate (slow_qps) · Slow (total).

Нижний блок анализа схемы (сворачиваемый) показывает общий объём, крупнейшие схемы, самые большие таблицы и состав движков (это не относится к концепциям настройки и берётся из information_schema).

Как пользоваться

Найдите на карточке БД проблемный показатель (красный или жёлтый значок либо высокое значение).
Наведите указатель на подпись или значок этого показателя, чтобы открыть контекстную подсказку — появятся причина, формула и порядок действий.
Разверните Подробности, чтобы изучить временной ряд управления памятью с кривыми «грязных» страниц и контрольной точки и с линиями порогов.
Примените на целевой БД тот рычаг конфигурации, на который указывает подсказка (например, innodb_buffer_pool_size, добавить индекс), либо сверьте его с текущим значением в параметрах экземпляра.

Ниже приведён сводный справочник по каждой концепции (эталоном служит сама подсказка).

Ожидание блокировки строки — 7 причин

Среднее время (мс), которое InnoDB ждал получения блокировки строки. Чем выше, тем сильнее конкуренция. По частоте:

#	Причина	Суть
1	Транзакции держат блокировки слишком долго	После `BEGIN … UPDATE …` поздняя фиксация (внешний API, время раздумья пользователя, медленный запрос) выстраивает в очередь все транзакции, ожидающие эту строку. Самый частый случай.
2	Конкуренция за «горячую» строку	Множество транзакций обновляют ОДНУ И ТУ ЖЕ строку (счётчик, остаток на складе, итоговая или сводная строка, последовательность) → работа сериализуется, ожидания накапливаются.
3	UPDATE/DELETE без индекса	Если WHERE не может использовать индекс, InnoDB блокирует гораздо больше строк и диапазонов, чем нужно, → и блокирует посторонние транзакции. На практике это скрытая причина номер один.
4	Блокировки промежутков / next-key	При REPEATABLE READ диапазонные блокировки захватывают и промежутки между строками, блокируя INSERT. Шаблон: диапазонный UPDATE + параллельный INSERT.
5	Крупные пакетные DML	Один UPDATE/DELETE, затрагивающий десятки тысяч строк, надолго удерживает множество блокировок.
6	Медленный держатель блокировки	Если удерживающий блокировку запрос медленный по вводу-выводу или по CPU, он дольше не отпускает блокировки — рост доли медленных запросов и рост ожиданий блокировки идут вместе.
7	Проверки внешних ключей	Обновление таблицы со ссылкой по FK берёт разделяемые блокировки на родительских строках, что добавляет ожидания.

На этом экране причина № 3 (UPDATE без индекса) — скрытый главный источник всплесков ожидания блокировки. Читайте раздел TRANSACTIONS в SHOW ENGINE INNODB STATUS вместе с оценкой использования индексов.

Коэффициент попаданий в кэш (буферный пул InnoDB)

Высокий, когда большинство чтений обслуживается из памяти (буферного пула), а не с диска.

hit ratio = 1 − Δinnodb_buffer_pool_reads / Δinnodb_buffer_pool_read_requests

Оценка	Порог
Отлично	≥ 99,5 %
Хорошо	≥ 98 %
Удовлетворительно	≥ 90 %

Как его повысить (кратко): увеличьте innodb_buffer_pool_size (эмпирическое правило — 50–75 % ОЗУ) · сократите полные сканирования, чтобы не засорять буферный пул · уменьшите рабочий набор (не выбирайте лишние столбцы, выносите крупные BLOB, архивируйте) · используйте innodb_buffer_pool_instances, чтобы снизить конкуренцию, и выгружайте/загружайте буферный пул при перезапуске.

Использование индексов

Доля чтений, нашедших строки по индексу, относительно чтений, дошедших сканированием до следующей строки.

index usage = Δhandler_read_key / (Δhandler_read_key + Δhandler_read_rnd_next)

Оценка	Порог
Отлично	≥ 90 %
Хорошо	≥ 70 %
Удовлетворительно	≥ 40 %

Как его повысить (кратко): с помощью EXPLAIN найдите полные сканирования (type=ALL) и сортировки во временных файлах, затем проиндексируйте столбцы из WHERE/JOIN/ORDER BY · стройте составные индексы, ставя самый селективный столбец первым (правило левого префикса) · не отключайте индексы функциями и приведением типов на столбце (WHERE DATE(col)=… → используйте диапазон) · применяйте покрывающие индексы · удаляйте дублирующие и неиспользуемые индексы.

Возраст контрольной точки (давление на redo-лог)

Насколько redo-лог ушёл вперёд от последней контрольной точки (в байтах) = объём изменений, ещё не сброшенных в файлы данных, и объём работы, который InnoDB воспроизвёл бы после сбоя. Показатель % of redo в заголовке — это расстояние до обрыва, за которым начинается шторм сбросов.

Состояние	% of redo	Значение
Безопасно	< 50 %	Изменения сбрасываются вовремя
Наблюдать	50 – 75 %	Запись опережает сброс; принудительный сброс приближается
Риск	≥ 75 %	Близко к порогу асинхронного/синхронного сброса — принудительный сброс грозит задержками и штормами ввода-вывода

Как это читать: рост = запись > сброс (redo заполняется) · спад = сброс догнал (здоровое состояние) · пилообразная форма = накопление ↔ сброс, нормальный ритм. Вблизи 75 % стоит подумать об увеличении буферного пула или innodb_log_file_size.

Сброс «грязных» страниц (управление памятью)

Страницы буферного пула, изменённые, но ещё не записанные на диск. Подходящее значение задают собственные пороги сброса сервера (innodb_max_dirty_pages_pct = MAX, innodb_max_dirty_pages_pct_lwm = LWM). Мнение специалистов: «грязные» страницы должны колебаться ниже LWM.

Состояние	Условие	Значение
Здоровое (good)	Колеблется ниже порога (здоровая пила)	БД не страдает во время сбросов
Внимание (warn)	Выше нижней отметки (зона перед сбросом)	Следите за давлением записи и отставанием контрольной точки
Риск (crit)	Достигнут порог MAX	Агрессивный сброс → всплески задержек и штормы ввода-вывода (увеличьте буферный пул или redo-лог)

Временной ряд управления памятью накладывает линии порогов LWM/MAX поверх «грязных» страниц и отмечает эпизоды вытеснения и сброса. Показатели справа дополнительно выводят Pending writes и Row-lock avg.

Источники данных

Реальные счётчики и хранилище, на которые опираются обучающие концепции:

Концепция	Исходный счётчик / состояние	Примечание
Ожидание блокировки	`SHOW GLOBAL STATUS` · `information_schema.processlist`	Вычисляет очередь; требуется привилегия PROCESS
Коэффициент попаданий в кэш	`Innodb_buffer_pool_reads` / `_read_requests`	Разность между замерами
Использование индексов	`Handler_read_key` / `Handler_read_rnd_next`	Разность между замерами
Контрольная точка · «грязные»	Состояние InnoDB (возраст контрольной точки · «грязные» страницы · ожидающие)	Временной ряд управления памятью
QPS · медленные	`Questions` · `Slow_queries` (накопительные)	Разность → отсюда выводятся QPS и доля медленных

Временной ряд хранится в infr_db_metric (миграция 000681). App\Services\Database\DbCollectorService подключается напрямую к зарегистрированной БД, читает SHOW GLOBAL STATUS + @@max_connections + processlist и вычисляет производные показатели как разность с предыдущим замером.
Сбор выполняется командой infr:collect-db (по умолчанию планируется ежеминутно, завершается после одного прохода). При входе на страницу, если последний замер устарел, ensureFresh() немедленно делает разовый замер.
Интервал опроса экрана: 30 с. Чтобы избежать утечек соединений, коллектор вызывает DB::purge в блоке finally и задаёт тайм-аут PDO.

Пороги состояния карточки (degraded) (значения по умолчанию из OpsPolicy::dbThresholds() — настраиваются в разделе Операционная политика):

Условие	Порог по умолчанию	Метка причины
Насыщение соединений `conn_pct` ≥	80 %	насыщение соединений
`threads_running` ≥	40	высокая параллельность
`waiting` (очередь) ≥	5	очередь запросов
`slow_qps` ≥	1/с	доля медленных запросов

Права доступа

Чтение (просмотр экрана и подсказок): demo (уровень 1) и выше. Подсказки — чисто обучающий материал, без дополнительных ограничений и аудита.
Изменение порогов состояния БД: управляется в разделе Операционная политика — администратор (уровень 5/10 · infr.level:N).
Регистрация и правка коллекторов БД: Коллекторы, администратор (уровень 5/10). Пароли хранятся в коллекторе в зашифрованном виде (Crypt), либо через cred_ref = env:PREFIX повторно используется набор учётных данных из .env (и никогда не записывается прямо в код).

Устранение неполадок и FAQ

В. Backlog показывает «own connections only». Для подсчёта запросов в очереди по всему серверу учётной записи БД нужна привилегия PROCESS. Без неё считаются только ваши собственные соединения. См. sql/infr_db_monitor_grant.sql (рекомендуется отдельная учётная запись для мониторинга).

В. Оценки кэша или индексов пусты (N/A). Производные показатели вычисляются как разность между двумя замерами. Если коллектор только что запущен или счётчики сброшены (после перезапуска), первый замер не даёт оценки — дождитесь следующего цикла.

В. Карточка показывает stale (нет свежего замера). В режиме economy сбор пропускается, когда в консоли нет ни одного пользователя. При входе ensureFresh(45s) делает немедленный замер, но если планировщик отстаёт, карточка может ненадолго понизиться до pending. Для постоянного сбора установите режим сбора БД в значение always в разделе Операционная политика.

В. Как перевести или отредактировать текст подсказок? Обучающий словарь находится в одном месте: ключ edu файла resources/lang/{ko,en}/dbmon.php (единственный источник). Остальные локали используют en как запасной вариант.

В. Как на деле сузить причину ожидания блокировки? На целевой БД изучите SHOW ENGINE INNODB STATUS (TRANSACTIONS · LATEST DETECTED DEADLOCK), а также information_schema.INNODB_TRX / data_locks, и сверьте результат с приведённой выше таблицей из 7 причин.

Смежные темы

Сервисы ▸ База данных — экран с актуальной нагрузкой и структурой, где встречаются эти концепции
Концепции настройки Redis — парные обучающие подсказки для Сессий (Redis): объём памяти, коэффициент попаданий, фрагментация
Коллекторы БД — конвейер сбора с прямым подключением к зарегистрированным БД
Операционная политика — настройка порогов состояния БД и режима сбора
Параметры экземпляра — фактический снимок значений настройки (my.cnf и т. д.), доказательная база для настройки с помощью ИИ