ღრმად ჩაეფლო PostgreSQL შიდა სტატისტიკაში. ალექსეი ლესოვსკი

ალექსეი ლესოვსკის 2015 წლის მოხსენების ტრანსკრიფცია "ღრმა ჩაძირვა PostgreSQL-ის შიდა სტატისტიკაში"

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

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

Საღამო მშვიდობისა მე მქვია ალექსი. როგორც ილიამ თქვა, PostgreSQL სტატისტიკაზე ვისაუბრებ.

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

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

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

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

მინდა გაჩვენოთ, რომ სტატისტიკის გამოყენება სასარგებლოა. Ეს აუცილებელია. გამოიყენეთ იგი უშიშრად. ყველაფერი რაც ჩვენ გვჭირდება არის მარტივი SQL და SQL-ის საბაზისო ცოდნა.

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

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

ჩვენ შეგვიძლია შევხედოთ CPU-ს დატვირთვას top, ჩვენ ვხედავთ მეხსიერების გამოყენებას ზოგიერთი სისტემის კომუნალური პროგრამის მიერ, მაგრამ ჩვენ ვერ შევძლებთ PostgreSQL-ის შიგნით ჩახედვას. ამისთვის გვჭირდება სხვა ინსტრუმენტები.

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

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

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

რა პრობლემები აქვს სტატისტიკას?

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

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

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

სტატისტიკა ბევრ რამეს გვეუბნება. ისინი შეიძლება დაიყოს კატეგორიებად.

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

სტატისტიკის წყაროები წარმოდგენილია შემდეგნაირად:

• გაზიარებულ მეხსიერებაში (გაზიარებული ბუფერები) არის სეგმენტი იქ სტატიკური მონაცემების განთავსებისთვის, ასევე არის ის მრიცხველები, რომლებიც მუდმივად იზრდება, როდესაც ხდება გარკვეული მოვლენები, ან ჩნდება გარკვეული მომენტები მონაცემთა ბაზის მუშაობაში.
• ყველა ეს მრიცხველი არ არის ხელმისაწვდომი მომხმარებლისთვის და არც კი არის ხელმისაწვდომი ადმინისტრატორისთვის. ეს დაბალი დონის რაღაცეებია. მათზე წვდომისთვის, PostgreSQL უზრუნველყოფს ინტერფეისს SQL ფუნქციების სახით. ჩვენ შეგვიძლია შევარჩიოთ არჩევანი ამ ფუნქციების გამოყენებით და მივიღოთ რაიმე სახის მეტრიკა (ან მეტრიკის ნაკრები).
• თუმცა, ყოველთვის არ არის მოსახერხებელი ამ ფუნქციების გამოყენება, ამიტომ ფუნქციები არის ხედების (VIEWs) საფუძველი. ეს არის ვირტუალური ცხრილები, რომლებიც აწვდიან სტატისტიკას კონკრეტულ ქვესისტემაზე, ან მონაცემთა ბაზის ზოგიერთ მოვლენაზე.
• ეს ჩაშენებული ხედები (VIEWs) არის სტატისტიკასთან მუშაობის მთავარი მომხმარებლის ინტერფეისი. ისინი ხელმისაწვდომია სტანდარტულად, ყოველგვარი დამატებითი პარამეტრების გარეშე, შეგიძლიათ დაუყოვნებლივ გამოიყენოთ ისინი, უყუროთ, მიიღოთ ინფორმაცია იქიდან. და ასევე არის წვლილი. წვლილი ოფიციალურია. შეგიძლიათ დააინსტალიროთ postgresql-contrib პაკეტი (მაგალითად, postgresql94-contrib), ჩატვირთოთ საჭირო მოდული კონფიგურაციაში, მიუთითოთ მისთვის პარამეტრები, გადატვირთოთ PostgreSQL და შეგიძლიათ გამოიყენოთ იგი. (Შენიშვნა. განაწილებიდან გამომდინარე, კონტრიბუციის ბოლო ვერსიებში პაკეტი ძირითადი პაკეტის ნაწილია).
• და არის არაოფიციალური წვლილი. მათ არ მიეწოდებათ სტანდარტული PostgreSQL განაწილება. ისინი ან უნდა იყოს შედგენილი ან დაინსტალირებული როგორც ბიბლიოთეკა. ვარიანტები შეიძლება ძალიან განსხვავებული იყოს, იმისდა მიხედვით, თუ რას მოიფიქრებს ამ არაოფიციალური წვლილის შემქმნელი.

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

თუმცა წინა სურათს თუ გადავიღებთ Как тратится время на PostgreSQL და შეესაბამება ამ სიას, ჩვენ ვიღებთ ამ სურათს. თითოეული ხედი (VIEWs) ან თითოეული ფუნქცია, ჩვენ შეგვიძლია გამოვიყენოთ ამა თუ იმ მიზნით, რომ მივიღოთ შესაბამისი სტატისტიკა, როდესაც ჩვენ გვაქვს PostgreSQL გაშვებული. და ჩვენ უკვე შეგვიძლია მივიღოთ გარკვეული ინფორმაცია ქვესისტემის მუშაობის შესახებ.

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

რა შეგვიძლია ავიღოთ იქიდან? დავიწყოთ უმარტივესი ნივთებით.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

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

Ნათელია, რომ რაც მეტი ქეში გვაქვს, მით უკეთესი. ჩვენ ვაფასებთ ამ მეტრიკას პროცენტულად. და, მაგალითად, თუ გვაქვს ამ ქეშის დარტყმების პროცენტი 90%-ზე მეტი, მაშინ ეს კარგია. თუ ის დაეცემა 90%-ზე დაბლა, მაშინ ჩვენ არ გვაქვს საკმარისი მეხსიერება, რომ შევინარჩუნოთ მონაცემთა ცხელი თავი მეხსიერებაში. და ამ მონაცემების გამოსაყენებლად, PostgreSQL იძულებულია შევიდეს დისკზე და ეს უფრო ნელია, ვიდრე მონაცემების მეხსიერებიდან წაკითხვის შემთხვევაში. და თქვენ უნდა იფიქროთ მეხსიერების გაზრდაზე: ან გაზარდეთ საერთო ბუფერები, ან გაზარდეთ რკინის მეხსიერება (RAM).

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

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

ჩვენ შეგვიძლია გამოვიყენოთ ეს მოთხოვნა. ეს SQL საკმაოდ მარტივია. და ჩვენ შეგვიძლია დავინახოთ ეს მონაცემები ჩვენთვის.

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

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

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

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

pg_stat_bgwriter - ეს ხედი აღწერს ორი PostgreSQL ფონური ქვესისტემის მუშაობას: checkpointer и background writer.

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

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

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

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

და არსებობს სამი პარამეტრი საგუშაგოს დასარეგულირებლად:

• сheckpoint_segments.

• сheckpoint_timeout.

• сheckpoint_competion_target.

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

გაფრთხილება: ანგარიშში გათვალისწინებული 9.4 ვერსია აღარ არის აქტუალური. PostgreSQL-ის თანამედროვე ვერსიებში პარამეტრი checkpoint_segments შეიცვალა პარამეტრებით min_wal_size и max_wal_size.

შემდეგი ქვესისტემა არის ფონის დამწერი − background writer. Რას აკეთებს? ის მუდმივად მუშაობს გაუთავებელ მარყუჟში. ის ასკანირებს გვერდებს გაზიარებულ ბუფერებში და ასუფთავებს ბინძურ გვერდებს, რომლებიც აღმოაჩენს დისკზე. ამგვარად, ის ეხმარება საგუშაგოს ნაკლები სამუშაოს შესრულებაში საგუშაგოს დროს.

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

და კიდევ ერთი ძალიან სასარგებლო მაჩვენებელია buffers_backend_fsync. Backends არ აკეთებენ fsync-ს, რადგან ის ნელია. ისინი გადიან fsync up IO stack checkpointer. გამშვებ პუნქტს აქვს საკუთარი რიგი, ის პერიოდულად ამუშავებს fsync-ს და სინქრონიზებს მეხსიერებაში არსებულ გვერდებს დისკზე არსებულ ფაილებთან. თუ გამშვები წერტილის რიგი დიდი და სავსეა, მაშინ backend იძულებულია თავად გააკეთოს fsync და ეს ანელებს backend-ს, ანუ კლიენტი მიიღებს პასუხს უფრო გვიან, ვიდრე შეეძლო. თუ ხედავთ, რომ თქვენ გაქვთ ეს მნიშვნელობა ნულზე მეტი, მაშინ ეს უკვე პრობლემაა და ყურადღება უნდა მიაქციოთ ფონის დამწერის პარამეტრებს და ასევე შეაფასოთ დისკის ქვესისტემის მუშაობა.

გაფრთხილება: _შემდეგი ტექსტი აღწერს რეპლიკაციასთან დაკავშირებულ სტატისტიკურ შეხედულებებს. ხედებისა და ფუნქციების სახელების უმეტესობა გადაერქვა Postgres 10-ში. გადარქმევების არსი იყო ჩანაცვლება. xlog on wal и location on lsn ფუნქციების/ნახვის სახელებში და ა.შ. კონკრეტული მაგალითი, ფუნქცია pg_xlog_location_diff() ეწოდა pg_wal_lsn_diff()._

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

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

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

сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

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

დაგვიანების სამი მიზეზი არსებობს:

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

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

შენიშვნა: _ pg_xlog_location-ის ნაცვლადdiff() ფუნქცია, შეგიძლიათ გამოიყენოთ გამოკლების ოპერატორი და გამოკლოთ ერთი ადგილი მეორეს. კომფორტული.

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

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

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

პირველი, რაც შეგვიძლია შევხედოთ არის ცხრილის თანმიმდევრული სკანირება. თავად რიცხვი ამ პასაჟების შემდეგ სულაც არ არის ცუდი და არ მიუთითებს იმაზე, რომ უკვე რაღაცის გაკეთება გვჭირდება.

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

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

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

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

ზოგადად, არის მეტაბრძანებები dt и di, რომელიც შეგიძლიათ გამოიყენოთ PSQL-ში და ასევე იხილოთ ცხრილისა და ინდექსის ზომები.

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

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

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

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

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

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

ALTER TABLE table_name SET (fillfactor = 70);

როგორ გავზარდოთ მოცულობა hot updateov? შეგვიძლია გამოვიყენოთ fillfactor. ის განსაზღვრავს დაჯავშნილი თავისუფალი სივრცის ზომას ცხრილში გვერდის შევსებისას INSERT-ების გამოყენებით. როდესაც ჩანართები მიდის მაგიდაზე, ისინი მთლიანად ავსებენ გვერდს, არ ტოვებენ მასში ცარიელ ადგილს. შემდეგ ახალი გვერდი მონიშნულია. მონაცემები ისევ ივსება. და ეს არის ნაგულისხმევი ქცევა, fillfactor = 100%.

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

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

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

შენიშვნა: _ Postgres 10-ის შემდეგ, ვაკუუმის ვაკუუმის თვალყურის დევნების სიტუაცია ძალიან გაუმჯობესდა - გამოჩნდა pg_stat_progress ხედი.ვაკუუმი, რაც მნიშვნელოვნად ამარტივებს ავტოვაკუუმის მონიტორინგის საკითხს.

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

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

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

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

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

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

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

როგორც უკვე ვთქვი, განახლება არა მხოლოდ ცხრილების განახლებაა, არამედ ინდექსების განახლებაც. შესაბამისად, თუ მაგიდაზე გვაქვს ბევრი ინდექსი, მაშინ ცხრილის რიგების განახლებისას საჭიროა ინდექსირებული ველების ინდექსების განახლებაც და თუ ჩვენ გვაქვს გამოუყენებელი ინდექსები, რომლებისთვისაც არ არის ინდექსის სკანირება, მაშინ ისინი ჩვენთან ერთად დაკიდება, როგორც ბალასტი. და თქვენ უნდა მოიცილოთ ისინი. ამისათვის ჩვენ გვჭირდება ველი idx_scan. ჩვენ უბრალოდ ვუყურებთ ინდექსის სკანირების რაოდენობას. თუ ინდექსებს აქვთ ნულოვანი სკანირება სტატისტიკის შენახვის შედარებით ხანგრძლივი პერიოდის განმავლობაში (მინიმუმ 2-3 კვირა), მაშინ, სავარაუდოდ, ეს ცუდი ინდექსებია, ჩვენ უნდა მოვიშოროთ ისინი.

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

ორი ბმული:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

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

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

კიდევ რა უნდა იყოს შეჯამებული ინდექსებით?

• გამოუყენებელი ინდექსები ცუდია.

• ისინი ადგილს იკავებენ.

• განახლების ოპერაციების შენელება.

• დამატებითი სამუშაო ვაკუუმისთვის.

თუ გამოუყენებელ ინდექსებს წავშლით, მხოლოდ მონაცემთა ბაზას გავაუმჯობესებთ.

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

რისი აღება შეგვიძლია იქიდან?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

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

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

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

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

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

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

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

ცუდი ტრანზაქციები არის უმოქმედო ტრანზაქციაში და უმოქმედო ტრანზაქციის (შეწყვეტილი) ტრანზაქციებში.

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

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

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

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

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

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

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

გაფრთხილება: _დაწყებული Postgres 9.6-დან, ველი waiting ამოღებულია და ჩანაცვლებულია კიდევ ორი ​​ინფორმაციული ველით wait_event_type и wait_event._

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

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

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

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

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

და მეორე ბმული არის სტატია საკეტების შესახებ. წასაკითხად სასარგებლოა, ძალიან საინტერესოა.

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

ასე შეგვიძლია გავარკვიოთ ვინ ვის ჩაკეტა, ვინ ვის უჭირავს და ამას შემდგომ გავუმკლავდეთ.

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

Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

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

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

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

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

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

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

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

რა გვაქვს კულისებში? ჯერ კიდევ არის რამდენიმე განცხადება, რომელიც არ განვიხილე, რადგან დრო შეზღუდულია.

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

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

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

შემდეგი მოდული არის pg_stat_kcache. ის ასევე იყენებს სისტემურ ზარს getrusage(). და ის ასრულებს მას მოთხოვნის შესრულებამდე და შემდეგ. და მიღებულ სტატისტიკაში, ის საშუალებას გვაძლევს გამოვთვალოთ რამდენი დახარჯა ჩვენი მოთხოვნა დისკზე I/O, ანუ ოპერაციები ფაილურ სისტემასთან და უყურებს პროცესორის გამოყენებას. თუმცა, მოდული ახალგაზრდაა (ხე-ხე) და მისი მუშაობისთვის საჭიროა PostgreSQL 9.4 და pg_stat_statements, რაც ადრე ვახსენე.

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

• სტატისტიკის გამოყენება მარტივია, ეს არის მარტივი SQL. თქვენ შეაგროვეთ მოთხოვნა, შეადგინეთ, გაგზავნეთ, შეხედეთ.

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

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

ლიტერატურა

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

ავტორი დაწერე მეტი
https://dataegret.com/news-blog (ინგლ)

სტატისტიკის შემგროვებელი
https://www.postgresql.org/docs/current/monitoring-stats.html

სისტემის ადმინისტრირების ფუნქციები
https://www.postgresql.org/docs/current/functions-admin.html

მოდულების წვლილი
https://www.postgresql.org/docs/current/pgstatstatements.html
https://www.postgresql.org/docs/current/pgstattuple.html
https://www.postgresql.org/docs/current/pgbuffercache.html
https://github.com/klando/pgfincore
https://github.com/dalibo/pg_stat_kcache

SQL კომუნალური და sql კოდის მაგალითები
https://github.com/dataegret/pg-utils

დიდი მადლობა ყურადღებისთვის!

წყარო: www.habr.com