Kako uravnate strežnik MySQL: buffer pool, dnevnik počasnih poizvedb in povezave?

Question

IT Interview Practice · Accepted Answer

Uravnavanje strežnika sledi zanki: **izmerite, poiščite ozko grlo, popravite eno stvar, ponovno izmerite.** Tri področja z največjim vzvodom so InnoDB buffer pool, dnevnik počasnih poizvedb in obravnavanje povezav.

## InnoDB buffer pool — gumb št. 1

Buffer pool predpomni strani s podatki in indeksi v RAM; ko vaša delovna množica vanj pade, so branja s hitrostjo pomnilnika, diskovni V/I pa skoraj izgine. Dimenzionirajte ga na ~50–75 % RAM na namenskem strežniku podatkovne baze.

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

## Dnevnik počasnih poizvedb — poiščite, kaj popraviti

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

## Povezave

Vsaka povezava stane pomnilnik in niti. Ne nastavljajte `max_connections` v nebesa, da bi prikrili puščanje — naval na tisoče povezav zbije strežnik. Uporabite **connection pool** v aplikaciji (ali posrednika, kot je ProxySQL), da ohranite majhno, ponovno uporabljeno množico, in spremljajte `Threads_connected` proti `Threads_running`.

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

## Vrstni red napada

Najprej popravite najpočasnejše poizvedbe (indekse) — to običajno premaga vsako spremembo konfiguracije. Nato pravilno dimenzionirajte buffer pool. Šele nato uravnajte `max_connections`, `innodb_log_file_size` in nastavitve izpiranja. Spreminjajte eno spremenljivko naenkrat in ponovno merite.

## Zakaj je to pomembno

Večina incidentov "MySQL je počasen" je peščica neindeksiranih poizvedb ali buffer pool, ki je premajhen, da bi zadržal delovno množico — ne eksotično uravnavanje. Starejši inženir najprej poseže po dnevniku počasnih poizvedb in stopnji zadetkov buffer poola ter popravi resnično ozko grlo, namesto da slepo vrti konfiguracijske gumbe.

InnoDB buffer pool — gumb št. 1

Dnevnik počasnih poizvedb — poiščite, kaj popraviti

Povezave

Vrstni red napada

Zakaj je to pomembno

Povezana vprašanja