Kif tirregola server MySQL: buffer pool, slow query log, u connections?

Question

IT Interview Practice · Accepted Answer

It-tuning tas-server isegwi loop: **kejjel, sib il-bottleneck, irranġa ħaġa waħda, erġa' kejjel.** It-tliet oqsma bl-akbar effett huma l-InnoDB buffer pool, is-slow query log, u l-immaniġġjar tal-connections.

## InnoDB buffer pool — l-ewwel knob

Il-buffer pool jikkaċċja data u index pages fir-RAM; meta l-working set tiegħek jidħol fih, ir-reads huma bil-veloċità tal-memorja u d-disk I/O kważi jisparixxi. Daqqsu għal ~50–75% tar-RAM fuq server DB dedikat.

```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 — sib x'għandek tirranġa

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

## Connections

Kull connection tiswa memorja u threads. Tissettjax `max_connections` għoli ħafna biex taħbi leak — mewġa ta' eluf ta' connections tħarbat is-server. Uża **connection pool** fl-app (jew proxy bħal ProxySQL) biex iżżomm sett żgħir u użat mill-ġdid, u osserva `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';
```

## Ordni tal-attakk

Irranġa l-ewwel l-aktar slow queries importanti (indexes) — dan ġeneralment jegħleb kwalunkwe bidla fil-config. Imbagħad daqqas il-buffer pool sew. Biss imbagħad irregola `max_connections`, `innodb_log_file_size`, u s-settings tal-flush. Ibdel varjabbli waħda kull darba u erġa' kejjel.

## Għaliex huwa importanti

Ħafna mill-inċidenti ta' "MySQL bilmod" huma ftit queries mingħajr index jew buffer pool żgħir wisq biex iżomm il-working set — mhux tuning eżotiku. Inġinier senior juża l-ewwel is-slow log u l-hit rate tal-buffer pool, u jirranġa l-bottleneck reali minflok idawwar il-knobs tal-config għamja.

InnoDB buffer pool — l-ewwel knob

Slow query log — sib x'għandek tirranġa

Connections

Ordni tal-attakk

Għaliex huwa importanti

Mistoqsijiet relatati