How do you tune a MySQL server: buffer pool, slow query log, and 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 దాదాపు అదృశ్యమవుతుంది. dedicated DB server లో దీన్ని RAM లో ~50–75% కు size చేయండి.

```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` ను ఆకాశమంత ఎత్తుగా set చేయవద్దు — వేలాది connections యొక్క తాకిడి server ను కుదిపేస్తుంది. ఒక చిన్న, తిరిగి ఉపయోగించే set ను ఉంచడానికి app లో ఒక **connection pool** (లేదా ProxySQL వంటి proxy) ను ఉపయోగించండి, మరియు `Threads_connected` vs `Threads_running` ను గమనించండి.

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

## దాడి క్రమం

మొదట top slow queries (indexes) ను సరిచేయండి — అది సాధారణంగా ఏ config మార్పునైనా అధిగమిస్తుంది. తర్వాత buffer pool ను సరైన పరిమాణంలో set చేయండి. అప్పుడు మాత్రమే `max_connections`, `innodb_log_file_size`, మరియు flush settings ను tune చేయండి. ఒక సమయంలో ఒక variable ను మార్చి తిరిగి కొలవండి.

## ఇది ఎందుకు ముఖ్యం

చాలా "MySQL నెమ్మదిగా ఉంది" సంఘటనలు కొన్ని 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

దాడి క్రమం

ఇది ఎందుకు ముఖ్యం

సంబంధిత ప్రశ్నలు