तुम्ही MySQL server कसा tune करता: buffer pool, slow query log, आणि connections?

Question

IT Interview Practice · Accepted Answer

Server tuning एका loop चे अनुसरण करते: **मोजा, bottleneck शोधा, एक गोष्ट fix करा, पुन्हा मोजा.** सर्वात जास्त प्रभाव असणारी तीन क्षेत्रे म्हणजे InnoDB buffer pool, slow query log, आणि connection handling.

## InnoDB buffer pool — #1 नियंत्रक

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 — काय fix करायचे ते शोधा

```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 fix करा (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 fix करतो.

InnoDB buffer pool — #1 नियंत्रक

Slow query log — काय fix करायचे ते शोधा

Connections

हल्ल्याचा क्रम

हे का महत्त्वाचे आहे

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