PostgreSQL მოთხოვნების მასობრივი ოპტიმიზაცია. კირილ ბოროვიკოვი (ტენსორი)

მოხსენებაში წარმოდგენილია რამდენიმე მიდგომა, რომელიც საშუალებას იძლევა SQL მოთხოვნების შესრულების მონიტორინგი, როდესაც დღეში მილიონობით მათგანია, და არსებობს ასობით მონიტორინგი PostgreSQL სერვერი.

რა ტექნიკური გადაწყვეტილებები გვაძლევს საშუალებას ეფექტურად დავამუშაოთ ასეთი მოცულობის ინფორმაცია და როგორ უადვილებს ეს ჩვეულებრივი დეველოპერის ცხოვრებას?

ვინ არის დაინტერესებული? კონკრეტული პრობლემების ანალიზი და ოპტიმიზაციის სხვადასხვა ტექნიკა SQL მოთხოვნები და ტიპიური DBA პრობლემების გადაჭრა PostgreSQL-ში - ასევე შეგიძლიათ წაიკითხეთ სტატიების სერია ამ თემაზე.


მე მქვია კირილ ბოროვიკოვი, მე წარმოვადგენ ტენსორის კომპანია. კონკრეტულად, სპეციალიზირებული ვარ ჩვენს კომპანიაში მონაცემთა ბაზებთან მუშაობაში.

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

ზოგადად, Tensor ჩვენი მილიონი კლიენტისთვის არის VLSI არის ჩვენი აპლიკაცია: კორპორატიული სოციალური ქსელი, გადაწყვეტილებები ვიდეო კომუნიკაციისთვის, დოკუმენტების შიდა და გარე ნაკადისთვის, აღრიცხვის სისტემები და საწყობები,... ეს არის ისეთი „მეგაკომბინა“ ინტეგრირებული ბიზნესის მართვისთვის, რომელშიც 100-ზე მეტი სხვადასხვა შიდა პროექტია. .

იმისათვის, რომ ყველა მათგანი ნორმალურად მუშაობდეს და განვითარდეს, ჩვენ გვაქვს 10 განვითარების ცენტრი მთელი ქვეყნის მასშტაბით, მათში მეტია 1000 დეველოპერი.

ჩვენ ვმუშაობთ PostgreSQL-თან 2008 წლიდან და დავაგროვეთ დიდი რაოდენობით რასაც ვამუშავებთ - კლიენტის მონაცემები, სტატისტიკური, ანალიტიკური, მონაცემები გარე საინფორმაციო სისტემებიდან - 400 ტბ-ზე მეტი. მხოლოდ წარმოებაში არის დაახლოებით 250 სერვერი და საერთო ჯამში არის დაახლოებით 1000 მონაცემთა ბაზის სერვერი, რომელსაც ჩვენ ვაკვირდებით.

SQL არის დეკლარაციული ენა. თქვენ აღწერთ არა „როგორ“ უნდა იმუშაოს რაღაცამ, არამედ „რისი“ მიღწევა გსურთ. DBMS-მა უკეთ იცის, როგორ გააკეთოს JOIN - როგორ დააკავშიროს თქვენი ცხრილები, რა პირობები დააწესოს, რა გაივლის ინდექსს, რა არა...

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

მაგრამ, იმისდა მიუხედავად, რომ PostgreSQL არ აძლევს საშუალებას "გარედან" გააკონტროლოს საკუთარი თავი, ის შესანიშნავად იძლევა საშუალებას ნახეთ რა ხდება მის შიგნითროდესაც თქვენ აწარმოებთ თქვენს შეკითხვას და სად აქვს მას პრობლემები.

ზოგადად, რა კლასიკურ პრობლემებს აწყდება დეველოპერი [DBA]? „აი, შევასრულეთ თხოვნა და ჩვენთან ყველაფერი ნელია, ყველაფერი კიდია, რაღაც ხდება... რაღაც უბედურება!“

მიზეზები თითქმის ყოველთვის იგივეა:

• არაეფექტური შეკითხვის ალგორითმი
  დეველოპერი: „ახლა მე ვაძლევ მას 10 ცხრილს SQL-ში JOIN-ის საშუალებით...“ - და ელის, რომ მისი პირობები სასწაულებრივად „გაიხსნება“ და ის ყველაფერს სწრაფად მიიღებს. მაგრამ სასწაულები არ ხდება და ასეთი ცვალებადობის მქონე ნებისმიერი სისტემა (10 ცხრილი ერთში FROM) ყოველთვის იძლევა რაიმე სახის შეცდომას. [სტატიის]
• მოძველებული სტატისტიკა
  ეს პუნქტი ძალიან აქტუალურია სპეციალურად PostgreSQL-სთვის, როდესაც თქვენ „ჩაასხით“ დიდი მონაცემთა ნაკრები სერვერზე, გააკეთეთ მოთხოვნა და ის „აქცევს“ თქვენს ტაბლეტს. იმის გამო, რომ გუშინ მასში 10 ჩანაწერი იყო, დღეს კი 10 მილიონი, მაგრამ PostgreSQL ჯერ არ იცის ამის შესახებ და ჩვენ უნდა ვუთხრათ ამის შესახებ. [სტატიის]
• რესურსების "ჩართვა".
  თქვენ დააინსტალირეთ დიდი და მძიმედ დატვირთული მონაცემთა ბაზა სუსტ სერვერზე, რომელსაც არ აქვს საკმარისი დისკი, მეხსიერება ან პროცესორის შესრულება. და ეს ყველაფერი... სადღაც არის შესრულების ჭერი, რომლის ზემოთაც ვეღარ გადახტები.
• დაბლოკვა
  ეს რთული წერტილია, მაგრამ ისინი ყველაზე აქტუალურია სხვადასხვა მოდიფიცირებისთვის (INSERT, UPDATE, DELETE) - ეს ცალკე დიდი თემაა.

გეგმის მიღება

...და სხვა ყველაფრისთვის ჩვენ სჭირდება გეგმა! ჩვენ უნდა ვნახოთ რა ხდება სერვერის შიგნით.

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

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

შეკითხვის გეგმის მისაღებად, უმარტივესი გზაა განცხადების შესრულება EXPLAIN. რომ მიიღოთ ყველა რეალური ატრიბუტი, ანუ რეალურად შეასრულოთ მოთხოვნა ბაზაზე - EXPLAIN (ANALYZE, BUFFERS) SELECT ....

ცუდი ნაწილი: როდესაც თქვენ გაუშვით, ეს ხდება "აქ და ახლა", ასე რომ ის მხოლოდ ადგილობრივი გამართვისთვის არის შესაფერისი. თუ აიღებთ მაღალ დატვირთულ სერვერს, რომელიც მონაცემთა ძლიერი ნაკადის ქვეშ იმყოფება, იცვლება და ნახავთ: „ოჰ! აქ ჩვენ გვაქვს ნელი შესრულებაXia მოთხოვნა." ნახევარი საათის, ერთი საათის წინ - სანამ აწარმოებდით და იღებდით ამ მოთხოვნას ჟურნალებიდან, აბრუნებდით მას სერვერზე, შეიცვალა თქვენი მთელი მონაცემთა ნაკრები და სტატისტიკა. თქვენ გაუშვით გამართვისთვის - და ის სწრაფად მუშაობს! და ვერ ხვდები რატომ, რატომ იყო ნელა.

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

თუ ის აცნობიერებს, რომ ზოგიერთი მოთხოვნა მუშაობს თქვენს მიერ მითითებულ ლიმიტზე მეტი ხნის განმავლობაში, ის ასეც ხდება ამ მოთხოვნის გეგმის „სნეფშოტი“ და ერთად წერს ჟურნალში.

როგორც ჩანს, ახლა ყველაფერი კარგადაა, ლოგინში მივდივართ და იქ ვნახავთ... [ტექსტის ძირი]. მაგრამ ამაზე ვერაფერს ვიტყვით, გარდა იმისა, რომ ეს შესანიშნავი გეგმაა, რადგან მის შესრულებას 11 ms დასჭირდა.

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

მაგრამ მაშინაც კი, თუ ეს არ არის აშკარა, თუნდაც არასასიამოვნო, არსებობს უფრო ფუნდამენტური პრობლემები:

• კვანძი მიუთითებს მთელი ქვეხის რესურსების ჯამი მის ქვეშ. ანუ, თქვენ არ შეგიძლიათ უბრალოდ გაარკვიოთ, რამდენი დრო დაიხარჯა ამ კონკრეტულ ინდექსის სკანირებაზე, თუ მის ქვეშ არის ჩასმული მდგომარეობა. ჩვენ დინამიურად უნდა შევხედოთ, არის თუ არა „ბავშვები“ და პირობითი ცვლადები, CTE შიგნით - და გამოვაკლოთ ეს ყველაფერი „ჩვენს გონებაში“.
• მეორე წერტილი: დრო, რომელიც მითითებულია კვანძზე არის ერთი კვანძის შესრულების დრო. თუ ეს კვანძი შესრულდა, მაგალითად, ცხრილის ჩანაწერების ციკლის შედეგად რამდენჯერმე, მაშინ ციკლების რაოდენობა - ამ კვანძის ციკლები - იზრდება გეგმაში. მაგრამ თავად ატომური შესრულების დრო იგივე რჩება გეგმის თვალსაზრისით. ანუ, იმისათვის, რომ გაიგოთ, რამდენ ხანს შესრულდა ეს კვანძი, თქვენ უნდა გაამრავლოთ ერთი რამ მეორეზე - ისევ "თქვენს თავში".

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

მაგრამ ჩვენ გვყავს 1000 დეველოპერი და თქვენ არ შეგიძლიათ ეს გამოცდილება თითოეულ მათგანს გადასცეთ. მე, შენ, მან ვიცი, მაგრამ იქ ვიღაცამ აღარ იცის. შესაძლოა ის ისწავლოს, ან შეიძლება არა, მაგრამ ახლა უნდა იმუშაოს - და საიდან მიიღებს ამ გამოცდილებას?

დაგეგმეთ ვიზუალიზაცია

ამიტომ მივხვდით, რომ ამ პრობლემებთან გამკლავებისთვის საჭიროა გეგმის კარგი ვიზუალიზაცია. [სტატია]

ჩვენ პირველად გავიარეთ "ბაზრობა" - მოდით გადავხედოთ ინტერნეტს, რომ ვნახოთ რა არის კიდეც.

მაგრამ აღმოჩნდა, რომ ძალიან ცოტაა შედარებით "ცოცხალი" გადაწყვეტილებები, რომლებიც მეტ-ნაკლებად ვითარდება - სიტყვასიტყვით, მხოლოდ ერთი: განმარტება.depesz.com ჰუბერტ ლუბაჩევსკის მიერ. როდესაც შეხვალთ ველში „შესანახი“ გეგმის ტექსტური წარმომადგენლობით, ის გიჩვენებთ ცხრილს გაანალიზებული მონაცემებით:

• კვანძის საკუთარი დამუშავების დრო
• მთლიანი დრო მთელი ქვეხისთვის
• მოძიებული ჩანაწერების რაოდენობა, რომელიც სტატისტიკურად მოსალოდნელი იყო
• თავად კვანძის სხეული

ამ სერვისს ასევე აქვს ბმულების არქივის გაზიარების შესაძლებლობა. თქვენ იქ ჩააგდეთ თქვენი გეგმა და თქვით: "ჰეი, ვასია, აქ არის ბმული, იქ რაღაც არასწორია."

მაგრამ ასევე არის მცირე პრობლემები.

პირველ რიგში, დიდი რაოდენობით "კოპი-პასტი". თქვენ აიღებთ მორის ნაჭერს, ჩასვით იქ და ისევ და ისევ.

მეორე, წაკითხული მონაცემების რაოდენობის ანალიზი არ არის - იგივე ბუფერები, რომლებიც გამოდის EXPLAIN (ANALYZE, BUFFERS), ჩვენ ამას აქ ვერ ვხედავთ. მან უბრალოდ არ იცის როგორ დაშალოს ისინი, გაიგოს და იმუშაოს მათთან. როდესაც ბევრ მონაცემს კითხულობთ და ხვდებით, რომ შესაძლოა არასწორად ანაწილოთ დისკი და მეხსიერების ქეში, ეს ინფორმაცია ძალიან მნიშვნელოვანია.

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

მაგრამ ეს ყველაფერი "ლირიკაა", ჩვენ შეგვიძლია როგორმე ვიცხოვროთ ამით, მაგრამ არის ერთი რამ, რამაც დიდად დაგვაშორა ამ სერვისს. ეს არის შეცდომები Common Table Expression (CTE) და სხვადასხვა დინამიური კვანძების ანალიზში, როგორიცაა InitPlan/SubPlan.

თუ გჯერათ ამ სურათის, მაშინ თითოეული ცალკეული კვანძის შესრულების დრო აღემატება მთლიანი მოთხოვნის შესრულების დროს. Ეს მარტივია - ამ CTE-ის გენერირების დრო არ გამოკლებულა CTE Scan კვანძს. ამიტომ, ჩვენ აღარ ვიცით სწორი პასუხი იმაზე, თუ რამდენ ხანს დასჭირდა თავად CTE სკანირება.

მაშინ მივხვდით, რომ დრო იყო საკუთარი თავის დაწერა - ჩქარა! ყველა დეველოპერი ამბობს: "ახლა ჩვენ დავწერთ საკუთარს, ეს იქნება ძალიან მარტივი!"

ჩვენ ავიღეთ ვებ სერვისებისთვის დამახასიათებელი დასტა: Node.js + Express-ზე დაფუძნებული ბირთვი, გამოვიყენეთ Bootstrap და D3.js ლამაზი დიაგრამებისთვის. და ჩვენი მოლოდინი სრულად გამართლდა - პირველი პროტოტიპი 2 კვირაში მივიღეთ:

• საბაჟო გეგმის პარსერი
  ანუ, ახლა ჩვენ შეგვიძლია გავაანალიზოთ ნებისმიერი გეგმა PostgreSQL-ის მიერ გენერირებული გეგმებისგან.
• დინამიური კვანძების სწორი ანალიზი - CTE სკანირება, InitPlan, SubPlan
• ბუფერების განაწილების ანალიზი - სად იკითხება მონაცემთა გვერდები მეხსიერებიდან, სად ლოკალური ქეშიდან, სად დისკიდან
• მიიღო სიცხადე
  ისე, რომ ეს ყველაფერი ლოგინში არ „გათხრა“, არამედ სურათზე მაშინვე დაინახოს „ყველაზე სუსტი რგოლი“.

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

ეს არის შემოკლებული წარმომადგენლობა, რომელსაც ჩვენ ვუწოდებთ გეგმის შაბლონი.

კიდევ რა იქნება მოსახერხებელი? მოსახერხებელი იქნება იმის დანახვა, თუ რომელ კვანძს ეთმობა ჩვენი მთლიანი დროის წილი - და უბრალოდ „დააწებეთ“ გვერდზე ტორტი ჩარტში.

მივუთითებთ კვანძს და ვხედავთ - გამოდის, რომ Seq Scan-ს მთლიანი დროის მეოთხედზე ნაკლები დასჭირდა, დარჩენილი 3/4 კი CTE Scan-მა აიღო. საშინელება! ეს არის მცირე შენიშვნა CTE Scan-ის „ცეცხლის სიჩქარის“ შესახებ, თუ მათ აქტიურად იყენებთ თქვენს შეკითხვებში. ისინი არ არიან ძალიან სწრაფი - ისინი ჩამორჩებიან ცხრილის რეგულარულ სკანირებასაც კი. [სტატია] [სტატია]

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

ბუნებრივია, იყო გარკვეული „რაკები“.

პირველი რაც შეგვხვდა იყო დამრგვალების პრობლემა. გეგმაში თითოეული ცალკეული კვანძის დრო მითითებულია 1 μs სიზუსტით. და როდესაც კვანძების ციკლების რაოდენობა აღემატება, მაგალითად, 1000-ს - შესრულების შემდეგ PostgreSQL იყოფა "სიზუსტის ფარგლებში", მაშინ უკან გამოთვლისას ვიღებთ მთლიან დროს "სადღაც 0.95ms-დან 1.05ms-მდე". როდესაც დათვლა მიდის მიკროწამებში, ეს კარგია, მაგრამ როდესაც ის უკვე [მილი] წამია, თქვენ უნდა გაითვალისწინოთ ეს ინფორმაცია, როდესაც „გახსნით“ რესურსებს „ვინ რამდენი მოიხმარა“ გეგმის კვანძებთან.

მეორე წერტილი, უფრო რთული, არის რესურსების (ეს ბუფერების) განაწილება დინამიურ კვანძებს შორის. ეს დაგვიჯდა პროტოტიპის პირველი 2 კვირა პლუს კიდევ 4 კვირა.

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

ჩვენ ვუყურებთ გეგმას და გვესმის - უცნაურია, ჩვენ გვაქვს 3 ბუფერი (მონაცემთა გვერდი) "მოხმარებული" Seq Scan-ში, კიდევ 1 CTE Scan-ში და კიდევ 2 მეორე CTE Scan-ში. ანუ, თუ უბრალოდ შევაჯამებთ ყველაფერს, მივიღებთ 6-ს, მაგრამ ტაბლეტიდან მხოლოდ 3-ს ვკითხულობთ! CTE Scan არ კითხულობს არაფერს, მაგრამ მუშაობს უშუალოდ პროცესურ მეხსიერებასთან. ანუ აქ აშკარად რაღაც არასწორია!

ფაქტობრივად გამოდის, რომ აქ არის ყველა ის 3 გვერდი მონაცემი, რომელიც მოითხოვა Seq Scan-ისგან, ჯერ 1 სთხოვეს 1-ლი CTE სკანირება, შემდეგ მე-2 და კიდევ 2 წაუკითხეს მას, ანუ სულ 3 გვერდი იყო წაკითხული მონაცემები და არა 6.

და ამ სურათმა მიგვიყვანა იმის გაგებამდე, რომ გეგმის შესრულება აღარ არის ხე, არამედ უბრალოდ ერთგვარი აციკლური გრაფიკი. და მივიღეთ მსგავსი დიაგრამა, რათა გავიგოთ "პირველ რიგში რა საიდან მოვიდა". ანუ აქ შევქმენით CTE pg_class-დან და ვითხოვეთ ორჯერ და თითქმის მთელი დრო დავხარჯეთ ფილიალზე, როცა მეორედ მოვითხოვეთ. ნათელია, რომ 2-ე ჩანაწერის წაკითხვა ბევრად უფრო ძვირია, ვიდრე უბრალოდ 101-ლი ჩანაწერის წაკითხვა ტაბლეტიდან.

ცოტა ხნით ამოვისუნთქეთ. მათ თქვეს: „ახლა, ნეო, შენ იცი კუნგ ფუ! ახლა ჩვენი გამოცდილება თქვენს ეკრანზეა. ახლა თქვენ შეგიძლიათ გამოიყენოთ იგი." [სტატია]

ჟურნალის კონსოლიდაცია

ჩვენმა 1000-მა დეველოპერმა შვებით ამოისუნთქა. მაგრამ ჩვენ გვესმოდა, რომ ჩვენ გვაქვს მხოლოდ ასობით "საბრძოლო" სერვერი და დეველოპერების მხრიდან ეს "კოპი-პასტი" სულაც არ არის მოსახერხებელი. მივხვდით, რომ თავად უნდა შეგვეგროვებინა.

ზოგადად, არსებობს სტანდარტული მოდული, რომელსაც შეუძლია სტატისტიკის შეგროვება, თუმცა, ის ასევე უნდა გააქტიურდეს კონფიგურაციაში - ეს მოდული pg_stat_statements. მაგრამ ის არ გვიწყობდა.

პირველ რიგში, ის ანიჭებს ერთსა და იმავე შეკითხვებს სხვადასხვა სქემების გამოყენებით იმავე მონაცემთა ბაზაში სხვადასხვა QueryIds. ანუ, თუ პირველად გააკეთებ SET search_path = '01'; SELECT * FROM user LIMIT 1;და შემდეგ SET search_path = '02'; და იგივე მოთხოვნა, მაშინ ამ მოდულის სტატისტიკას ექნება განსხვავებული ჩანაწერები და მე ვერ შევაგროვებ ზოგად სტატისტიკას კონკრეტულად ამ მოთხოვნის პროფილის კონტექსტში, სქემების გათვალისწინების გარეშე.

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

და ბოლო მომენტი - "ფაქტების" ნაკლებობა. ანუ, თქვენ არ შეგიძლიათ მიმართოთ შეკითხვის შესრულების კონკრეტულ შემთხვევას - არ არსებობს, არსებობს მხოლოდ აგრეგირებული სტატისტიკა. მიუხედავად იმისა, რომ შესაძლებელია ამაზე მუშაობა, უბრალოდ ძალიან რთულია.

ამიტომ გადავწყვიტეთ კოპირ-პასიტთან ბრძოლა და დავიწყეთ წერა კოლექციონერი.

კოლექციონერი აკავშირებს SSH-ის საშუალებით, ამყარებს უსაფრთხო კავშირს სერვერთან მონაცემთა ბაზასთან სერთიფიკატის გამოყენებით და tail -F „იკვრის“ მას ჟურნალის ფაილში. ასე რომ, ამ სესიაზე ჩვენ ვიღებთ მთლიანი ჟურნალის ფაილის სრულ „სარკეს“., რომელსაც სერვერი ქმნის. თავად სერვერზე დატვირთვა მინიმალურია, რადგან ჩვენ იქ არაფერს ვაანალიზებთ, უბრალოდ ასახავს ტრაფიკს.

ვინაიდან ჩვენ უკვე დავიწყეთ ინტერფეისის წერა Node.js-ში, გავაგრძელეთ მასში კოლექტორის წერა. და ამ ტექნოლოგიამ გაამართლა თავი, რადგან ძალიან მოსახერხებელია JavaScript-ის გამოყენება სუსტ ფორმატირებულ ტექსტურ მონაცემებთან მუშაობისთვის, რაც არის ჟურნალი. თავად Node.js ინფრასტრუქტურა, როგორც სარეზერვო პლატფორმა, საშუალებას გაძლევთ მარტივად და მოხერხებულად იმუშაოთ ქსელურ კავშირებთან და, მართლაც, მონაცემთა ნებისმიერ ნაკადთან.

შესაბამისად, ჩვენ „გავაჭიმეთ“ ორ კავშირს: პირველი, რომ „მოუსმინოთ“ თავად ჟურნალს და მივიღოთ იგი საკუთარ თავთან, ხოლო მეორე - პერიოდულად ვთხოვოთ ბაზა. "მაგრამ ჟურნალი აჩვენებს, რომ ნიშანი oid 123-ით დაბლოკილია", მაგრამ ეს არაფერს ნიშნავს დეველოპერისთვის და კარგი იქნებოდა მონაცემთა ბაზას ჰკითხოთ: "რა არის OID = 123 მაინც?" და ამიტომ პერიოდულად ვეკითხებით საფუძველს, რაც ჯერ არ ვიცით საკუთარ თავზე.

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

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

მაგრამ უბრალოდ მონაცემების „ჩასხმა“ ჩვენი ტექნოლოგია ნამდვილად არ არის. იმის გამო, რომ თუ თქვენ გაქვთ დაახლოებით 50 ათასი მოთხოვნა წამში ას სერვერზე, მაშინ ეს გამოიმუშავებს 100-150 GB ჟურნალს დღეში. ამიტომ, საფუძვლიანად მოგვიწია ბაზის „გაჭრა“.

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

მეორეც, ვისწავლეთ (იძულებულები ვიყავით) ძალიან, ძალიან სწრაფად იწერება გამოყენებით COPY. ანუ არა მხოლოდ COPYრადგან ის უფრო სწრაფია ვიდრე INSERTდა კიდევ უფრო სწრაფად.

მესამე პუნქტი - მომიწია მიატოვეთ ტრიგერები, შესაბამისად, და უცხო კლავიშები. ანუ რეფერენციული მთლიანობა საერთოდ არ გვაქვს. რადგან თუ თქვენ გაქვთ ცხრილი, რომელსაც აქვს FK-ების წყვილი, და თქვენ ამბობთ მონაცემთა ბაზის სტრუქტურაში, რომ „აქ არის ჟურნალის ჩანაწერი, რომელიც მითითებულია FK-ის მიერ, მაგალითად, ჩანაწერების ჯგუფზე“, მაშინ როდესაც მას ჩასვამთ, PostgreSQL აღარაფერი დარჩა, თუ როგორ უნდა აიღოს და პატიოსნად გააკეთოს SELECT 1 FROM master_fk1_table WHERE ... იდენტიფიკატორით, რომლის ჩასმასაც ცდილობთ - უბრალოდ შეამოწმეთ, არის თუ არა ეს ჩანაწერი, რომ არ „გატეხოთ“ ეს უცხოური გასაღები თქვენი ჩასმით.

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

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

ახლა წარმოიდგინეთ, რომ თქვენ გაქვთ ცხრილი, რომელშიც უბრალოდ ითვლით მოთხოვნების რაოდენობას, რომლებიც გაიარა კონკრეტულ ჰოსტზე: +1, +1, +1, ..., +1. და თქვენ, პრინციპში, არ გჭირდებათ ეს - ეს ყველაფერი შესაძლებელია მეხსიერების თანხა კოლექციონერზე და გაგზავნეთ მონაცემთა ბაზაში ერთი ნაბიჯით +10.

დიახ, გარკვეული პრობლემების შემთხვევაში, თქვენი ლოგიკური მთლიანობა შეიძლება "დაიშლება", მაგრამ ეს თითქმის არარეალური შემთხვევაა - რადგან თქვენ გაქვთ ნორმალური სერვერი, მას აქვს ბატარეა კონტროლერში, თქვენ გაქვთ ტრანზაქციის ჟურნალი, ჟურნალი ფაილური სისტემა... ზოგადად, არ ღირს. პროდუქტიულობის დაკარგვა, რომელსაც მიიღებთ ტრიგერების/FK-ის გაშვებით, არ ღირს თქვენს მიერ გაწეული ხარჯი.

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

ჩვენთვის უბრალოდ იდეალური იყო ტექსტიდან MD5-ის გამოყენება - მოთხოვნა, გეგმა, შაბლონი,... ვიანგარიშებთ კოლექციონერის მხარეს და მზა ID-ს „ვასხამთ“ მონაცემთა ბაზაში. MD5-ის სიგრძე და ყოველდღიური დანაყოფი საშუალებას გვაძლევს არ ვიფიქროთ შესაძლო შეჯახებაზე.

მაგრამ იმისათვის, რომ ეს ყველაფერი სწრაფად ჩაგვეწერა, დაგვჭირდა თავად ჩაწერის პროცედურის შეცვლა.

როგორ წერთ ჩვეულებრივ მონაცემებს? ჩვენ გვაქვს ერთგვარი მონაცემთა ნაკრები, ვყოფთ მას რამდენიმე ცხრილად და შემდეგ COPY - ჯერ პირველში, შემდეგ მეორეში, მესამეში... მოუხერხებელია, რადგან, როგორც ჩანს, მონაცემთა ერთ ნაკადს ვწერთ სამ ნაბიჯში. თანმიმდევრობით. უსიამოვნო. შეიძლება უფრო სწრაფად გაკეთდეს? შეიძლება!

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

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

გარდა ამისა, ჩვენ გავარკვიეთ, რომ ასეთი დატვირთვის პროფილისთვის, ნებისმიერი აგრეგაცია, როდესაც ჩანაწერები გროვდება ჯგუფურად, ბოროტებაა. კლასიკური ბოროტებაა INSERT ... VALUES და კიდევ 1000 ჩანაწერი. იმის გამო, რომ იმ მომენტში თქვენ გაქვთ ჩაწერის პიკი მედიაზე და ყველა, ვინც ცდილობს დისკზე რაღაცის დაწერას, დაელოდება.

ასეთი ანომალიებისგან თავის დასაღწევად, უბრალოდ არ შეაგროვოთ არაფერი, არ ბუფერული საერთოდ. და თუ დისკზე ბუფერირება მოხდა (საბედნიეროდ, Stream API Node.js-ში საშუალებას გაძლევთ გაარკვიოთ) - გადადეთ ეს კავშირი. როდესაც მიიღებთ ღონისძიებას, რომ ის კვლავ უფასოა, დაწერეთ მას დაგროვილი რიგიდან. და სანამ ის დაკავებულია, აიღეთ შემდეგი უფასო აუზიდან და დაწერეთ.

მონაცემთა ჩაწერის ამ მიდგომის დანერგვამდე ჩვენ გვქონდა დაახლოებით 4K ჩაწერის ოპერაციები და ამ გზით 4-ჯერ შევამცირეთ დატვირთვა. ახლა ისინი კიდევ 6-ჯერ გაიზარდა ახალი მონიტორინგის მონაცემთა ბაზების გამო - 100 მბ/წმ-მდე. ახლა კი ჩვენ ვინახავთ ბოლო 3 თვის ჟურნალებს დაახლოებით 10-15 ტბ მოცულობით, იმ იმედით, რომ სულ რაღაც სამ თვეში ნებისმიერი დეველოპერი შეძლებს ნებისმიერი პრობლემის გადაჭრას.

ჩვენ გვესმის პრობლემები

მაგრამ ყველა ამ მონაცემის უბრალოდ შეგროვება კარგია, სასარგებლო, აქტუალური, მაგრამ არა საკმარისი - მისი გაგებაა საჭირო. რადგან ეს არის მილიონობით განსხვავებული გეგმა დღეში.

მაგრამ მილიონები უმართავია, ჯერ „პატარა“ უნდა გავაკეთოთ. და, უპირველეს ყოვლისა, თქვენ უნდა გადაწყვიტოთ, როგორ მოაწყოთ ეს "პატარა" რამ.

ჩვენ გამოვყავით სამი ძირითადი პუნქტი:

• ვინც გაგზავნა ეს მოთხოვნა
  ანუ რომელი აპლიკაციიდან „ჩამოვიდა“: ვებ ინტერფეისი, ბექენდი, გადახდის სისტემა თუ სხვა.
• სადაც ეს მოხდა
  რომელ კონკრეტულ სერვერზე? იმის გამო, რომ თუ თქვენ გაქვთ რამდენიმე სერვერი ერთი აპლიკაციის ქვეშ და მოულოდნელად ერთი "სულელდება" (რადგან "დისკი დამპალია", "მეხსიერება გაჟონა", სხვა პრობლემა), მაშინ კონკრეტულად უნდა მიმართოთ სერვერს.
• როგორც პრობლემა ასე თუ ისე იჩენდა თავს

იმის გასაგებად, თუ ვინ გამოგვიგზავნა მოთხოვნა, ვიყენებთ სტანდარტულ ინსტრუმენტს - სესიის ცვლადის დაყენებას: SET application_name = '{bl-host}:{bl-method}'; — ჩვენ ვაგზავნით ბიზნეს ლოგიკის ჰოსტის სახელს, საიდანაც მოდის მოთხოვნა, და მეთოდის ან აპლიკაციის სახელს, რომელმაც ის წამოიწყო.

მას შემდეგ, რაც ჩვენ გადავეცით მოთხოვნის „მფლობელს“, ის უნდა გამოვიდეს ჟურნალში - ამისათვის ჩვენ ვაკონფიგურირებთ ცვლადს log_line_prefix = ' %m [%p:%v] [%d] %r %a'. დაინტერესებულთათვის, შესაძლოა იხილეთ სახელმძღვანელოშირას ნიშნავს ეს ყველაფერი. გამოდის, რომ ჩვენ ვხედავთ ჟურნალში:

• დრო
• პროცესის და ტრანზაქციის იდენტიფიკატორები
• მონაცემთა ბაზის სახელი
• იმ პირის IP, ვინც ეს მოთხოვნა გამოაგზავნა
• და მეთოდის სახელი

შემდეგ მივხვდით, რომ არც თუ ისე საინტერესოა სხვადასხვა სერვერებს შორის ერთი მოთხოვნის კორელაციის ნახვა. ხშირად არ არის ისეთი სიტუაცია, როდესაც ერთი აპლიკაცია თანაბრად ხრახნის აქეთ-იქით. მაგრამ მაშინაც კი, თუ ეს იგივეა, გადახედეთ რომელიმე ამ სერვერს.

ასე რომ, აქ არის cut "ერთი სერვერი - ერთი დღე" ჩვენთვის საკმარისი აღმოჩნდა ნებისმიერი ანალიზისთვის.

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

როდესაც კონკრეტული შემთხვევებიდან შაბლონებზე გადავედით, ერთდროულად ორი უპირატესობა მივიღეთ:

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

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

იმის გამო, რომ, მაგალითად, თქვენ მოდიხართ ჰოსტის ანალიტიკის გვერდზე, შეხედეთ - დისკზე რაღაცის წაკითხვა იწყება. სერვერზე დისკი ვერ უმკლავდება - ვინ კითხულობს მისგან?

და თქვენ შეგიძლიათ დაალაგოთ ნებისმიერი სვეტის მიხედვით და გადაწყვიტოთ, რასთან გექნებათ საქმე ახლა - პროცესორზე ან დისკზე დატვირთვა, თუ მოთხოვნის საერთო რაოდენობა... დავახარისხეთ, გადავხედეთ „ტოპებს“, გავასწორეთ და გამოუშვა აპლიკაციის ახალი ვერსია.
[ვიდეო ლექცია]

და მაშინვე შეგიძლიათ იხილოთ სხვადასხვა აპლიკაციები, რომლებიც მოყვება იგივე შაბლონს მოთხოვნიდან, როგორიცაა SELECT * FROM users WHERE login = 'Vasya'. Frontend, backend, დამუშავება... და გაინტერესებთ, რატომ წაიკითხავს დამუშავება მომხმარებელი, თუ ის მასთან არ ურთიერთობს.

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

გარკვეული პერიოდის შემდეგ მივხვდით, რომ აგრეგაცია გვაკლდა სტატისტიკა გეგმის კვანძების მიხედვით. ჩვენ გეგმებიდან გამოვყავით მხოლოდ ის კვანძები, რომლებიც თავად აკეთებენ რაღაცას ცხრილების მონაცემებთან (კითხულობენ/ჩაწერენ მათ ინდექსით თუ არა). სინამდვილეში, წინა სურათთან შედარებით მხოლოდ ერთი ასპექტია დამატებული - რამდენი ჩანაწერი მოგვიტანა ამ კვანძმა?და რამდენი გაუქმდა (მწკრივები ამოღებულია ფილტრით).

თქვენ არ გაქვთ შესაფერისი ინდექსი ფირფიტაზე, თქვენ მიმართავთ მას, ის გადის ინდექსს, მოხვდება Seq Scan-ში... თქვენ გაფილტრეთ ყველა ჩანაწერი ერთის გარდა. რატომ გჭირდებათ 100 მილიონი გაფილტრული ჩანაწერი დღეში? არ ჯობია ინდექსის შემობრუნება?

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

ყველას, ვინც კითხვებს დაწერა, ალბათ შეხვედრია ეს ნიმუში: „მომეცი ვასიას ბოლო შეკვეთა, მისი თარიღი“. გადააბიჯე ზუსტად იმავე „რაკზე“.

მაგრამ ჩვენ ვიცით, რომ ეს არის „რაკი“ - ასე რომ, რატომ დაუყოვნებლივ არ უთხარით დეველოპერს რა უნდა გააკეთოს. შესაბამისად, ახლავე გეგმის გახსნისას, ჩვენი დეველოპერი მაშინვე ხედავს ლამაზ სურათს რჩევებით, სადაც ისინი მაშინვე ეუბნებიან მას: ”თქვენ გაქვთ პრობლემები აქეთ-იქით, მაგრამ ისინი მოგვარებულია ასე და ისე.”

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

წყარო: www.habr.com