İnşaatçılar üçün B2B xidmətinin nümunəsindən istifadə edərək verilənlər bazası sorğularının optimallaşdırılması

Daha məhsuldar bir serverə keçmədən verilənlər bazasına sorğuların sayını 10 dəfə artırmaq və sistemin funksionallığını necə saxlamaq olar? Mən sizə məlumat bazamızın performansının azalması ilə necə məşğul olduğumuzu, SQL sorğularını mümkün qədər çox istifadəçiyə xidmət etmək və hesablama resurslarının qiymətini artırmamaq üçün necə optimallaşdırdığımızı söyləyəcəyəm.

Tikinti şirkətlərində biznes proseslərinin idarə olunması üzrə xidmət göstərirəm. Bizimlə 3 minə yaxın şirkət işləyir. Bizim sistemimizlə hər gün 10-4 saat ərzində 10 mindən çox insan işləyir. Planlaşdırma, bildiriş, xəbərdarlıq, doğrulama kimi müxtəlif problemləri həll edir... Biz PostgreSQL 9.6-dan istifadə edirik. Verilənlər bazasında 300-ə yaxın cədvəlimiz var və hər gün 200 milyona qədər sorğu (10 min müxtəlif) qəbul edilir. Orta hesabla saniyədə 3-4 min sorğumuz olur, ən aktiv anlarda saniyədə 10 mindən çox sorğu olur. Sorğuların əksəriyyəti OLAP-dır. Daha az əlavələr, dəyişikliklər və silinmələr var, yəni OLTP yükü nisbətən yüngüldür. Mən bütün bu rəqəmləri ona görə verdim ki, siz layihəmizin miqyasını qiymətləndirəsiniz və təcrübəmizin sizin üçün nə qədər faydalı ola biləcəyini başa düşəsiniz.

Şəkil bir. Lirik

İnkişafa başlayanda verilənlər bazasına hansı yükün düşəcəyini və server çəkməyi dayandırsa nə edəcəyimizi düşünmürdük. Verilənlər bazasını tərtib edərkən biz ümumi tövsiyələrə əməl etdik və özümüzü ayağımızdan vurmamağa çalışdıq, lakin “naxışdan istifadə etməyin” kimi ümumi tövsiyələrdən kənara çıxdıq. Müəssisə Atribut Dəyərləri girmədik. Biz normallaşdırma prinsiplərinə əsaslanaraq məlumatların artıqlığından qaçaraq dizayn etdik və müəyyən sorğuların sürətləndirilməsinə əhəmiyyət vermədik. İlk istifadəçilər gələn kimi biz performans problemi ilə qarşılaşdıq. Həmişəki kimi buna tam hazır deyildik. İlk problemlərin sadə olduğu ortaya çıxdı. Bir qayda olaraq, hər şey yeni bir indeks əlavə etməklə həll edildi. Ancaq elə bir vaxt gəldi ki, sadə yamalar işləməyi dayandırdı. Təcrübəmiz olmadığını və problemlərin nədən qaynaqlandığını başa düşməyimizin getdikcə çətinləşdiyini anlayaraq, serveri düzgün qurmağa, monitorinqi birləşdirməyə kömək edən və əldə etmək üçün hara baxacağımızı göstərən mütəxəssisləri işə götürdük. statistika.

Şəkil iki. Statistik

Beləliklə, hər gün verilənlər bazamızda icra edilən 10 minə yaxın müxtəlif sorğularımız var. Bu 10 mindən orta icra müddəti 2-3 ms olan 0.1-0.3 milyon dəfə icra edilən canavarlar və gündə 30 dəfə adlandırılan orta icra müddəti 100 saniyə olan sorğular var.

10 min sorğunun hamısını optimallaşdırmaq mümkün olmadı, ona görə də verilənlər bazasının işini düzgün şəkildə yaxşılaşdırmaq üçün səylərimizi hara yönəltəcəyimizi anlamağa qərar verdik. Bir neçə təkrarlamadan sonra sorğuları növlərə bölməyə başladıq.

TOP sorğular

Bunlar ən çox vaxt aparan ən ağır sorğulardır (ümumi vaxt). Bunlar ya çox tez-tez çağırılan sorğulardır, ya da yerinə yetirilməsi çox uzun vaxt aparan sorğulardır (uzun və tez-tez sorğular sürət uğrunda mübarizənin ilk təkrarlamalarında optimallaşdırılmışdır). Nəticədə, server onların icrasına ən çox vaxt sərf edir. Üstəlik, üst sorğuları ümumi icra müddəti və ayrıca IO vaxtı ilə ayırmaq vacibdir. Bu cür sorğuların optimallaşdırılması üsulları bir qədər fərqlidir.

Bütün şirkətlərin adi təcrübəsi TOP tələbləri ilə işləməkdir. Onlardan bir neçəsi var, hətta bir sorğunun optimallaşdırılması resursların 5-10%-ni boşalda bilər. Bununla belə, layihə yetkinləşdikcə TOP sorğuların optimallaşdırılması getdikcə əhəmiyyətsiz bir işə çevrilir. Bütün sadə üsullar artıq işlənib hazırlanmışdır və ən "ağır" sorğu resursların "cəmi" 3-5% -ni tutur. Ümumilikdə TOP sorğular vaxtın 30-40%-dən azını tutursa, çox güman ki, onların tez işləməsi üçün artıq səy göstərmisiniz və növbəti qrupdan sorğuların optimallaşdırılmasına keçməyin vaxtıdır.
Bu qrupa neçə ən yaxşı sorğunun daxil edilməli olduğu sualına cavab vermək qalır. Mən adətən ən azı 10 alıram, amma 20-dən çox deyil. Mən çalışıram ki, TOP qrupda birinci və sonuncunun vaxtı 10 dəfədən çox olmasın. Yəni, əgər sorğunun icra müddəti 1-ci yerdən 10-cu yerə kəskin enirsə, onda TOP-10-u götürürəm, əgər azalma daha tədricəndirsə, onda qrup ölçüsünü 15 və ya 20-yə qədər artırıram.

Orta kəndlilər

Bunlar son 5-10% istisna olmaqla, TOP-dan dərhal sonra gələn sorğulardır. Adətən, bu sorğuların optimallaşdırılmasında server performansını əhəmiyyətli dərəcədə artırmaq imkanı olur. Bu sorğuların çəkisi 80%-ə qədər ola bilər. Lakin onların payı 50%-i keçsə belə, onlara daha diqqətlə baxmaq vaxtıdır.

Quyruq

Qeyd edildiyi kimi, bu sorğular sonunda gəlir və vaxtın 5-10%-ni alır. Siz onları yalnız avtomatik sorğu analizi alətlərindən istifadə etməsəniz unuda bilərsiniz, o zaman onları optimallaşdırmaq da ucuz başa gələ bilər.

Hər bir qrupu necə qiymətləndirmək olar?

Mən PostgreSQL üçün belə bir qiymətləndirmə aparmağa kömək edən SQL sorğusundan istifadə edirəm (əminəm ki, oxşar sorğu bir çox digər DBMS üçün də yazıla bilər)

TOP-MEDIUM-TAIL qruplarının ölçüsünü qiymətləndirmək üçün SQL sorğusu

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

Sorğunun nəticəsi üç sütundur, hər birində bu qrupdan sorğuları emal etmək üçün lazım olan vaxtın faizi var. Sorğunun içərisində bir qrupdan digərindən sorğuları ayıran iki nömrə var (mənim vəziyyətimdə bu 20 və 800-dür).

Optimallaşdırma işlərinin başladığı vaxtla indiki sorğuların payları təxminən belədir.

Diaqram göstərir ki, TOP müraciətlərin payı kəskin şəkildə azalıb, lakin “orta kəndlilər” artıb.
Əvvəlcə TOP sorğular açıq-aşkar kobud səhvləri ehtiva edirdi. Zamanla uşaqlıq xəstəlikləri aradan qalxdı, TOP müraciətlərin payı azaldı və çətin müraciətləri sürətləndirmək üçün getdikcə daha çox səy göstərilməli oldu.

Sorğuların mətnini əldə etmək üçün aşağıdakı sorğudan istifadə edirik

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

Budur, TOP sorğuları sürətləndirməyə kömək edən ən çox istifadə edilən texnikaların siyahısı:

• Sistemin yenidən dizaynı, məsələn, verilənlər bazasına dövri sorğular əvəzinə mesaj brokerindən istifadə edərək bildiriş məntiqinin yenidən işlənməsi
• İndekslərin əlavə edilməsi və ya dəyişdirilməsi
• ORM sorğularının təmiz SQL-ə yenidən yazılması
• Tənbəl məlumat yükləmə məntiqinin yenidən yazılması
• Məlumatların normallaşdırılması yolu ilə keşləmə. Məsələn, bizdə cədvəl bağlantısı Çatdırılma -> Faktura -> Sorğu -> Tətbiq var. Yəni, hər bir çatdırılma digər cədvəllər vasitəsilə tətbiq ilə əlaqələndirilir. Hər sorğuda bütün cədvəlləri əlaqələndirməmək üçün biz Çatdırılma cədvəlindəki sorğuya keçidi təkrarladıq.
• Statik cədvəllərin istinad kitabları ilə keşləşdirilməsi və proqram yaddaşında nadir hallarda dəyişən cədvəllər.

Bəzən dəyişikliklər təsirli bir yenidən dizayn idi, lakin sistem yükünün 5-10% -ni təmin etdi və əsaslandırıldı. Zaman keçdikcə egzoz getdikcə azaldı və getdikcə daha ciddi yenidən dizayn tələb olundu.

Sonra diqqətimizi ikinci qrup müraciətlərə - orta kəndlilər qrupuna yönəltdik. Orada daha çox sorğular var və görünürdü ki, bütün qrupu təhlil etmək çox vaxt aparacaq. Bununla belə, əksər sorğuların optimallaşdırılması çox sadə olduğu ortaya çıxdı və bir çox problem müxtəlif variasiyalarda onlarla dəfə təkrarlandı. Onlarla oxşar sorğulara tətbiq etdiyimiz bəzi tipik optimallaşdırma nümunələri və optimallaşdırılmış sorğuların hər bir qrupu verilənlər bazasını 3-5% boşaldır.

• COUNT və tam cədvəl taramasından istifadə edərək qeydlərin mövcudluğunu yoxlamaq əvəzinə EXISTS istifadə edilməyə başlandı.
• DISTINCT-dən xilas oldum (ümumi resept yoxdur, lakin bəzən sorğunu 10-100 dəfə sürətləndirməklə asanlıqla ondan qurtula bilərsiniz).

  Məsələn, böyük bir çatdırılma cədvəlindən bütün sürücüləri seçmək üçün sorğu yerinə (DELIVERY)

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

  nisbətən kiçik PERSON cədvəlində sorğu etdi

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

  Görünür ki, biz əlaqəli alt sorğudan istifadə etdik, lakin bu, 10 dəfədən çox sürət verir.

• Bir çox hallarda COUNT tamamilə tərk edildi və
  təxmini dəyərin hesablanması ilə əvəz olunur
• əvəzinə

  UPPER(s) LIKE JOHN%’ 
  

  istifadə

  s ILIKE “John%”
  

Hər bir xüsusi sorğu bəzən 3-1000 dəfə sürətləndirilirdi. Təsirli performansa baxmayaraq, əvvəlcə bizə elə gəldi ki, tamamlanması 10 ms vaxt aparan, 3-cü yüz ən ağır sorğulardan biri olan və ümumi verilənlər bazası yükləmə vaxtının yüzdə birini tutan sorğunu optimallaşdırmağın mənası yoxdur. Ancaq eyni resepti eyni tipli sorğular qrupuna tətbiq etməklə biz bir neçə faiz geri qazandıq. Bütün yüzlərlə sorğuları əl ilə nəzərdən keçirməyə vaxt itirməmək üçün eyni tipli sorğuları tapmaq üçün müntəzəm ifadələrdən istifadə edən bir neçə sadə skript yazdıq. Nəticədə, sorğu qruplarının avtomatik axtarışı bizə təvazökar səylə performansımızı daha da təkmilləşdirməyə imkan verdi.

Nəticədə, artıq üç ildir ki, eyni aparat üzərində işləyirik. Orta gündəlik yük təxminən 30%, zirvələrdə 70% -ə çatır. Müraciətlərin sayı, eləcə də istifadəçilərin sayı təxminən 10 dəfə artıb. Bütün bunlar TOP-MEDIUM sorğu qruplarının daimi monitorinqi sayəsində. TOP qrupunda yeni sorğu görünən kimi dərhal onu təhlil edirik və sürətləndirməyə çalışırıq. Biz sorğu təhlili skriptlərindən istifadə edərək həftədə bir dəfə MEDIUM qrupunu nəzərdən keçiririk. Artıq necə optimallaşdırmağı bildiyimiz yeni sorğularla qarşılaşsaq, onları tez dəyişdiririk. Bəzən biz eyni anda bir neçə sorğuya tətbiq oluna bilən yeni optimallaşdırma üsulları tapırıq.

Proqnozlarımıza görə, indiki server istifadəçilərin sayının daha 3-5 dəfə artmasına tab gətirəcək. Düzdür, əlimizdə daha bir ace var - tövsiyə edildiyi kimi hələ də SELECT sorğularını güzgüyə köçürməmişik. Ancaq bunu şüurlu şəkildə etmirik, çünki "ağır artilleriya" işə başlamazdan əvvəl "ağıllı" optimallaşdırma imkanlarını tamamilə tükəndirmək istəyirik.
Görülən işə tənqidi baxış şaquli miqyasdan istifadə etməyi təklif edə bilər. Mütəxəssislərin vaxtını itirmək əvəzinə daha güclü server alın. Server o qədər də baha olmaya bilər, xüsusən də şaquli miqyaslamanın hüdudlarını hələ tükətməmişik. Bununla belə, yalnız müraciətlərin sayı 10 dəfə artıb. Bir neçə il ərzində sistemin funksionallığı artdı və indi daha çox növ sorğular var. Keşləmə sayəsində mövcud funksionallıq daha az sorğuda və daha səmərəli sorğularda yerinə yetirilir. Bu o deməkdir ki, real sürətlənmə əmsalını əldə etmək üçün təhlükəsiz şəkildə başqa 5-ə vura bilərsiniz. Beləliklə, ən mühafizəkar hesablamalara görə, sürətlənmənin 50 dəfə və ya daha çox olduğunu söyləyə bilərik. Bir serveri şaquli olaraq yelləmək 50 dəfə baha başa gələcək. Xüsusilə nəzərə alsaq ki, optimallaşdırma aparıldıqdan sonra o, hər zaman işləyir və icarəyə götürülmüş server üçün hesab hər ay gəlir.

Mənbə: www.habr.com