ডেভেলপাররা প্রায়ই আমাকে একই প্রশ্ন করেন: "আমার API স্লো। আমি কোথা থেকে শুরু করব?" সাধারণত তাৎক্ষণিক প্রতিক্রিয়া হিসেবে সার্ভার আপগ্রেড করা বা RAM দ্বিগুণ করার কথা ভাবা হয়। এতে খরচ বাড়ে এবং খুব কম ক্ষেত্রেই মূল সমস্যার সমাধান হয়। বেশিরভাগ Laravel অ্যাপ্লিকেশনে, মূল বাধা বা bottleneck থাকে ডাটাবেস লেয়ারে। ফ্রেমওয়ার্কের চমৎকার সিনট্যাক্সের কারণে আমরা প্রায়ই ভুলে যাই যে প্রতিটি Eloquent কল শেষ পর্যন্ত SQL-এ রূপান্তরিত হয়, আর সেই SQL-ই প্রায়শই সমস্যার মূল কারণ হয়ে দাঁড়ায়।
সার্ভার সেটিংসে হাত দেওয়ার আগে, আপনার কুয়েরিগুলো পদ্ধতিগতভাবে পরীক্ষা করুন।
সঠিক ডায়াগনস্টিক দিয়ে শুরু করুন
অন্ধকারে ঢিল ছুড়ে অপ্টিমাইজ করার চেষ্টা করবেন না। এলোমেলোভাবে কুয়েরি পরিবর্তন করা মানে হলো আন্দাজে কাজ করা, আর আন্দাজে কাজ করলে ঘণ্টার পর ঘণ্টা সময় নষ্ট হয়।
আপনাকে সেই স্টেটমেন্টগুলো খুঁজে বের করতে হবে যা সবচেয়ে বেশি মোট সময় নিচ্ছে। রিয়েল লোডের (real load) অধীনে আপনার অ্যাপ্লিকেশনটি পর্যবেক্ষণ করুন। Laravel Telescope আপনাকে একটি রিকোয়েস্ট চলাকালীন প্রতিটি কুয়েরির একটি পরিষ্কার ভিউ প্রদান করে, সাথে তার সময়ও (timing) দেখায়। Laravel Debugbar লোকাল ডেভেলপমেন্টের সময় ব্রাউজারে এগুলো প্রদর্শন করে যাতে আপনি তাৎক্ষণিকভাবে কোনো অসঙ্গতি শনাক্ত করতে পারেন। যখন প্রোডাকশনে সমস্যা শনাক্ত করার প্রয়োজন হবে, তখন MySQL Slow Query Log চালু করুন। এটি আপনার নির্ধারিত থ্রেশহোল্ড (threshold) অতিক্রম করা স্টেটমেন্টগুলো রেকর্ড করে, যা ছোট ডেটাসেটে দেখা যায় না এমন সমস্যা খুঁজে বের করার জন্য আদর্শ। আপনি যদি আরও বড় পরিসরে কাজ করেন, তবে একটি Application Performance Monitoring (APM) টুল স্লো HTTP এন্ডপয়েন্টগুলোর সাথে নির্দিষ্ট ডাটাবেস কলের সম্পর্ক খুঁজে পেতে সাহায্য করতে পারে।
ডেটা পর্যালোচনার সময় দুটি জিনিসের দিকে নজর দিন: পরম এক্সিকিউশন টাইম (absolute execution time) এবং কলের ফ্রিকোয়েন্সি (call frequency)। একটি কুয়েরি নিতে যদি ৪০ মিলিসেকেন্ড সময় লাগে, তবে তা আপাতদৃষ্টিতে ক্ষতিকর মনে না হলেও, সেটি যদি প্রতি মিনিটে দুই হাজার বার চলে তবে তা বড় সমস্যা। আবার প্রতি ঘণ্টায় একবার চলা ৩ সেকেন্ডের একটি রিপোর্ট, প্রতি পেজে চলা আধা সেকেন্ডের একটি লুকআপের চেয়ে কম গুরুত্বপূর্ণ হতে পারে। তাই প্রথমে উচ্চ-প্রভাবশালী (high-impact) সমস্যাগুলো সমাধান করুন।
সবকিছু চাওয়ার অভ্যাস ত্যাগ করুন
SELECT * ব্যবহার করা সুবিধাজনক। এটি বেশ ব্যয়বহুলও বটে। যখন আপনি Model::all() লিখছেন বা কলামের নাম উল্লেখ না করে কোনো রেজাল্ট সেট আনছেন, তখন MySQL প্রতিটি ম্যাচ করা রো-এর জন্য প্রতিটি ফিল্ড নিয়ে আসে। এর মধ্যে বড় টেক্সট ফিল্ড, JSON blobs এবং টেবিলের অন্যান্য সব কিছু অন্তর্ভুক্ত থাকে। ফলে রেজাল্ট সেট বড় হয়ে যায়, মেমরি ব্যবহার বেড়ে যায় এবং রেসপন্স সিরিয়ালাইজ (serializing) করতে বেশি সময় লাগে।
সুনির্দিষ্ট হোন। আপনার কন্ট্রোলারে যদি শুধুমাত্র id, name, এবং email ফিল্ডগুলোর প্রয়োজন হয়, তবে ঠিক সেগুলোই রিকোয়েস্ট করুন:
User::select('id', 'name', 'email')->get();
কুয়েরি বিল্ডারের ক্ষেত্রেও একই নীতি প্রযোজ্য। ছোট পেলোড (payload) নেটওয়ার্কের মাধ্যমে দ্রুত চলাচল করে এবং আপনার অ্যাপ্লিকেশন সার্ভারে কম RAM ব্যবহার করে। এটি সবচেয়ে সহজ এবং সাশ্রয়ী সমাধানগুলোর একটি, তবুও এটি প্রায়ই বাদ পড়ে যায় কারণ Laravel-এ SELECT * ডিফল্ট আচরণ হিসেবে থাকে।
EXPLAIN-কে আপনার পরিবর্তনের নির্দেশক হতে দিন
EXPLAIN না চালিয়ে কখনোই একটি স্লো কুয়েরি রিফ্যাক্টর করবেন না। MySQL-এ, EXPLAIN কিওয়ার্ডটি কুয়েরি এক্সিকিউশন প্ল্যান দেখায়। এটি ঠিকভাবে প্রকাশ করে যে অপ্টিমাইজার কীভাবে আপনার ডেটা খুঁজে বের করার পরিকল্পনা করছে।
type কলামের দিকে নজর দিন। যদি আপনি ALL দেখেন, তবে বুঝতে হবে MySQL একটি ফুল টেবিল স্ক্যান (full table scan) করছে। এর মানে হলো আপনার WHERE ক্লজটি পূরণ করার জন্য এটি প্রতিটি রো পড়ছে। অপ্টিমাইজার কোনো ইনডেক্স ব্যবহার করছে কি না তা দেখতে key কলামটি দেখুন। এরপর Extra কলামটি পরীক্ষা করুন। যদি আপনি Using temporary বা Using filesort দেখতে পান, তবে বুঝতে হবে আপনার বর্তমান স্ট্রাকচার কুয়েরিটি সুন্দরভাবে সম্পন্ন করতে পারছে না বলে MySQL ইন্টারমিডিয়েট টেবিল তৈরি করছে বা মেমরিতে সর্টিং করছে।
আপনার MySQL ক্লায়েন্টে EXPLAIN চালান, অথবা এমন কোনো টুল ব্যবহার করুন যা আউটপুটটি ফরম্যাট করে দেখায়। একবার আপনি প্ল্যানটি দেখে ফেললে, আপনি বুঝতে পারবেন সমস্যাটি কি একটি মিসিং ইনডেক্স, একটি খারাপ জয়েন (bad join), নাকি এমন কোনো প্রেডিকেট (predicate) যা ইঞ্জিন অপ্টিমাইজ করতে পারছে না। তখন আর আন্দাজে কাজ করার প্রয়োজন থাকবে না।
উদ্দেশ্যমূলকভাবে ইনডেক্স করুন
লুকআপ (lookup) দ্রুত করার জন্য ইনডেক্স হলো সবচেয়ে শক্তিশালী হাতিয়ার, কিন্তু এগুলো তখনই কাজ করে যখন সেগুলো আপনার কুয়েরি করার পদ্ধতির সাথে মিলে যায়। সঠিক ইনডেক্স ছাড়া MySQL প্রতিটি রো একে একে স্ক্যান করে। হাজারটি রো বিশিষ্ট একটি টেবিলে ডেভেলপমেন্টের সময় এটি ঠিক মনে হতে পারে, কিন্তু দশ মিলিয়ন রো বিশিষ্ট একটি টেবিলে প্রোডাকশনে এটি বিপর্যয় ডেকে আনতে পারে।
WHERE ক্লজে ঘন ঘন ব্যবহৃত ফিল্ডগুলোর জন্য সিঙ্গেল-কলাম ইনডেক্স দিয়ে শুরু করুন। আপনি যদি প্রতিনিয়ত status দিয়ে ফিল্টার করেন, তবে status-এ একটি ইনডেক্স যোগ করুন।
যখন একটি কুয়েরি একসাথে একাধিক কলামের ওপর ফিল্টার করে, তখন কম্পোজিট ইনডেক্সের (composite index) দিকে নজর দিন। ইনডেক্সের ভেতরে কলামের ক্রম গুরুত্বপূর্ণ কারণ MySQL বাম থেকে ডানে কম্পোজিট ইনডেক্স পড়ে। একে বলা হয় 'leftmost prefix rule'। যদি আপনার কুয়েরি user_id দিয়ে সার্চ করে এবং তারপর created_at দিয়ে অর্ডার করে, তবে (user_id, created_at) এর ওপর একটি কম্পোজিট ইনডেক্স উল্লেখযোগ্যভাবে সাহায্য করবে। ক্রম উল্টে দিলে অপ্টিমাইজার ফিল্টারের জন্য ইনডেক্সটি নাও ব্যবহার করতে পারে।
প্রতিটি কলামে ইনডেক্স করবেন না। প্রতিটি ইনডেক্স ইনসার্ট (insert), আপডেট (update) এবং ডিলিট (delete) অপারেশনে অতিরিক্ত চাপ (overhead) তৈরি করে কারণ MySQL-কে সেই স্ট্রাকচার বজায় রাখতে হয়। পরিমাপের সময় আপনি যে প্যাটার্নগুলো খুঁজে পেয়েছেন তার ওপর ভিত্তি করে সুচিন্তিতভাবে ইনডেক্স যোগ করুন।
কলাম থেকে ফাংশন দূরে রাখুন
এই ভুলটি নিঃশব্দে ইনডেক্সগুলোকে নিষ্ক্রিয় করে দেয়। যখন আপনি একটি WHERE ক্লজের ভেতরে কোনো কলামের ওপর একটি ফাংশন প্রয়োগ করেন, তখন MySQL পারে না