Ta yaya kake tune MySQL server: buffer pool, slow query log, da connections?

Question

IT Interview Practice · Accepted Answer

Tune server yana bin madauki: **auna, sami cikas, gyara abu ɗaya, sāke auna.** Wuraren uku masu babban lever su ne InnoDB buffer pool, slow query log, da sarrafa connections.

## InnoDB buffer pool — knob na #1

Buffer pool yana cache pages na bayanai da index a RAM; lokacin da working set ɗinka ya dace a cikinsa, karatu na saurin memory ne kuma disk I/O kusan ya ɓace. Auna shi zuwa ~50–75% na RAM a kan server DB da aka keɓe.

```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 — sami abin da za a gyara

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

Kowace connection tana cin memory da threads. Kada ka saita `max_connections` sama sosai don rufe leak — cunkoson connections dubbai yana thrash server. Yi amfani da **connection pool** a manhaja (ko proxy kamar ProxySQL) don riƙe ƙananan saiti da ake sāke amfani da su, kuma kalli `Threads_connected` da `Threads_running`.

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

## Tsarin farmaki

Gyara manyan queries masu jinkiri (indexes) da farko — wannan yawanci ya fi kowane canjin config. Sannan auna buffer pool daidai. Sai kawai sannan tune `max_connections`, `innodb_log_file_size`, da flush settings. Canza variable ɗaya a lokaci ɗaya kuma sāke auna.

## Me ya sa yake da muhimmanci

Yawancin abubuwan da suka faru na "MySQL yana da jinkiri" queries ne kaɗan marasa index ko buffer pool ƙarami wanda ba zai iya riƙe working set ba — ba tuning na ban mamaki ba. Injiniya senior yana juyawa zuwa slow log da buffer-pool hit rate da farko, yana gyara ainihin cikas maimakon murza knobs na config a makauniyar hanya.

InnoDB buffer pool — knob na #1

Slow query log — sami abin da za a gyara

Connections

Tsarin farmaki

Me ya sa yake da muhimmanci

Tambayoyi masu alaƙa