Bagaimana anda menala pelayan MySQL: buffer pool, slow query log, dan sambungan?

Question

IT Interview Practice · Accepted Answer

Penalaan pelayan mengikut satu gelung: **ukur, cari halangan (bottleneck), baiki satu perkara, ukur semula.** Tiga bidang yang paling tinggi tuasnya ialah InnoDB buffer pool, slow query log, dan pengendalian sambungan.

## InnoDB buffer pool — tombol #1

Buffer pool menyimpan cache halaman data dan indeks dalam RAM; apabila set kerja (working set) anda muat di dalamnya, bacaan adalah kelajuan-memori dan I/O cakera hampir hilang. Saizkannya kepada ~50–75% RAM pada pelayan DB yang 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 — cari apa yang perlu dibaiki

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

Setiap sambungan menelan memori dan benang. Jangan tetapkan `max_connections` terlalu tinggi untuk menyembunyikan kebocoran (leak) — rempuhan beribu-ribu sambungan mengasak (thrash) pelayan. Gunakan **connection pool** dalam aplikasi (atau proksi seperti ProxySQL) untuk mengekalkan set kecil yang boleh diguna semula, dan perhatikan `Threads_connected` berbanding `Threads_running`.

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

## Susunan serangan

Baiki pertanyaan perlahan teratas (indeks) dahulu — itu biasanya mengatasi sebarang perubahan konfigurasi. Kemudian saizkan buffer pool dengan betul. Barulah kemudian tala `max_connections`, `innodb_log_file_size`, dan tetapan flush. Tukar satu pemboleh ubah pada satu masa dan ukur semula.

## Mengapa ia penting

Kebanyakan insiden "MySQL perlahan" ialah segelintir pertanyaan tanpa indeks atau buffer pool yang terlalu kecil untuk memuatkan set kerja — bukan penalaan eksotik. Seorang jurutera senior mencapai slow log dan kadar hit buffer pool dahulu, membaiki halangan sebenar dan bukannya memutar tombol konfigurasi secara membuta.

InnoDB buffer pool — tombol #1

Slow query log — cari apa yang perlu dibaiki

Sambungan

Susunan serangan

Mengapa ia penting

Soalan berkaitan