آپ ایک MySQL server کو کیسے tune کرتے ہیں: buffer pool، slow query log، اور connections؟

Question

IT Interview Practice · Accepted Answer

Server tuning ایک loop کی پیروی کرتی ہے: **پیمائش کریں، bottleneck تلاش کریں، ایک چیز ٹھیک کریں، دوبارہ پیمائش کریں۔** سب سے زیادہ فائدہ مند تین علاقے InnoDB buffer pool، slow query log، اور connection handling ہیں۔

## InnoDB buffer pool — #1 knob

buffer pool ڈیٹا اور index pages کو RAM میں cache کرتی ہے؛ جب آپ کا working set اس میں سما جاتا ہے، تو reads memory-speed پر ہوتی ہیں اور disk I/O تقریباً ختم ہو جاتی ہے۔ ایک وقف شدہ DB server پر اسے RAM کے ~50–75% تک سائز دیں۔

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

## Connections

ہر connection memory اور threads خرچ کرتی ہے۔ کسی leak کو چھپانے کے لیے `max_connections` کو آسمان تک نہ بڑھائیں — ہزاروں connections کی بھگدڑ server کو thrash کرتی ہے۔ app میں ایک **connection pool** (یا ProxySQL جیسا proxy) استعمال کریں تاکہ ایک چھوٹا، دوبارہ استعمال ہونے والا set رہے، اور `Threads_connected` بمقابلہ `Threads_running` پر نظر رکھیں۔

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

## حملے کی ترتیب

سب سے پہلے سب سے سست queries (indexes) ٹھیک کریں — یہ عموماً کسی بھی config تبدیلی سے بہتر ہوتا ہے۔ پھر buffer pool کو درست سائز دیں۔ اس کے بعد ہی `max_connections`، `innodb_log_file_size`، اور flush settings کو tune کریں۔ ایک وقت میں ایک variable بدلیں اور دوبارہ پیمائش کریں۔

## یہ کیوں اہم ہے

زیادہ تر "MySQL سست ہے" واقعات چند بغیر index queries یا working set رکھنے کے لیے بہت چھوٹا buffer pool ہوتے ہیں — کوئی نادر tuning نہیں۔ ایک senior engineer پہلے slow log اور buffer-pool hit rate کی طرف جاتا ہے، اندھا دھند config knobs گھمانے کے بجائے اصل bottleneck ٹھیک کرتا ہے۔

InnoDB buffer pool — #1 knob

Slow query log — تلاش کریں کہ کیا ٹھیک کرنا ہے

Connections

حملے کی ترتیب

یہ کیوں اہم ہے

متعلقہ سوالات