Query ধীর গতিতে চলছে, কিভাবে খুঁজে বের করবেন সমস্যাটা? (পর্ব ৩)

আমার colleague এখন প্ল্যান দেখতে পারছে। Scan types বুঝতে পারছে। Join types বুঝতে পারছে। Estimate আর actual এর gap দেখতে পারছে। BUFFERS ও দেখছে।

কিন্তু সে প্রশ্ন করল। এসব দেখে কি করব? Step by step কোন পথে যাব?

আমি বললাম। পাঁচটা step আছে। অর্ডার অনুযায়ী।

পর্ব ২ এ আমি বলেছিলাম scan types, join types, estimate আর actual এর gap। BUFFERS কি। এবার আসি সমাধান এ।

Diagnostic Workflow

আপনার কাছে একটা slow query এসেছে। কিভাবে debug করবেন?

এই পাঁচটা প্রশ্ন করুন অর্ডার অনুযায়ী। ৯০% slow query প্রথম বা দ্বিতীয় ধাপেই solve হয়ে যায়।

১. Deepest Seq Scan দেখুন

Table বড় কি না? Filter selective কি না? Missing index থাকলে add করুন।

আজই শুরু করুন যখন একটা Seq Scan দেখবেন big table এ, প্রথমে WHERE clause টা check করুন। Selective কি না? ৫% এর কম row return হওয়ার কথা? যদি তাই হয়, index missing। CREATE INDEX idx_name ON table(column) run করুন।

২. Join types দেখুন

কোনো Nested Loop আছে কিন্তু দুই পাশেই বড় table? Hash Join force করুন বা ডান পাশে index add করুন।

আজই শুরু করুন Nested Loop দেখলে ডান পাশের table এ index check করুন। যদি না থাকে, create করুন। Index থাকা সত্ত্বেও planner Nested Loop use করছে? SET enable_nestloop = off temporarily disable করে দেখুন। Hash Join আসবে কি না।

৩. Row estimates দেখুন

Estimate vs actual ১০x এর বেশি difference? ANALYZE table দিন বা predicate rewrite করুন।

আজই শুরু করুন rows=1 estimate কিন্তু rows=100000 actual দেখলে ANALYZE tablename run করুন। Statistics refresh হবে। তারপর plan আবার দেখুন। যদি তাও না আসে, WHERE clause rewrite করুন। Function call থাকলে remove করুন। Type mismatch থাকলে fix করুন।

৪. BUFFERS add করুন

কোনো node এ অনেক disk reads? Caching investigate করুন।

আজই শুরু করুন EXPLAIN (ANALYZE, BUFFERS) run করে দেখুন shared read high কোথায়। সেই node টাই bottleneck। Index add করলে reads কমবে। Pre-warm cache করতে পারেন। Data pre-load করতে পারেন।

৫. Sorts আর hashes দেখুন

কোনো spill-to-disk আছে? work_mem raise করুন বা sort eliminate করুন।

আজই শুরু করুন Plan এ external merge Disk: 421MB দেখলে spill-to-disk হয়েছে। SET work_mem = '256MB' temporarily raise করে দেখুন। অথবা query rewrite করে memory requirement কমান। Hash join এর যে পাশ ছোট সেই পাশ এ পাঠান।

এই পাঁচটা ধাপে ১০ মিনিট এর মধ্যে সব slow query solve হবে। যদি পঞ্চম ধাপে যাওয়ার পরেও কিছু না পান, তারমানে query already as fast as possible। এখন schema change করতে হবে। Denormalized column add করতে পারেন। Pre-aggregation করতে পারেন। Materialized view বানাতে পারেন।

BigQuery, Snowflake এ কি?

আপনি যদি BigQuery বা Snowflake use করেন তাহলে কি? কথা একই। Vocabulary change হয়। Postgres এ Hash Join, BigQuery এ Stage 3: JOIN। Snowflake এ Hash Join probe side। কিন্তু concept same। তিনটা জিনিস দেখবেনই। table কিভাবে read হচ্ছে, join কিভাবে implement হচ্ছে, estimate match করছে কি না।

Columnar warehouse এ একটা extra জিনিস। কত bytes query scan করেছে। ওরা per byte charge করে। ৫TB scan আর ৫০GB scan একই answer দিতে পারে, কিন্তু ৫০GB সস্তা।

Common Mistakes

কিছু common mistake আছে যা avoid করবেন।

Cost number কে seconds ভাববেন না। সেটা not seconds। Node compare করার জন্য, time predict করার জন্য না।

ANALYZE skip করবেন না। EXPLAIN without ANALYZE planner এর guess। Actuals থেকেই সত্যি বের হয়।

ANALYZE ছাড়া query tune করবেন না। Stale statistics থেকে bad plan। Refresh করুন, তারপর plan পড়ুন, তারপর tune করুন।

Local dev এর plan এর উপর ভরসা করবেন না। আপনার machine এ tiny data, hot cache। Production এ large data, cold cache। Prod এ বা prod-like copy তে EXPLAIN ANALYZE run করুন।

শুধু "it uses an index" এ থেমে যাবেন না। Index ব্যবহার করতে পারে ভুল ভাবে। ১০ মিলিয়ন outer row এর সাথে Nested Loop যেখানে প্রতিটা row এর জন্য index lookup, সেটা ১০ মিলিয়ন round trip। Plan index use করছে, query slow।

বিনা measure এ index add করবেন না। প্রতিটা new index write cost। Insert আর update এর জন্য। শুধু যে query এর দরকার সেটাই add করুন, বাকি গুলো remove করুন।

BUFFERS ignore করবেন না। একই plan এর দুটা query ৩০x difference হতে পারে cache এর কারণে। BUFFERS সেটা বের করে।

শেষ করি

EXPLAIN planner এর plan দেখায়। EXPLAIN ANALYZE query run করে truth দেখায়। যখন পারবেন ANALYZE use করুন।

তিনটা signal most important। biggest table এ scan type, join type, estimate আর actual row এর gap।

Seq Scan ছোট table এ fine, বড় table এ red flag। Nested Loop index এর সাথে fast, index ছাড়া disaster।

Cost numbers seconds না। Compare করার জন্য, wall-clock time এর জন্য actual time।

EXPLAIN (ANALYZE, BUFFERS) দেখায় disk থেকে কি এসেছে। cold আর warm cache এর মধ্যে ৩০x gap এখানে থাকে।

Diagnostic workflow পাঁচটা প্রশ্ন অর্ডারে। সব slow query missing index বা stale statistics এ পড়ে যায়।

শেষ করি এই কথা দিয়ে। আমার colleague যে query নিয়ে এসেছিল, সেটা solve করতে কত time লাগল জানেন? ৫ মিনিট। তিনি dev এর plan দেখছিলেন। আমি বললাম prod copy তে EXPLAIN ANALYZE run করতে। সেখানে দেখা গেল orders table এ ৫ মিলিয়ন row, but planner estimate করেছিল ১০০। ANALYZE run করার পর plan change হলো। Nested Loop থেকে Hash Join এ। ২০ মিনিট থেকে ২ সেকেন্ড এ এসে গেল।

মানে, সমস্যা ছিল statistics এত। আর সমাধান ছিল PLAN এর মধ্যে, না guess এর মধ্যে।

প্রশ্ন হলো, আপনার পরের slow query তে আপনি guess করবেন, নাকি PLAN পড়বেন?

Source: Reading an EXPLAIN plan by Amirul Islam

পূর্ববর্তী পর্ব: পর্ব ২ | পর্ব ১