🥇 এক এসকিউএল তদন্তের গল্প

গত ডিসেম্বরে আমি VWO সমর্থন দলের কাছ থেকে একটি আকর্ষণীয় বাগ রিপোর্ট পেয়েছি। একটি বৃহৎ কর্পোরেট ক্লায়েন্টের জন্য বিশ্লেষণ প্রতিবেদনগুলির একটির জন্য লোড করার সময়টি নিষিদ্ধ বলে মনে হয়েছিল। এবং যেহেতু এটি আমার দায়িত্বের ক্ষেত্র, আমি অবিলম্বে সমস্যা সমাধানের দিকে মনোনিবেশ করেছি।

প্রাগঐতিহাসিক

আমি কি সম্পর্কে কথা বলছি তা পরিষ্কার করার জন্য, আমি আপনাকে VWO সম্পর্কে একটু বলব। এটি এমন একটি প্ল্যাটফর্ম যার সাহায্যে আপনি আপনার ওয়েবসাইটগুলিতে বিভিন্ন লক্ষ্যযুক্ত প্রচারাভিযান চালু করতে পারেন: A/B পরীক্ষা পরিচালনা করুন, দর্শক এবং রূপান্তরগুলি ট্র্যাক করুন, বিক্রয় ফানেল বিশ্লেষণ করুন, তাপ মানচিত্র প্রদর্শন করুন এবং ভিজিট রেকর্ডিং প্লে করুন৷

কিন্তু প্ল্যাটফর্মের সবচেয়ে গুরুত্বপূর্ণ বিষয় হল রিপোর্টিং। উপরের সমস্ত ফাংশন আন্তঃসংযুক্ত। এবং কর্পোরেট ক্লায়েন্টদের জন্য, একটি শক্তিশালী প্ল্যাটফর্ম ছাড়া বিপুল পরিমাণ তথ্য কেবল অকেজো হয়ে যাবে যা বিশ্লেষণ আকারে উপস্থাপন করে।

প্ল্যাটফর্মটি ব্যবহার করে, আপনি একটি বড় ডেটা সেটে একটি এলোমেলো প্রশ্ন করতে পারেন। এখানে একটি সহজ উদাহরণ:

যারা ক্রোম ব্যবহার করেছেন বা (ইউরোপে অবস্থিত এবং একটি আইফোন ব্যবহার করেছেন) তাদের জন্য <date d1> থেকে <date d2> পৃষ্ঠা "abc.com"-এ সমস্ত ক্লিক দেখান

বুলিয়ান অপারেটরদের প্রতি মনোযোগ দিন। তারা নমুনা প্রাপ্ত করার জন্য নির্বিচারে জটিল প্রশ্ন করতে ক্যোয়ারী ইন্টারফেসে ক্লায়েন্টদের কাছে উপলব্ধ।

ধীর অনুরোধ

প্রশ্নযুক্ত ক্লায়েন্ট এমন কিছু করার চেষ্টা করছিল যা স্বজ্ঞাতভাবে দ্রুত কাজ করা উচিত:

ব্যবহারকারীদের জন্য সমস্ত সেশন রেকর্ড দেখান যারা "/jobs" যুক্ত URL সহ যেকোনো পৃষ্ঠা পরিদর্শন করেছেন

এই সাইটে প্রচুর ট্রাফিক ছিল এবং আমরা এটির জন্য এক মিলিয়নেরও বেশি অনন্য URL সঞ্চয় করছিলাম৷ এবং তারা তাদের ব্যবসার মডেলের সাথে সম্পর্কিত একটি মোটামুটি সহজ URL টেমপ্লেট খুঁজে পেতে চেয়েছিল।

প্রাথমিক তদন্ত

আসুন ডাটাবেসে কি হচ্ছে তা একবার দেখে নেওয়া যাক। নীচে মূল ধীর SQL ক্যোয়ারী আছে:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

এবং এখানে সময় আছে:

পরিকল্পিত সময়: 1.480 ms সম্পাদনের সময়: 1431924.650 ms

প্রশ্নটি 150 হাজার সারি ক্রল করেছে৷ ক্যোয়ারী পরিকল্পনাকারী কয়েকটি আকর্ষণীয় বিবরণ দেখিয়েছে, কিন্তু কোন সুস্পষ্ট বাধা নেই।

এর অনুরোধ আরও অধ্যয়ন করা যাক. আপনি দেখতে পাচ্ছেন, তিনি করেন JOIN তিনটি টেবিল:

সেশন: সেশন তথ্য প্রদর্শন করতে: ব্রাউজার, ব্যবহারকারী এজেন্ট, দেশ, এবং তাই।
রেকর্ডিং_ডেটা: রেকর্ড করা URL, পৃষ্ঠা, পরিদর্শনের সময়কাল
URL গুলি: অত্যন্ত বড় URL-এর নকল এড়াতে, আমরা সেগুলিকে একটি পৃথক টেবিলে সংরক্ষণ করি।

এছাড়াও মনে রাখবেন যে আমাদের সমস্ত টেবিল ইতিমধ্যে দ্বারা বিভাজন করা হয়েছে account_id. এইভাবে, একটি পরিস্থিতি যেখানে একটি বিশেষভাবে বড় অ্যাকাউন্ট অন্যদের জন্য সমস্যা সৃষ্টি করে তা বাদ দেওয়া হয়।

ক্লুস খুঁজছি

ঘনিষ্ঠভাবে পরিদর্শন করার পরে, আমরা দেখতে পাই যে একটি নির্দিষ্ট অনুরোধে কিছু ভুল আছে। এই লাইনটি ঘনিষ্ঠভাবে দেখার জন্য এটি মূল্যবান:

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

প্রথম চিন্তা ছিল যে সম্ভবত কারণ ILIKE এই সমস্ত দীর্ঘ URL-এ (আমাদের 1,4 মিলিয়নেরও বেশি অনন্য এই অ্যাকাউন্টের জন্য সংগৃহীত URL) কর্মক্ষমতা ক্ষতিগ্রস্ত হতে পারে।

কিন্তু না, বিষয়টা এমন নয়!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

টেমপ্লেট অনুসন্ধানের অনুরোধ নিজেই মাত্র 5 সেকেন্ড সময় নেয়। এক মিলিয়ন অনন্য URL-এ একটি প্যাটার্ন অনুসন্ধান করা স্পষ্টতই একটি সমস্যা নয়।

তালিকায় পরবর্তী সন্দেহভাজন বেশ কয়েকজন JOIN. সম্ভবত তাদের অত্যধিক ব্যবহার মন্দার কারণ হয়েছে? সাধারণত JOINপারফরম্যান্স সমস্যার জন্য সবচেয়ে সুস্পষ্ট প্রার্থী, কিন্তু আমি বিশ্বাস করিনি যে আমাদের কেসটি সাধারণ।

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

এবং এটি আমাদের ক্ষেত্রে ছিল না। JOINএটা বেশ দ্রুত হতে পরিণত.

সন্দেহভাজনদের বৃত্ত সংকুচিত করা হচ্ছে

আমি কোনো সম্ভাব্য কর্মক্ষমতা উন্নতি অর্জনের জন্য ক্যোয়ারী পরিবর্তন শুরু করতে প্রস্তুত ছিলাম। আমার দল এবং আমি 2টি প্রধান ধারণা তৈরি করেছি:

সাবকোয়েরি ইউআরএলের জন্য বিদ্যমান ব্যবহার করুন: URL-এর জন্য সাবকোয়েরিতে কোনো সমস্যা আছে কিনা আমরা আবার পরীক্ষা করতে চেয়েছিলাম। এটি অর্জন করার একটি উপায় হল সহজভাবে ব্যবহার করা EXISTS. EXISTS করতে পারেন কর্মক্ষমতা ব্যাপকভাবে উন্নত করে যেহেতু এটি শর্তের সাথে মেলে এমন একমাত্র স্ট্রিং খুঁজে পাওয়ার সাথে সাথে এটি অবিলম্বে শেষ হয়ে যায়।

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

হ্যাঁ ঠিক. সাবকোয়ারি যখন মোড়ানো EXISTS, সবকিছু অতি দ্রুত করে তোলে। পরবর্তী যৌক্তিক প্রশ্ন কেন সঙ্গে অনুরোধ JOIN-ami এবং subquery নিজেই স্বতন্ত্রভাবে দ্রুত, কিন্তু একসঙ্গে ভয়ঙ্কর ধীর?

সাবকোয়ারিটি CTE-তে সরানো হচ্ছে : যদি ক্যোয়ারীটি নিজে থেকে দ্রুত হয়, তাহলে আমরা প্রথমে দ্রুত ফলাফল গণনা করতে পারি এবং তারপর এটি মূল ক্যোয়ারীতে প্রদান করতে পারি

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

কিন্তু এটি এখনও খুব ধীর ছিল.

অপরাধীর খোঁজ

এই সমস্ত সময়, একটি ছোট জিনিস আমার চোখের সামনে ভেসে উঠল, যা আমি ক্রমাগত একপাশে সরিয়ে রেখেছি। কিন্তু যেহেতু আর কিছুই অবশিষ্ট ছিল না, তাই আমি তার দিকেও তাকানোর সিদ্ধান্ত নিলাম। আমি যে বিষয়ে কথা বলছি && অপারেটর. বিদায় EXISTS শুধু উন্নত কর্মক্ষমতা && ধীরগতির কোয়েরির সমস্ত সংস্করণে একমাত্র অবশিষ্ট সাধারণ ফ্যাক্টর ছিল।

দিকে তাকাও ডকুমেন্টেশন, আমরা যে দেখতে && যখন আপনাকে দুটি অ্যারের মধ্যে সাধারণ উপাদানগুলি খুঁজে বের করতে হবে তখন ব্যবহৃত হয়।

মূল অনুরোধে এটি হল:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

যার মানে আমরা আমাদের URL গুলিতে একটি প্যাটার্ন অনুসন্ধান করি, তারপর সাধারণ পোস্ট সহ সমস্ত URL এর সাথে ছেদ খুঁজে পাই৷ এটি কিছুটা বিভ্রান্তিকর কারণ এখানে "urls" সমস্ত ইউআরএল সমন্বিত সারণীকে উল্লেখ করে না, কিন্তু টেবিলের "urls" কলামে উল্লেখ করে recording_data.

সম্পর্কে ক্রমবর্ধমান সন্দেহ সঙ্গে &&, আমি জেনারেট করা ক্যোয়ারী প্ল্যানে তাদের জন্য নিশ্চিতকরণ খোঁজার চেষ্টা করেছি EXPLAIN ANALYZE (আমার ইতিমধ্যে একটি পরিকল্পনা সংরক্ষিত ছিল, কিন্তু আমি সাধারণত কোয়েরি প্ল্যানারদের অস্বচ্ছতা বোঝার চেষ্টা করার চেয়ে SQL এ পরীক্ষা করতে বেশি স্বাচ্ছন্দ্যবোধ করি)।

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

শুধুমাত্র থেকে ফিল্টার বিভিন্ন লাইন ছিল &&. যার অর্থ এই অপারেশনটি কেবল ব্যয়বহুল নয়, বেশ কয়েকবার করা হয়েছিল।

আমি শর্ত বিচ্ছিন্ন করে এটি পরীক্ষা করেছি

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

এই প্রশ্ন ধীর ছিল. কারন JOIN-s দ্রুত এবং subqueries দ্রুত, শুধুমাত্র জিনিস বাকি ছিল && অপারেটর.

এটি একটি মূল অপারেশন মাত্র। একটি প্যাটার্ন অনুসন্ধান করার জন্য আমাদের সর্বদা URL-এর সম্পূর্ণ অন্তর্নিহিত সারণী অনুসন্ধান করতে হবে এবং আমাদের সর্বদা ছেদ খুঁজে বের করতে হবে। আমরা সরাসরি ইউআরএল রেকর্ড দ্বারা অনুসন্ধান করতে পারি না, কারণ এগুলি শুধুমাত্র আইডি উল্লেখ করে urls.

সমাধানের পথে

&& ধীর কারণ উভয় সেট বিশাল। আমি প্রতিস্থাপন করলে অপারেশন তুলনামূলকভাবে দ্রুত হবে urls উপর { "http://google.com/", "http://wingify.com/" }.

আমি ব্যবহার না করে পোস্টগ্রেসে সেট ইন্টারসেকশন করার একটি উপায় খুঁজতে শুরু করেছি &&, কিন্তু অনেক সাফল্য ছাড়া.

শেষ পর্যন্ত, আমরা বিচ্ছিন্নভাবে সমস্যাটি সমাধান করার সিদ্ধান্ত নিয়েছি: আমাকে সবকিছু দিন urls যে লাইনগুলির জন্য URL প্যাটার্নের সাথে মেলে। অতিরিক্ত শর্ত ছাড়া এটি হবে -

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

এর পরিবর্তে JOIN সিনট্যাক্স আমি শুধু একটি সাবকোয়েরি ব্যবহার করেছি এবং প্রসারিত করেছি recording_data.urls অ্যারে যাতে আপনি সরাসরি শর্তটি প্রয়োগ করতে পারেন WHERE.

এখানে সবচেয়ে গুরুত্বপূর্ণ বিষয় হল যে && প্রদত্ত এন্ট্রিতে একটি মিল ইউআরএল রয়েছে কিনা তা পরীক্ষা করতে ব্যবহৃত হয়। আপনি যদি একটু squint, আপনি দেখতে পারেন এই অপারেশন একটি অ্যারের উপাদান (বা একটি টেবিলের সারি) মাধ্যমে সরানো এবং একটি শর্ত (ম্যাচ) পূরণ হলে বন্ধ হয়. কিছু মনে করিয়ে দেয় না? হ্যাঁ, EXISTS.

থেকে recording_data.urls সাবকোয়েরি প্রসঙ্গের বাইরে থেকে উল্লেখ করা যেতে পারে, যখন এটি ঘটে তখন আমরা আমাদের পুরানো বন্ধুর দিকে ফিরে যেতে পারি EXISTS এবং এটি দিয়ে সাবকুয়েরি মোড়ানো।

সবকিছু একসাথে রেখে, আমরা চূড়ান্ত অপ্টিমাইজ করা প্রশ্নটি পাই:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

এবং চূড়ান্ত লিড সময় Time: 1898.717 ms উদযাপনের সময়?!?

এত দ্রুত না! প্রথমে আপনাকে সঠিকতা পরীক্ষা করতে হবে। আমি অত্যন্ত সন্দেহজনক ছিল EXISTS অপ্টিমাইজেশান যেহেতু এটি লজিককে আগে সম্পূর্ণ করতে পরিবর্তন করে। আমাদের নিশ্চিত হওয়া দরকার যে আমরা অনুরোধে একটি অ-স্পষ্ট ত্রুটি যোগ করিনি।

একটি সহজ পরীক্ষা চালানোর ছিল count(*) প্রচুর পরিমাণে বিভিন্ন ডেটা সেটের জন্য ধীর এবং দ্রুত উভয় প্রশ্নেই। তারপর, ডেটার একটি ছোট উপসেটের জন্য, আমি নিজে যাচাই করেছি যে সমস্ত ফলাফল সঠিক ছিল।

সমস্ত পরীক্ষা ধারাবাহিকভাবে ইতিবাচক ফলাফল দিয়েছে। আমরা সবকিছু ঠিক করেছি!

পাঠ শিখেছি

এই গল্প থেকে অনেক শিক্ষা আছে:

ক্যোয়ারী প্ল্যান পুরো গল্প বলে না, কিন্তু তারা সূত্র দিতে পারে
প্রধান সন্দেহভাজনরা সবসময় প্রকৃত অপরাধী হয় না
বাধা বিচ্ছিন্ন করার জন্য ধীরগতির প্রশ্নগুলি ভেঙে দেওয়া যেতে পারে
সমস্ত অপ্টিমাইজেশন প্রকৃতিতে হ্রাসমূলক নয়
ব্যবহারের EXIST, যেখানে সম্ভব, উত্পাদনশীলতা নাটকীয় বৃদ্ধি হতে পারে

উপসংহার

আমরা ~24 মিনিট থেকে 2 সেকেন্ডের একটি ক্যোয়ারী সময় চলেছি - বেশ উল্লেখযোগ্য কর্মক্ষমতা বৃদ্ধি! যদিও এই নিবন্ধটি বড় আকারে প্রকাশিত হয়েছে, আমরা যে সমস্ত পরীক্ষা-নিরীক্ষা করেছিলাম তা একদিনে ঘটেছিল এবং অনুমান করা হয়েছিল যে তারা অপ্টিমাইজেশন এবং পরীক্ষার জন্য 1,5 থেকে 2 ঘন্টা সময় নিয়েছে।

এসকিউএল একটি দুর্দান্ত ভাষা যদি আপনি এটিকে ভয় না পান তবে এটি শিখতে এবং ব্যবহার করার চেষ্টা করুন। এসকিউএল কোয়েরিগুলি কীভাবে কার্যকর করা হয়, ডাটাবেস কীভাবে কোয়েরি পরিকল্পনা তৈরি করে, কীভাবে সূচকগুলি কাজ করে এবং আপনি যে ডেটার সাথে কাজ করছেন তার আকার সম্পর্কে ভাল বোঝার মাধ্যমে, আপনি কোয়েরিগুলি অপ্টিমাইজ করার ক্ষেত্রে খুব সফল হতে পারেন। এটি সমান গুরুত্বপূর্ণ, তবে, বিভিন্ন পদ্ধতির চেষ্টা চালিয়ে যাওয়া এবং ধীরে ধীরে সমস্যাটি ভেঙে ফেলা, বাধাগুলি খুঁজে বের করা।

এই ধরনের ফলাফল অর্জনের সবচেয়ে ভালো দিকটি হল লক্ষণীয়, দৃশ্যমান গতির উন্নতি - যেখানে একটি প্রতিবেদন যা আগে লোড হতো না এখন প্রায় তাৎক্ষণিকভাবে লোড হয়।

বিশেষ ধন্যবাদ আমার কমরেডরা আদিত্য মিশ্রের নির্দেশে, আদিত্য গৌরু и বরুণ মালহোত্রা বুদ্ধিমত্তার জন্য এবং দিনকর পণ্ডির আমরা শেষ পর্যন্ত বিদায় জানানোর আগে আমাদের চূড়ান্ত অনুরোধে একটি গুরুত্বপূর্ণ ত্রুটি খুঁজে পাওয়ার জন্য!

উত্স: www.habr.com

একটি এসকিউএল তদন্তের গল্প