নির্মাতাদের জন্য একটি B2B পরিষেবার উদাহরণ ব্যবহার করে ডাটাবেস প্রশ্নগুলি অপ্টিমাইজ করা

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

আমি নির্মাণ কোম্পানিতে ব্যবসায়িক প্রক্রিয়া পরিচালনার জন্য একটি পরিষেবা তৈরি করি। আমাদের সঙ্গে প্রায় তিন হাজার কোম্পানি কাজ করে। 3 হাজারেরও বেশি লোক আমাদের সিস্টেমের সাথে প্রতিদিন 10-4 ঘন্টা কাজ করে। এটি পরিকল্পনা, বিজ্ঞপ্তি, সতর্কতা, যাচাইকরণের বিভিন্ন সমস্যার সমাধান করে... আমরা PostgreSQL 10 ব্যবহার করি। আমাদের ডাটাবেসে প্রায় 9.6টি টেবিল রয়েছে এবং প্রতিদিন 300 মিলিয়ন প্রশ্ন (200 হাজার বিভিন্ন) পাওয়া যায়। গড়ে আমাদের প্রতি সেকেন্ডে 10-3 হাজার অনুরোধ রয়েছে, সবচেয়ে সক্রিয় মুহূর্তে প্রতি সেকেন্ডে 4 হাজারের বেশি অনুরোধ। বেশিরভাগ প্রশ্নই OLAP। অনেক কম সংযোজন, পরিবর্তন এবং মুছে ফেলা হয়েছে, যার অর্থ OLTP লোড তুলনামূলকভাবে হালকা। আমি এই সমস্ত নম্বর প্রদান করেছি যাতে আপনি আমাদের প্রকল্পের স্কেল মূল্যায়ন করতে পারেন এবং বুঝতে পারেন যে আমাদের অভিজ্ঞতা আপনার জন্য কতটা কার্যকর হতে পারে।

ছবি এক. লিরিক্যাল

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

ছবি দুই. পরিসংখ্যানগত

তাই আমাদের প্রায় 10 হাজার বিভিন্ন প্রশ্ন রয়েছে যা প্রতিদিন আমাদের ডাটাবেসে কার্যকর করা হয়। এই 10 হাজারের মধ্যে, এমন দানব রয়েছে যেগুলি 2-3 ms এর গড় কার্যকর করার সময় সহ 0.1-0.3 মিলিয়ন বার মৃত্যুদন্ড কার্যকর করা হয় এবং 30 সেকেন্ডের গড় কার্যকর করার সময় সহ প্রশ্ন রয়েছে যাকে দিনে 100 বার বলা হয়।

সমস্ত 10 হাজার প্রশ্ন অপ্টিমাইজ করা সম্ভব ছিল না, তাই আমরা সঠিকভাবে ডাটাবেসের কার্যকারিতা উন্নত করার জন্য আমাদের প্রচেষ্টাকে কোথায় নির্দেশ করতে হবে তা নির্ধারণ করার সিদ্ধান্ত নিয়েছি। বেশ কয়েকটি পুনরাবৃত্তির পরে, আমরা অনুরোধগুলিকে প্রকারগুলিতে ভাগ করতে শুরু করি।

শীর্ষ অনুরোধ

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

সব কোম্পানির স্বাভাবিক অভ্যাস হল টপ রিকোয়েস্ট নিয়ে কাজ করা। তাদের মধ্যে কয়েকটি আছে; এমনকি একটি কোয়েরি অপ্টিমাইজ করলে 5-10% সংস্থান মুক্ত হতে পারে। যাইহোক, প্রকল্পটি পরিপক্ক হওয়ার সাথে সাথে শীর্ষ প্রশ্নগুলি অপ্টিমাইজ করা একটি ক্রমবর্ধমান অ-তুচ্ছ কাজ হয়ে ওঠে। সমস্ত সহজ পদ্ধতি ইতিমধ্যে কাজ করা হয়েছে, এবং সবচেয়ে "ভারী" অনুরোধ "শুধুমাত্র" 3-5% সম্পদ লাগে। যদি মোট শীর্ষ প্রশ্নগুলি 30-40% এরও কম সময় নেয়, তবে সম্ভবত আপনি ইতিমধ্যেই সেগুলিকে দ্রুত কাজ করার জন্য প্রচেষ্টা করেছেন এবং পরবর্তী গ্রুপ থেকে অনুসন্ধানগুলি অপ্টিমাইজ করার জন্য এগিয়ে যাওয়ার সময় এসেছে৷
এই গ্রুপে কতগুলি শীর্ষ প্রশ্ন অন্তর্ভুক্ত করা উচিত সেই প্রশ্নের উত্তর দেওয়া বাকি। আমি সাধারণত কমপক্ষে 10 নিই, তবে 20 এর বেশি নয়। আমি নিশ্চিত করার চেষ্টা করি যে TOP গ্রুপে প্রথম এবং শেষের সময় 10 বারের বেশি আলাদা না হয়। অর্থাৎ, যদি ক্যোয়ারী এক্সিকিউশন টাইম 1ম স্থান থেকে 10 তম স্থানে দ্রুত নেমে যায়, তাহলে আমি TOP-10 নিই, যদি ড্রপটি আরও ধীরে ধীরে হয়, তাহলে আমি গ্রুপের আকার 15 বা 20-এ বাড়িয়ে দিই।

মধ্য কৃষক

শেষ 5-10% বাদ দিয়ে এই সমস্ত অনুরোধগুলি TOP-এর পরপরই আসে। সাধারণত, এই প্রশ্নগুলি অপ্টিমাইজ করার ক্ষেত্রে সার্ভারের কার্যকারিতা ব্যাপকভাবে বৃদ্ধি করার সুযোগ রয়েছে। এই অনুরোধগুলি 80% পর্যন্ত ওজন করতে পারে। তবে তাদের ভাগ 50% ছাড়িয়ে গেলেও তাদের আরও সাবধানে দেখার সময় এসেছে।

লেজ

উল্লিখিত হিসাবে, এই প্রশ্নগুলি শেষে আসে এবং 5-10% সময় নেয়। আপনি তাদের সম্পর্কে ভুলে যেতে পারেন শুধুমাত্র যদি আপনি স্বয়ংক্রিয় ক্যোয়ারী বিশ্লেষণ সরঞ্জাম ব্যবহার না করেন, তাহলে সেগুলি অপ্টিমাইজ করাও সস্তা হতে পারে৷

কিভাবে প্রতিটি গ্রুপ মূল্যায়ন?

আমি একটি এসকিউএল ক্যোয়ারী ব্যবহার করি যা PostgreSQL-এর জন্য এমন একটি মূল্যায়ন করতে সাহায্য করে (আমি নিশ্চিত যে একই ধরনের প্রশ্ন অন্য অনেক DBMS-এর জন্য লেখা যেতে পারে)

TOP-MEDIUM-tail গোষ্ঠীর আকার অনুমান করার জন্য SQL ক্যোয়ারী

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

ক্যোয়ারীটির ফলাফল হল তিনটি কলাম, যার প্রতিটিতে এই গ্রুপ থেকে প্রশ্নগুলি প্রক্রিয়া করতে কত শতাংশ সময় লাগে তা রয়েছে৷ অনুরোধের ভিতরে দুটি সংখ্যা রয়েছে (আমার ক্ষেত্রে এটি 20 এবং 800) যা একটি গ্রুপ থেকে অন্য গ্রুপ থেকে আলাদা করে অনুরোধ করে।

অপ্টিমাইজেশনের কাজ শুরু হওয়ার সময় এবং এখনকার সময়ে অনুরোধের শেয়ারগুলি মোটামুটি তুলনা করে।

চিত্রটি দেখায় যে শীর্ষ অনুরোধগুলির ভাগ দ্রুত হ্রাস পেয়েছে, তবে "মধ্য কৃষক" বৃদ্ধি পেয়েছে।
প্রথমে, শীর্ষ অনুরোধগুলির মধ্যে স্পষ্ট ভুল ছিল। সময়ের সাথে সাথে, শৈশব রোগগুলি অদৃশ্য হয়ে গেছে, শীর্ষ অনুরোধগুলির ভাগ হ্রাস পেয়েছে এবং কঠিন অনুরোধগুলিকে দ্রুত করার জন্য আরও বেশি প্রচেষ্টা করতে হয়েছিল।

অনুরোধের পাঠ্য পেতে আমরা নিম্নলিখিত অনুরোধটি ব্যবহার করি

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

এখানে সর্বাধিক ব্যবহৃত কৌশলগুলির একটি তালিকা রয়েছে যা আমাদের শীর্ষ প্রশ্নের গতি বাড়াতে সাহায্য করেছে:

• সিস্টেমের পুনরায় নকশা, উদাহরণস্বরূপ, ডাটাবেসে পর্যায়ক্রমিক প্রশ্নের পরিবর্তে একটি বার্তা ব্রোকার ব্যবহার করে বিজ্ঞপ্তি যুক্তি পুনরায় কাজ করা
• ইনডেক্স যোগ করা বা পরিবর্তন করা
• বিশুদ্ধ SQL-এ ORM প্রশ্ন পুনঃলিখন
• অলস ডেটা লোডিং যুক্তি পুনর্লিখন
• ডেটা ডিনরমালাইজেশনের মাধ্যমে ক্যাশিং। উদাহরণস্বরূপ, আমাদের কাছে একটি টেবিল সংযোগ রয়েছে ডেলিভারি -> চালান -> অনুরোধ -> অ্যাপ্লিকেশন। অর্থাৎ, প্রতিটি ডেলিভারি অন্য টেবিলের মাধ্যমে একটি অ্যাপ্লিকেশনের সাথে যুক্ত। প্রতিটি অনুরোধে সমস্ত টেবিল লিঙ্ক না করার জন্য, আমরা ডেলিভারি টেবিলে অনুরোধের লিঙ্কটি নকল করেছি।
• রেফারেন্স বই সহ স্ট্যাটিক টেবিল ক্যাশ করা এবং প্রোগ্রাম মেমরিতে খুব কমই টেবিল পরিবর্তন করা।

কখনও কখনও পরিবর্তনগুলি একটি চিত্তাকর্ষক পুনঃডিজাইন হিসাবে পরিমান, কিন্তু তারা সিস্টেম লোডের 5-10% প্রদান করে এবং ন্যায়সঙ্গত ছিল। সময়ের সাথে সাথে, নিষ্কাশন ছোট এবং ছোট হয়ে ওঠে এবং আরও বেশি গুরুতর পুনঃডিজাইন প্রয়োজন হয়।

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

• COUNTটি এবং একটি সম্পূর্ণ টেবিল স্ক্যান ব্যবহার করে রেকর্ডের উপস্থিতি পরীক্ষা করার পরিবর্তে, EXISTS ব্যবহার করা শুরু হয়েছে
• DISTINCT থেকে পরিত্রাণ পেয়েছেন (কোন সাধারণ রেসিপি নেই, তবে কখনও কখনও আপনি 10-100 বার অনুরোধের গতি বাড়িয়ে সহজেই এটি থেকে মুক্তি পেতে পারেন)।

  উদাহরণস্বরূপ, ডেলিভারির একটি বড় টেবিল থেকে সমস্ত ড্রাইভার নির্বাচন করার জন্য একটি প্রশ্নের পরিবর্তে (ডেলিভারি)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  একটি অপেক্ষাকৃত ছোট টেবিলে একটি প্রশ্ন করেছেন PERSON

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  মনে হচ্ছে আমরা একটি পারস্পরিক সম্পর্কযুক্ত সাবকোয়েরি ব্যবহার করেছি, তবে এটি 10 ​​বারের বেশি গতি দেয়।

• অনেক ক্ষেত্রে, COUNTটি সম্পূর্ণরূপে পরিত্যাগ করা হয়েছিল এবং৷
  আনুমানিক মান গণনা দ্বারা প্রতিস্থাপিত
• পরিবর্তে

  UPPER(s) LIKE JOHN%’ 
  

  ব্যবহার

  s ILIKE “John%”
  

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

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

আমাদের পূর্বাভাস অনুসারে, বর্তমান সার্ভার ব্যবহারকারীর সংখ্যা আরও 3-5 গুণ বৃদ্ধি সহ্য করবে। সত্য, আমাদের হাতা উপরে আরও একটি টেক্কা আছে - আমরা এখনও SELECT প্রশ্নগুলি আয়নায় স্থানান্তর করিনি, যেমনটি সুপারিশ করা হয়৷ তবে আমরা সচেতনভাবে এটি করি না, কারণ আমরা প্রথমে "ভারী আর্টিলারি" চালু করার আগে "স্মার্ট" অপ্টিমাইজেশনের সম্ভাবনাগুলি সম্পূর্ণরূপে নিঃশেষ করতে চাই।
সম্পন্ন কাজের একটি সমালোচনামূলক দৃষ্টিভঙ্গি উল্লম্ব স্কেলিং ব্যবহার করার পরামর্শ দিতে পারে। বিশেষজ্ঞদের সময় নষ্ট না করে আরও শক্তিশালী সার্ভার কিনুন। সার্ভারের এত বেশি খরচ নাও হতে পারে, বিশেষ করে যেহেতু আমরা এখনও উল্লম্ব স্কেলিং এর সীমা শেষ করিনি। তবে, শুধুমাত্র অনুরোধের সংখ্যা 10 গুণ বেড়েছে। বেশ কয়েক বছর ধরে, সিস্টেমের কার্যকারিতা বৃদ্ধি পেয়েছে এবং এখন আরও ধরণের অনুরোধ রয়েছে। ক্যাশে করার জন্য ধন্যবাদ, বিদ্যমান কার্যকারিতা কম অনুরোধে এবং আরও দক্ষ অনুরোধে সঞ্চালিত হয়। এর মানে আপনি প্রকৃত ত্বরণ সহগ পেতে নিরাপদে অন্য 5 দ্বারা গুণ করতে পারেন। সুতরাং, সবচেয়ে রক্ষণশীল অনুমান অনুসারে, আমরা বলতে পারি যে ত্বরণ 50 গুণ বা তার বেশি ছিল। উল্লম্বভাবে একটি সার্ভার সুইং করতে 50 গুণ বেশি খরচ হবে। বিশেষ করে বিবেচনা করে যে একবার অপ্টিমাইজেশন বাহিত হলে এটি সব সময় কাজ করে এবং ভাড়া করা সার্ভারের বিল প্রতি মাসে আসে।

উত্স: www.habr.com