მონაცემთა ბაზის მოთხოვნების ოპტიმიზაცია მშენებლებისთვის B2B სერვისის მაგალითის გამოყენებით

როგორ გავზარდოთ 10-ჯერ მეტი მოთხოვნები მონაცემთა ბაზაში უფრო პროდუქტიულ სერვერზე გადასვლის გარეშე და შევინარჩუნოთ სისტემის ფუნქციონირება? მე გეტყვით, როგორ გავუმკლავდით ჩვენი მონაცემთა ბაზის მუშაობის შემცირებას, როგორ მოვახდინეთ SQL მოთხოვნების ოპტიმიზაცია, რათა რაც შეიძლება მეტ მომხმარებელს მოემსახუროს და არ გავზარდოთ გამოთვლითი რესურსების ღირებულება.

ვაკეთებ სერვისს სამშენებლო კომპანიებში ბიზნეს პროცესების მართვისთვის. ჩვენთან დაახლოებით 3 ათასი კომპანია მუშაობს. ჩვენს სისტემაში ყოველდღიურად 10-4 საათის განმავლობაში მუშაობს 10 ათასზე მეტი ადამიანი. ის წყვეტს დაგეგმვის, შეტყობინების, გაფრთხილების, ვალიდაციის სხვადასხვა პრობლემას... ჩვენ ვიყენებთ PostgreSQL 9.6. მონაცემთა ბაზაში გვაქვს 300-მდე ცხრილი და ყოველდღიურად 200 მილიონამდე მოთხოვნა (10 ათასი განსხვავებული) მიიღება. საშუალოდ გვაქვს 3-4 ათასი მოთხოვნა წამში, ყველაზე აქტიურ მომენტებში 10 ათასზე მეტი მოთხოვნა წამში. მოთხოვნების უმეტესობა არის OLAP. გაცილებით ნაკლებია დამატება, მოდიფიკაცია და წაშლა, რაც იმას ნიშნავს, რომ OLTP დატვირთვა შედარებით მსუბუქია. მე მივაწოდე ყველა ეს რიცხვი, რათა შეაფასოთ ჩვენი პროექტის მასშტაბები და გაიგოთ, რამდენად სასარგებლო შეიძლება იყოს თქვენთვის ჩვენი გამოცდილება.

სურათი ერთი. ლირიკული

როდესაც ჩვენ დავიწყეთ განვითარება, ჩვენ ნამდვილად არ გვიფიქრია იმაზე, თუ რა სახის დატვირთვა დადგებოდა მონაცემთა ბაზაზე და რას გავაკეთებდით, თუ სერვერი შეწყვეტდა დაჭერას. მონაცემთა ბაზის შექმნისას ჩვენ მივყვებოდით ზოგად რეკომენდაციებს და ვცდილობდით არ გვესროლა ფეხში, მაგრამ გავედით ზოგადი რჩევების ფარგლებს გარეთ, როგორიცაა „არ გამოიყენო ნიმუში ერთეულის ატრიბუტის მნიშვნელობები ჩვენ არ შევედით. ჩვენ დავაპროექტეთ ნორმალიზაციის პრინციპებზე დაყრდნობით, თავიდან ავიცილეთ მონაცემების სიჭარბე და არ ვზრუნავთ გარკვეული მოთხოვნების დაჩქარებაზე. როგორც კი პირველი მომხმარებლები მოვიდნენ, მუშაობის პრობლემას წავაწყდით. ჩვეულებისამებრ, ამისთვის სრულიად მოუმზადებლები ვიყავით. პირველი პრობლემები მარტივი აღმოჩნდა. როგორც წესი, ყველაფერი ახალი ინდექსის დამატებით წყდებოდა. მაგრამ დადგა დრო, როდესაც უბრალო პატჩებმა შეწყვიტეს მუშაობა. იმის გაცნობიერებით, რომ გამოცდილება გვაკლია და ჩვენთვის სულ უფრო რთული ხდება იმის გაგება, თუ რა იწვევს პრობლემებს, დავიქირავეთ სპეციალისტები, რომლებიც დაგვეხმარნენ სერვერის სწორად დაყენებაში, მონიტორინგის დაკავშირებაში და გვაჩვენეს, სად უნდა ვეძებოთ. სტატისტიკა.

სურათი ორი. სტატისტიკური

ასე რომ, ჩვენ გვაქვს დაახლოებით 10 ათასი სხვადასხვა მოთხოვნა, რომლებიც შესრულებულია ჩვენს მონაცემთა ბაზაში დღეში. ამ 10 ათასიდან არის მონსტრები, რომლებიც შესრულებულია 2-3 მილიონჯერ, შესრულების საშუალო დროით 0.1-0.3 ms და არის მოთხოვნები 30 წამის შესრულების საშუალო ხანგრძლივობით, რომლებსაც დღეში 100-ჯერ იძახიან.

შეუძლებელი იყო ყველა 10 ათასი მოთხოვნის ოპტიმიზაცია, ამიტომ გადავწყვიტეთ გაერკვია, თუ სად უნდა მივმართოთ ჩვენი ძალისხმევა მონაცემთა ბაზის მუშაობის სწორად გასაუმჯობესებლად. რამდენიმე გამეორების შემდეგ დავიწყეთ მოთხოვნების ტიპებად დაყოფა.

TOP მოთხოვნები

ეს არის ყველაზე მძიმე მოთხოვნები, რომლებსაც ყველაზე მეტი დრო სჭირდება (საერთო დრო). ეს არის მოთხოვნები, რომლებსაც ან ძალიან ხშირად უწოდებენ, ან მოთხოვნები, რომელთა შესრულებას ძალიან დიდი დრო სჭირდება (გრძელი და ხშირი მოთხოვნები ოპტიმიზირებული იყო სიჩქარისთვის ბრძოლის პირველ გამეორებებში). შედეგად, სერვერი ყველაზე მეტ დროს ხარჯავს მათ შესრულებაზე. უფრო მეტიც, მნიშვნელოვანია ძირითადი მოთხოვნების გამოყოფა მთლიანი შესრულების დროით და ცალკე IO დროით. ასეთი მოთხოვნების ოპტიმიზაციის მეთოდები ოდნავ განსხვავებულია.

ყველა კომპანიის ჩვეულებრივი პრაქტიკაა TOP მოთხოვნებთან მუშაობა. რამდენიმე მათგანია; თუნდაც ერთი შეკითხვის ოპტიმიზაციამ შეიძლება გაათავისუფლოს რესურსების 5-10%. თუმცა, როგორც პროექტი მომწიფდება, TOP მოთხოვნების ოპტიმიზაცია სულ უფრო არატრივიალური ამოცანა ხდება. ყველა მარტივი მეთოდი უკვე შემუშავებულია და ყველაზე „მძიმე“ მოთხოვნა რესურსების „მხოლოდ“ 3-5%-ს იღებს. თუ TOP შეკითხვებს 30-40%-ზე ნაკლები დრო სჭირდება, მაშინ, სავარაუდოდ, თქვენ უკვე ცდილობდით მათ სწრაფად იმუშაოთ და დროა გადახვიდეთ შემდეგი ჯგუფიდან მოთხოვნების ოპტიმიზაციაზე.
რჩება პასუხის გაცემა კითხვაზე, თუ რამდენი მთავარი მოთხოვნა უნდა იყოს შეტანილი ამ ჯგუფში. მე ჩვეულებრივ ვიღებ მინიმუმ 10-ს, მაგრამ არაუმეტეს 20-ს. ვცდილობ, რომ TOP ჯგუფში პირველი და ბოლო დრო განსხვავდებოდეს არაუმეტეს 10-ჯერ. ანუ, თუ შეკითხვის შესრულების დრო მკვეთრად დაეცემა 1 ადგილიდან მე-10-მდე, მაშინ ვიღებ TOP-10-ს, თუ ვარდნა უფრო ეტაპობრივია, მაშინ ჯგუფის ზომას გავზრდი 15-მდე ან 20-მდე.

შუა გლეხები

ეს არის ყველა მოთხოვნა, რომელიც მოდის TOP-ის შემდეგ, ბოლო 5-10%-ის გარდა. ჩვეულებრივ, ამ მოთხოვნების ოპტიმიზაციაში არის შესაძლებლობა მნიშვნელოვნად გაზარდოს სერვერის შესრულება. ეს მოთხოვნები შეიძლება იყოს 80% -მდე. მაგრამ მაშინაც კი, თუ მათმა წილმა გადააჭარბა 50% -ს, მაშინ დროა უფრო ყურადღებით დავაკვირდეთ მათ.

კუდი

როგორც აღვნიშნეთ, ეს მოთხოვნები ჩნდება ბოლოს და იღებს დროის 5-10%. თქვენ შეგიძლიათ დაივიწყოთ ისინი მხოლოდ იმ შემთხვევაში, თუ არ იყენებთ ავტომატური შეკითხვის ანალიზის ინსტრუმენტებს, მაშინ მათი ოპტიმიზაცია ასევე შეიძლება იაფი იყოს.

როგორ შევაფასოთ თითოეული ჯგუფი?

მე ვიყენებ SQL მოთხოვნას, რომელიც მეხმარება ასეთი შეფასების გაკეთებაში PostgreSQL-ისთვის (დარწმუნებული ვარ, რომ მსგავსი შეკითხვის დაწერა შეიძლება მრავალი სხვა DBMS-ისთვის)

SQL მოთხოვნა TOP-MEDIUM-TAIL ჯგუფების ზომის შესაფასებლად

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), რომლებიც განასხვავებენ მოთხოვნებს ერთი ჯგუფისგან მეორისგან.

ასე უხეშად ადარებს მოთხოვნის წილებს ოპტიმიზაციის სამუშაოების დაწყების დროს და ახლა.

დიაგრამა აჩვენებს, რომ TOP მოთხოვნების წილი მკვეთრად შემცირდა, მაგრამ "საშუალო გლეხები" გაიზარდა.
თავდაპირველად, TOP მოთხოვნები მოიცავდა აშკარა შეცდომებს. დროთა განმავლობაში, ბავშვთა დაავადებები გაქრა, TOP მოთხოვნების წილი შემცირდა და უფრო და უფრო მეტი ძალისხმევა იყო საჭირო რთული მოთხოვნების დასაჩქარებლად.

მოთხოვნის ტექსტის მისაღებად ვიყენებთ შემდეგ მოთხოვნას

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

აქ არის ყველაზე ხშირად გამოყენებული ტექნიკის სია, რომლებიც დაგვეხმარა დავაჩქაროთ TOP მოთხოვნები:

• სისტემის ხელახალი დიზაინი, მაგალითად, შეტყობინებების ლოგიკის გადამუშავება შეტყობინებების ბროკერის გამოყენებით მონაცემთა ბაზაში პერიოდული მოთხოვნების ნაცვლად.
• ინდექსების დამატება ან შეცვლა
• ORM მოთხოვნების გადაწერა სუფთა SQL-ზე
• ზარმაცი მონაცემთა ჩატვირთვის ლოგიკის ხელახლა ჩაწერა
• ქეშირება მონაცემთა დენორმალიზების გზით. მაგალითად, გვაქვს მაგიდასთან კავშირი მიწოდება -> ინვოისი -> მოთხოვნა -> აპლიკაცია. ანუ, თითოეული მიწოდება ასოცირდება აპლიკაციასთან სხვა ცხრილების საშუალებით. იმისათვის, რომ ყველა ცხრილი არ დავაკავშიროთ თითოეულ მოთხოვნაში, ჩვენ დავამატეთ ბმული მოთხოვნაზე მიწოდების ცხრილში.
• სტატიკური ცხრილების ქეშირება საცნობარო წიგნებით და იშვიათად იცვლება ცხრილები პროგრამის მეხსიერებაში.

ზოგჯერ ცვლილებები შთამბეჭდავ რედიზაინს წარმოადგენდა, მაგრამ ისინი უზრუნველყოფდნენ სისტემის დატვირთვის 5-10%-ს და გამართლებული იყო. დროთა განმავლობაში, გამონაბოლქვი უფრო და უფრო პატარა ხდებოდა და უფრო და უფრო სერიოზული რედიზაინი იყო საჭირო.

შემდეგ ყურადღება მივაქციეთ თხოვნების მეორე ჯგუფს - საშუალო გლეხების ჯგუფს. მასში კიდევ ბევრი შეკითხვაა და ჩანდა, რომ დიდი დრო დასჭირდებოდა მთელი ჯგუფის გაანალიზებას. თუმცა, მოთხოვნების უმეტესობა ოპტიმიზაციისთვის ძალიან მარტივი აღმოჩნდა და ბევრი პრობლემა ათობითჯერ განმეორდა სხვადასხვა ვარიაციებში. აქ მოცემულია რამდენიმე ტიპიური ოპტიმიზაციის მაგალითები, რომლებიც ჩვენ გამოვიყენეთ ათობით მსგავს მოთხოვნაზე და ოპტიმიზებული მოთხოვნების თითოეულმა ჯგუფმა გადმოტვირთა მონაცემთა ბაზა 3-5%-ით.

• ჩანაწერების არსებობის შემოწმების ნაცვლად COUNT და ცხრილის სრული სკანირების გამოყენებით, დაიწყო EXISTS გამოყენება
• მოიშორეთ DISTINCT (ზოგადი რეცეპტი არ არსებობს, მაგრამ ზოგჯერ შეგიძლიათ მარტივად მოიცილოთ იგი მოთხოვნის 10-100-ჯერ დაჩქარებით).

  მაგალითად, მოთხოვნის ნაცვლად, რომ აირჩიოთ ყველა დრაივერი მიწოდების დიდი ცხრილიდან (DELIVERY)

  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 მოთხოვნების იგივე ჯგუფების მუდმივი მონიტორინგის წყალობით. როგორც კი TOP ჯგუფში ახალი მოთხოვნა გამოჩნდება, მაშინვე ვაანალიზებთ და ვცდილობთ დავაჩქაროთ. ჩვენ განვიხილავთ MEDIUM ჯგუფს კვირაში ერთხელ შეკითხვის ანალიზის სკრიპტების გამოყენებით. თუ შეგვხვდება ახალი მოთხოვნები, რომელთა ოპტიმიზაცია უკვე ვიცით, სწრაფად ვცვლით მათ. ზოგჯერ ჩვენ ვპოულობთ ოპტიმიზაციის ახალ მეთოდებს, რომლებიც შეიძლება გამოყენებულ იქნას რამდენიმე მოთხოვნაზე ერთდროულად.

ჩვენი პროგნოზით, ამჟამინდელი სერვერი გაუძლებს მომხმარებელთა რაოდენობის კიდევ 3-5-ჯერ გაზრდას. მართალია, ჩვენ გვაქვს კიდევ ერთი ტუზი - ჩვენ ჯერ კიდევ არ გადავიტანეთ SELECT მოთხოვნები სარკეში, როგორც რეკომენდებულია. მაგრამ ჩვენ ამას შეგნებულად არ ვაკეთებთ, რადგან გვსურს, პირველ რიგში, სრულად გამოვწუროთ „ჭკვიანი“ ოპტიმიზაციის შესაძლებლობები „მძიმე არტილერიის“ ჩართვამდე.
შესრულებული სამუშაოს კრიტიკული დათვალიერება შეიძლება ვარაუდობდეს ვერტიკალური სკალირების გამოყენებას. შეიძინეთ უფრო ძლიერი სერვერი სპეციალისტების დროის დაკარგვის ნაცვლად. სერვერი შეიძლება არც ისე ძვირი ღირდეს, მით უმეტეს, რომ ჩვენ ჯერ არ ამოვიწურეთ ვერტიკალური სკალირების საზღვრები. თუმცა 10-ჯერ გაიზარდა მხოლოდ მოთხოვნების რაოდენობა. რამდენიმე წლის განმავლობაში, სისტემის ფუნქციონირება გაიზარდა და ახლა უფრო მეტი სახის მოთხოვნაა. ქეშირების წყალობით, ფუნქციონალობა, რომელიც არსებობდა, შესრულებულია ნაკლები მოთხოვნით და უფრო ეფექტური მოთხოვნებით. ეს ნიშნავს, რომ თქვენ შეგიძლიათ უსაფრთხოდ გაამრავლოთ კიდევ 5-ით, რომ მიიღოთ რეალური აჩქარების კოეფიციენტი. ასე რომ, ყველაზე კონსერვატიული შეფასებით, შეგვიძლია ვთქვათ, რომ აჩქარება იყო 50-ჯერ ან მეტი. სერვერის ვერტიკალურად რხევა 50-ჯერ მეტი დაჯდება. განსაკუთრებით იმის გათვალისწინებით, რომ ოპტიმიზაციის განხორციელების შემდეგ ის მუდმივად მუშაობს და დაქირავებული სერვერის ანგარიში ყოველთვიურად მოდის.

წყარო: www.habr.com