মনে আছে কিভাবে শুরু হয়েছিল সব। সবকিছু প্রথম বার এবং বারবার ছিল

পোস্টগ্রেএসকিউএল ক্যোয়ারী অপ্টিমাইজেশানের সাথে আমাকে কীভাবে মোকাবেলা করতে হয়েছিল এবং এই সমস্ত থেকে কী বেরিয়ে এসেছে সে সম্পর্কে।
কেন করতে হবে? হ্যাঁ, কারণ আগের 4 বছর সবকিছু শান্তভাবে কাজ করেছিল, শান্তভাবে, যেমন একটি ঘড়ি টিক টিক করছিল।
একটি এপিগ্রাফ হিসাবে।

বাস্তব ঘটনার উপর ভিত্তি করে।
সব নাম পরিবর্তন করা হয়েছে, কাকতালীয় এলোমেলো।

যখন একটি নির্দিষ্ট ফলাফল অর্জন করা হয়, এটি সর্বদা মনে রাখা আকর্ষণীয় যে শুরুর জন্য অনুপ্রেরণা কী ছিল, এটি কীভাবে শুরু হয়েছিল।

সুতরাং, ফলস্বরূপ যা ঘটেছে তা সংক্ষিপ্তভাবে নিবন্ধে বর্ণিত হয়েছে "PostgreSQL কর্মক্ষমতা উন্নত করার পদ্ধতিগুলির মধ্যে একটি হিসাবে সংশ্লেষণ».

পূর্ববর্তী ইভেন্টগুলির চেইনটি পুনরায় তৈরি করা সম্ভবত আকর্ষণীয় হবে।
ইতিহাস সঠিক শুরুর তারিখ রেখেছে — 2018-09-10 18:02:48।
এছাড়াও, গল্পে একটি অনুরোধ রয়েছে যেখান থেকে এটি সব শুরু হয়েছিল:
সমস্যা অনুরোধনির্বাচন
p. "PARAMETER_ID" parameter_id হিসাবে,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" হিসাবে গ্রাহক_অংশীদার নম্বর,
w"LRM" AS LRM,
w. "LOTID" যেমন লটিড,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" হিসাবে low_spec_limit,
w. "UPPER_SPEC_LIMIT" as upper_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS spent_date,
নির্যাস ("SPENT_DATE" থেকে বছর) AS বছর,
মাস হিসাবে নির্যাস ("SPENT_DATE" থেকে মাস),
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
wdataw থেকে,
খরচ
pmtrp,
spent_pdsp,
pd pd
কোথায় s।"SPENT_ID" = w."SPENT_ID"
এবং p."PARAMETER_ID" = w."PARAMETER_ID"
এবং s."SPENT_ID" = sp."SPENT_ID"
এবং pd. "PD_ID" = sp. "PD_ID"
AND s."SPENT_DATE" >= '2018-07-01' এবং s."SPENT_DATE" <= '2018-09-30'
এবং s."SPENT_DATE" = (নির্বাচন করুন MAX(s2."SPENT_DATE")
s2 খরচ করা থেকে,
wdata w2
যেখানে s2।"SPENT_ID" = w2।"SPENT_ID"
এবং w2."LRM" = w."LRM");

সমস্যার বর্ণনা, অনুমানযোগ্যভাবে আদর্শ - "সবকিছুই খারাপ। কি সমস্যা বলো।"
আমার সাথে সাথে সাড়ে ৩ ইঞ্চি ড্রাইভের সময়ের একটি কৌতুক মনে পড়ে গেল:

ল্যামার হ্যাকারের কাছে আসে।
- আমার জন্য কিছুই কাজ করে না, সমস্যা কোথায় বলুন।
-ডিএনএ-তে...

কিন্তু, অবশ্যই, এই কর্মক্ষমতা ঘটনা সমাধান করার উপায় নয়. "আমরা হয়তো বুঝতে পারছি না" (সঙ্গে). এটা বের করতে হবে।
আচ্ছা, এর খনন করা যাক। হয়তো সেটার ফলে জমে উঠবে।

বিনিয়োগ শুরু হয়েছে

সুতরাং, খালি চোখে অবিলম্বে কী দেখা যায়, এমনকি EXPLAIN এর সাহায্য না নিয়েও।
1) JOIN ব্যবহার করা হয় না। এটি খারাপ, বিশেষ করে যদি সংযোগের সংখ্যা একাধিক হয়।
2) কিন্তু এর চেয়েও খারাপ কি - একটি পারস্পরিক সম্পর্কযুক্ত সাবকুয়েরি, অধিকন্তু, সমষ্টি সহ। এটা খুব খারাপ.
এটা অবশ্যই খারাপ। কিন্তু এটি শুধুমাত্র একদিকে। অন্যদিকে, এটি খুব ভাল, কারণ সমস্যার স্পষ্টভাবে একটি সমাধান রয়েছে এবং অনুরোধটি উন্নত করা যেতে পারে।
ভাগ্যবানের (সি) কাছে যাবেন না।
ক্যোয়ারী পরিকল্পনাটি জটিল নয়, তবে বেশ ইঙ্গিতপূর্ণ:
হত্যা পরিকল্পনা

সবচেয়ে আকর্ষণীয় এবং দরকারী, যথারীতি, শুরুতে এবং শেষে।
নেস্টেড লুপ (খরচ=935.84..479763226.18 সারি=3322 প্রস্থ=135) (প্রকৃত সময়=31.536..8220420.295 সারি=8111656 লুপ=1)
পরিকল্পনার সময়: 3.807 মি
সম্পাদনের সময়: 8222351.640ms
মৃত্যুদন্ড কার্যকর করার সময় 2 ঘন্টার বেশি।

মিথ্যা অনুমান যা সময় নিয়েছে

হাইপোথিসিস 1- অপ্টিমাইজার ভুল, ভুল পরিকল্পনা তৈরি করে।

এক্সিকিউশন প্ল্যানটি কল্পনা করতে, আমরা সাইটটি ব্যবহার করব https://explain.depesz.com/. তবে, সাইটটি আকর্ষণীয় বা দরকারী কিছু দেখায়নি। প্রথম এবং দ্বিতীয় নজরে - সত্যিই সাহায্য করতে পারে যে কিছুই. যদি না - সম্পূর্ণ স্ক্যান ন্যূনতম। এগিয়ে যান.

হাইপোথিসিস 2-অটোভ্যাকুয়ামের পাশ থেকে বেসের উপর প্রভাব, আপনাকে ব্রেকগুলি থেকে মুক্তি পেতে হবে।

কিন্তু, অটোভ্যাকুয়াম ডেমনগুলি ভাল আচরণ করে, কোনও দীর্ঘ-ঝুলে থাকা প্রক্রিয়া নেই। কোন গুরুতর লোড - না. অন্য কিছু খুঁজতে হবে।

হাইপোথিসিস 3-পরিসংখ্যান পুরানো, আপনাকে সবকিছু পুনঃগণনা করতে হবে

আবার, যে না. পরিসংখ্যান আপ টু ডেট. যা, অটোভ্যাকুয়ামের সমস্যার অভাবের কারণে, আশ্চর্যজনক নয়।

অপ্টিমাইজ করা শুরু হচ্ছে

মূল টেবিল 'wdata' অবশ্যই ছোট নয়, প্রায় 3 মিলিয়ন রেকর্ড।
এবং এটি এই টেবিলে যে সম্পূর্ণ স্ক্যান যায়.

হ্যাশ কন্ড: ((w."SPENT_ID" = s."SPENT_ID") এবং ((সাবপ্ল্যান 1) = s."SPENT_DATE"))
-> সেক স্ক্যান wdata w এ (cost=0.00..574151.49 সারি=26886249 প্রস্থ=46) (প্রকৃত সময়=0.005..8153.565 সারি=26873950 লুপ=1)
আমরা স্ট্যান্ডার্ড হিসাবে কাজ করি: "আসুন একটি সূচক তৈরি করি এবং সবকিছু উড়ে যায়"।
"SPENT_ID" ক্ষেত্রে একটি সূচক তৈরি করেছে
ফলস্বরূপ:
একটি সূচক ব্যবহার করে এক্সিকিউশন প্ল্যান জিজ্ঞাসা করুন

আচ্ছা, এটা সাহায্য করেছে?
ইহা ছিল: 8 222 351.640 ms (মাত্র 2 ঘন্টার বেশি)
এটা হয়ে ওঠে: 6 985 431.575 ms (প্রায় 2 ঘন্টা)
সাধারণভাবে, একই আপেল, সাইড ভিউ।
আসুন ক্লাসিক মনে রাখা যাক:
“আপনার কি একই আছে, কিন্তু ডানা ছাড়া? খুঁজবে"।

নীতিগতভাবে, এটি একটি ভাল ফলাফল বলা যেতে পারে, ভাল, ভাল নয়, তবে গ্রহণযোগ্য। অন্ততপক্ষে, কতটা করা হয়েছে এবং কেন কী করা হয়েছে তা বর্ণনা করে গ্রাহককে একটি বড় রিপোর্ট প্রদান করুন।
তবে চূড়ান্ত সিদ্ধান্ত এখনো অনেক দূরে। অনেক দূর।

এবং এখন সবচেয়ে আকর্ষণীয় জিনিস - আমরা অপ্টিমাইজ করা চালিয়ে যাচ্ছি, আমরা ক্যোয়ারীটি পোলিশ করব

প্রথম ধাপ - JOIN ব্যবহার করুন

পুনরায় লিখিত প্রশ্ন, এখন এই মত দেখায় (ভাল অন্তত সুন্দর):
JOIN ব্যবহার করে প্রশ্ন করুননির্বাচন
p. "PARAMETER_ID" parameter_id হিসাবে,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" হিসাবে গ্রাহক_অংশীদার নম্বর,
w"LRM" AS LRM,
w. "LOTID" যেমন লটিড,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" হিসাবে low_spec_limit,
w. "UPPER_SPEC_LIMIT" as upper_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS spent_date,
নির্যাস ("SPENT_DATE" থেকে বছর) AS বছর,
মাস হিসাবে নির্যাস ("SPENT_DATE" থেকে মাস),
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
ডাব্লুডাটা থেকে ভিতরের যোগদানের জন্য খরচ হয়েছে w."SPENT_ID"=s."SPENT_ID"
অভ্যন্তরীণ যোগদান pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER Join spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
অভ্যন্তরীণ যোগদান pd pd ON pd."PD_ID" = sp."PD_ID"
কোথায়
s."SPENT_DATE" >= '2018-07-01' এবং s."SPENT_DATE" <= '2018-09-30'AND
s."SPENT_DATE" = (MAX(s2."SPENT_DATE") নির্বাচন করুন
ডাব্লুডাটা থেকে w2 অভ্যন্তরীণ যোগদান w2 তে s2 ব্যয় করেছে।"SPENT_ID"=s2।"SPENT_ID"
অভ্যন্তরীণ যোগদান wdata w
ON w2."LRM" = w."LRM" );
পরিকল্পনার সময়: 2.486 মি
সম্পাদনের সময়: 1223680.326ms

তাই এখানে প্রথম ফলাফল.
ইহা ছিল: 6 985 431.575 ms (প্রায় 2 ঘন্টা)।
এটা হয়ে ওঠে: 1 223 680.326 ms (মাত্র 20 মিনিটের বেশি)।
ভালো ফলাফল. নীতিগতভাবে, আবার, সেখানে থামানো সম্ভব হবে। কিন্তু এত আগ্রহহীন, আপনি থামাতে পারবেন না।
জন্য

ধাপ দুই - সম্পর্কযুক্ত সাবকোয়েরি থেকে মুক্তি পান

পরিবর্তিত অনুরোধ পাঠ্য:
কোনো সম্পর্কযুক্ত সাবকোয়েরি নেইনির্বাচন
p. "PARAMETER_ID" parameter_id হিসাবে,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" হিসাবে গ্রাহক_অংশীদার নম্বর,
w"LRM" AS LRM,
w. "LOTID" যেমন লটিড,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" হিসাবে low_spec_limit,
w. "UPPER_SPEC_LIMIT" as upper_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS spent_date,
নির্যাস ("SPENT_DATE" থেকে বছর) AS বছর,
মাস হিসাবে নির্যাস ("SPENT_DATE" থেকে মাস),
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
ডাব্লুডাটা থেকে INNER JoIN খরচ s ON s."SPENT_ID" = w."SPENT_ID"
অভ্যন্তরীণ যোগদান pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER Join spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
অভ্যন্তরীণ যোগদান pd pd ON pd."PD_ID" = sp."PD_ID"
অভ্যন্তরীণ যোগদান (w2 নির্বাচন করুন।"LRM", MAX(s2."SPENT_DATE")
খরচ s2 INNER Wdata w2 ON s2 এ যোগ দিন।"SPENT_ID" = w2."SPENT_ID"
গ্রুপ W2.LRM দ্বারা
) md on w. "LRM" = md. "LRM"
কোথায়
s."SPENT_DATE" >= '2018-07-01' এবং s."SPENT_DATE" <= '2018-09-30';
পরিকল্পনার সময়: 2.291 মি
সম্পাদনের সময়: 165021.870ms

ইহা ছিল: 1 223 680.326 ms (মাত্র 20 মিনিটের বেশি)।
এটা হয়ে ওঠে: 165 021.870 ms (মাত্র 2 মিনিটের বেশি)।
এই ইতিমধ্যে বেশ ভাল.
যাইহোক, ইংরেজরা যেমন বলে,কিন্তু, সবসময় একটি কিন্তু আছে" খুব ভাল ফলাফল স্বয়ংক্রিয়ভাবে সন্দেহ জাগ্রত করা উচিত. এখানে কিছু ভুল আছে.

পারস্পরিক সম্পর্কযুক্ত সাবকোয়েরি থেকে পরিত্রাণ পেতে ক্যোয়ারী সংশোধন করার অনুমান সঠিক। কিন্তু চূড়ান্ত ফলাফল সঠিক পেতে এটি একটু tweaking প্রয়োজন.
ফলস্বরূপ, প্রথম মধ্যবর্তী ফলাফল:
সম্পর্কযুক্ত সাবকোয়েরি ছাড়াই সম্পাদিত প্রশ্ননির্বাচন
p. "PARAMETER_ID" parameter_id হিসাবে,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" হিসাবে গ্রাহক_অংশীদার নম্বর,
w"LRM" AS LRM,
w. "LOTID" যেমন লটিড,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" হিসাবে low_spec_limit,
w. "UPPER_SPEC_LIMIT" as upper_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS spent_date,
নির্যাস (s. "SPENT_DATE" থেকে বছর) AS বছর,
নিষ্কাশন (s. "SPENT_DATE" থেকে মাস) মাস হিসাবে,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
ডাব্লুডাটা থেকে INNER JoIN খরচ s ON s."SPENT_ID" = w."SPENT_ID"
অভ্যন্তরীণ যোগদান pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER Join spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
অভ্যন্তরীণ যোগদান pd pd ON pd."PD_ID" = sp."PD_ID"
অভ্যন্তরীণ যোগদান ( নির্বাচন করুন w2."LRM", MAX(s2."SPENT_DATE") হিসাবে "SPENT_DATE"
খরচ s2 INNER Wdata w2 ON s2 এ যোগ দিন।"SPENT_ID" = w2."SPENT_ID"
গ্রুপ W2.LRM দ্বারা
) md ON md."SPENT_DATE" = s."SPENT_DATE" AND md."LRM" = w."LRM"
কোথায়
s."SPENT_DATE" >= '2018-07-01' এবং s."SPENT_DATE" <= '2018-09-30';
পরিকল্পনার সময়: 3.192 মি
সম্পাদনের সময়: 208014.134ms

সুতরাং, ফলস্বরূপ আমাদের যা আছে তা হল প্রথম গ্রহণযোগ্য ফলাফল, যা গ্রাহককে দেখাতে আমরা লজ্জিত নই:
দিয়ে শুরু: 8 222 351.640 ms (2 ঘন্টার বেশি)
অর্জিত: 1 ms (মাত্র 223 মিনিটের বেশি)।
ফলাফল (মধ্যবর্তী): 208 014.134 ms (মাত্র 3 মিনিটের বেশি)।

চমৎকার ফলাফল.

ফলাফল

এই বন্ধ হতে পারে.
কিন্তু…
খাওয়ার সাথে সাথে ক্ষুধা আসে। রাস্তা হেঁটে আয়ত্ত হবে। যেকোনো ফলাফল মধ্যবর্তী। থেমে গেল মৃত। ইত্যাদি।
চলুন অপ্টিমাইজেশান চালিয়ে যাই।
ভালো বুদ্ধি. বিশেষ করে গ্রাহকরা এর বিরুদ্ধেও ছিলেন না তা বিবেচনা করে। এবং এমনকি দৃঢ়ভাবে - জন্য।

সুতরাং, এটি ডাটাবেস পুনরায় ডিজাইন করার সময়. অনুরোধের কাঠামোটি নিজেই আর অপ্টিমাইজ করা যাবে না (যদিও, এটি পরে দেখা গেছে, সত্যিই উড়ার জন্য সবকিছুর জন্য একটি বিকল্প রয়েছে)। কিন্তু এখন ডাটাবেস ডিজাইনের অপ্টিমাইজেশন এবং ডেভেলপমেন্ট করতে, এটি ইতিমধ্যেই একটি খুব আশাব্যঞ্জক ধারণা। এবং সবচেয়ে গুরুত্বপূর্ণভাবে আকর্ষণীয়। আবার মনে পড়ে যৌবনের কথা। সর্বোপরি, আমি অবিলম্বে একজন ডিবিএ হয়ে উঠিনি, আমি প্রোগ্রামারদের থেকে বড় হয়েছি (বেসিক, অ্যাসেম্বলার, si, si দুইবার প্লাসড, ওরাকল, plsql)। একটি আকর্ষণীয় বিষয়, অবশ্যই, পৃথক স্মৃতিকথার জন্য ;-)।
যাইহোক, চলুন ডিগ্রীস না.

এবং তাই,

এবং হয়তো বিভাগ আমাদের সাহায্য করবে?
স্পয়লার - "হ্যাঁ, এটি সাহায্য করেছে, এবং কর্মক্ষমতা অপ্টিমাইজ করতে, সহ।"

কিন্তু এটা সম্পূর্ণ ভিন্ন গল্প...

চলবে…

উত্স: www.habr.com