Unaupangaje (tune) server ya MySQL: buffer pool, slow query log, na connections?

Question

IT Interview Practice · Accepted Answer

Kupanga (tuning) server hufuata mzunguko: **pima, tafuta bottleneck, rekebisha kitu kimoja, pima tena.** Maeneo matatu yenye ushawishi mkubwa zaidi ni InnoDB buffer pool, slow query log, na ushughulikiaji wa connections.

## InnoDB buffer pool — kifunguo namba 1

Buffer pool hu-cache data na index pages katika RAM; wakati working set yako inatoshea ndani yake, reads ni za kasi ya memory na disk I/O karibu inatoweka. Iweke ukubwa wa ~50–75% ya RAM kwenye server ya DB iliyojitolea kwa hilo.

```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 — tafuta cha kurekebisha

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

Kila connection hugharimu memory na threads. Usiweke `max_connections` juu sana ili kuficha leak — mkusanyiko wa maelfu ya connections hukwaruza server. Tumia **connection pool** katika app (au proxy kama ProxySQL) ili kuweka seti ndogo inayotumika tena, na fuatilia `Threads_connected` dhidi ya `Threads_running`.

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

## Mpangilio wa kushambulia

Rekebisha kwanza queries za polepole za juu (indexes) — hilo mara nyingi hushinda badiliko lolote la config. Kisha weka ukubwa sahihi wa buffer pool. Ndipo tu upange `max_connections`, `innodb_log_file_size`, na flush settings. Badilisha variable moja kwa wakati na pima tena.

## Kwa nini ni muhimu

Matukio mengi ya "MySQL ni polepole" ni queries chache zisizo na index au buffer pool ndogo mno kushikilia working set — siyo tuning ya kigeni. Mhandisi senior hutumia slow log na buffer-pool hit rate kwanza, akirekebisha bottleneck halisi badala ya kuzungusha config knobs kwa upofu.

InnoDB buffer pool — kifunguo namba 1

Slow query log — tafuta cha kurekebisha

Connections

Mpangilio wa kushambulia

Kwa nini ni muhimu

Maswali yanayohusiana