Kepiye carane nyetél server MySQL: buffer pool, slow query log, lan sambungan?

Question

IT Interview Practice · Accepted Answer

Nyetél server ngetutake siklus: **ukur, temokake bottleneck, benerake siji bab, ukur manèh.** Telung wilayah sing paling gedhé pengaruhé yaiku buffer pool InnoDB, slow query log, lan panganan sambungan.

## Buffer pool InnoDB — knop nomer 1

Buffer pool nyimpen kaca data lan index ing RAM; nalika working set sampeyan pas ing kono, maca dadi kacepetan-memori lan I/O disk mèh ilang. Ukurné dadi ~50–75% saka RAM ing server DB khusus.

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

## Slow query log — golèk apa sing kudu dibenerake

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

## Sambungan

Saben sambungan regané memori lan thread. Aja nyetél `max_connections` dhuwur banget kanggo nutupi bocor — serangan èwonan sambungan ngremuk server. Nganggo **connection pool** ing aplikasi (utawa proxy kaya ProxySQL) kanggo njaga set cilik sing dienggo manèh, lan awasi `Threads_connected` vs `Threads_running`.

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

## Urutan serangan

Benerake query paling alon (index) dhisik — iku biasané ngungkuli owahan config apa waé. Banjur ukur bener buffer pool. Sawisé iku lagi nyetél `max_connections`, `innodb_log_file_size`, lan setelan flush. Ganti siji variabel saben wektu lan ukur manèh.

## Kenapa iki penting

Umumé insiden "MySQL alon" iku sawetara query tanpa index utawa buffer pool sing kecilikan kanggo nyekel working set — dudu tuning sing ndakik. Insinyur senior njupuk slow log lan hit rate buffer pool dhisik, mbenerake bottleneck sing nyata tinimbang muter knop config kanthi wuta.

Buffer pool InnoDB — knop nomer 1

Slow query log — golèk apa sing kudu dibenerake

Sambungan

Urutan serangan

Kenapa iki penting

Pitakon sing gegandhengan