ნელი PostgreSQL მოთხოვნების პოვნა pg_stat_statements-ით

pg_stat_statements PostgreSQL-ის გაფართოებაა (extension), რომელიც შენი ბაზის მიერ შესრულებული თითოეული განსხვავებული მოთხოვნისთვის ინახავს ერთ სტრიქონს მიმდინარე ჯამებით: რამდენჯერ შესრულდა, ჯამში რა დრო დახარჯა, რამდენი სტრიქონი დააბრუნა და რამდენ buffer-ს შეეხო. ამ ხედს ალაგებ ჯამური დროით, იღებ პირველ ხუთს და გიგროვდება მოთხოვნების სია, რომლებსაც ყურადღება ღირს. ეს წარმადობაზე მუშაობისთვის ყველაზე სასარგებლო ხედია, რადგან პასუხობს კითხვას, რომელსაც ვარაუდი ვერ პასუხობს: რომელი მოთხოვნები უჯდება ამ სერვერს ყველაზე ძვირად. მის ჩასართავად საჭიროა სერვერის გაშვებისას ჩატვირთული ბიბლიოთეკა, ერთი CREATE EXTENSION და დაახლოებით ერთი მეგაბაიტი shared memory. ეს სახელმძღვანელო მოიცავს დაყენებას, ყველა სვეტს, რომელიც წასაკითხად ღირს, გასაშვებ მოთხოვნებს და იმას, თუ როგორ გადასცე ნაპოვნი EXPLAIN-ს.

რას ინახავს pg_stat_statements და რას არა#

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

SELECT * FROM orders WHERE customer_id = 42;SELECT * FROM orders WHERE customer_id = 9001;

ორივე ერთ ჩანაწერად იქცევა და ინახება როგორც SELECT * FROM orders WHERE customer_id = $1. ამას მოთხოვნის ნორმალიზება ჰქვია და სწორედ ამიტომაა ხედი სასარგებლო: აპლიკაციის ათასობით ვარიაცია იკეცება იმ რამდენიმე მოთხოვნის ფორმად, რომელიც შენს კოდს ნამდვილად აქვს.

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

სანამ მასზე დაეყრდნობი, სამი შეზღუდვა უნდა იცოდე:

• პარამეტრებს არ ინახავს. ხედავ $1-ს და არასოდეს იმ მნიშვნელობას, რომელმაც შეანელა. კონკრეტული მნიშვნელობისთვის გამოიყენე ლოგი (log_min_duration_statement) ან auto_explain.
• ითვლის შესრულების დროს და არა აპლიკაციაში ლოდინის დროს. მოთხოვნა, რომელიც 3 ms სრულდება, მაგრამ connection pool-ის რიგში 400 ms ელოდება, აქ სწრაფად გამოიყურება. ამ მხარისთვის იხილე connection pool-ები და ლიმიტები.
• ჩანაწერების შეზღუდულ რაოდენობას ინახავს. ნაგულისხმევია 5,000 განსხვავებული ბრძანება; როცა ცხრილი სავსეა, ყველაზე იშვიათად გამოყენებული ჩანაწერები იშლება. პატარა აპლიკაციების უმეტესობა მას არასოდეს აღწევს, მაგრამ აპლიკაცია, რომელიც SQL-ს ცვალებადი ფორმით აგებს, შეიძლება აღწევდეს.

შეამოწმე, უკვე დაყენებულია თუ არა#

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

SELECT name, default_version, installed_versionFROM pg_available_extensionsWHERE name = 'pg_stat_statements';SELECT extname, extversion FROM pg_extension;SHOW shared_preload_libraries;

სამივე შედეგი ერთად წაიკითხე:

ბოლო სტრიქონი ის არის, რომელიც ხალხს უკვირს. CREATE EXTENSION შეიძლება წარმატებით დასრულდეს ბიბლიოთეკის ჩატვირთვის გარეშე, და მერე ხედიდან არჩევა ვერ ხერხდება შეტყობინებით pg_stat_statements must be loaded via "shared_preload_libraries". გაფართოება თითო ბაზაზეა, მაგრამ როცა აგროვებს, ნებისმიერ ბაზაში ხედი აჩვენებს ინსტანციის ყველა ბაზის ბრძანებებს, რომელთა გასარჩევად dbid სვეტია.

იმას, შეგიძლია თუ არა shared_preload_libraries-ის შეცვლა, მთლიანად განსაზღვრავს ის, სად მუშაობს შენი ბაზა. შენ მიერ ადმინისტრირებულ სერვერზე შეგიძლია. ჰოსტინგის ბაზაზე შეიძლება უკვე დაყენებული იყოს, შეიძლება მხოლოდ პროვაიდერს შეეძლოს მისი შეცვლა, ან საერთოდ ხელმისაწვდომი არ იყოს. არსად ვაცხადებთ, რომ RE:NODE-ის PostgreSQL გეგმები ამას იძლევა, ამიტომ უსაფრთხო გზა ზემოთ მოცემული შემოწმებაა: თუ გაფართოება არ არის, პანელიდან გახსენი ticket და ჰკითხე მხარდაჭერას, შეიძლება თუ არა მისი ჩართვა შენს ინსტანციაზე. არც ერთი პასუხი წინასწარ არ ივარაუდო. დაყენების განყოფილების შემდეგ ყველაფერი მუშაობს ნებისმიერ ჰოსტზე, სადაც ხედი არსებობს.

ჩართვა სერვერზე, რომელსაც თავად აკონტროლებ#

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

ჯერ დაამატე postgresql.conf-ში:

shared_preload_libraries = 'pg_stat_statements'pg_stat_statements.max = 5000pg_stat_statements.track = topcompute_query_id = autotrack_io_timing = on

თუ ხაზზე უკვე სხვა ბიბლიოთეკებია ჩამოთვლილი, დატოვე და მძიმეებით გამოყავი. არსებული shared_preload_libraries-ის გადაწერა კლასიკური გზაა, რომ გამორთო რაღაც სხვა, რაც გჭირდებოდა. ფაილის ხელით რედაქტირების გარეშე იგივეს აკეთებს ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';, რომელიც postgresql.auto.conf-ში წერს.

მეორე, გადატვირთე სერვერი. ამ პარამეტრისთვის reload (SELECT pg_reload_conf();) არ კმარა. დაგეგმე წყნარ მომენტში; გადატვირთვა ყველა კავშირს წყვეტს, ხოლო PostgreSQL-ის გაწყობა პატარა სერვერებისთვის ჩამოთვლის, სხვა რომელ პარამეტრებს აქვს იგივე მოთხოვნა, რომ ერთ გადატვირთვაში მოაქციო.

მესამე, შექმენი გაფართოება იმ ბაზაში, საიდანაც მოთხოვნებს გაუშვებ:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

ამას სჭირდება როლი, რომელსაც გაფართოებების შექმნა შეუძლია; თვითმართულ სერვერზე ეს superuser-ია. დარწმუნდი, რომ მუშაობს:

SELECT count(*) FROM pg_stat_statements;

რიცხვი, თუნდაც მცირე, ნიშნავს, რომ აგროვებს. თუ „must be loaded" შეცდომას მიიღებ, გადატვირთვამ პარამეტრი ვერ აიღო, ამიტომ გაუშვი SHOW shared_preload_libraries; და ნახე, სერვერმა რეალურად რა ჩატვირთა.

მთავარი ვერსიის განახლების შემდეგ გაუშვი ALTER EXTENSION pg_stat_statements UPDATE; თითოეულ ბაზაში, სადაც ის არის. ხედის სვეტები ვერსიებს შორის იცვლება და ძველი განსაზღვრება ძველ სვეტებს ინარჩუნებს, სანამ ამას არ გააკეთებ.

პარამეტრები, რომლებსაც მნიშვნელობა აქვს#

გაფართოებას ოთხი პარამეტრი აკონტროლებს. მხოლოდ max-ს სჭირდება გადატვირთვა.

top თითქმის ყველასთვის სწორია. all-ზე გადადი მხოლოდ მაშინ, როცა ეჭვობ ნელ stored function-ზე და გინდა ნახო, მის შიგნით რომელი ბრძანებაა პასუხისმგებელი; ფასი მეტი ჩანაწერი და მეტი ხმაურია. pg_stat_statements.track_planning ასევე არსებობს და ნაგულისხმევად off-ია, რადგან დაგეგმვის დროის გაზომვა დატვირთულ სერვერებზე დამატებით დატვირთვას ქმნის. დროებით ჩართე, თუ ეჭვობ, რომ პრობლემა დაგეგმვის დროშია, მაგალითად, ძალიან ფართო დაყოფილი ცხრილების შემთხვევაში.

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

• `compute_query_id` (PostgreSQL 14 და უფრო ახალი) ნაგულისხმევად auto-ა, რომელიც მოთხოვნის იდენტიფიკატორებს რთავს, როცა pg_stat_statements ჩატვირთულია. დატოვე ისე. იგივე იდენტიფიკატორი ჩნდება pg_stat_activity.query_id-შიც და EXPLAIN (VERBOSE)-შიც, რაც ცოცხალ მოთხოვნას მის ისტორიასთან დაკავშირების საშუალებას გაძლევს.
• `track_io_timing` ნაგულისხმევად off-ია. გამორთულობისას ბლოკების კითხვისა და ჩაწერის დროის სვეტები ნულზე რჩება და ვერ გაიგებ, მოთხოვნა დისკის გამო ნელია თუ არა. თანამედროვე აპარატურაზე დამატებითი დატვირთვა მცირეა; pg_test_timing მას შენს მანქანაზე ზომავს. თუ შეგიძლია, ჩართე.

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

სვეტები და ვერსიების ხაფანგი#

გაუშვი \d pg_stat_statements psql-ში, რომ შენი ვერსიის ზუსტი სია ნახო. ყოველდღიურად გამოსაყენებელი სვეტები:

და I/O სვეტები, რომლებიც გეუბნება, დრო მეხსიერებაში წავიდა თუ დისკზე:

რამდენიმე სვეტს განმარტება სჭირდება. shared_blks_read აუცილებლად დისკი არაა: ეს ბლოკებია, რომლებიც PostgreSQL-ის საკუთარ buffer-ებში არ იყო, და ოპერაციული სისტემის cache-მა შეიძლება მაინც მოემსახურა. მაღალი რიცხვი მაინც ნიშნავს, რომ მოთხოვნა მეტ მონაცემს ეხება, ვიდრე shared_buffers-ში ეტევა, და ეს სიგნალია, რომ გეგმას დააკვირდე. temp_blks_written ნულზე მეტი ნიშნავს, რომ დალაგებას ან hash-ს work_mem არ ეყო და დისკზე ჩაიწერა, რაც სხვა გამოსწორებაა, ვიდრე ინდექსის ნაკლებობა. PostgreSQL 17-სა და უფრო ახალზე I/O დროის სვეტებს shared_ პრეფიქსი დაემატა, მაგალითად shared_blk_read_time; იგივე \d შემოწმება გეტყვის, რომელი გაქვს.

სამი top-N მოთხოვნა#

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

ჯამური დროით: სად ხარჯავს სერვერი თავის დღეს

SELECT queryid,       calls,       round(total_exec_time::numeric, 1)                      AS total_ms,       round(mean_exec_time::numeric, 2)                       AS mean_ms,       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,       rows,       left(query, 80)                                         AS queryFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 10;

ეს არის მოთხოვნა, რომელიც პირველი უნდა გაუშვა და ყველაზე ხშირად. pct სვეტი აჩვენებს თითოეული ბრძანების წილს შესრულების მთელ დროში; ტიპურ აპლიკაციაში სამი-ოთხი ჩანაწერი უმეტესობას ჯამავს. მოთხოვნა, რომელიც 4 ms სრულდება, მაგრამ დღეში ორი მილიონჯერ გადის, ჯამში 8,000 წამით, უფრო ძვირი ჯდება, ვიდრე ცხრაწამიანი რეპორტი, რომელიც ღამეში ერთხელ სრულდება. ის ხშირად გამოსასწორებლადაც უფრო ადვილია, რადგან, როგორც წესი, ერთი ინდექსი ან ერთით ნაკლები round trip სჭირდება.

საშუალო დროით: ინდივიდუალურად ნელი მოთხოვნები

SELECT queryid,       calls,       round(mean_exec_time::numeric, 2)   AS mean_ms,       round(stddev_exec_time::numeric, 2) AS stddev_ms,       left(query, 80)                     AS queryFROM pg_stat_statementsWHERE calls >= 20ORDER BY mean_exec_time DESCLIMIT 10;

ფილტრს calls >= 20 მნიშვნელობა აქვს. მის გარეშე ერთჯერადი CREATE INDEX ან ხელით გაშვებული VACUUM სიას თავში მოექცევა და არაფერს გასწავლის. ეს დალაგება პოულობს მოთხოვნებს, რომლებსაც მომხმარებელი გრძნობს: რეპორტის გვერდი, ექსპორტი, ძიება წინა wildcard-ით. საშუალოს გვერდით შეხედე stddev_ms-საც. 50 ms საშუალო 400 ms სტანდარტული გადახრით ნელი მოთხოვნა არ არის, ეს მოთხოვნაა ორი ქცევით, ხშირად იმიტომ, რომ planner პარამეტრის მიხედვით სხვადასხვა გეგმას ირჩევს. ეს auto_explain-ის საქმეა, რომელსაც ქვემოთ განვიხილავთ.

გამოძახებებით: მოლაპარაკე მოთხოვნები

SELECT queryid,       calls,       round(mean_exec_time::numeric, 3) AS mean_ms,       rows / calls                      AS rows_per_call,       left(query, 80)                   AS queryFROM pg_stat_statementsORDER BY calls DESCLIMIT 10;

მოთხოვნა მილიონობით გამოძახებითა და პაწაწინა საშუალოთი N+1 ნიმუშის ნიშანია: აპლიკაცია იღებს სიას, შემდეგ კი თითო ელემენტზე ერთ მოთხოვნას უშვებს. ამას ინდექსი ვერ გამოასწორებს; join ან ჯგუფური WHERE id = ANY($1) გამოასწორებს. rows_per_call სვეტი გეხმარება: ბრძანება, რომელიც ზუსტად ერთ სტრიქონს აბრუნებს და მილიონჯერ გამოიძახება, მონაცემებს სათითაოდ ეძებს.

cache-ის დაცდენებით და დროებითი ფაილებით

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

SELECT queryid,       shared_blks_hit,       shared_blks_read,       round(100.0 * shared_blks_hit             / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,       left(query, 70) AS queryFROM pg_stat_statementsORDER BY shared_blks_read DESCLIMIT 10;

მეორე, ORDER BY temp_blks_written DESC, ჩამოთვლის მოთხოვნებს, რომლებიც დალაგებას დისკზე ანაწილებს. არც ერთი მათგანი თავისთავად სასწრაფო არაა. ოთხმოცდაათიანი პროცენტებში მოხვედრის წილი ნორმალურია; ხშირად გამოძახებული ბრძანება 70 პროცენტზე შენს cache-ს ანგრევს და იმ მონაცემებს განდევნის, რომლებიც სხვა მოთხოვნებს სჭირდება.

ფანჯრის გაზომვა და განულება#

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

SELECT pg_stat_statements_reset();

PostgreSQL 12-სა და უფრო ახალზე ფუნქცია იღებს userid, dbid და queryid არგუმენტებსაც, ამიტომ შეგიძლია გამოსწორების შემდეგ ერთი ბრძანება გაასუფთავო და ნახო, როგორ იქცევა. ნაგულისხმევად მისი გამოძახება მხოლოდ superuser-ს შეუძლია და ის შეუქცევადია, ამიტომ ჯერ ჩაინიშნე, რაც გჭირდება. ისტორიის დასაგროვებლად დამატებითი ხელსაწყოების გარეშე იაფი გზა snapshot ცხრილია:

CREATE TABLE stmt_snapshot ASSELECT now() AS taken_at, queryid, calls, total_exec_time, rows, queryFROM pg_stat_statements;-- later: INSERT INTO stmt_snapshot SELECT now(), queryid, calls, ... FROM pg_stat_statements;

ერთი snapshot-ის calls და total_exec_time გამოაკელი შემდეგს და მათ შორის ღირებულებას მიიღებ. გაუშვი განრიგით, მაგალითად cron-ით ან pg_cron-ით, ყოველ საათში და ერთი კვირის ტრენდის მონაცემები გექნება. ცხრილი პატარა გაამყარე ძველი სტრიქონების წაშლით, ხოლო მოთხოვნის ტექსტი საკმარისია ერთხელ შეინახო, თუ queryid-ის მიხედვით ცალკე ცხრილში გაქვს.

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

queryid-დან გამოსწორებამდე: EXPLAIN (ANALYZE, BUFFERS)#

ხედი გეუბნება, რომელ მოთხოვნას დააკვირდე. იმას ვერ გეტყვის, რატომაა ნელი. დააკოპირე ნორმალიზებული ტექსტი, თითოეული $1-ის ადგილას ჩასვი რეალისტური მნიშვნელობა და გაუშვი EXPLAIN-ით, I/O-ს გამომჩენი პარამეტრებით:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT * FROM ordersWHERE customer_id = 42 AND status = 'open'ORDER BY created_at DESCLIMIT 20;

ANALYZE ბრძანებას ნამდვილად ასრულებს, ამიტომ ყველაფერი, რაც რამეს წერს, გაახვიე ტრანზაქციაში, რომელსაც უკან დააბრუნებ: BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE ...; ROLLBACK;. BUFFERS ამატებს ხაზებს Buffers: shared hit=... read=..., რომლებიც შეგიძლია შეადარო ხედის სვეტებს shared_blks_hit და shared_blks_read. თუ ხედი ამბობს, რომ ეს მოთხოვნა გამოძახებაზე 40,000 ბლოკს კითხულობს, გეგმაში უნდა იყოს კვანძი, რომელიც ამას ხსნის, ჩვეულებრივ, მიმდევრობითი სკანირება ან დალაგება. სრული ლექსიკონი არის სტატიაში EXPLAIN ANALYZE: Postgres-ის მოთხოვნის გეგმების კითხვა; ეს პოსტი მხოლოდ სწორ მოთხოვნამდე მიგიყვანს.

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

• მიმდევრობითი სკანირება დიდ ცხრილზე შერჩევითი ფილტრით ინდექსის ნაკლებობაზე მიანიშნებს; PostgreSQL-ის ინდექსები: ტიპები, თანმიმდევრობა და ფასი მოიცავს სვეტების რიგს და ნაწილობრივ ინდექსებს.
• ძალიან მაღალი Rows Removed by Filter ნიშნავს, რომ ინდექსი არსებობს, მაგრამ ფილტრს არ ემთხვევა.
• შეფასება, რომელიც რეალური სტრიქონების რაოდენობისგან შორსაა, ნიშნავს მოძველებულ სტატისტიკას: გაუშვი ANALYZE tablename;.
• ცხრილი, რომელიც საჭირო ზომაზე მრავალჯერ გაიზარდა და vacuum ჩამორჩება, ყველა სკანირებას ანელებს; იხილე Postgres-ის vacuum, bloat და autovacuum.

ცვლილების შემდეგ გაანულე ეს ერთი queryid (ან ჯერ snapshot აიღე), მიეცი ტრაფიკს გაიაროს და შეადარე mean_exec_time. გამოსწორება, რომელიც შემდეგ pg_stat_statements-ში არ გაგიზომავს, ვარაუდია.

ცოცხალი მოთხოვნები: pg_stat_activity#

pg_stat_statements ისტორიაა. იმისთვის, რაც ამ წამს ხდება, მაგალითად, გაყინული გვერდი ან კავშირების რაოდენობის ნახტომი, გამოიყენე pg_stat_activity:

SELECT pid,       usename,       state,       wait_event_type,       wait_event,       now() - query_start AS runtime,       left(query, 80)     AS queryFROM pg_stat_activityWHERE state <> 'idle'  AND pid <> pg_backend_pid()ORDER BY runtime DESC;

პირველი, რაც უნდა წაიკითხო, state სვეტია. active ახლა სრულდება; idle in transaction ნიშნავს, რომ კლიენტმა ტრანზაქცია გახსნა და არაფერს აკეთებს, რაც lock-ებს იჭერს და vacuum-ს წმენდას უშლის. idle in transaction სესიების გრძელი სია აპლიკაციის ხარვეზია და არა ბაზისა. wait_event_type მნიშვნელობით Lock ნიშნავს, რომ მოთხოვნა სხვის უკან რიგშია, ხოლო pg_blocking_pids(pid) გიბრუნებს, ვინ იკავებს მას.

გაქცეული მოთხოვნის შესაჩერებლად SELECT pg_cancel_backend(pid); აუქმებს მიმდინარე მოთხოვნას და კავშირს ტოვებს; pg_terminate_backend(pid) მთელ სესიას კლავს. ჯერ პირველი სცადე. PostgreSQL 14-დან pg_stat_activity.query_id ემთხვევა pg_stat_statements.queryid-ს, ამიტომ შეგიძლია ნახო, როგორ იქცეოდა ახლა გაშვებული მოთხოვნა ისტორიულად:

SELECT a.pid, now() - a.query_start AS runtime, s.calls, s.mean_exec_timeFROM pg_stat_activity aJOIN pg_stat_statements s ON s.queryid = a.query_idWHERE a.state = 'active';

გაითვალისწინე, რომ pg_stat_activity.query იკვეთება track_activity_query_size-ზე, ნაგულისხმევად 1024 ბაიტზე. გენერირებული მოთხოვნა უზარმაზარი IN სიით იქ ჩამოიჭრება, ხოლო პარამეტრის გასაზრდელად გადატვირთვაა საჭირო.

დამატებითი ხელსაწყოები: log_min_duration_statement და auto_explain#

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

`log_min_duration_statement` სერვერის ლოგში წერს ყველა ბრძანებას, რომელიც ზღვარზე ნელია, მათ შორის ფაქტობრივ პარამეტრულ მნიშვნელობებს:

ALTER SYSTEM SET log_min_duration_statement = '500ms';SELECT pg_reload_conf();

ამას reload სჭირდება და არა გადატვირთვა. არაფერი ჯდება, როცა არაფერია ნელი, და პასუხობს კითხვას, რომელსაც ხედი ვერ პასუხობს: ზუსტად რომელმა მნიშვნელობამ შეანელა და რომელ დროს. 0 გამოიყენე მხოლოდ ხანმოკლე დროით, რადგან ყველაფერს ჩაწერს. თუ ლოგი ჰოსტზეა, რომლის წაკითხვაც მხოლოდ კონსოლიდან შეგიძლია, ზომიერი ზღვარი, როგორიცაა 500 ms ან 1 s, წაკითხვადს ინარჩუნებს.

`auto_explain` ერთი ნაბიჯით წინ მიდის და ნელი ბრძანებებისთვის თავად გეგმას წერს ლოგში, რაც იჭერს გეგმას, რომელიც მხოლოდ ზოგჯერ ფუჭდება. ის იტვირთება ისევე, როგორც pg_stat_statements, shared_preload_libraries-ში (ან session_preload_libraries-ში), და ასე კონფიგურირდება:

shared_preload_libraries = 'pg_stat_statements,auto_explain'auto_explain.log_min_duration = '1s'auto_explain.log_analyze = offauto_explain.log_buffers = offauto_explain.log_nested_statements = off

log_analyze გამორთული დატოვე, თუ ფაქტობრივი დროები არ გჭირდება: ჩართვა ყველა ბრძანებას, სწრაფსაც, აინსტრუმენტებს და დატვირთულ ბაზას შესამჩნევად ანელებს. superuser-საც შეუძლია გაუშვას LOAD 'auto_explain'; და ერთი სესიისთვის დააყენოს პარამეტრები, რომ გადატვირთვის გარეშე სცადოს. გადაწყვეტის წესი: pg_stat_statements გეუბნება, რას დააკვირდე, log_min_duration_statement - როდის და რა მნიშვნელობებით, auto_explain - რა გეგმა აირჩია. MySQL-ის მომხმარებლები შუალედურს გაიცნობენ MySQL-ის ნელი მოთხოვნების ლოგიდან.

ჩვევად ამის სიჯანსაღის შესანარჩუნებლად: კვირაში ერთხელ აირჩიე ჯამური დროით პირველი ხუთი და საშუალო დროით პირველი ხუთი calls >= 20-ით. თუ იგივე სახელები თვის შემდეგაც ჩნდება, გამოსწორება არ მოხვდა. მონიტორინგი, რომელიც რამეს გეუბნება ეხება იმას, თუ როგორ გახადო ამგვარი რიცხვები ისეთად, რასაც ნამდვილად უყურებ.

პრივილეგიები: ვის რა უნდა ხედავდეს#

ნაგულისხმევად როლი სრულ ტექსტსა და სტატისტიკას მხოლოდ საკუთარი ბრძანებებისთვის ხედავს. სხვა მომხმარებლების სტრიქონებისთვის pg_stat_statements აჩვენებს სტატისტიკას, მაგრამ ტექსტს ცვლის <insufficient privilege>-ით და queryid სვეტს მალავს. superuser-ები ყველაფერს ხედავენ. ყველა დანარჩენისთვის სუფთა გამოსავალია ჩაშენებული როლი pg_read_all_stats, რომელიც PostgreSQL 10-დან არსებობს:

GRANT pg_read_all_stats TO monitoring_user;

ის იძლევა სტატისტიკის ხედებზე წაკითხვის წვდომას, მათ შორის pg_stat_statements-ზე და სხვა სესიების მოთხოვნებზე pg_stat_activity-ში, მონაცემებზე წვდომის გარეშე, ამიტომ ის სწორი უფლებაა dashboard-ისთვის ან ინჟინრისთვის, რომელსაც ცხრილების შიგთავსი არ უნდა წაიკითხოს. განულება ცალკეა: pg_stat_statements_reset() შესრულებადია მხოლოდ superuser-ისთვის, თუ მასზე GRANT EXECUTE არ გასცემ, და CREATE EXTENSION-საც საკმარისი უფლებები სჭირდება. PostgreSQL-ის როლები და უფლებები განმარტავს, როგორ ააგო ასეთი როლი სწორად.

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

გამოყენება ჰოსტინგის ბაზაზე#

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

RE:NODE-ის PostgreSQL ხაზისთვის, რომელიც თვეში $6-დან იწყება, დოკუმენტირებული ფაქტებია, რომ credentials თითო სერვერზე გენერირდება და უკავშირდები გეგმის ჰოსტსა და პორტზე. იქ არსად წერია, რედაქტირებადია თუ არა shared_preload_libraries ან რომელი გაფართოებებია ხელმისაწვდომი. ამიტომ პრაქტიკული თანმიმდევრობა ასეთია:

1. დაუკავშირდი psql-ით და გაუშვი წინა განყოფილების სამი შემოწმება.
2. თუ pg_stat_statements დაყენებულია და ხედი პასუხობს, გამოიყენე აღწერილი წესით.
3. თუ არა, ჰკითხე მხარდაჭერას პანელიდან ticket-ით, დაასახელე გაფართოება და რისთვის გჭირდება. შეიძლება თუ არა მისი ჩართვა შენს ინსტანციაზე, მათ სათქმელია.
4. ამასობაში pg_stat_activity და დაგროვილი ხედები, როგორიცაა pg_stat_user_tables, მუშაობს ყოველგვარი დაყენების გარეშე, ხოლო EXPLAIN (ANALYZE, BUFFERS)-ს საერთოდ არაფერი სჭირდება.

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

FAQ#

ანელებს თუ არა pg_stat_statements ბაზას?

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

რატომ მაჩვენებს მოთხოვნის ტექსტი $1-ს მნიშვნელობის ნაცვლად?

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

რატომ არის ხედი ცარიელი ან აბრუნებს შეცდომას CREATE EXTENSION-ის შემდეგ?

ან ბიბლიოთეკა shared_preload_libraries-ში არ არის, ან დამატების შემდეგ სერვერი არ გადაგიტვირთავს. SHOW shared_preload_libraries; აჩვენებს, ახლა რა არის ჩატვირთული. შეტყობინება pg_stat_statements must be loaded via "shared_preload_libraries" ზუსტად ამას ნიშნავს. ცარიელი ხედი ჩატვირთულ გაფართოებაზე უბრალოდ იმას ნიშნავს, რომ ბოლო განულების შემდეგ არაფერი გაშვებულა.

ჯამური დროით დავალაგო თუ საშუალო დროით?

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

მუშაობს თუ არა ის MySQL-ზე, MongoDB-სა და SQL Server-ზე?

არა, ის მხოლოდ PostgreSQL-ისთვისაა. MySQL-ს აქვს ნელი მოთხოვნების ლოგი და performance_schema-ს digest ცხრილები; SQL Server-ს აქვს Query Store, რომელიც განხილულია SQL Server-ის Query Store და ნელი მოთხოვნები-ში. იდეა ყველგან იგივეა: დააჯგუფე ნორმალიზებული ბრძანებით, დაალაგე ჯამური ღირებულებით, გაასწორე სიის თავი.

როგორ გავიგო, რომელი queryid რომელ ცხრილს ეკუთვნის?

მოძებნე ტექსტში: WHERE query ILIKE '%orders%'. queryid დამუშავებული ბრძანების ჰეშია და ცხრილის შესახებ საკუთარ ინფორმაციას არ ატარებს. პერიოდში ერთი ცხრილის ყველა ბრძანებისთვის გაფილტრე ტექსტით, შეაჯამე total_exec_time და შეადარე ცხრილის სკანირების მთვლელებს pg_stat_user_tables-ში.