Jak stroić serwer MySQL: buffer pool, dziennik wolnych zapytań i połączenia?

Question

IT Interview Practice · Accepted Answer

Strojenie serwera przebiega w pętli: **mierz, znajdź wąskie gardło, popraw jedną rzecz, zmierz ponownie.** Trzy obszary o największej dźwigni to buffer pool InnoDB, dziennik wolnych zapytań oraz obsługa połączeń.

## Buffer pool InnoDB — pokrętło nr 1

Buffer pool buforuje strony danych i indeksów w pamięci RAM; gdy Twój zbiór roboczy się w nim mieści, odczyty odbywają się z prędkością pamięci, a I/O dyskowe niemal znika. Ustaw jego rozmiar na ~50–75% RAM na dedykowanym serwerze bazodanowym.

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

-- Health check: hit rate should be ~99%+
SHOW ENGINE INNODB STATUS\G
--   Buffer pool hit rate 999 / 1000     <- good; near 1000/1000 = mostly RAM
```

## Dziennik wolnych zapytań — znajdź, co poprawić

```sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- log anything over 500 ms
-- Then aggregate with pt-query-digest / mysqldumpslow to rank the worst queries,
-- and EXPLAIN each one. Most wins are a missing index, not a server setting.
```

## Połączenia

Każde połączenie kosztuje pamięć i wątki. Nie ustawiaj `max_connections` pod niebiosa, by zamaskować wyciek — nawał tysięcy połączeń zajeżdża serwer. Użyj **puli połączeń** w aplikacji (lub proxy w rodzaju ProxySQL), by utrzymać mały, wielokrotnie używany zestaw, i obserwuj `Threads_connected` względem `Threads_running`.

```sql
SHOW GLOBAL STATUS LIKE 'Threads_running';   -- how many are actually doing work
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
```

## Kolejność ataku

Najpierw popraw najgorsze wolne zapytania (indeksy) — to zwykle bije każdą zmianę konfiguracji. Potem dobierz właściwy rozmiar buffer poola. Dopiero wtedy strój `max_connections`, `innodb_log_file_size` i ustawienia zrzucania. Zmieniaj jedną zmienną naraz i mierz ponownie.

## Dlaczego to ważne

Większość incydentów „MySQL jest wolny" to garść niezaindeksowanych zapytań albo buffer pool zbyt mały, by pomieścić zbiór roboczy — a nie egzotyczne strojenie. Senioryjny inżynier najpierw sięga po dziennik wolnych zapytań i współczynnik trafień buffer poola, naprawiając rzeczywiste wąskie gardło zamiast na oślep podkręcać pokrętła konfiguracji.

Buffer pool InnoDB — pokrętło nr 1

Dziennik wolnych zapytań — znajdź, co poprawić

Połączenia

Kolejność ataku

Dlaczego to ważne

Powiązane pytania