PostgreSQL sorğularının optimallaşdırılması ilə necə məşğul olmalı olduğum və bütün bunlardan nə çıxdığı haqqında.
Niyə məcbur oldun? Bəli, çünki əvvəlki 4 ildə hər şey saatın tıqqıltısı kimi sakit, sakit işləyirdi.
epiqraf kimi.
Real hadisələr əsasında.
Bütün adlar dəyişdirilib, təsadüflər təsadüfi olur.
Müəyyən bir nəticə əldə edildikdə, başlanğıc üçün nəyin təkan olduğunu, hər şeyin necə başladığını xatırlamaq həmişə maraqlıdır.
Beləliklə, nəticədə baş verənlər məqalədə qısaca təsvir edilmişdir ".
Yəqin ki, əvvəlki hadisələr zəncirini yenidən yaratmaq maraqlı olacaq.
Tarix dəqiq başlama tarixini saxladı — 2018-09-10 18:02:48.
Həm də hekayədə hər şeyin başladığı bir sorğu var:
Problemli sorğuSELECT
p. "PARAMETER_ID" parametr_id kimi,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS müştəri_hissə nömrəsi,
w."LRM" AS LRM,
w. "LOTID" lotid kimi,
w. "RTD_VALUE" RTD_dəyəri kimi,
w. "LOWER_SPEC_LIMIT" AS aşağı_spec_limit,
w. "UPPER_SPEC_LIMIT" AS yuxarı_spec_limit,
p."TYPE_CALCUL" AS type_hesab,
s."SPENT_NAME" xərclənən_adı,
s."SPENT_DATE" xərclənən_tarix kimi,
çıxarış("SPENT_DATE" tarixindən) il kimi,
ay kimi çıxarış("SPENT_DATE" tarixindən ay),
s."REPORT_NAME" AS hesabat_adı,
səh. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS müştəriparam_name
wdataw-dan,
sərf s,
pmtrp,
xərclənmiş_pdsp,
pd pd
WHERE s."SPENT_ID" = w."SPENT_ID"
VƏ p."PARAMETER_ID" = w."PARAMETER_ID"
VƏ s."SPENT_ID" = sp."SPENT_ID"
VƏ pd. "PD_ID" = sp. "PD_ID"
VƏ s."SPENT_DATE" >= '2018-07-01' VƏ s."SPENT_DATE" <= '2018-09-30'
və s."SPENT_DATE" = (SEÇ MAX(s2."SPENT_DATE")
Xərclənmiş s2-dən,
wdata w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
AND w2."LRM" = w."LRM");
Problemin təsviri, proqnozlaşdırıla bilən standart - “Hər şey pisdir. Problemin nə olduğunu deyin”.
Dərhal 3 yarım düymlük sürücülər dövründən bir zarafat yadıma düşdü:
Lamer hakerin yanına gəlir.
- Mənim işimə heç nə gəlmir, problemin harada olduğunu deyin.
-DNT-də...
Lakin, əlbəttə ki, performans insidentlərini həll etməyin yolu bu deyil. "Ola bilər ki, başa düşülməyək" (Birlikdə). Bunu başa düşmək lazımdır.
Yaxşı, qazaq. Ola bilsin ki, nəticədə yığılacaq.
sərmayə qoyuluşu başladı
Beləliklə, AŞAĞININ köməyinə belə müraciət etmədən dərhal gözlə görülə bilər.
1) JOIN istifadə edilmir. Bu pisdir, xüsusən də əlaqələrin sayı birdən çox olarsa.
2) Ancaq daha da pisi - əlaqəli alt sorğu, üstəlik, toplama ilə. Bu çox pisdir.
Bu, əlbəttə ki, pisdir. Ancaq bu, yalnız bir tərəfdən. Digər tərəfdən, bu, çox yaxşıdır, çünki problemin açıq şəkildə həlli var və sorğu yaxşılaşdırıla bilər.
Falçıya getmə (C).
Sorğu planı o qədər də mürəkkəb deyil, lakin olduqca göstəricidir:
İcra planı
Ən maraqlı və faydalı, həmişə olduğu kimi, əvvəlində və sonunda.
İç-içə Döngə (qiymət=935.84..479763226.18 sıra=3322 en=135) (faktiki vaxt=31.536..8220420.295 sıra=8111656 döngə=1)
Planlaşdırma vaxtı: 3.807ms
İcra müddəti: 8222351.640ms
İcra müddəti 2 saatdan çoxdur.
Zaman alan yanlış fərziyyələr
Hipotez 1- Optimizator səhvdir, səhv plan qurur.
İcra planını vizuallaşdırmaq üçün saytdan istifadə edəcəyik . Bununla belə, saytda maraqlı və faydalı heç nə göstərilməyib. Birinci və ikinci baxışda - həqiqətən kömək edə biləcək heç bir şey. Əgər - Tam Skan minimaldır. Davam et.
Hipotez 2-Avtovakuum tərəfdən bazaya təsir, əyləclərdən xilas olmaq lazımdır.
Ancaq avtovakuum demonları özlərini yaxşı aparır, uzun müddət davam edən proseslər yoxdur. Hər hansı bir ciddi yük - yox. Başqa bir şey axtarmaq lazımdır.
Hipotez 3-Statistika köhnəlib, hər şeyin uçduğunu yenidən hesablamaq lazımdır
Yenə o yox. Statistikalar aktualdır. Hansı ki, avtovakuumla bağlı problemlərin olmadığını nəzərə alsaq, təəccüblü deyil.
Optimallaşdırmağa başlayaq
Əsas cədvəl 'wdata' əlbəttə ki, kiçik deyil, demək olar ki, 3 milyon qeyddir.
Və məhz bu cədvəldə Tam Scan gedir.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") VƏ ((Alt Plan 1) = s."SPENT_DATE"))
-> Seq Scan wdata w (qiymət=0.00..574151.49 sətir=26886249 eni=46) (faktiki vaxt=0.005..8153.565 sətir=26873950 döngə=1)
Biz standart olaraq hərəkət edirik: "bir indeks yaradaq və hər şey uçur".
"SPENT_ID" sahəsində indeks yaratdı
Nəticədə:
İndeksdən istifadə edərək sorğunun icra planı
Yaxşı, kömək etdi?
Bu idi: 8 222 351.640 ms (2 saatdan çox)
Qəbul edildi: 6 985 431.575 ms (demək olar ki, 2 saat)
Ümumiyyətlə, eyni alma, yan görünüş.
Klassikləri xatırlayaq:
“Sizdə də eyni var, amma qanadsız? axtaracaq".
Prinsipcə, bunu yaxşı nəticə adlandırmaq olar, yaxşı, yaxşı deyil, məqbuldur. Ən azı, müştəriyə nə qədər iş görüldüyünü və nə üçün yaxşı olduğunu izah edən böyük bir hesabat təqdim edin.
Bununla belə, yekun qərar hələ çox uzaqdır. Çox uzaq.
İndi ən maraqlısı - optimallaşdırmağa davam edirik, sorğunu cilalayacağıq
Birinci addım - JOIN istifadə edin
Yenidən yazılmış sorğu, indi belə görünür (yaxşı heç olmasa daha gözəl):
JOIN istifadə edərək sorğu edinSELECT
p. "PARAMETER_ID" parametr_id kimi,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS müştəri_hissə nömrəsi,
w."LRM" AS LRM,
w. "LOTID" lotid kimi,
w. "RTD_VALUE" RTD_dəyəri kimi,
w. "LOWER_SPEC_LIMIT" AS aşağı_spec_limit,
w. "UPPER_SPEC_LIMIT" AS yuxarı_spec_limit,
p."TYPE_CALCUL" AS type_hesab,
s."SPENT_NAME" xərclənən_adı,
s."SPENT_DATE" xərclənən_tarix kimi,
çıxarış("SPENT_DATE" tarixindən) il kimi,
ay kimi çıxarış("SPENT_DATE" tarixindən ay),
s."REPORT_NAME" AS hesabat_adı,
səh. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS müştəriparam_name
FROM wdata w INNER JOIN sərf olunan s ON w."SPENT_ID"=s."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN harcanan_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
HARADA
s."SPENT_DATE" >= '2018-07-01' VƏ s."SPENT_DATE" <= '2018-09-30'VƏ
s."SPENT_DATE" = (SEÇ MAX(s2."SPENT_DATE")
FROM wdata w2 INNER JOIN s2 on w2."SPENT_ID"=s2."SPENT_ID"
INNER JOIN wdata w
ON w2."LRM" = w."LRM" );
Planlaşdırma vaxtı: 2.486ms
İcra müddəti: 1223680.326ms
Beləliklə, ilk nəticə budur.
Bu idi: 6 985 431.575 ms (demək olar ki, 2 saat).
Qəbul edildi: 1 223 680.326 ms (yalnız 20 dəqiqədən çox).
Yaxşı nəticə. Prinsipcə, yenə də orada dayanmaq olardı. Amma o qədər maraqsız ki, dayana bilməzsən.
ÜÇÜN
İkinci addım - Əlaqəli alt sorğudan qurtulun
Dəyişdirilmiş sorğu mətni:
Əlaqəli alt sorğu yoxdurSELECT
p. "PARAMETER_ID" parametr_id kimi,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS müştəri_hissə nömrəsi,
w."LRM" AS LRM,
w. "LOTID" lotid kimi,
w. "RTD_VALUE" RTD_dəyəri kimi,
w. "LOWER_SPEC_LIMIT" AS aşağı_spec_limit,
w. "UPPER_SPEC_LIMIT" AS yuxarı_spec_limit,
p."TYPE_CALCUL" AS type_hesab,
s."SPENT_NAME" xərclənən_adı,
s."SPENT_DATE" xərclənən_tarix kimi,
çıxarış("SPENT_DATE" tarixindən) il kimi,
ay kimi çıxarış("SPENT_DATE" tarixindən ay),
s."REPORT_NAME" AS hesabat_adı,
səh. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS müştəriparam_name
FROM wdata w INNER JOIN s ON s sərf etdi."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN harcanan_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
DAXİLİ QOŞULMA (SEÇİM w2."LRM", MAX(s2."SPENT_DATE")
FROM sərf edilmiş s2 İNNER QOŞULUN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
w2.LRM İLƏ QRUPLAMA
) md on w. "LRM" = md. "LRM"
HARADA
s."SPENT_DATE" >= '2018-07-01' VƏ s."SPENT_DATE" <= '2018-09-30';
Planlaşdırma vaxtı: 2.291ms
İcra müddəti: 165021.870ms
Bu idi: 1 223 680.326 ms (yalnız 20 dəqiqədən çox).
Qəbul edildi: 165 021.870 ms (cəmi 2 dəqiqədən çox).
Bu, artıq olduqca yaxşıdır.
Ancaq ingilislərin dediyi kimi,Ancaq həmişə bir ancaq var". Çox yaxşı nəticə avtomatik olaraq şübhə doğurmalıdır. Burada nəsə səhvdir.
Əlaqəli alt sorğudan xilas olmaq üçün sorğunun düzəldilməsi ilə bağlı fərziyyə doğrudur. Ancaq son nəticəni düzgün əldə etmək üçün bir az tənzimləmə lazımdır.
Nəticədə, ilk ara nəticə:
Əlaqəli alt sorğu olmadan redaktə edilmiş sorğuSELECT
p. "PARAMETER_ID" parametr_id kimi,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS müştəri_hissə nömrəsi,
w."LRM" AS LRM,
w. "LOTID" lotid kimi,
w. "RTD_VALUE" RTD_dəyəri kimi,
w. "LOWER_SPEC_LIMIT" AS aşağı_spec_limit,
w. "UPPER_SPEC_LIMIT" AS yuxarı_spec_limit,
p."TYPE_CALCUL" AS type_hesab,
s."SPENT_NAME" xərclənən_adı,
s."SPENT_DATE" xərclənən_tarix kimi,
çıxarış("SPENT_DATE" s.-dən il) il kimi,
ay kimi çıxarış("SPENT_DATE"-dən etibarən ay),
s."REPORT_NAME" AS hesabat_adı,
səh. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS müştəriparam_name
FROM wdata w INNER JOIN s ON s sərf etdi."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN harcanan_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
DAXİLİ QOŞULUŞ ( SEÇİN w2."LRM", MAX(s2."SPENT_DATE") "SPENT_DATE" KİMİ
FROM sərf edilmiş s2 İNNER QOŞULUN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
w2.LRM İLƏ QRUPLAMA
) md ON md."SPENT_DATE" = s."SPENT_DATE" VƏ md."LRM" = w."LRM"
HARADA
s."SPENT_DATE" >= '2018-07-01' VƏ s."SPENT_DATE" <= '2018-09-30';
Planlaşdırma vaxtı: 3.192ms
İcra müddəti: 208014.134ms
Beləliklə, nəticədə əldə etdiyimiz ilk məqbul nəticədir, biz bunu müştəriyə göstərməkdən utanmırıq:
Başladı: 8 222 351.640 ms (2 saatdan çox)
Əldə edildi: 1 ms (yalnız 223 dəqiqədən çox).
Nəticə (orta): 208 014.134 ms (cəmi 3 dəqiqədən çox).
Əla nəticə.
Ümumi
Bu dayana bilərdi.
AMMA…
İştah yeməklə gəlir. Yol piyada keçməklə mənimsəniləcək. İstənilən nəticə orta səviyyədədir. Ölü dayandı. və s.
Optimallaşdırmaya davam edək.
Əla fikir. Xüsusilə nəzərə alsaq ki, müştəri hətta buna qarşı deyildi. Və hətta güclü - üçün.
Beləliklə, verilənlər bazasını yenidən dizayn etməyin vaxtı gəldi. Sorğu strukturunun özü artıq optimallaşdırıla bilməz (baxmayaraq ki, sonradan məlum oldu ki, hər şeyin həqiqətən uçması üçün bir seçim var). Ancaq indi verilənlər bazası dizaynının optimallaşdırılması və inkişafı ilə məşğul olmaq üçün bu, artıq çox perspektivli bir fikirdir. Və ən əsası maraqlıdır. Yenə də gəncliyi xatırlayın. Axı mən dərhal DBA olmamışam, proqramçılardan (basic, assembler, si, si double plused, oracle, plsql) böyümüşəm. Maraqlı mövzu, təbii ki, ayrı-ayrı xatirələr üçün ;-).
Bununla belə, kənara çıxmayaq.
Belə ki,
Və bəlkə bölmələr bizə kömək edəcək?
Spoyler - "Bəli, bu kömək etdi və performansı optimallaşdırmaqda, o cümlədən."
Amma bu tamam başqa hekayədir...
Ardı var…
Mənbə: www.habr.com