તમે MySQL server ને કેવી રીતે tune કરો છો: buffer pool, slow query log, અને connections?

Question

IT Interview Practice · Accepted Answer

Server tuning એક loop ને અનુસરે છે: **માપો, bottleneck શોધો, એક વસ્તુ fix કરો, ફરી માપો.** સૌથી વધુ-leverage વાળા ત્રણ ક્ષેત્રો છે 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 — શું 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 ને thrash કરે છે. app માં (કે ProxySQL જેવા proxy માં) **connection pool** વાપરો જેથી નાનો, ફરીવપરાતો set જળવાય, અને `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';
```

## હુમલાનો ક્રમ

પહેલાં ટોચની slow queries (indexes) fix કરો — તે સામાન્ય રીતે કોઈ પણ config ફેરફારને હરાવે છે. પછી buffer pool ને યોગ્ય size આપો. તે પછી જ `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 fix કરે છે.

InnoDB buffer pool — #1 knob

Slow query log — શું fix કરવું તે શોધો

Connections

હુમલાનો ક્રમ

આ શા માટે મહત્વનું છે

સંબંધિત પ્રશ્નો