Miten virität MySQL-palvelimen: buffer pool, slow query log ja yhteydet?

Question

IT Interview Practice · Accepted Answer

Palvelimen viritys noudattaa silmukkaa: **mittaa, löydä pullonkaula, korjaa yksi asia, mittaa uudelleen.** Kolme suurimman vipuvaikutuksen aluetta ovat InnoDB buffer pool, slow query log ja yhteyksien käsittely.

## InnoDB buffer pool — tärkein nuppi

Buffer pool välimuistittaa data- ja indeksisivut RAMiin; kun työjoukkosi mahtuu siihen, luvut ovat muistinopeutta ja levy-I/O lähes katoaa. Mitoita se ~50–75 %:iin RAMista omistetulla DB-palvelimella.

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

-- Terveystarkistus: osumaprosentin pitäisi olla ~99 %+
SHOW ENGINE INNODB STATUS\G
--   Buffer pool hit rate 999 / 1000     <- hyvä; lähellä 1000/1000 = enimmäkseen RAM
```

## Slow query log — löydä mitä korjata

```sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- lokita kaikki yli 500 ms
-- Aggregoi sitten pt-query-digest / mysqldumpslow:lla luokitellaksesi pahimmat kyselyt,
-- ja EXPLAIN kukin. Useimmat voitot ovat puuttuva indeksi, ei palvelinasetus.
```

## Yhteydet

Kukin yhteys maksaa muistia ja säikeitä. Älä aseta `max_connections`:ia pilviin peittääksesi vuodon — tuhansien yhteyksien ryntäys jauhaa palvelimen. Käytä **connection pool**:ia sovelluksessa (tai välityspalvelinta kuten ProxySQL) pitääksesi pienen, uudelleenkäytetyn joukon, ja tarkkaile `Threads_connected` vs `Threads_running`.

```sql
SHOW GLOBAL STATUS LIKE 'Threads_running';   -- kuinka moni oikeasti tekee työtä
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
```

## Hyökkäysjärjestys

Korjaa pahimmat hitaat kyselyt (indeksit) ensin — se yleensä voittaa minkä tahansa konfiguraatiomuutoksen. Mitoita sitten buffer pool oikein. Vasta sitten viritä `max_connections`, `innodb_log_file_size` ja flush-asetukset. Muuta yhtä muuttujaa kerrallaan ja mittaa uudelleen.

## Miksi se on tärkeää

Useimmat "MySQL on hidas" -tapaukset ovat kourallinen indeksoimattomia kyselyitä tai buffer pool liian pieni pitämään työjoukon — eivät eksoottista viritystä. Seniori-insinööri tarttuu slow logiin ja buffer poolin osumaprosenttiin ensin, korjaten todellisen pullonkaulan sen sijaan että vääntäisi sokeasti konfiguraationuppeja.

InnoDB buffer pool — tärkein nuppi

Slow query log — löydä mitä korjata

Yhteydet

Hyökkäysjärjestys

Miksi se on tärkeää

Aiheeseen liittyvät kysymykset