MySQL Performance এর কিছু সাধারণ সমস‌্যা ও সমাধান (বেশি ডাটা এর জন‌্য প্রযোজ‌্য। কমপক্ষে ৫-৬মিলিয়ন বা এর বেশি)।

আমার একটা প্রজেক্টে প্রায় ১০৪টা টেবিল আছে, ডাটা আছে প্রায় ৬মিলিয়ন। এই প্রজেক্ট নিয়ে আমি ২বছর প্রচুর কথা শুনেছি। সলুশন খুবই স্লো। কিছু রিপোর্টে মাঝে মাঝেই টাইম আউট আসে। বিশেষ করে ৪টা রিপোর্ট আছে যে রিপোর্ট গুলো জেনারেট করতে প্রায় ২০-২৫টা টেবিল রিলেশন করতে হয় এবং আরো কিছু টেবিলের ডাটা আলাদা ক‌্যালকুলেট করে এর সাথে মার্জ করে দেখাতে হয়। ডাটা থাকে কমপক্ষে ১,০০০-১২,০০০ রো, মাঝে মাঝে ২৩,০০০ পর্যন্ত। পেজিনেশন ব‌্যবহার করেছি কিন্তু যাদের প্রোজেক্ট তাদের দাবি সব ডাটা এক বারে দেখাতে হবে। কারণ তাদের লোকাল সার্ভার। সো পার্ফমেন্স খুব সমস‌্যা হবার কথা না। ৩২জিবি র‌্যাম, ৮কোর প্রসেসর ২.২গিগাহার্জ । ২টা লো গ্রেড জিনিস, হার্ডডিক্স HDD, আর সার্ভার খুব পুরাতন Dell R720, ১২ বছর আগে কেনা। অপারেটিং সিস্টেম উইন্ডোস সার্ভার ২০১২। লোকাল নেটওয়ার্কে যা রিসোর্স আছে তাতে সমস‌্যা হওয়া উচিৎ না। কিন্তু মজা হল কোন কারণে ডাটা ২০০ এর বেশি দেখাতে গেলে টাইম-আউট খায়। ৫০রো ডাটা দেখাতে প্রায় ৪০-৫০সেকেন্ড টাইম লাগে। আগে আরো বেশি লাগত কারণ আমাদের এক ডেভেলপার সকল ডাটাবেজ রিলেশন ফেলে দিছিল। এই প্রোজেক্ট আগে অন‌্য কোম্পানি করেছিল। তাদের অনেক ফিচার ছিল না, তাই নতুন করে আমরা করেছি। আগের কোম্পানির করা সলুশন এ ২১৮টা টেবিল ছিল এবং কোন রিলেশন ছিল না। ফলে ডাটা ইন্টিগ্রিটি ছিল না। প‌্যারেন্ট ডাটা ডিলিট করা হয়েছে কিন্তু চাইল্ড ডাটা থেকে গেছে। আমরা নতুন ডিজাইন করে ১০৪টা টেবিল করি। তখন আগের ডাটাবেজ থেকে ডাটা মাইগ্রেশন করা হয়েছিল। কিন্তু প‌্যারেন্ট ছাড়া চাইল্ড ডাটা মাইগ্রেশন এ এরর আসতেছিল। তাই ডেভলপার সব রিলেশন ফেলে দিয়ে ছিল ডাটা মাইগ্রেশন এর জন‌্য। তখন রিলেশন এর অভাবে প্রায় কাজই করতেছিল না প্রোজেক্ট। পরে খুজে দেখা গেল রিলেশন একটাও নাই। পরে রিলেশন ইন্ডেক্স যোগ করা হয় আবার এবং বেওয়ারিশ ডাটা ফেলে দেওয়া হয়। রিলেশন করার পরে পার্ফমেন্স ৪০-৫০সেকেন্ড! কিন্তু আমাদের VPS সার্ভারে আবার পার্ফমেন্স ঠিক আছে। সবোর্চ ৪-৬সেকেন্ড লাগে। তো আমি দোষ দিতাম HDD এর উপর, কারণ আমাদের VPS আর তাদের সার্ভারের মধ‌্যে একমাত্র পার্থক‌্য হল আমাদের স্টোরেজ SSD (NVMe), আর তাদের HDD। তাই আমি বারবার বলছি যে তাদের সার্ভার চেন্জ করে SSD স্টোরেজ সার্ভার বসাইতে। যেদিন ফাইনাল ডেলিভারি দিব সলুশন এর, সেদিন নতুন একটা টেবিল লেফট জয়েন করা হয়েছিল একটা রিপোর্টে। ফলাফল পেজিনেশন ৫০ দিয়েও কিছুই আসছিল না। টাইম আউট বাড়ানো হল, ৩০০ সেকেন্ড। লাভ নাই। ইন্টারনাল সার্ভার এরর। মাথা খারাপ হবার যোগাড়। এইটা চেক করি, সেইটা চেক করি আর বসে বসে সার্ভার রে গালাগালি করি। কারণ প্রোজেক্ট আমাদের সার্ভারে সুন্দর চলতেছে। তাদের সার্ভারে গেলেই সব সমস‌্যা। কি যেন মনে করে আমি MySQL এর মেমোরি চেক করলাম। এবং মাথায় হাত দিয়ে বসে পড়লাম। মাত্র ৮এমবি। কি একটা অবস্থা। সো মেমোরি বাড়াতে হবে। এবার পড়লাম অন‌্য সমস‌্যায়। MySQL এর ফোল্ডারে কোন কনফিগারেশন ফাইল খুজে পাই না। অনেক নেটে সার্চ করলাম কিন্তু কোথায় থাকে তা পাই না। পুরা ড্রাইভ ধরে সার্চ দিলাম, যা তাই। প্রায় ১ঘন্টা পর একটা স্টাকওভারফ্লোর সলুশন এ পাইলাম যে C ড্রাইভে একটা হিডেন ফোল্ডার আছে, যেটা সরাসরি অ‌্যাড্রেসবার এ টাইপ করে ঢুকতে হয়, হিডেন Show বা Show সিস্টেম ফোল্ডার দিলেও কাজ করে না। ফোল্ডারটার নাম ভুলে গেছি। যাই হোক, ঐখান থেকে মেমোরি ১জিবি করে দিয়ে সার্ভিস রিস্টার্ট দিলাম। আর ওয়ালা! যেই রিপোর্ট আসতে ৪০-৫০ সেকেন্ড লাগত সেটা আসতে এখন মাত্র ২সেকেন্ড লাগে। পরে মনে পড়ল আমাদের VPS এ আমি আগে কোন এক সময় MySQL এর মেমোরি ২জিবি করে রাখছিলাম। তাই আমাদের সার্ভারে সমস‌্যা হয় না, কিন্তু ওদের সার্ভারে হত কারণ ওদের মেমোরি মাত্র ৮এমবি ছিল যেটা দিয়ে যে কিভাবে রিপোর্ট গুলো আগে দেখাত আল্লামাবুদ। এখন প্রোজেক্ট সুন্দর চলতেছে। কোন স্লো কমপ্লেন নাই। নেক্সট বাগ ফিক্স এর সময় গিয়ে মেমোরি ৪-৫জিবি করে দিয়ে আসব সেই রকম ই প্লান।

অন‌্য একটি প্রজেক্টে GPS ডাটা নিয়ে কাজ করতে হয়। একটা টেবিল এ প্রায় ২৩মিলিয়ন ডাটা আছে। ল‌্যাটিচুড ও লংগিচুড টাইম অনুসারে। সব রিলেশন ইনডেক্স ঠিক আছে। আমাকে যত গুলো ভেয়িকেল আছে, তারা আজকে এখন পর্যন্ত কতদুর চলেছে এবং পুরা লাইফটাইমে কত দুর চলেছে তা দেখাতে হবে। পুরা লাইফটাইমের জন‌্য অন‌্য একটা সিস্টেম করা আছে। ঐ ডাটা ১০-১২ মিলি সেকেন্ডে পাওয়া যায়। সমস‌্যা আজকে এই মুহুর্ত পর্যন্ত কত দুর চলেছে সেটা বের করা। প্রতিদিন প্রতি ভেয়িকেলের জন‌্য প্রায় ১৫০০ ডাটা সেভ হয়। এই ১৫০০ ল‌্যাট-লং পয়েন্ট একটা থেকে পরেরটার দুরত্ব কত তা বের করে যোগ করে করে মোট কতদুর চলেছে বের করতে হয়। প্রতি পেজে যদি ৫০টা ভেয়িকেল ও দেখাই তাও মিনিমাম ৫০x১৪৯৯ টা ক‌্যালকুলেশন। ল‌্যাট-লং থেকে দুরত্ব বের করার কোডের কমপ্লেক্সিটি যদি কনসিডার নাও করি, তার পর ও মেলা ক‌্যালকুলেশন। প্রতিভেয়িকেলের ডাটা বের কর, যোগ কর, মোট বের কর, তারপর দেখাও। প্রথম দিকে প্রতি পেজে ৫০ভেয়িকেল হলেও ডাটা দেখাচ্ছিল। কিছুদিন পর ইন্টারনাল সার্ভার এরর দেওয়া শুরু করল। তখন পেজিনেশন কমায়ে ৩০ করলাম। দেখলাম কাজ করে, কিন্তু সময় লাগে প্রায় ৫০-৮০ সেকেন্ড। মনে করলাম যেহেতু অনেক ক‌্যালকুলেশন, তাই সম্ভবত এত সময় লাগছে। ক্লায়েন্ট জানতে চাইলে তাই বললাম। উনি জানতে চাইলেন, সলুশন কি। আমি তাকে বেশি পার্ফমেন্স এর সার্ভার নিতে বললাম যার কোর স্পিড কমপক্ষে ৩গিগাহার্জ, কারণ তার সার্ভার VPS, ১কোর ২গিগাহার্জ, ৪জিবি র‌্যাম। সে বলল পরে নিবে, আপাতত তো চলছে। আমি ও তাই মত দিলাম। তখন ডাটা ছিল সম্ভবত ৪-৫মিলিয়ন। এর পর হটাৎ করে তার ডাটা খুব বেড়ে গেল। পার্ফমেন্স এ আবার সমস‌্যা দেখা গেল। কখনো লোড হয়, কখনো এরর ৫০০, ইন্টারনাল সার্ভার এরর। আমি আবার তাকে সার্ভার চেন্জ করতে বললাম, সে বলল পরে। আপাতত কাজ চালায়ে নাও। একবারে বড় সার্ভার নিব ডিসেম্বর এ। আমি পেজিনেশন আরও কমায়ে ১৫ করলাম। এরপর ১ম প্রোজেক্ট এর স্পিড সমস‌্যা সমাধান করলাম। মনে হল এই সার্ভারে ও কি একই সমস‌্যা (ডাক্তার নতুন জিনিস শিখলে সব একই টাইপের সমস‌্যায় সেই সমাধান চেষ্টা করে দেখে প্রথমে, আর আমরা তো আরো বেশি)? ব‌্যাস, সেটিং চেক করে দেখলাম হ‌্যা তাই মাত্র ৮এমবি। চেন্জ করলাম, র‌্যাম দিলাম ১জিবি (উনার মোট র‌্যাম ৪জিবি, তার মধ‌্য অপারেটিং সিস্টেম Ubuntu-18 প্রায় ১জিবি খেয়ে দিছে)। সার্ভিস রিস্টার্ট দিলাম, আবার চেক করলাম। কোন উন্নতি তো হয়ই নাই, উল্টা পেজ এখন আর কখোনোই আসতেছে না। মাথা খারাপ অবস্থা। সেটিং আগের জায়গায় নিয়ে গেলাম। লাউ-কদু। কি মরা। ২দিন ধরে কোন রিপোর্ট দেখা যায় না। পরে কতটুকু গেছে তা দেখানো বন্ধ করে দিলাম। কিন্তু রিকুয়ারমেন্ট তো পুরণ করতে হবে। কি করা যায়। কোড দেখি, কমানোর কোন জায়গা খুজে পাই না, যে ক‌্যালকুলেশন কমবে। কুয়েরি সবথেকে অপটিমাইজ অবস্থায় আছে। এর থেকে আর কমানো যায় না। কোড পার্ফমেন্স চেক করা শুরু করলাম। দেখলাম যেটা আমি মনে করতেছিলাম যে ১৫০০ডাটা থেকে দুরত্ব বের করার পার্ট অনেক সময় নেয়, আসলে সেটা মাত্র ০.০৩ সেকেন্ড নিচ্ছে। মোট দুরত্ব বের করা মাত্র ০.০০৭ সেকেন্ড নিচ্ছে। সময় নিচ্ছে ঐ ১৫০০ডাটা ডাটাবেজ থেকে পড়ে আনতে। প্রায় ১১সেকেন্ড প্রতি ভেয়িকেলের জন‌্য। তখন ডাটাবেজ এ শুধু ঐ টেবিল এ ২৩মিলিয়ন ডাটা।ভেয়িকেল আইডি দিয়ে ইন্ডেক্স করা। সো পার্ফমেন্স খারাপ হবার কোন কারণ নাই। তারপর পড়ালেখা শুরু করলাম যে কি করা যায়। সার্চ করে দেখলাম যে, MySQL এর একটি টেবিল এ ২০০-৩০০মিলিয়ন ডাটা কোন ব‌্যাপার না। মিলিসেকেন্ড পার্ফমেন্স থাকার কথা। সেখানে ১১সেকেন্ড! কুয়েরি চেক করলাম, দেখলাম ২টা প‌্যারামিটার দিয়ে সার্চ হয়। এক হল ভেয়িকেল আইডি, অন‌্যটি হল ডাটা ইনসার্ট ডেটটাইম বা এই ডাটা আজকের কিনা তাই। কি করা যায় চিন্তা করতে করতে কি মনে করে ডেটটাইম ফিল্ডটা ইনডেক্স করতে দিলাম। প্রায় ১মিনিট এর ও বেশি লাগল। এরপর আবার কুয়েরি পার্ফমেন্স চেক করলাম। মাত্র ০.০০৩ সেকেন্ড!!!! জীবনে আইডি ছাড়া অন‌্য কোন ফিল্ড ইনডেক্স করি নাই। এই প্রথমবার ডেটটাইম ফিল্ড ইনডেক্স করলাম, তাও কি মনে করে ঠিক মনে নাই। এবার সব কোড আগের অবস্থায় নিয়ে গেলাম মানে সব দেখাবে দুরত্ব সহ। পেজিনেশন উঠায়ে দিলাম। কোন সমস‌্যাই নাই। সর্বোচ্চ ১২-১৫ সেকেন্ড লাগতেছে সব ভেয়িকেলের ডাটা আসতে। ক্লায়েন্ট খুব খুশি। তার দামী সার্ভারে যাওয়া লাগবে না ডিসেম্বর এ।

এই ২টা প্রোজেক্ট থেকে ২টা জিনিস শিখছি। এক হল ডাটা বেশি এবং রিপোর্ট বড় মানে অনেক টেবিল নিয়ে কুয়েরি করতে হলে অবশ‌্যই MySQL এর মেমোরি বেশি থাকতে হবে, কমপক্ষে ১জিবি। আর দুই হল যদি মিলিয়ন মিলিয়ন ডাটা থেকে কোন ডাটা সার্চ করা লাগে, তবে যে যে কলাম দিয়ে সার্চ করব, সেই সেই কলাম ইনডেক্স করে নিব। প্রথম প্রোজেক্টে ২নং কাজ টা করা হয় নাই। তাই নেক্সট বাগফিক্স এ গেলে এটিও করে দিব। তবে সেক্ষেত্রে মনে হয় সব টেবিল এর সব কলাম ইনডেক্স করা লাগবে। তাদের যে ডিমান্ড, সব কলাম দিয়ে সার্চ করার ব‌্যবস্থা থাকতে হবে কিনা!!!