আপনি কীভাবে একটি MySQL server tune করেন: buffer pool, slow query log, এবং connection?

Question

IT Interview Practice · Accepted Answer

Server tuning একটি loop অনুসরণ করে: **পরিমাপ করুন, bottleneck খুঁজুন, একটি জিনিস ঠিক করুন, পুনরায় পরিমাপ করুন।** সর্বোচ্চ-leverage তিনটি ক্ষেত্র হলো InnoDB buffer pool, slow query log, এবং connection handling।

## InnoDB buffer pool — #১ knob

buffer pool RAM-এ data ও index page cache করে; যখন আপনার working set এর মধ্যে ফিট করে, read memory-গতির হয় এবং 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.
```

## Connection

প্রতিটি connection memory ও thread খরচ করে। একটি leak ঢাকতে `max_connections` আকাশচুম্বী সেট করবেন না — হাজার হাজার connection-এর হুড়োহুড়ি 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';
```

## আক্রমণের ক্রম

প্রথমে শীর্ষ slow query (index) ঠিক করুন — এটি সাধারণত যেকোনো config পরিবর্তনকে হারায়। তারপর buffer pool সঠিক-size করুন। কেবল তখনই `max_connections`, `innodb_log_file_size`, এবং flush setting tune করুন। একবারে একটি variable পরিবর্তন করুন এবং পুনরায় পরিমাপ করুন।

## এটি কেন গুরুত্বপূর্ণ

বেশিরভাগ "MySQL ধীর" ঘটনা হলো মুষ্টিমেয় কিছু unindexed query বা working set ধরে রাখতে খুব ছোট একটি buffer pool — বিদেশী tuning নয়। একজন senior engineer প্রথমে slow log ও buffer-pool hit rate-এর দিকে হাত বাড়ান, অন্ধভাবে config knob ঘোরানোর বদলে প্রকৃত bottleneck ঠিক করেন।

InnoDB buffer pool — #১ knob

Slow query log — কী ঠিক করতে হবে খুঁজুন

Connection

আক্রমণের ক্রম

এটি কেন গুরুত্বপূর্ণ

সম্পর্কিত প্রশ্ন