आप MySQL server को कैसे tune करते हैं: buffer pool, slow query log और connections?

Question

IT Interview Practice · Accepted Answer

Server tuning एक चक्र का अनुसरण करता है: **मापें, bottleneck खोजें, एक चीज़ ठीक करें, फिर से मापें।** तीन सबसे अधिक प्रभावी क्षेत्र हैं InnoDB buffer pool, slow query log, और connection handling।

## InnoDB buffer pool — #1 knob

buffer pool data और index pages को RAM में cache करता है; जब आपका working set इसमें समा जाता है, तो reads memory-गति के होते हैं और 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 को घिस देती है। एक छोटे, पुन: उपयोग किए गए सेट को बनाए रखने के लिए app में एक **connection pool** (या ProxySQL जैसे proxy) का उपयोग करें, और `Threads_connected` बनाम `Threads_running` पर नज़र रखें।

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

## हमले का क्रम

पहले शीर्ष slow queries (indexes) ठीक करें — यह आमतौर पर किसी भी config परिवर्तन से बेहतर होता है। फिर buffer pool को सही आकार दें। उसके बाद ही `max_connections`, `innodb_log_file_size`, और flush settings tune करें। एक बार में एक variable बदलें और फिर से मापें।

## यह क्यों महत्वपूर्ण है

अधिकांश "MySQL slow है" घटनाएँ मुट्ठी भर unindexed 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

हमले का क्रम

यह क्यों महत्वपूर्ण है

संबंधित प्रश्न