অসুস্থ এসকিউএল প্রশ্নের জন্য রেসিপি

মাস কতক পূর্বে আমরা ঘোষণা করেছি ব্যাখ্যা.tensor.ru - পাবলিক ক্যোয়ারী পরিকল্পনা পার্সিং এবং ভিজ্যুয়ালাইজ করার জন্য পরিষেবা PostgreSQL এ।

আপনি ইতিমধ্যে এটি 6000 বারের বেশি ব্যবহার করেছেন, কিন্তু একটি সহজ বৈশিষ্ট্য যা অলক্ষিত হয়ে থাকতে পারে কাঠামোগত সূত্র, যা দেখতে এইরকম কিছু:

তাদের কথা শুনুন এবং আপনার অনুরোধগুলি "মসৃণ এবং রেশমী হয়ে উঠবে।" 🙂

কিন্তু গুরুত্ব সহকারে, অনেক পরিস্থিতি যা একটি অনুরোধ ধীর এবং সম্পদ-ক্ষুধার্ত করে তোলে সাধারণ এবং পরিকল্পনার গঠন এবং ডেটা দ্বারা স্বীকৃত হতে পারে.

এই ক্ষেত্রে, প্রতিটি স্বতন্ত্র বিকাশকারীকে তার নিজের অভিজ্ঞতার উপর নির্ভর করে নিজের থেকে একটি অপ্টিমাইজেশন বিকল্পের সন্ধান করতে হবে না - আমরা তাকে বলতে পারি এখানে কী ঘটছে, কারণ কী হতে পারে এবং কিভাবে একটি সমাধানের কাছে যেতে হয়. সেটাই আমরা করেছি।

আসুন এই ক্ষেত্রেগুলি ঘনিষ্ঠভাবে দেখে নেওয়া যাক - কীভাবে সেগুলি সংজ্ঞায়িত করা হয় এবং তারা কী সুপারিশ করে।

বিষয়টিতে নিজেকে আরও ভালভাবে নিমজ্জিত করতে, আপনি প্রথমে সংশ্লিষ্ট ব্লক থেকে শুনতে পারেন PGConf.Russia 2020 এ আমার প্রতিবেদন, এবং শুধুমাত্র তারপর প্রতিটি উদাহরণের বিশদ বিশ্লেষণে যান:

#1: সূচক "আন্ডারসর্টিং"

যখন উদয় হয়

ক্লায়েন্ট "LLC Kolokolchik" এর জন্য সর্বশেষ চালান দেখান।

কিভাবে সনাক্ত করা যায়

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

সুপারিশ

সূচক ব্যবহার করা হয়েছে সাজানোর ক্ষেত্র সহ প্রসারিত করুন.

উদাহরণ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- отбор по конкретной связи
ORDER BY
  pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;

[explain.tensor.ru দেখুন]

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

সংশোধন করা হচ্ছে:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

[explain.tensor.ru দেখুন]

এমনকি এমন একটি আদিম নমুনার উপরও - 8.5 গুণ দ্রুত এবং 33 গুণ কম পড়া. প্রতিটি মানের জন্য আপনার কাছে যত বেশি "তথ্য" থাকবে, প্রভাব তত বেশি স্পষ্ট হবে fk.

আমি মনে করি যে এই ধরনের একটি সূচক একটি "প্রিফিক্স" সূচক হিসাবে কাজ করবে অন্য প্রশ্নের জন্য আগের চেয়ে খারাপ নয় fk, যেখানে সাজান pk সেখানে ছিল না এবং নেই (আপনি এটি সম্পর্কে আরও পড়তে পারেন অকার্যকর সূচক খোঁজার বিষয়ে আমার নিবন্ধে) সহ, এটি স্বাভাবিক প্রদান করবে সুস্পষ্ট বিদেশী কী সমর্থন এই মাঠে।

#2: সূচক ছেদ (বিটম্যাপএন্ড)

যখন উদয় হয়

ক্লায়েন্ট "LLC Kolokolchik" এর জন্য সমস্ত চুক্তি দেখান, "NAO Buttercup" এর পক্ষে সমাপ্ত৷

কিভাবে সনাক্ত করা যায়

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

সুপারিশ

তৈরি যৌগিক সূচক উভয় মূল থেকে ক্ষেত্র দ্বারা বা দ্বিতীয় থেকে ক্ষেত্র সহ বিদ্যমানগুলির একটিকে প্রসারিত করুন।

উদাহরণ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре

[explain.tensor.ru দেখুন]

সংশোধন করা হচ্ছে:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

[explain.tensor.ru দেখুন]

এখানে পেঅফ কম, যেহেতু বিটম্যাপ হিপ স্ক্যান নিজেই বেশ কার্যকর। কিন্তু যাই হোক 7 গুণ দ্রুত এবং 2.5 গুণ কম পড়া.

#3: ইন্ডেক্স একত্রিত করুন (বিটম্যাপঅর)

যখন উদয় হয়

আপনার অগ্রাধিকার সহ, প্রক্রিয়াকরণের জন্য প্রথম 20টি প্রাচীনতম "আমাদের" বা আনঅ্যাসাইন করা অনুরোধগুলি দেখান৷

কিভাবে সনাক্ত করা যায়

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

সুপারিশ

ব্যবহার করতে ইউনিয়ন [সমস্ত] শর্তগুলির প্রতিটি OR-ব্লকগুলির জন্য সাবকোয়ারিগুলিকে একত্রিত করতে৷

উদাহরণ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- свои
  fk_own IS NULL -- ... или "ничьи"
ORDER BY
  pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;

[explain.tensor.ru দেখুন]

সংশোধন করা হচ্ছে:

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 -- сначала "свои" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL -- потом "ничьи" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо

[explain.tensor.ru দেখুন]

আমরা এই সত্যের সুবিধা নিয়েছি যে প্রথম ব্লকে 20টি প্রয়োজনীয় রেকর্ড অবিলম্বে প্রাপ্ত হয়েছিল, তাই দ্বিতীয়টি, আরও "ব্যয়বহুল" বিটম্যাপ হিপ স্ক্যান সহ, এমনকি কার্যকর করা হয়নি - শেষ পর্যন্ত 22 গুণ দ্রুত, 44 গুণ কম পড়া!

এই অপ্টিমাইজেশান পদ্ধতি সম্পর্কে আরো বিস্তারিত গল্প নির্দিষ্ট উদাহরণ ব্যবহার করে নিবন্ধে পড়া যেতে পারে পোস্টগ্রেএসকিউএল অ্যান্টিপ্যাটার্নস: ক্ষতিকারক যোগদান এবং ওআর и পোস্টগ্রেএসকিউএল অ্যান্টিপ্যাটার্নস: নামের দ্বারা অনুসন্ধানের পুনরাবৃত্তিমূলক পরিমার্জনার গল্প, বা "অপ্টিমাইজ করা এবং সামনের দিকে".

সাধারণ সংস্করণ বিভিন্ন কীগুলির উপর ভিত্তি করে নির্বাচনের আদেশ দেওয়া হয়েছে (এবং শুধুমাত্র const/NULL জোড়া নয়) নিবন্ধে আলোচনা করা হয়েছে SQL HowTo: ক্যোয়ারীতে সরাসরি কিছুক্ষণ লুপ লিখুন বা "প্রাথমিক তিন-পদক্ষেপ".

#4: আমরা অনেক অপ্রয়োজনীয় জিনিস পড়ি

যখন উদয় হয়

একটি নিয়ম হিসাবে, যখন আপনি ইতিমধ্যে বিদ্যমান অনুরোধে "অন্য ফিল্টার সংযুক্ত করতে" চান তখন এটি দেখা দেয়।

"এবং আপনার একই একটি নেই, কিন্তু মুক্তার বোতাম সহ? " ফিল্ম "ডায়মন্ড আর্ম"

উদাহরণস্বরূপ, উপরের টাস্কটি পরিবর্তন করে, তাদের উদ্দেশ্য নির্বিশেষে, প্রক্রিয়াকরণের জন্য প্রথম 20টি প্রাচীনতম "সমালোচনামূলক" অনুরোধগুলি দেখান৷

কিভাবে সনাক্ত করা যায়

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

সুপারিশ

তৈরি করুন [আরো] বিশেষায়িত WHERE শর্ত সহ সূচক বা সূচকে অতিরিক্ত ক্ষেত্র অন্তর্ভুক্ত করুন।

যদি ফিল্টার শর্ত আপনার উদ্দেশ্যে "স্থির" হয় - যে হয় সম্প্রসারণ বোঝায় না ভবিষ্যতে মান তালিকা - এটি একটি WHERE সূচক ব্যবহার করা ভাল। বিভিন্ন বুলিয়ান/এনাম স্ট্যাটাস এই ক্যাটাগরিতে ভালোভাবে ফিট করে।

ফিল্টারিং অবস্থায় থাকলে বিভিন্ন অর্থ গ্রহণ করতে পারে, তাহলে এই ক্ষেত্রগুলির সাথে সূচকটি প্রসারিত করা ভাল - যেমনটি বিটম্যাপ এবং উপরের পরিস্থিতির মতো।

উদাহরণ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

[explain.tensor.ru দেখুন]

সংশোধন করা হচ্ছে:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[explain.tensor.ru দেখুন]

আপনি দেখতে পাচ্ছেন, পরিকল্পনা থেকে ফিল্টারিং সম্পূর্ণরূপে অদৃশ্য হয়ে গেছে এবং অনুরোধটি হয়ে গেছে 5 গুণ দ্রুত.

#5: স্পার্স টেবিল

যখন উদয় হয়

আপনার নিজস্ব টাস্ক প্রসেসিং সারি তৈরি করার বিভিন্ন প্রচেষ্টা, যখন টেবিলে প্রচুর পরিমাণে আপডেট/মোছার রেকর্ডগুলি প্রচুর পরিমাণে "মৃত" রেকর্ডের পরিস্থিতির দিকে নিয়ে যায়।

কিভাবে সনাক্ত করা যায়

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

সুপারিশ

ম্যানুয়ালি নিয়মিত আউট বহন ভ্যাকুয়াম [সম্পূর্ণ] বা পর্যাপ্ত ঘন ঘন প্রশিক্ষণ অর্জন করুন অটোভ্যাকুয়াম এর পরামিতিগুলিকে ফাইন-টিউনিং করে, সহ একটি নির্দিষ্ট টেবিলের জন্য.

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

কিন্তু আপনাকে বুঝতে হবে যে এমনকি ভ্যাকুয়াম ফুল সবসময় সাহায্য করতে পারে না। এই জাতীয় ক্ষেত্রে, নিবন্ধ থেকে অ্যালগরিদমের সাথে নিজেকে পরিচিত করা মূল্যবান ডিবিএ: ভ্যাকুয়াম ব্যর্থ হলে, আমরা ম্যানুয়ালি টেবিলটি পরিষ্কার করি.

#6: সূচকের "মাঝখানে" থেকে পড়া

যখন উদয় হয়

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

কিভাবে সনাক্ত করা যায়

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

সুপারিশ

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

উদাহরণ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;

[explain.tensor.ru দেখুন]

সবকিছু ঠিক আছে বলে মনে হচ্ছে, এমনকি সূচক অনুযায়ী, কিন্তু এটি একরকম সন্দেহজনক - পড়া 20টি রেকর্ডের প্রতিটির জন্য, আমাদের 4 পৃষ্ঠার ডেটা বিয়োগ করতে হয়েছিল, রেকর্ড প্রতি 32KB - এটি কি সাহসী নয়? এবং সূচক নাম tbl_fk_org_fk_cli_idx চিন্তা-উদ্দীপক

সংশোধন করা হচ্ছে:

CREATE INDEX ON tbl(fk_cli);

[explain.tensor.ru দেখুন]

হঠাৎ- 10 গুণ দ্রুত, এবং 4 গুণ কম পড়তে!

সূচকগুলির অকার্যকর ব্যবহারের পরিস্থিতিগুলির অন্যান্য উদাহরণ নিবন্ধে দেখা যেতে পারে DBA: অকেজো সূচী খোঁজা.

#7: CTE × CTE

যখন উদয় হয়

অনুরোধে স্কোর "ফ্যাট" CTE বিভিন্ন টেবিল থেকে, এবং তারপর তাদের মধ্যে এটি করার সিদ্ধান্ত নিয়েছে JOIN.

কেসটি v12 এর নীচের সংস্করণগুলির জন্য বা এর সাথে অনুরোধগুলির জন্য প্রাসঙ্গিক৷ WITH MATERIALIZED.

কিভাবে সনাক্ত করা যায়

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

সুপারিশ

অনুরোধটি সাবধানতার সাথে বিশ্লেষণ করুন - এবং এখানে কি CTEs আদৌ প্রয়োজন?? যদি হ্যাঁ, তাহলে hstore/json এ "অভিধান" প্রয়োগ করুন বর্ণিত মডেল অনুযায়ী পোস্টগ্রেএসকিউএল অ্যান্টিপ্যাটার্নস: আসুন একটি অভিধানের সাথে ভারী যোগদান করি.

#8: ডিস্কে অদলবদল (টেম্পলি লেখা)

যখন উদয় হয়

বিপুল সংখ্যক রেকর্ডের এককালীন প্রক্রিয়াকরণ (বাছাই বা অনন্যকরণ) এর জন্য বরাদ্দ করা মেমরির সাথে খাপ খায় না।

কিভাবে সনাক্ত করা যায়

-> *
   && temp written > 0

সুপারিশ

অপারেশন দ্বারা ব্যবহৃত মেমরি পরিমাণ ব্যাপকভাবে পরামিতি নির্দিষ্ট মান অতিক্রম না হলে কাজ_মেম, এটা সংশোধন মূল্য. আপনি অবিলম্বে প্রত্যেকের জন্য কনফিগারেশন করতে পারেন, অথবা আপনি মাধ্যমে করতে পারেন SET [LOCAL] একটি নির্দিষ্ট অনুরোধ/লেনদেনের জন্য।

উদাহরণ:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

[explain.tensor.ru দেখুন]

সংশোধন করা হচ্ছে:

SET work_mem = '128MB'; -- перед выполнением запроса

[explain.tensor.ru দেখুন]

সুস্পষ্ট কারণে, যদি শুধুমাত্র মেমরি ব্যবহার করা হয় এবং ডিস্ক নয়, তাহলে ক্যোয়ারীটি অনেক দ্রুত কার্যকর হবে। একই সময়ে, HDD থেকে লোডের অংশও সরানো হয়।

কিন্তু আপনাকে বুঝতে হবে যে আপনি সর্বদা প্রচুর এবং প্রচুর মেমরি বরাদ্দ করতে সক্ষম হবেন না - সেখানে সবার জন্য যথেষ্ট হবে না।

#9: অপ্রাসঙ্গিক পরিসংখ্যান

যখন উদয় হয়

তারা একবারে ডাটাবেসে অনেক কিছু ঢেলে দিয়েছে, কিন্তু এটি দূরে সরিয়ে দেওয়ার সময় ছিল না ANALYZE.

কিভাবে সনাক্ত করা যায়

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

সুপারিশ

এটা বহন করে নাও ANALYZE.

এই পরিস্থিতি আরও বিশদে বর্ণনা করা হয়েছে PostgreSQL Antipatterns: পরিসংখ্যান সবকিছু.

#10: "কিছু ভুল হয়েছে"

যখন উদয় হয়

একটি প্রতিযোগী অনুরোধ দ্বারা আরোপিত একটি লকের জন্য অপেক্ষা ছিল, অথবা অপর্যাপ্ত CPU/হাইপারভাইজার হার্ডওয়্যার সংস্থান ছিল।

কিভাবে সনাক্ত করা যায়

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

সুপারিশ

বাহ্যিক ব্যবহার করুন পর্যবেক্ষণ সিস্টেম ব্লকিং বা অস্বাভাবিক সম্পদ ব্যবহারের জন্য সার্ভার। আমরা ইতিমধ্যে শত শত সার্ভারের জন্য এই প্রক্রিয়াটি সংগঠিত করার আমাদের সংস্করণ সম্পর্কে কথা বলেছি এখানে и এখানে.

উত্স: www.habr.com