MySQL Advanced Architecture (InnoDB Internals Deep Dive)
গাড়ির বনেট খুলে ইঞ্জিনের পিস্টন আর ভালভ কীভাবে কাজ করে, সেটা যেমন মেকানিক ছাড়া কেউ বোঝে না; তেমনি InnoDB-র ইন্টারনাল আর্কিটেকচার বোঝা মানে আপনি এখন Database Internals Expert।
এই অধ্যায়ে আমরা ডেটাবেসের একদম গভীরে (Microscopic Level-এ) প্রবেশ করব।
২০.১ InnoDB Page Architecture (সবকিছুর ভিত্তি)
InnoDB ডেটাবেসের সব ডেটা হার্ডডিস্কে Page আকারে সেভ করে।
📄 Standard Page Size:
16KB
🏗️ গঠন:
Header + Body (Rows) + Trailer
💡 কেন ১৬KB? অপারেটিং সিস্টেম সাধারণত ৪KB ব্লক ব্যবহার করে। MySQL ১৬KB পেজ ব্যবহার করে যাতে এক রিডে (I/O) অনেকগুলো রো একসাথে র্যামে আনা যায়। এটি B+ Tree এর নোড হিসেবে কাজ করে।
┌─────────────────────────────────────┐
│ Page Header (38 bytes) │
├─────────────────────────────────────┤
│ Infimum + Supremum Records │
├─────────────────────────────────────┤
│ User Records (যেখানে আসল ডেটা) │
├─────────────────────────────────────┤
│ Free Space │
├─────────────────────────────────────┤
│ Page Directory (B+ Tree Node) │
├─────────────────────────────────────┤
│ Page Trailer (Checksum) │
└─────────────────────────────────────┘
২০.২ Buffer Pool (র্যামের জাদুকরী বাক্স)
Buffer Pool হলো র্যামের একটি অংশ যেখানে ডেটা এবং ইনডেক্স ক্যাশ করা থাকে। ডিস্ক থেকে डेटा পড়া স্লো, তাই MySQL সবসময় বাফার পুল থেকে পড়ার চেষ্টা করে।
🔄 ইন্টারনাল মেকানিজম (LRU List & Midpoint Insertion):
বাফার পুল পূর্ণ হয়ে গেলে নতুন ডেটা রাখার জন্য পুরনো ডেটা ফেলে দিতে হয় (Eviction)। InnoDB এখানে LRU (Least Recently Used) অ্যালগরিদম ব্যবহার করে, কিন্তু একটু চালাকি আছে।
🟡 Old Sublist (37%)
নতুন পড়া পেজগুলো প্রথমে এখানে আসে
🟢 New Sublist (63%)
যদি সেই পেজটি বারবার এক্সেস হয়, তবে সেটি "Young" হয়ে নিউ সাবলিস্টে প্রমোট হয়
💡 Senior Insight:
কেন সরাসরি টপে রাখে না? কারণ মাঝে মাঝে আমরা mysqldump বা ফুল টেবিল স্ক্যান চালাই। যদি সব নতুন পেজ টপে আসত, তবে দরকারি "Hot Data" র্যাম থেকে বের হয়ে যেত। এই মেকানিজম বাফার পুলকে দূষিত হওয়া থেকে বাঁচায়।
২০.৩ Redo Log & WAL (Write-Ahead Logging)
ধরুন বাফার পুলে ডেটা আপডেট হলো, কিন্তু ডিস্কে সেভ হওয়ার আগেই কারেন্ট চলে গেল। ডেটা কি হারাবে? না!
📝 WAL (Write-Ahead Logging) রুল:
"আসল ডেটা ফাইলে (Tablespace) লেখার আগে অবশ্যই লগ ফাইলে (Redo Log) লিখতে হবে।"
🔄 ফ্লো:
- ইউজার আপডেট কুয়েরি দিল
- বাফার পুলে পেজ আপডেট হলো (একে Dirty Page বলে)
- সাথে সাথে Redo Log ফাইলে চেঞ্জটি লেখা হলো
- ট্রানজেকশন COMMIT হলো
- (পরে কোনো এক সময়) ব্যাকগ্রাউন্ড প্রসেস Dirty Page-টি আসল ডিস্কে সেভ করবে
UPDATE t SET balance=100 WHERE id=1;
↓
Buffer Pool (Dirty Page: id=1 balance=100)
↓
Redo Log (Transaction 101: set id=1 to 100)
↓
COMMIT → Client gets "Success"
↓ (Later)
Disk (id=1 balance=100)
২০.৪ Undo Log (MVCC & Rollback)
এটি হলো ডেটাবেসের "Time Machine"। যখন আপনি কোনো রো আপডেট করেন, InnoDB আগের ভ্যালুটি Undo Log এ কপি করে রাখে।
🔄 Rollback:
আপনি যদি ভুল করে আপডেট করে ফেলেন এবং ROLLBACK কমান্ড দেন, MySQL এই লগ দেখে আগের ভ্যালু ফিরিয়ে আনে।
👁️ MVCC (Consistent Read):
ইউজার A ডেটা আপডেট করছে, কিন্তু কমিট করেনি। ইউজার B একই সময়ে ডেটা পড়তে চাইলে Undo Log থেকে পুরনো ভ্যালুটি দেখবে (লক ছাড়াই)।
User A: UPDATE users SET balance=100 WHERE id=1; (Not Committed)
User B: SELECT balance FROM users WHERE id=1;
↓
Undo Log returns OLD value (balance=50) → No Lock Required!
২০.৫ Doublewrite Buffer (ডেটা করাপশন প্রোটেকশন)
⚠️ সমস্যা (Partial Page Write):
MySQL এর পেজ সাইজ 16KB, কিন্তু অপারেটিং সিস্টেমের পেজ সাইজ 4KB। ডেটা রাইট করার সময় যদি 4KB লেখার পর কারেন্ট চলে যায়? পেজটি ভেঙে যাবে (Corrupt)।
✅ সমাধান (Doublewrite Buffer):
- InnoDB প্রথমে পেজটি Doublewrite Buffer (সিস্টেম টেবিলস্পেসের একটি জায়গা) এ লেখে
- সেখানে লেখা শেষ করে
fsync()করে - তারপর আসল ডেটা ফাইলে (Tablespace) লেখে
- ক্র্যাশ হলে MySQL রিস্টার্টের সময় Doublewrite Buffer চেক করে এবং করাপ্ট পেজ রিপেয়ার করে
২০.৬ Change Buffer (Insert Optimization)
সেকেন্ডারি ইনডেক্স (যেমন: email বা phone) আপডেট করা খুব ব্যয়বহুল কারণ এগুলো ডিস্কে এলোমেলোভাবে (Random I/O) থাকে।
🎯 কৌশল:
যদি টার্গেট পেজটি বাফার পুলে না থাকে, তবে InnoDB ডিস্ক থেকে পেজটি লোড না করে চেঞ্জটি Change Buffer এ লিখে রাখে।
পরে যখন সার্ভার ফ্রি থাকে বা পেজটি রিড হয়, তখন মার্জ করে।
এতে রাইট পারফরম্যান্স ১০ গুণ বাড়তে পারে!
২০.৭ Checkpointing (ডিস্কে সেভ করার প্রক্রিয়া)
Redo Log এবং Buffer Pool এর সাইজ লিমিটেড। তাই নিয়মিত Dirty Page গুলোকে আসল ডিস্কে সেভ করতে হয়। এই প্রক্রিয়াকে Checkpointing বলে।
🔵 Sharp Checkpoint
শাটডাউনের সময় সব ফ্ল্যাশ করে
🟢 Fuzzy Checkpoint
সিস্টেম চালু থাকা অবস্থায় অল্প অল্প করে ফ্ল্যাশ করে, যাতে সার্ভার স্লো না হয়
২০.৮ Adaptive Hash Index (AHI)
InnoDB মূলত B+ Tree ব্যবহার করে। কিন্তু কিছু ডেটা যদি খুব বেশি এক্সেস হয়, InnoDB মেমোরিতে অটোমেটিক একটি Hash Index তৈরি করে নেয়।
🌲 B-Tree
কয়েক ধাপে ডেটা খোঁজে (O(log n))
⚡ Hash
এক লাফে (O(1)) ডেটা পায়
এটি সম্পূর্ণ অটোমেটিক, আপনি শুধু অন/অফ করতে পারেন।
২০.৯ InnoDB Thread Model (কারা কাজ করে?)
ব্যাকগ্রাউন্ডে অনেকগুলো থ্রেড কাজ করে:
👑 Master Thread
সব অপারেশন কোঅর্ডিনেট করে
💾 IO Threads
ডিস্ক রিড/রাইট হ্যান্ডেল করে
🧹 Page Cleaner Thread
ডার্টি পেজগুলো ফ্ল্যাশ করে (Checkpointing)
🗑️ Purge Thread
ট্রানজেকশন শেষ হওয়ার পর অপ্রয়োজনীয় Undo Log ডিলিট করে (Garbage Collection)
২০.১০ Real-World Tuning (my.cnf)
একজন আর্কিটেক্ট হিসেবে আপনি কনফিগ ফাইলে কী চেঞ্জ করবেন?
📦 Buffer Pool Size:
innodb_buffer_pool_size = 8G # মোট র্যামের ৭০-৮০%📝 Log File Size:
innodb_log_file_size = 2G # বড় লগ ফাইল = কম চেকপয়েন্ট = ফাস্ট রাইট💾 Flush Method (Linux Only):
innodb_flush_method = O_DIRECTব্যাখ্যা: এটি OS Cache বাইপাস করে সরাসরি ডিস্কে লেখে। এতে ডাবল বাফারিং (MySQL Buffer + OS Buffer) এড়ানো যায় এবং র্যাম বাঁচে।
📁 File Per Table:
innodb_file_per_table = 1২০.১১ Locking Internals
InnoDB-র লকগুলো মেমোরিতে খুব কম জায়গা নেয় (বিটস হিসেবে থাকে)।
❓ Interview Q&A
Q: ACID প্রপার্টির সাথে InnoDB ইন্টারনালস এর সম্পর্ক কী?
Q: DELETE করার পরও ডিস্ক স্পেস খালি হয় না কেন?
কারণ DELETE শুধু রো-গুলোকে "Deleted" মার্ক করে রাখে (Purge Thread পরে ক্লিন করে)। কিন্তু ফাইলের সাইজ কমে না। স্পেস রিক্লেইম করতে OPTIMIZE TABLE চালাতে হয় (যা টেবিল রি-বিল্ড করে)।
🎯 অধ্যায় ২০ এর সারাংশ (Summary)
এই অধ্যায়ে আমরা শিখলাম:
- ✓Page Architecture: 16KB পেজ, B+ Tree নোড আকারে সংগঠিত
- ✓Buffer Pool: LRU List + Midpoint Insertion - Hot Data সুরক্ষিত রাখে
- ✓WAL (Redo Log): ডেটা লেখার আগে লগ লেখে - Durability নিশ্চিত করে
- ✓Undo Log: Rollback + MVCC (Time Machine)
- ✓Doublewrite Buffer: Partial Page Write থেকে রক্ষা করে
- ✓Change Buffer: সেকেন্ডারি ইনডেক্স লেখা ১০x ফাস্ট করে
✨ অভিনন্দন! আপনি এখন MySQL এর নাড়ি-নক্ষত্র জানেন। এই নলেজ দিয়ে আপনি শুধু ডেভেলপার নন, একজন Database Administrator (DBA) বা Architect হিসেবেও কাজ করতে পারবেন।
🏁 MySQL Journey Completed! 🚀