Πώς να βρείτε και να σκοτώσετε μακροχρόνια και αποκλεισμένα ερωτήματα στο Postgres

1
Πώς να βρείτε και να σκοτώσετε μακροχρόνια και αποκλεισμένα ερωτήματα στο Postgres

Στο EdgeTierχρησιμοποιούμε PostgreSQL βάσεις δεδομένων για το σύνολο σχεδόν της αποθήκευσης σχεσιακών δεδομένων μας. PostgreSQL ανοιχτού κώδικα, γρήγορο, έχει ενδιαφέρον δυνατότητες αναζήτησης κειμένουέχει ωραία Λειτουργία ανάλυσης JSONλειτουργεί καλά Amazon AWS. Ωστόσο, με πολύπλοκες εφαρμογές όπως π.χ WatchTower (ένα εργαλείο παρακολούθησης του κέντρου επικοινωνίας)μερικές φορές βρισκόμαστε σε ένα κυνήγι βελτιστοποίησης SQL – εργαζόμαστε για να επιταχύνουμε μια απόκριση API, ένα αποτέλεσμα ερωτήματος ή μια διαδικασία εφαρμογής.

PostgreSQL είναι μια δωρεάν και ανοιχτού κώδικα απόδοσης σχεσιακή μηχανή βάσης δεδομένων. PostgreSQL, σε έκδοση 14 από τη στιγμή της σύνταξης, βρίσκεται σε εξέλιξη για περισσότερα από 30 χρόνια, με πρώτη κυκλοφορία το 1996.

Για ενεργά ερωτήματα, η PostgreSQL έχει ένα «Συλλέκτες στατιστικών στοιχείων» υποσύστημα που συγκεντρώνει δεδομένα σε δραστηριότητα πίνακα, διακομιστή, ερωτήματος, ευρετηρίου και σύνδεσης. Η βάση δεδομένων εκθέτει πληροφορίες μέσω ενός αριθμού προκαθορισμένων προβολών «pg_stat» και ορισμένων στατιστικές συναρτήσεις χαμηλότερου επιπέδου για ισχυρούς χρήστες.

Επιπλέον, υπάρχουν αρκετά λειτουργίες πληροφοριών συστήματος που μπορεί να εξάγει πληροφορίες συνεδρίας και συστήματος για διαδικασίες και ερωτήματα που βρίσκονται σε εξέλιξη.

Οι βασικοί πίνακες και οι συναρτήσεις που βρήκα χρήσιμες είναι:

  • pg_stat_activity: Ένας πίνακας με μία καταχώρηση ανά διεργασία διακομιστή, που δείχνει λεπτομέρειες του ερωτήματος που εκτελείται για καθεμία.
  • pg_locks: Πληροφορίες σχετικά με τις τρέχουσες κλειδαριές που διατηρούνται στη βάση δεδομένων από ανοιχτές συναλλαγές, με μία σειρά ανά αντικείμενο με δυνατότητα κλειδώματος.
  • pg_blocking_pids(): Μια συνάρτηση που μπορεί να βρει τα αναγνωριστικά διεργασίας (PID) των περιόδων σύνδεσης που εμποδίζουν τη διαδικασία διακομιστή PostgreSQL ενός παρεχόμενου PID.
  • pg_cancel_backend(): Λειτουργία που ακυρώνει το τρέχον ερώτημα στέλνοντας ένα SIGINT σε ένα αναγνωριστικό διεργασίας.
  • pg_terminate_backend(): Τερματίστε εντελώς μια διαδικασία υποστήριξης (το ερώτημα και συνήθως η σύνδεση) στη βάση δεδομένων (χρησιμοποιεί το SIGTERM αντί για το SIGINT).

Καταχωρίστε τα μακροχρόνια και αργά ερωτήματα στο PostgreSQL

Τα ερωτήματα στις βάσεις δεδομένων Postgresql μπορεί να είναι αργά ή να σταματήσουν ανάλογα με τον τρόπο εγγραφής τους, τον τρόπο χρήσης ευρετηρίων και τον αριθμό κλειδαριών που διατηρούνται αυτήν τη στιγμή στη βάση δεδομένων σας. Η προβολή Pg_stat_activity είναι ένα βασικό εργαλείο για τη διάγνωση μακρών και αργών ερωτημάτων στον διακομιστή της βάσης δεδομένων σας.
Τα ερωτήματα στο PostgreSQL μπορούν να εκτελούνται αργά εάν δεν είναι καλά γραμμένα ή εάν δεν εκμεταλλεύονται διάφορα ευρετήρια για την ένωση και την ομαδοποίηση στηλών. Η προβολή pg_stat_activity σάς επιτρέπει να βλέπετε όλα τα ερωτήματα που εκτελούνται και πόσο χρόνο διαρκούν στη βάση δεδομένων PostgreSQL. Φωτογραφία από Βέρι Ιβάνοβα επί Ξεβιδώστε

Τα κακώς γραμμένα ερωτήματα ή τα κακώς δομημένα δεδομένα μπορούν να προκαλέσουν πολύ μεγάλους χρόνους εκτέλεσης στη βάση δεδομένων σας. Συνήθως ανακαλύπτεται μέσω αργής απόκρισης ή εκτεταμένων αυξήσεων της CPU της βάσης δεδομένων pg_stat_activity Η προβολή μπορεί να σας βοηθήσει να μάθετε ποιο ερώτημα προκαλεί προβλήματα. Η προβολή pg_stat_activity περιέχει λεπτομέρειες για όλα τα ερωτήματα που εκτελούνται αυτήν τη στιγμή, συμπεριλαμβανομένων των στοιχείων χρήστη, σύνδεσης και χρονισμού.

Ενα απλό select * from pg_stat_activity θα παρέχει ένα στιγμιότυπο του τι συμβαίνει στη βάση δεδομένων PostgreSQL, με μία γραμμή ανά τρέχουσα συναλλαγή, και τις στήλες κλειδιών:

  • όνομα δεδομένων: Το όνομα της βάσης δεδομένων στο οποίο εκτελείται το ερώτημα.
  • pid: Το αναγνωριστικό διεργασίας του backend για το ερώτημα που εκτελείται. Το PID μπορεί να χρησιμοποιηθεί με την pg_terminate_backend() ή την pg_cancel_backend() ξεχωριστά.
  • είστε ευχαριστημένοι: όνομα χρήστη που εκτελείται/συνδέεται σε αυτό το backend
  • client_addr: Διεύθυνση IP της σύνδεσης σε αυτό το backend. Εάν αυτό είναι κενό/μηδενικό, η σειρά μπορεί να αναφέρεται σε εσωτερική σύνδεση ή εσωτερική διεργασία.
  • backend_start: Ώρα κατά την έναρξη της διαδικασίας υποστήριξης ή κατά τη σύνδεση του προγράμματος-πελάτη.
  • query_start: Ώρα έναρξης του ενεργού ερωτήματος.
  • κατάσταση: Η τρέχουσα κατάσταση του backend, με επιλογές:
    • ενεργό: εκτελείται αυτήν τη στιγμή το ερώτημα στη στήλη ερωτήματος.
    • idle: δεν εκτελείται τίποτα και περιμένει μια νέα εντολή.
    • αδράνεια στη συναλλαγή: το backend βρίσκεται σε μια συναλλαγή, αλλά επί του παρόντος δεν κάνει καμία εργασία. Αυτό είναι το ίδιο με το „αδράνεια στη συναλλαγή (σφάλμα)“, εκτός από το ότι μία από τις δηλώσεις έχει σφάλμα.
    • Κλήση συνάρτησης fastpath: Εκτέλεση α λειτουργία γρήγορης διαδρομής.
    • disabled: το track_activities έχει απενεργοποιηθεί για αυτό το backend.
  • αναμονή_γεγονός & τύπος_συμβάντος_αναμονής: Ο τύπος συμβάντος που περιμένει το backend. Αυτό μπορεί να είναι ένα εκτενής κατάλογος. Παρακολουθήστε για κλειδαριές σε αντικείμενα βάσης δεδομένων εδώ.
  • ερώτηση: Το κείμενο του πιο πρόσφατου ή ενεργού ερωτήματος backend. Σύμφωνα με την τεκμηρίωση, αυτό περικόπτεται στα 1024 byte, αλλάζει με „track_activity_query_size“ στη διαμόρφωση του διακομιστή.
  • backend_type: Υπάρχουν μερικοί διαφορετικοί τύποι, αλλά για τις περισσότερες συνδέσεις από εξωτερικούς πελάτες, αυτό θα είναι „υποστήριξη πελάτη“.

Βρείτε ερωτήματα που διαρκούν περισσότερο από 5 λεπτά

Μια χρήσιμη προσθήκη σε αυτό είναι η εμφάνιση όλων των ερωτημάτων που εκτελούνται για περισσότερο από πέντε λεπτά για σκοπούς εντοπισμού σφαλμάτων:

SELECT
  pid,
  user,
  pg_stat_activity.query_start,
  now() - pg_stat_activity.query_start AS query_time,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Καταργήστε τις μακροχρόνιες διαδικασίες ερωτημάτων PostgreSQL

Όπου ορισμένα ερωτήματα φαίνονται ότι δεν πρόκειται να ολοκληρωθούν, μπορείτε να χρησιμοποιήσετε το pid (αναγνωριστικό διεργασίας) από τις προβολές pg_stat_activity ή pg_locks για να τερματιστεί η εκτελούμενη διαδικασία.

  • pg_cancel_backend(pid) θα προσπαθήσει να σκοτώσει με χάρη μια εκτελούμενη διαδικασία ερωτήματος.
  • pg_terminate_backend(pid) θα σκοτώσει αμέσως τη διαδικασία ερωτήματος που εκτελείται, αλλά ενδέχεται να έχει παρενέργειες σε πρόσθετα ερωτήματα που εκτελούνται στον διακομιστή της βάσης δεδομένων σας. Η πλήρης σύνδεση ενδέχεται να μηδενιστεί κατά την εκτέλεση του pg_terminate_backend, επομένως ενδέχεται να επηρεαστούν άλλα ερωτήματα που εκτελούνται. Χρησιμοποιήστε ως έσχατη λύση.

Εύρεση αποκλεισμένων διαδικασιών και αποκλεισμός ερωτημάτων

ο pg_blocking_pids() λειτουργία είναι μια χρήσιμη συντόμευση για την εύρεση των συνδέσεων / συνεδριών βάσης δεδομένων που εμποδίζουν μια άλλη συνεδρία. Η συνάρτηση pg_blocking_pids() επιστρέφει an πίνακας postgreSQL των PID που μπλοκάρουν το καθορισμένο PID διαδικασίας διακομιστή που παρέχετε στο ερώτημα. Συνήθως, μια διεργασία διακομιστή μπλοκάρει μια άλλη εάν κρατά ένα κλείδωμα που απαιτείται για τη δεύτερη διαδικασία.

Μια χρήσιμη λειτουργία (από“Πώς να εντοπίσετε το ερώτημα που κρατά το κλείδωμα στο Postgres;” Απάντηση Stackoverflow) για να εμφανίσετε τις μπλοκαρισμένες διεργασίες στη βάση δεδομένων σας μαζί με το πραγματικό ερώτημα που τις αποκλείει είναι:

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

Προβολή κλειδαριών σε τραπέζια – pg_lock

Οι κλειδαριές βάσεων δεδομένων σε βάσεις δεδομένων Postgresql μπορούν να προβληθούν με την προβολή συστήματος pg_locks.
Η προβολή pg_lock παρέχει μία γραμμή ανά ενεργό κλείδωμα στις βάσεις δεδομένων PostgreSQL όπου μπορείτε να προβάλετε το αντικείμενο που είναι κλειδωμένο και τι κρατά ή περιμένει για αυτό το κλείδωμα. (Φωτογραφία από Τζον Σαλβίνο επί Ξεβιδώστε)

ο pg_lock Η προβολή παρέχει πληροφορίες σχετικά με τις κλειδαριές που διατηρούνται από ενεργές διεργασίες στη βάση δεδομένων, με μία μόνο καταχώρηση ανά αντικείμενο με δυνατότητα κλειδώματος (που μπορεί να είναι πίνακες, σελίδες, αναγνωριστικά συναλλαγών και άλλα αντικείμενα βάσης δεδομένων). Κλειδαριές αποκτώνται ως μέρος συναλλαγών και χρησιμοποιούνται συνήθως όπου είναι ότι τα δεδομένα σε ένα αντικείμενο πίνακα ή βάσης δεδομένων δεν αλλάζουν πριν ολοκληρωθεί η πλήρης συναλλαγή.

Ενα απλό select * from pg_locks θα σας παράσχει αμέσως (στήλες κλειδιά):

  • τύπος κλειδαριάς: Ο τύπος αντικειμένου που είναι κλειδωμένο – (βλ εδώ) σχέση, σελίδα, αντικείμενο, tranasctionid, userlock κ.λπ.
  • σχέση: OID (αναγνωριστικό αντικειμένου) της σχέσης που στοχεύει η κλειδαριά. Εγγραφείτε στο pg_class OID για να λάβετε τα ονόματα των πινάκων εδώ.
  • σελίδα: αριθμός σελίδας που στοχεύει το κλείδωμα εντός της σχέσης.
  • Ταυτότητα συναλλαγής xid: Αναγνωριστικό της συναλλαγής που στοχεύει το κλείδωμα.
  • pid: Αναγνωριστικό διεργασίας της διαδικασίας διακομιστή που κρατά/αναμένει για αυτό το κλείδωμα. Σύνδεση αυτής της στήλης στο pg_stat_activity Η προβολή μπορεί να παρέχει όλες τις παραπάνω πληροφορίες σχετικά με το ερώτημα / χρήστη / ώρα κ.λπ.
  • χορηγείται: Σωστό εάν το κλείδωμα κρατιέται, ψευδές εάν η διαδικασία είναι σε αναμονή.

Προβολή κλειδαριών με ονόματα πινάκων και ερωτήματα

Συμμετοχή στο pg_lock θέα προς pg_stat_activity για το ερώτημα και pg_class για τα ονόματα των πινάκων μπορεί να είναι χρήσιμα για τη συγκέντρωση περισσότερου πλαισίου ως προς το τι κλειδώνει στη βάση δεδομένων σας ανά πάσα στιγμή (για περισσότερα βλέπω „Η PostgreSQL βρίσκει κλειδαριές συμπεριλαμβανομένου του ονόματος του πίνακα“ επί Υπερχείλιση στοίβας):

select 
    relname as relation_name, 
    query, 
    pg_locks.* 
from pg_locks
join pg_class on pg_locks.relation = pg_class.oid
join pg_stat_activity on pg_locks.pid = pg_stat_activity.pid

Schreibe einen Kommentar