จูน MySQL server อย่างไร: buffer pool, slow query log และ connection?

Question

IT Interview Practice · Accepted Answer

การจูน server เป็นวงจร: **วัด หาคอขวด แก้ทีละอย่าง แล้ววัดใหม่** สามจุดที่ให้ผลมากที่สุดคือ InnoDB buffer pool, slow query log และการจัดการ connection

## InnoDB buffer pool — ปุ่มอันดับ 1

Buffer pool แคชหน้าข้อมูลและ index ไว้ใน RAM; เมื่อ working set ของคุณพอดีกับมัน การอ่านจะเร็วเท่าความเร็วหน่วยความจำและ disk I/O แทบหายไป ตั้งขนาดให้ราว ~50–75% ของ RAM บน DB server ที่ใช้เฉพาะงานนี้

```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 — หาว่าจะแก้อะไร

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

## Connection

แต่ละ connection มีต้นทุนหน่วยความจำและ thread อย่าตั้ง `max_connections` ให้สูงลิ่วเพื่อกลบการรั่ว (leak) — connection เป็นพัน ๆ ที่แห่กันเข้ามาจะทำให้ server รวน ให้ใช้ **connection pool** ในแอป (หรือ proxy อย่าง ProxySQL) เพื่อรักษาชุด connection เล็ก ๆ ที่ใช้ซ้ำได้ และเฝ้าดู `Threads_connected` เทียบกับ `Threads_running`

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

## ลำดับการโจมตี

แก้ query ที่ช้าที่สุดก่อน (ด้วย index) — นั่นมักได้ผลดีกว่าการเปลี่ยน config ใด ๆ แล้วจึงปรับขนาด buffer pool ให้เหมาะสม หลังจากนั้นค่อยจูน `max_connections`, `innodb_log_file_size` และการตั้งค่า flush เปลี่ยนทีละตัวแล้ววัดใหม่

## ทำไมจึงสำคัญ

เหตุการณ์ "MySQL ช้า" ส่วนใหญ่เกิดจาก query ไม่กี่ตัวที่ไม่มี index หรือ buffer pool เล็กเกินกว่าจะเก็บ working set ได้ — ไม่ใช่การจูนพิสดารอะไร วิศวกรระดับ senior จะหยิบ slow log และดู hit rate ของ buffer pool ก่อน แล้วแก้คอขวดตัวจริงแทนที่จะไล่หมุนปุ่ม config แบบมั่ว ๆ

InnoDB buffer pool — ปุ่มอันดับ 1

Slow query log — หาว่าจะแก้อะไร

Connection

ลำดับการโจมตี

ทำไมจึงสำคัญ

คำถามที่เกี่ยวข้อง