چندین ماه پیش - عمومی به PostgreSQL.
از آن زمان تاکنون بیش از 6000 بار از آن استفاده کرده‌اید، اما یکی از ویژگی‌های مفید ممکن است مورد توجه قرار نگیرد سرنخ های ساختاری، که چیزی شبیه به این هستند:
به آنها گوش دهید و درخواست های شما "آرامش ابریشمی" خواهد شد. 🙂
اما به طور جدی، بسیاری از موقعیت‌ها که درخواست را از نظر منابع کند و «پرخور» می‌کنند، معمولی هستند و با ساختار و داده های طرح قابل تشخیص هستند.
در این حالت، هر توسعه دهنده ای مجبور نیست به تنهایی به دنبال گزینه بهینه سازی باشد و صرفاً به تجربه خود تکیه کند - ما می توانیم به او بگوییم که اینجا چه اتفاقی می افتد، دلیل آن چه می تواند باشد و چگونه یک راه حل پیدا کنیم. کاری که ما انجام دادیم.
بیایید نگاهی دقیق تر به این موارد بیندازیم - چگونه آنها تعریف می شوند و به چه توصیه هایی منجر می شوند.
برای غوطه ور شدن بهتر در موضوع، می توانید ابتدا به بلوک مربوطه گوش دهید ، و تنها پس از آن به تجزیه و تحلیل دقیق هر مثال بروید:
شماره 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;
بلافاصله می توانید متوجه شوید که بیش از 100 رکورد توسط ایندکس کم شد، که سپس همه آنها مرتب شدند و سپس تنها یکی باقی ماند.
ما رفع می کنیم:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
حتی در چنین نمونه ابتدایی - 8.5 برابر سریعتر و 33 برابر خواندن کمتر. تأثیر واضح‌تر خواهد بود، «حقایق» بیشتری برای هر مقدار دارید. fk.
توجه داشته باشم که چنین نمایه ای به عنوان یک شاخص "پیشوند" برای سایر پرس و جوها بدتر از قبلی نیست. fk، جایی که مرتب سازی بر اساس pk نبود و نیست (می توانید در این مورد بیشتر بخوانید ). به طور خاص، آن را ارائه خواهد کرد طبیعی است پشتیبانی صریح کلید خارجی توسط این رشته
شماره 2: تقاطع شاخص (BitmapAnd)
وقتی بوجود می آید
نمایش تمام قراردادهای مشتری "LLC Kolokolchik" که از طرف "NJSC Lyutik" منعقد شده است.
نحوه شناسایی
-> 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); -- отбор по конкретной паре
ما رفع می کنیم:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
در اینجا سود کمتر است، زیرا Bitmap Heap Scan به خودی خود کاملاً مؤثر است. ولی به هر حال 7 برابر سریعتر و 2.5 برابر خواندن کمتر.
شماره 3: ترکیب ایندکس ها (BitmapOr)
وقتی بوجود می آید
اولین 20 درخواست قدیمی "خود" یا اختصاص نیافته برای پردازش را با اولویت درخواست های خود نشان دهید.
نحوه شناسایی
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanتوصیه
استفاده کنید اتحادیه [ALL] برای ترکیب پرس و جوهای فرعی برای هر یک از بلوک های شرط 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;
ما رفع می کنیم:
(
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, больше и не надо
ما از این واقعیت استفاده کردیم که همه 20 رکورد ضروری بلافاصله در بلوک اول دریافت شدند، بنابراین مورد دوم، با اسکن Bitmap Heap "گران تر"، حتی اجرا نشد - در نتیجه 22 برابر سریعتر، 44 برابر خواندن کمتر!
یک داستان دقیق تر در مورد این روش بهینه سازی روی نمونه های عینی را می توان در مقالات خواند и .
نسخه تعمیم یافته انتخاب با چندین کلید دستور داد (و نه فقط برای یک جفت const / NULL) در مقاله مورد بحث قرار گرفته است .
شماره 4: ما بیش از حد می خوانیم
وقتی بوجود می آید
به عنوان یک قاعده، زمانی اتفاق می‌افتد که می‌خواهید «فیلتر دیگری» را به یک درخواست موجود متصل کنید.
"و شما همان را ندارید، اما با دکمه های مروارید؟ " فیلم "دست الماس"
به عنوان مثال، با اصلاح وظیفه بالا، اولین 20 درخواست قدیمی "بحرانی" برای پردازش را بدون توجه به هدف آنها نشان دهید.
نحوه شناسایی
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
توصیه
[بیشتر] تخصصی ایجاد کنید ایندکس با عبارت WHERE یا فیلدهای اضافی را در فهرست قرار دهید.
اگر شرایط فیلتر برای وظایف شما "ایستا" است - یعنی شامل گسترش نمی شود لیست مقادیر در آینده - بهتر است از شاخص WHERE استفاده کنید. وضعیت های مختلف بولی/enum به خوبی در این دسته قرار می گیرند.
اگر شرایط فیلتراسیون می تواند ارزش های متفاوتی به خود بگیرد، بهتر است ایندکس را با این فیلدها گسترش دهید - مانند وضعیت BitmapAnd در بالا.
به عنوان مثال:
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;
ما رفع می کنیم:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
همانطور که می بینید، فیلترینگ طرح به طور کامل از بین رفته است و درخواست تبدیل شده است 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
توصیه
به صورت دستی به طور منظم انجام دهید خلاء [فول] یا به پردازش مکرر کافی برسید با تنظیم دقیق پارامترهای آن، از جمله .
در بیشتر موارد، چنین مشکلاتی به دلیل طرح‌بندی ضعیف پرس و جو هنگام فراخوانی از منطق تجاری ایجاد می‌شوند، مانند مواردی که در .
اما باید درک کنیم که حتی VACUUM FULL همیشه نمی تواند کمک کند. برای چنین مواردی، باید با الگوریتم مقاله آشنا شوید. .
شماره 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;
به نظر می رسد همه چیز خوب است، حتی از نظر شاخص، اما به نوعی مشکوک است - برای هر یک از 20 رکورد خوانده شده، 4 صفحه داده باید کم شود، 32 کیلوبایت در هر رکورد - پررنگ نیست؟ بله و نام ایندکس tbl_fk_org_fk_cli_idx به فکر منجر می شود
ما رفع می کنیم:
CREATE INDEX ON tbl(fk_cli);
ناگهان - 10 برابر سریعتر و 4 برابر کمتر برای خواندن!
برای مشاهده نمونه های بیشتر از استفاده ناکارآمد از نمایه ها به مقاله مراجعه کنید .
شماره 7: CTE × CTE
وقتی بوجود می آید
در درخواست امتیاز "چاق" CTE از جداول مختلف، و سپس تصمیم به انجام بین آنها JOIN.
مورد مربوط به نسخه های زیر v12 یا درخواست با WITH MATERIALIZED.
نحوه شناسایی
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
توصیه
درخواست را به دقت تجزیه و تحلیل کنید ? اگر بله، پس "Dictionary" را در hstore/json اعمال کنید با توجه به مدلی که در .
شماره 8: جابجایی روی دیسک (تغییر نوشته شده)
وقتی بوجود می آید
پردازش یکباره (مرتب سازی یا منحصر به فرد کردن) تعداد زیادی رکورد در حافظه اختصاص داده شده برای این کار نمی گنجد.
نحوه شناسایی
-> *
&& temp written > 0توصیه
اگر مقدار حافظه استفاده شده توسط عملیات از مقدار تنظیم شده پارامتر بیشتر نباشد ، باید اصلاح شود. شما می توانید بلافاصله در تنظیمات برای همه، و یا شما می توانید از طریق SET [LOCAL] برای یک درخواست/تراکنش خاص
به عنوان مثال:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
ما رفع می کنیم:
SET work_mem = '128MB'; -- перед выполнением запроса
به دلایل واضح، اگر فقط از حافظه و نه دیسک استفاده شود، پرس و جو بسیار سریعتر خواهد بود. در همان زمان، بخشی از بار نیز از HDD حذف می شود.
اما باید بدانید که تخصیص مقدار زیادی حافظه نیز همیشه کار نخواهد کرد - به سادگی برای همه کافی نخواهد بود.
شماره 9: آمار بی ربط
وقتی بوجود می آید
مقدار زیادی به یکباره به پایگاه ریخته شد، اما آنها وقت نداشتند آن را دور کنند ANALYZE.
نحوه شناسایی
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10توصیه
همینطور خرج کن ANALYZE.
این وضعیت با جزئیات بیشتر در .
شماره 10: "مشکلی پیش آمد"
وقتی بوجود می آید
یک قفل در انتظار یک درخواست رقیب بود یا منابع سخت افزاری CPU/hypervisor کافی وجود نداشت.
نحوه شناسایی
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
توصیه
از خارجی استفاده کنید سیستم نظارت سرور برای مسدود کردن یا مصرف غیرعادی منابع. ما قبلاً در مورد نسخه خود از سازماندهی این فرآیند برای صدها سرور صحبت کرده ایم. и .
منبع: www.habr.com