Kaip derinti MySQL serverį: buferio talpyklą (buffer pool), lėtų užklausų žurnalą ir jungtis?

Question

IT Interview Practice · Accepted Answer

Serverio derinimas seka ciklą: **išmatuok, rask kliūtį, pataisyk vieną dalyką, permatuok.** Trys didžiausio sverto sritys yra InnoDB buferio talpykla (buffer pool), lėtų užklausų žurnalas ir jungčių tvarkymas.

## InnoDB buferio talpykla — svarbiausia rankenėlė

Buferio talpykla kešuoja duomenų ir indeksų puslapius RAM; kai jūsų darbinis rinkinys telpa į ją, skaitymai yra atminties greičio, o disko I/O beveik išnyksta. Nustatykite jos dydį ~50–75% RAM skirtame DB serveryje.

```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
```

## Lėtų užklausų žurnalas — rask ką taisyti

```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.
```

## Jungtys

Kiekviena jungtis kainuoja atmintį ir gijas. Nenustatykite `max_connections` iki dangaus, kad užmaskuotumėte nutekėjimą — tūkstančių jungčių antplūdis smaugia serverį. Naudokite **jungčių telkinį** (connection pool) programoje (arba tarpinį serverį kaip ProxySQL), kad išlaikytumėte mažą, pakartotinai naudojamą rinkinį, ir stebėkite `Threads_connected` prieš `Threads_running`.

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

## Atakos tvarka

Pirmiausia pataisykite geriausias lėtas užklausas (indeksai) — tai paprastai nugali bet kokį konfigūracijos pakeitimą. Tada tinkamai nustatykite buferio talpyklos dydį. Tik tada derinkite `max_connections`, `innodb_log_file_size` ir išplovimo (flush) nustatymus. Keiskite po vieną kintamąjį ir permatuokite.

## Kodėl tai svarbu

Dauguma „MySQL lėtas“ incidentų yra saujelė neindeksuotų užklausų arba per maža buferio talpykla darbiniam rinkiniui laikyti — o ne egzotiškas derinimas. Vyresnysis inžinierius pirmiausia griebiasi lėtų užklausų žurnalo ir buferio talpyklos pataikymo dažnio, taisydamas tikrąją kliūtį, o ne aklai sukiodamas konfigūracijos rankenėles.

InnoDB buferio talpykla — svarbiausia rankenėlė

Lėtų užklausų žurnalas — rask ką taisyti

Jungtys

Atakos tvarka

Kodėl tai svarbu

Susiję klausimai