Как тюнинговать сервер MySQL: buffer pool, slow query log и соединения?

Question

IT Interview Practice · Accepted Answer

Тюнинг сервера следует циклу: **измерь, найди узкое место, исправь одну вещь, измерь снова.** Три области с наибольшим рычагом — это InnoDB buffer pool, slow query log и обработка соединений.

## InnoDB buffer pool — регулятор №1

Buffer pool кэширует страницы данных и индексов в RAM; когда ваш рабочий набор помещается в него, чтения идут со скоростью памяти, а дисковый ввод-вывод почти исчезает. Задавайте размер ~50–75% RAM на выделенном сервере БД.

```sql
SET GLOBAL innodb_buffer_pool_size = 12 * 1024 * 1024 * 1024;  -- 12 GB

-- Проверка здоровья: hit rate должен быть ~99%+
SHOW ENGINE INNODB STATUS\G
--   Buffer pool hit rate 999 / 1000     <- хорошо; близко к 1000/1000 = в основном RAM
```

## Slow query log — найти, что исправить

```sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- логировать всё дольше 500 мс
-- Затем агрегируйте через pt-query-digest / mysqldumpslow, чтобы ранжировать худшие запросы,
-- и делайте EXPLAIN для каждого. Большинство выигрышей — это отсутствующий индекс, а не настройка сервера.
```

## Соединения

Каждое соединение стоит памяти и потоков. Не задавайте `max_connections` до небес, чтобы маскировать утечку — лавина из тысяч соединений загоняет сервер. Используйте **пул соединений** в приложении (или прокси вроде ProxySQL), чтобы держать небольшой, переиспользуемый набор, и следите за `Threads_connected` против `Threads_running`.

```sql
SHOW GLOBAL STATUS LIKE 'Threads_running';   -- сколько реально выполняют работу
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
```

## Порядок атаки

Сначала исправьте топ медленных запросов (индексы) — это обычно превосходит любое изменение конфигурации. Затем правильно задайте размер buffer pool. Только потом тюнингуйте `max_connections`, `innodb_log_file_size` и настройки сброса. Меняйте по одной переменной за раз и измеряйте снова.

## Почему это важно

Большинство инцидентов "MySQL тормозит" — это горстка неиндексированных запросов или слишком маленький buffer pool, не вмещающий рабочий набор, — а не экзотический тюнинг. Инженер уровня senior сначала обращается к slow log и hit rate buffer pool, исправляя реальное узкое место вместо слепого выкручивания регуляторов конфигурации.

InnoDB buffer pool — регулятор №1

Slow query log — найти, что исправить

Соединения

Порядок атаки

Почему это важно

Похожие вопросы