Μεταγραφή της έκθεσης του 2015 από τον Alexey Lesovsky "Deep dive into PostgreSQL interior statistics"
Αποποίηση ευθύνης από τον συντάκτη της αναφοράς: Σημειώνω ότι αυτή η έκθεση χρονολογείται από τον Νοέμβριο του 2015 - έχουν περάσει περισσότερα από 4 χρόνια και έχει περάσει πολύς χρόνος. Η έκδοση 9.4 που συζητείται στην αναφορά δεν υποστηρίζεται πλέον. Τα τελευταία 4 χρόνια, κυκλοφόρησαν 5 νέες εκδόσεις στις οποίες εμφανίστηκαν πολλές καινοτομίες, βελτιώσεις και αλλαγές σχετικά με τα στατιστικά στοιχεία, ενώ μέρος του υλικού είναι ξεπερασμένο και μη σχετικό. Καθώς το σχολίασα, προσπάθησα να επισημάνω αυτά τα μέρη για να μην σας παραπλανήσω τον αναγνώστη. Δεν ξαναέγραψα αυτά τα μέρη, υπάρχουν πολλά και ως αποτέλεσμα θα βγει μια εντελώς διαφορετική αναφορά.
Το PostgreSQL DBMS είναι ένας τεράστιος μηχανισμός και αυτός ο μηχανισμός αποτελείται από πολλά υποσυστήματα, η συντονισμένη λειτουργία των οποίων επηρεάζει άμεσα την απόδοση του DBMS. Κατά τη λειτουργία, συλλέγονται στατιστικά στοιχεία και πληροφορίες σχετικά με τη λειτουργία των στοιχείων, τα οποία σας επιτρέπουν να αξιολογήσετε την αποτελεσματικότητα της PostgreSQL και να λάβετε μέτρα για τη βελτίωση της απόδοσης. Ωστόσο, υπάρχουν πολλές από αυτές τις πληροφορίες και παρουσιάζονται σε μια μάλλον απλοποιημένη μορφή. Η επεξεργασία αυτών των πληροφοριών και η ερμηνεία τους είναι μερικές φορές μια εντελώς μη τετριμμένη εργασία και ο «ζωολογικός κήπος» των εργαλείων και των βοηθητικών προγραμμάτων μπορεί εύκολα να μπερδέψει ακόμη και ένα προηγμένο DBA.
Καλό απόγευμα Το όνομά μου είναι Aleksey. Όπως είπε ο Ilya, θα μιλήσω για τα στατιστικά PostgreSQL.
Στατιστικά δραστηριότητας PostgreSQL. Η PostgreSQL έχει δύο στατιστικά στοιχεία. Στατιστικά στοιχεία δραστηριότητας, τα οποία θα συζητηθούν. Και στατιστικά χρονοπρογραμματιστή σχετικά με τη διανομή δεδομένων. Θα μιλήσω συγκεκριμένα για τα στατιστικά της δραστηριότητας PostgreSQL, τα οποία μας επιτρέπουν να κρίνουμε την απόδοση και με κάποιο τρόπο να τη βελτιώνουμε.
Θα σας πω πώς να χρησιμοποιήσετε αποτελεσματικά τα στατιστικά στοιχεία για να λύσετε μια ποικιλία προβλημάτων που έχετε ή μπορεί να έχετε.
Τι δεν θα υπάρχει στην έκθεση; Στην έκθεση δεν θα θίξω τα στατιστικά του χρονοπρογραμματιστή, γιατί. Αυτό είναι ένα ξεχωριστό θέμα για μια ξεχωριστή αναφορά σχετικά με τον τρόπο αποθήκευσης των δεδομένων στη βάση δεδομένων και τον τρόπο με τον οποίο ο προγραμματιστής ερωτημάτων αποκτά μια ιδέα σχετικά με τα ποιοτικά και ποσοτικά χαρακτηριστικά αυτών των δεδομένων.
Και δεν θα υπάρχουν κριτικές εργαλείων, δεν θα συγκρίνω ένα προϊόν με ένα άλλο. Δεν θα υπάρχει διαφήμιση. Ας το αφήσουμε αυτό.
Θέλω να σας δείξω ότι η χρήση στατιστικών είναι χρήσιμη. Είναι απαραίτητο. Χρησιμοποιήστε το άφοβα. Το μόνο που χρειαζόμαστε είναι απλή SQL και βασικές γνώσεις SQL.
Και θα μιλήσουμε για το ποια στατιστικά να επιλέξουμε για την επίλυση προβλημάτων.
Αν κοιτάξουμε την PostgreSQL και εκτελέσουμε μια εντολή στο λειτουργικό σύστημα για να προβάλουμε τις διεργασίες, θα δούμε ένα "μαύρο κουτί". Θα δούμε κάποιες διαδικασίες που κάνουν κάτι, και ονομαστικά μπορούμε να φανταστούμε χονδρικά τι κάνουν εκεί, τι κάνουν. Αλλά, στην πραγματικότητα, αυτό είναι ένα μαύρο κουτί, δεν μπορούμε να κοιτάξουμε μέσα.
Μπορούμε να δούμε το φορτίο της CPU μέσα top, μπορούμε να δούμε τη χρήση της μνήμης από ορισμένα βοηθητικά προγράμματα συστήματος, αλλά δεν θα μπορούμε να κοιτάξουμε μέσα στο PostgreSQL. Για αυτό χρειαζόμαστε άλλα εργαλεία.
Και συνεχίζοντας παρακάτω, θα σας πω πού ξοδεύεται ο χρόνος. Εάν αντιπροσωπεύσουμε την PostgreSQL με τη μορφή ενός τέτοιου σχήματος, τότε θα είναι δυνατό να απαντήσουμε πού ξοδεύεται ο χρόνος. Αυτά είναι δύο πράγματα: είναι η επεξεργασία των αιτημάτων πελατών από εφαρμογές και οι εργασίες παρασκηνίου που εκτελεί η PostgreSQL για να συνεχίσει να λειτουργεί.
Αν αρχίσουμε να κοιτάμε στην επάνω αριστερή γωνία, μπορούμε να δούμε πώς επεξεργάζονται τα αιτήματα πελατών. Το αίτημα προέρχεται από την εφαρμογή και ανοίγει μια συνεδρία πελάτη για περαιτέρω εργασία. Το αίτημα διαβιβάζεται στον προγραμματιστή. Ο σχεδιαστής δημιουργεί ένα σχέδιο ερωτημάτων. Το στέλνει περαιτέρω για εκτέλεση. Υπάρχει κάποιο είδος δεδομένων μπλοκ I/O που σχετίζεται με πίνακες και ευρετήρια. Τα απαραίτητα δεδομένα διαβάζονται από τους δίσκους στη μνήμη σε μια ειδική περιοχή που ονομάζεται "shared buffers". Τα αποτελέσματα του ερωτήματος, εάν είναι ενημερώσεις, διαγραφές, καταγράφονται στο αρχείο καταγραφής συναλλαγών στο WAL. Ορισμένες στατιστικές πληροφορίες καταλήγουν σε ένα αρχείο καταγραφής ή μια συλλογή στατιστικών στοιχείων. Και το αποτέλεσμα του αιτήματος επιστρέφεται στον πελάτη. Μετά από αυτό, ο πελάτης μπορεί να επαναλάβει τα πάντα με ένα νέο αίτημα.
Τι έχουμε με τις εργασίες παρασκηνίου και τις διαδικασίες παρασκηνίου; Έχουμε αρκετές διεργασίες που διατηρούν τη βάση δεδομένων σε λειτουργία σε κανονική λειτουργία. Αυτές οι διαδικασίες θα καλυφθούν επίσης στην αναφορά: αυτές είναι η αυτόματη σκούπα, το σημείο ελέγχου, οι διαδικασίες που σχετίζονται με την αναπαραγωγή, η εγγραφή υποβάθρου. Θα θίξω το καθένα από αυτά καθώς αναφέρω.
Ποια είναι τα προβλήματα με τα στατιστικά στοιχεία;
- Πολλές πληροφορίες. Το PostgreSQL 9.4 παρέχει 109 μετρήσεις για την προβολή στατιστικών δεδομένων. Ωστόσο, εάν η βάση δεδομένων αποθηκεύει πολλούς πίνακες, σχήματα, βάσεις δεδομένων, τότε όλες αυτές οι μετρήσεις θα πρέπει να πολλαπλασιαστούν με τον αντίστοιχο αριθμό πινάκων, βάσεων δεδομένων. Δηλαδή, υπάρχουν ακόμη περισσότερες πληροφορίες. Και είναι πολύ εύκολο να πνιγείς σε αυτό.
- Το επόμενο πρόβλημα είναι ότι τα στατιστικά στοιχεία αντιπροσωπεύονται από μετρητές. Αν κοιτάξουμε αυτά τα στατιστικά στοιχεία, θα δούμε συνεχώς αυξανόμενους μετρητές. Και αν έχει περάσει πολύς χρόνος από την επαναφορά των στατιστικών, θα δούμε δισεκατομμύρια τιμές. Και δεν μας λένε τίποτα.
- Δεν υπάρχει ιστορία. Εάν έχετε κάποιο είδος αποτυχίας, κάτι έπεσε πριν από 15-30 λεπτά, δεν θα μπορείτε να χρησιμοποιήσετε τα στατιστικά και να δείτε τι συνέβη πριν από 15-30 λεπτά. Αυτό είναι πρόβλημα.
- Η έλλειψη ενός εργαλείου ενσωματωμένου στην PostgreSQL είναι ένα πρόβλημα. Οι προγραμματιστές του πυρήνα δεν παρέχουν κανένα βοηθητικό πρόγραμμα. Δεν έχουν κάτι τέτοιο. Απλώς δίνουν στατιστικά στοιχεία στη βάση δεδομένων. Χρησιμοποιήστε το, κάντε ένα αίτημα σε αυτό, ό,τι θέλετε, μετά κάντε το.
- Δεδομένου ότι δεν υπάρχει ενσωματωμένο εργαλείο στην PostgreSQL, αυτό προκαλεί ένα άλλο πρόβλημα. Πολλά εργαλεία τρίτων. Κάθε εταιρεία που έχει περισσότερο ή λιγότερο άμεσα χέρια προσπαθεί να γράψει το δικό της πρόγραμμα. Και ως αποτέλεσμα, η κοινότητα έχει πολλά εργαλεία που μπορείτε να χρησιμοποιήσετε για να εργαστείτε με στατιστικά στοιχεία. Και σε ορισμένα εργαλεία υπάρχουν κάποιες δυνατότητες, σε άλλα εργαλεία δεν υπάρχουν άλλες δυνατότητες ή υπάρχουν κάποιες νέες δυνατότητες. Και προκύπτει μια κατάσταση που πρέπει να χρησιμοποιήσετε δύο, τρία ή τέσσερα εργαλεία που επικαλύπτονται μεταξύ τους και έχουν διαφορετικές λειτουργίες. Αυτό είναι πολύ ενοχλητικό.
Τι προκύπτει από αυτό; Είναι σημαντικό να μπορείτε να λαμβάνετε άμεσα στατιστικά στοιχεία, ώστε να μην εξαρτάστε από προγράμματα ή να βελτιώσετε με κάποιο τρόπο αυτά τα προγράμματα μόνοι σας: προσθέστε μερικές λειτουργίες για να επωφεληθείτε από το δικό σας όφελος.
Και χρειάζεστε βασικές γνώσεις SQL. Για να λάβετε κάποια δεδομένα από στατιστικά στοιχεία, πρέπει να κάνετε ερωτήματα SQL, δηλαδή πρέπει να γνωρίζετε πώς γίνεται η επιλογή, η συμμετοχή.
Οι στατιστικές μας λένε πολλά πράγματα. Μπορούν να χωριστούν σε κατηγορίες.
- Η πρώτη κατηγορία είναι τα γεγονότα που λαμβάνουν χώρα στη βάση δεδομένων. Αυτό συμβαίνει όταν συμβαίνει κάποιο συμβάν στη βάση δεδομένων: ένα αίτημα, πρόσβαση σε έναν πίνακα, αυτόματη εκκένωση, δεσμεύσεις, τότε όλα αυτά είναι συμβάντα. Οι μετρητές που αντιστοιχούν σε αυτά τα γεγονότα αυξάνονται. Και μπορούμε να παρακολουθήσουμε αυτά τα γεγονότα.
- Η δεύτερη κατηγορία είναι οι ιδιότητες αντικειμένων όπως πίνακες, βάσεις δεδομένων. Έχουν ιδιότητες. Αυτό είναι το μέγεθος των τραπεζιών. Μπορούμε να παρακολουθήσουμε την ανάπτυξη των πινάκων, την ανάπτυξη των δεικτών. Μπορούμε να δούμε αλλαγές στη δυναμική.
- Και η τρίτη κατηγορία είναι ο χρόνος που αφιερώθηκε στην εκδήλωση. Το αίτημα είναι ένα γεγονός. Έχει το δικό του συγκεκριμένο μέτρο διάρκειας. Ξεκίνησε εδώ, τελείωσε εδώ. Μπορούμε να το παρακολουθήσουμε. Είτε την ώρα της ανάγνωσης ενός μπλοκ από το δίσκο είτε της εγγραφής. Αυτά τα πράγματα παρακολουθούνται επίσης.
Οι πηγές στατιστικών στοιχείων παρουσιάζονται ως εξής:
- Στην κοινόχρηστη μνήμη (κοινόχρηστα buffer) υπάρχει ένα τμήμα για την τοποθέτηση στατικών δεδομένων εκεί, υπάρχουν επίσης εκείνοι οι μετρητές που αυξάνονται συνεχώς όταν συμβαίνουν ορισμένα συμβάντα ή προκύπτουν κάποιες στιγμές στη λειτουργία της βάσης δεδομένων.
- Όλοι αυτοί οι μετρητές δεν είναι διαθέσιμοι στον χρήστη και δεν είναι καν διαθέσιμοι στον διαχειριστή. Αυτά είναι πράγματα χαμηλού επιπέδου. Για πρόσβαση σε αυτά, η PostgreSQL παρέχει μια διεπαφή με τη μορφή συναρτήσεων SQL. Μπορούμε να κάνουμε επιλεγμένες επιλογές χρησιμοποιώντας αυτές τις συναρτήσεις και να πάρουμε κάποιο είδος μέτρησης (ή σύνολο μετρήσεων).
- Ωστόσο, η χρήση αυτών των λειτουργιών δεν είναι πάντα βολική, επομένως οι συναρτήσεις αποτελούν τη βάση για τις προβολές (VIEWs). Αυτοί είναι εικονικοί πίνακες που παρέχουν στατιστικά στοιχεία για ένα συγκεκριμένο υποσύστημα ή για ένα συγκεκριμένο σύνολο συμβάντων στη βάση δεδομένων.
- Αυτές οι ενσωματωμένες προβολές (VIEWs) είναι η κύρια διεπαφή χρήστη για την εργασία με στατιστικά στοιχεία. Είναι διαθέσιμα από προεπιλογή χωρίς πρόσθετες ρυθμίσεις, μπορείτε να τα χρησιμοποιήσετε αμέσως, να παρακολουθήσετε, να πάρετε πληροφορίες από εκεί. Και υπάρχουν και συνεισφορές. Οι συνεισφορές είναι επίσημες. Μπορείτε να εγκαταστήσετε το πακέτο postgresql-contrib (για παράδειγμα, postgresql94-contrib), να φορτώσετε την απαραίτητη ενότητα στη διαμόρφωση, να καθορίσετε παραμέτρους για αυτήν, να επανεκκινήσετε την PostgreSQL και να τη χρησιμοποιήσετε. (Σημείωση. Ανάλογα με τη διανομή, στις πρόσφατες εκδόσεις του contrib το πακέτο είναι μέρος του κύριου πακέτου).
- Και υπάρχουν ανεπίσημη συνεισφορά. Δεν παρέχονται με την τυπική διανομή PostgreSQL. Πρέπει είτε να μεταγλωττιστούν είτε να εγκατασταθούν ως βιβλιοθήκη. Οι επιλογές μπορεί να είναι πολύ διαφορετικές, ανάλογα με το τι βρήκε ο προγραμματιστής αυτής της ανεπίσημης συνεισφοράς.
Αυτή η διαφάνεια παρουσιάζει όλες εκείνες τις VIEW και μερικές από τις λειτουργίες που είναι διαθέσιμες στο 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;Το πρώτο πράγμα που μπορούμε να δούμε είναι το ποσοστό επιτυχίας της κρυφής μνήμης. Το ποσοστό επίσκεψης στην κρυφή μνήμη είναι μια χρήσιμη μέτρηση. Σας επιτρέπει να υπολογίσετε πόσα δεδομένα λαμβάνονται από την προσωρινή μνήμη κοινόχρηστων buffers και πόσα διαβάζονται από το δίσκο.
Είναι ξεκάθαρο ότι όσο περισσότερα χτυπήματα κρυφής μνήμης έχουμε, τόσο το καλύτερο. Αξιολογούμε αυτή τη μέτρηση ως ποσοστό. Και, για παράδειγμα, αν έχουμε ένα ποσοστό αυτών των επισκέψεων στην κρυφή μνήμη μεγαλύτερο από 90%, τότε αυτό είναι καλό. Εάν πέσει κάτω από το 90%, τότε δεν έχουμε αρκετή μνήμη για να διατηρήσουμε τη ζεστή κεφαλή δεδομένων στη μνήμη. Και για να χρησιμοποιήσει αυτά τα δεδομένα, η PostgreSQL αναγκάζεται να αποκτήσει πρόσβαση στο δίσκο και αυτό είναι πιο αργό από ό,τι αν τα δεδομένα διαβάζονταν από τη μνήμη. Και πρέπει να σκεφτείτε την αύξηση της μνήμης: είτε αυξήστε τα κοινόχρηστα buffer είτε αυξήστε τη σιδερένια μνήμη (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 είναι μια επιτυχής επιβεβαίωση της συναλλαγής. Το Rollbacks είναι μια επαναφορά, δηλαδή η συναλλαγή έκανε κάποια δουλειά, καταπόνησε τη βάση δεδομένων, εξέτασε κάτι και στη συνέχεια παρουσιάστηκε μια αποτυχία και τα αποτελέσματα της συναλλαγής απορρίπτονται. δηλ. Ο αριθμός των ανατροπών που συνεχώς αυξάνεται είναι κακός. Και θα πρέπει με κάποιο τρόπο να τα αποφύγετε και να επεξεργαστείτε τον κώδικα για να μην συμβεί αυτό.
Οι συγκρούσεις σχετίζονται με την αναπαραγωγή. Και πρέπει επίσης να αποφεύγονται. Εάν έχετε κάποια ερωτήματα που εκτελούνται σε ένα αντίγραφο και προκύψουν διενέξεις, τότε πρέπει να επιλύσετε αυτές τις διενέξεις και να δείτε τι συμβαίνει. Μπορείτε να βρείτε λεπτομέρειες στα αρχεία καταγραφής. Και εξαλείψτε τις καταστάσεις σύγκρουσης, ώστε τα αιτήματα εφαρμογής να λειτουργούν χωρίς σφάλματα.
Τα αδιέξοδα είναι επίσης μια κακή κατάσταση. Όταν τα αιτήματα ανταγωνίζονται για πόρους, ένα αίτημα είχε πρόσβαση σε έναν πόρο και πήρε το κλείδωμα, το δεύτερο αίτημα είχε πρόσβαση στον δεύτερο πόρο και πήρε επίσης το κλείδωμα, και στη συνέχεια και τα δύο αιτήματα είχαν πρόσβαση στους πόρους του άλλου και απέκλεισαν την αναμονή για την απελευθέρωση της κλειδαριάς από τον γείτονα. Αυτή είναι επίσης μια προβληματική κατάσταση. Πρέπει να αντιμετωπιστούν σε επίπεδο επανεγγραφής εφαρμογών και σειριακής πρόσβασης σε πόρους. Και αν δείτε ότι τα αδιέξοδά σας αυξάνονται συνεχώς, πρέπει να δείτε τις λεπτομέρειες στα αρχεία καταγραφής, να αναλύσετε τις καταστάσεις που έχουν προκύψει και να δείτε ποιο είναι το πρόβλημα.
Τα προσωρινά αρχεία (temp_files) είναι επίσης κακά. Όταν ένα αίτημα χρήστη δεν έχει αρκετή μνήμη για να φιλοξενήσει τα λειτουργικά, προσωρινά δεδομένα, δημιουργεί ένα αρχείο στο δίσκο. Και όλες οι λειτουργίες που θα μπορούσε να εκτελέσει σε ένα προσωρινό buffer στη μνήμη, αρχίζει να εκτελεί ήδη στο δίσκο. Είναι αργό. Αυτό αυξάνει τον χρόνο εκτέλεσης του ερωτήματος. Και ο πελάτης που έστειλε ένα αίτημα στην PostgreSQL θα λάβει απάντηση λίγο αργότερα. Εάν όλες αυτές οι λειτουργίες εκτελούνται στη μνήμη, το Postgres θα ανταποκριθεί πολύ πιο γρήγορα και ο πελάτης θα περιμένει λιγότερο.
pg_stat_bgwriter - Αυτή η προβολή περιγράφει τη λειτουργία δύο υποσυστημάτων φόντου PostgreSQL: checkpointer и background writer.
Αρχικά, ας αναλύσουμε τα σημεία ελέγχου, τα λεγόμενα. checkpoints. Τι είναι τα σημεία ελέγχου; Ένα σημείο ελέγχου είναι μια θέση στο αρχείο καταγραφής συναλλαγών που υποδεικνύει ότι όλες οι αλλαγές δεδομένων που πραγματοποιούνται στο αρχείο καταγραφής συγχρονίζονται επιτυχώς με τα δεδομένα στο δίσκο. Η διαδικασία, ανάλογα με τον φόρτο εργασίας και τις ρυθμίσεις, μπορεί να είναι χρονοβόρα και συνίσταται κυρίως στον συγχρονισμό βρώμικων σελίδων σε κοινόχρηστα buffer με αρχεία δεδομένων στο δίσκο. Σε τι χρησιμεύει; Εάν η PostgreSQL είχε πρόσβαση στο δίσκο όλη την ώρα και έπαιρνε δεδομένα από εκεί και έγραφε δεδομένα σε κάθε πρόσβαση, θα ήταν αργή. Επομένως, η PostgreSQL έχει ένα τμήμα μνήμης, το μέγεθος του οποίου εξαρτάται από τις παραμέτρους στη διαμόρφωση. Το Postgres εκχωρεί λειτουργικά δεδομένα σε αυτή τη μνήμη για περαιτέρω επεξεργασία ή αναζήτηση. Στην περίπτωση αιτημάτων αλλαγής δεδομένων, αλλάζουν. Και έχουμε δύο εκδόσεις των δεδομένων. Το ένα είναι στη μνήμη, το άλλο στο δίσκο. Και περιοδικά πρέπει να συγχρονίζετε αυτά τα δεδομένα. Χρειαζόμαστε ότι έχει αλλάξει στη μνήμη να συγχρονιστεί με το δίσκο. Αυτό απαιτεί ένα σημείο ελέγχου.
Το σημείο ελέγχου περνά από κοινόχρηστα buffer, επισημαίνει βρώμικες σελίδες ότι χρειάζονται για το σημείο ελέγχου. Στη συνέχεια ξεκινά το δεύτερο πέρασμα μέσω κοινόχρηστων buffer. Και τις σελίδες που επισημαίνονται ως σημείο ελέγχου, τις συγχρονίζει ήδη. Έτσι, τα δεδομένα συγχρονίζονται ήδη με το δίσκο.
Υπάρχουν δύο τύποι σημείων ελέγχου. Ένα σημείο ελέγχου εκτελείται στο χρονικό όριο. Αυτό το σημείο ελέγχου είναι χρήσιμο και καλό - checkpoint_timed. Και υπάρχουν σημεία ελέγχου κατά παραγγελία - checkpoint required. Ένα τέτοιο σημείο ελέγχου συμβαίνει όταν έχουμε ένα πολύ μεγάλο αρχείο δεδομένων. Καταγράψαμε πολλά αρχεία καταγραφής συναλλαγών. Και η PostgreSQL πιστεύει ότι πρέπει να συγχρονίσει όλα αυτά όσο το δυνατόν γρηγορότερα, να κάνει ένα σημείο ελέγχου και να προχωρήσει.
Και αν κοιτάξεις τα στατιστικά pg_stat_bgwriter και δες τι έχεις Το checkpoint_req είναι πολύ μεγαλύτερο από το checkpoint_timed, τότε αυτό είναι κακό. Γιατί κακό; Αυτό σημαίνει ότι η PostgreSQL βρίσκεται υπό συνεχή πίεση όταν χρειάζεται να γράψει δεδομένα στο δίσκο. Το Checkpoint by timeout είναι λιγότερο αγχωτικό και εκτελείται σύμφωνα με το εσωτερικό χρονοδιάγραμμα και, όπως λέμε, τεντώνεται με την πάροδο του χρόνου. Η PostgreSQL έχει τη δυνατότητα να κάνει παύση στην εργασία και να μην καταπονεί το υποσύστημα του δίσκου. Αυτό είναι χρήσιμο για την PostgreSQL. Και τα αιτήματα που εκτελούνται κατά τη διάρκεια του σημείου ελέγχου δεν θα αντιμετωπίσουν άγχος από το γεγονός ότι το υποσύστημα του δίσκου είναι απασχολημένο.
Και υπάρχουν τρεις παράμετροι για να ρυθμίσετε το σημείο ελέγχου:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Σας επιτρέπουν να ελέγχετε τη λειτουργία των σημείων ελέγχου. Αλλά δεν θα σταθώ σε αυτά. Η επιρροή τους είναι ένα ξεχωριστό θέμα.
Внимание: Η έκδοση 9.4 που εξετάζεται στην αναφορά δεν είναι πλέον σχετική. Στις σύγχρονες εκδόσεις της PostgreSQL, η παράμετρος checkpoint_segments αντικαθίστανται από παραμέτρους min_wal_size и max_wal_size.
Το επόμενο υποσύστημα είναι το background writer − background writer. Τι κάνει? Λειτουργεί συνεχώς σε έναν ατελείωτο βρόχο. Σαρώνει σελίδες σε κοινόχρηστα buffer και ξεπλένει τις βρώμικες σελίδες που βρίσκει στο δίσκο. Με αυτόν τον τρόπο, βοηθά το checkpointer να κάνει λιγότερη εργασία κατά τη διάρκεια του checkpoint.
Τι άλλο τον χρειάζεται; Προβλέπει την ανάγκη για καθαρές σελίδες σε κοινόχρηστα buffers, εάν απαιτηθούν ξαφνικά (σε μεγάλες ποσότητες και αμέσως) για την αποθήκευση δεδομένων. Ας υποθέσουμε ότι προέκυψε μια κατάσταση όταν το αίτημα απαιτούσε καθαρές σελίδες και βρίσκονται ήδη σε κοινόχρηστα buffer. Postgres backend απλά τα παίρνει και τα χρησιμοποιεί, δεν χρειάζεται να καθαρίσει τίποτα μόνος του. Αλλά αν ξαφνικά δεν υπάρχουν τέτοιες σελίδες, το backend σταματά και αρχίζει να ψάχνει για σελίδες για να τις ξεπλύνει στο δίσκο και να τις μεταφέρει για τις δικές του ανάγκες - κάτι που επηρεάζει αρνητικά τον χρόνο της τρέχουσας αίτησης που εκτελείται. Αν δείτε ότι έχετε μια παράμετρο maxwritten_clean μεγάλο, αυτό σημαίνει ότι ο συντάκτης παρασκηνίου δεν κάνει τη δουλειά του και πρέπει να αυξήσετε τις παραμέτρους bgwriter_lru_maxpagesώστε να μπορεί να κάνει περισσότερη δουλειά σε έναν κύκλο, να καθαρίσει περισσότερες σελίδες.
Και ένας άλλος πολύ χρήσιμος δείκτης είναι buffers_backend_fsync. Τα backend δεν κάνουν fsync επειδή είναι αργό. Περνούν fsync στο σημείο ελέγχου στοίβας IO. Το σημείο ελέγχου έχει τη δική του ουρά, επεξεργάζεται περιοδικά το fsync και συγχρονίζει σελίδες στη μνήμη με αρχεία στο δίσκο. Εάν η ουρά του σημείου ελέγχου είναι μεγάλη και γεμάτη, τότε το backend αναγκάζεται να κάνει μόνο του fsync και αυτό επιβραδύνει το backend, δηλαδή ο πελάτης θα λάβει απάντηση αργότερα από ό,τι θα μπορούσε. Εάν δείτε ότι έχετε αυτήν την τιμή μεγαλύτερη από το μηδέν, τότε αυτό είναι ήδη ένα πρόβλημα και πρέπει να δώσετε προσοχή στις ρυθμίσεις του προγράμματος εγγραφής φόντου και επίσης να αξιολογήσετε την απόδοση του υποσυστήματος του δίσκου.
Внимание: _Το παρακάτω κείμενο περιγράφει τις στατιστικές προβολές που σχετίζονται με την αναπαραγωγή. Τα περισσότερα από τα ονόματα προβολής και συναρτήσεων έχουν μετονομαστεί στο Postgres 10. Η ουσία των μετονομασιών ήταν να αντικατασταθούν xlog επί wal и location επί 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());Αν αυτά τα πράγματα είναι διαφορετικά, τότε υπάρχει κάποιου είδους υστέρηση. Η καθυστέρηση είναι η καθυστέρηση του αντιγράφου από τον κύριο, δηλαδή τα δεδομένα διαφέρουν μεταξύ των διακομιστών.
Υπάρχουν τρεις λόγοι για την καθυστέρηση:
- Είναι το υποσύστημα του δίσκου που δεν μπορεί να χειριστεί τις εγγραφές συγχρονισμού αρχείων.
- Αυτά είναι πιθανά σφάλματα δικτύου ή υπερφόρτωση δικτύου, όταν τα δεδομένα δεν έχουν χρόνο να φτάσουν στο αντίγραφο και δεν μπορεί να το αναπαραγάγει.
- Και ο επεξεργαστής. Ο επεξεργαστής είναι μια πολύ σπάνια περίπτωση. Και το έχω δει δύο ή τρεις φορές, αλλά μπορεί να συμβεί και αυτό.
Και εδώ υπάρχουν τρία ερωτήματα που μας επιτρέπουν να χρησιμοποιούμε στατιστικά. Μπορούμε να υπολογίσουμε πόσα έχουν καταγραφεί στο αρχείο καταγραφής συναλλαγών μας. Υπάρχει μια τέτοια λειτουργία pg_xlog_location_diff και μπορούμε να υπολογίσουμε την καθυστέρηση αναπαραγωγής σε byte και δευτερόλεπτα. Χρησιμοποιούμε επίσης την τιμή από αυτήν την προβολή (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 (ο σύνδεσμος στη διαφάνεια δεν είναι έγκυρος και έχει ενημερωθεί στο κείμενο).
Έγραψαν για που λαμβάνει υπόψη αυτά τα πράγματα. Υπάρχει ποδόπανο σε δύο σεντόνια. Αλλά σκέφτεται σωστά και αρκετά αποτελεσματικά μας επιτρέπει να αξιολογήσουμε πού χρειαζόμαστε πολύ κενό για τραπέζια όπου υπάρχει λίγο.
Τι μπορούμε να κάνουμε για αυτό; Εάν έχουμε μια μεγάλη ουρά και η αυτόματη σκούπα δεν μπορεί να αντεπεξέλθει, τότε μπορούμε να αυξήσουμε τον αριθμό των εργαζομένων στην ηλεκτρική σκούπα ή απλώς να κάνουμε το κενό πιο επιθετικόώστε να ενεργοποιηθεί νωρίτερα, επεξεργάζεται το τραπέζι σε μικρά κομμάτια. Και έτσι η ουρά θα μειωθεί. - Το κύριο πράγμα εδώ είναι να παρακολουθείτε το φορτίο στους δίσκους, επειδή. Το θέμα του κενού δεν είναι δωρεάν, αν και με την έλευση των συσκευών SSD / NVMe, το πρόβλημα έχει γίνει λιγότερο αισθητό.
Το Pg_stat_all_indexes είναι στατιστικά στοιχεία για ευρετήρια. Δεν είναι μεγάλη. Και μπορούμε να το χρησιμοποιήσουμε για να λάβουμε πληροφορίες σχετικά με τη χρήση των ευρετηρίων. Και για παράδειγμα, μπορούμε να καθορίσουμε ποιους δείκτες έχουμε επιπλέον.
Όπως είπα ήδη, Η ενημέρωση δεν είναι μόνο η ενημέρωση πινάκων, αλλά και η ενημέρωση ευρετηρίων. Αντίστοιχα, εάν έχουμε πολλά ευρετήρια στον πίνακα, τότε κατά την ενημέρωση των σειρών στον πίνακα, τα ευρετήρια των πεδίων με ευρετήριο πρέπει επίσης να ενημερωθούν, και αν έχουμε αχρησιμοποίητα ευρετήρια για τα οποία δεν υπάρχουν σαρώσεις ευρετηρίου, τότε μας κολλάνε ως έρμα. Και πρέπει να απαλλαγείτε από αυτά. Για αυτό χρειαζόμαστε ένα χωράφι idx_scan. Απλώς εξετάζουμε τον αριθμό των σαρώσεων ευρετηρίου. Εάν τα ευρετήρια έχουν μηδενικές σαρώσεις για μια σχετικά μεγάλη περίοδο αποθήκευσης στατιστικών στοιχείων (τουλάχιστον 2-3 εβδομάδες), τότε πιθανότατα πρόκειται για κακά ευρετήρια, πρέπει να τα ξεφορτωθούμε.
Σημείωση: Κατά την αναζήτηση αχρησιμοποίητων ευρετηρίων στην περίπτωση συμπλεγμάτων αναπαραγωγής ροής, πρέπει να ελέγξετε όλους τους κόμβους του συμπλέγματος, επειδή Τα στατιστικά στοιχεία δεν είναι καθολικά και εάν το ευρετήριο δεν χρησιμοποιείται στο κύριο, τότε μπορεί να χρησιμοποιηθεί σε αντίγραφα (αν υπάρχει φόρτωση).
Δύο σύνδεσμοι:
Αυτά είναι πιο προηγμένα παραδείγματα ερωτημάτων για τον τρόπο αναζήτησης αχρησιμοποίητων ευρετηρίων.
Ο δεύτερος σύνδεσμος είναι μια αρκετά ενδιαφέρουσα ερώτηση. Υπάρχει μια πολύ μη τετριμμένη λογική σε αυτό. Το προτείνω για αναθεώρηση.
Τι άλλο αξίζει να συνοψίσουμε χρησιμοποιώντας δείκτες;
-
Τα αχρησιμοποίητα ευρετήρια είναι κακά.
-
Καταλαμβάνουν χώρο.
-
Επιβραδύνετε τις λειτουργίες ενημέρωσης.
-
Επιπλέον εργασία για το κενό.
Εάν αφαιρέσουμε τα αχρησιμοποίητα ευρετήρια, τότε μόνο θα βελτιώσουμε τη βάση δεδομένων.
Η επόμενη άποψη είναι 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 με boolean τιμή true ή false.
Αλήθεια - αυτό σημαίνει ότι η διαδικασία περιμένει, κάτι πρέπει να γίνει. Όταν μια διαδικασία βρίσκεται σε αναμονή, τότε ο πελάτης που ξεκίνησε τη διαδικασία περιμένει επίσης. Ο πελάτης στο πρόγραμμα περιήγησης κάθεται και επίσης περιμένει.
Внимание: _Ξεκινώντας από Postgres 9.6, το πεδίο waiting καταργήθηκε και αντικαταστάθηκε από δύο ακόμη ενημερωτικά πεδία wait_event_type и wait_event._
Τι να κάνω; Εάν βλέπετε αλήθεια για μεγάλο χρονικό διάστημα, τότε θα πρέπει να απαλλαγείτε από τέτοια αιτήματα. Απλώς πυροβολούμε τέτοιες συναλλαγές. Γράφουμε στους προγραμματιστές τι πρέπει να βελτιστοποιηθεί με κάποιο τρόπο, ώστε να μην υπάρχει κούρσα για πόρους. Και στη συνέχεια οι προγραμματιστές βελτιστοποιούν την εφαρμογή έτσι ώστε να μην συμβεί αυτό.
Και είναι μια ακραία, αλλά δυνητικά όχι μοιραία περίπτωση εμφάνιση αδιεξόδων. Δύο συναλλαγές έχουν ενημερώσει δύο πόρους και στη συνέχεια τους έχουν πρόσβαση ξανά, ήδη σε αντίθετους πόρους. Η PostgreSQL σε αυτήν την περίπτωση παίρνει και αποκλείει την ίδια τη συναλλαγή, ώστε η άλλη να συνεχίσει να λειτουργεί. Αυτή είναι μια κατάσταση αδιέξοδο και δεν καταλαβαίνει τον εαυτό της. Επομένως, η PostgreSQL αναγκάζεται να λάβει ακραία μέτρα.
Και εδώ υπάρχουν δύο ερωτήματα που σας επιτρέπουν να παρακολουθείτε κλειδαριές. Χρησιμοποιούμε τη θέα pg_locks, που σας επιτρέπει να παρακολουθείτε βαριές κλειδαριές.
Και ο πρώτος σύνδεσμος είναι το ίδιο το κείμενο της αίτησης. Είναι αρκετά μακρύ.
Και ο δεύτερος σύνδεσμος είναι ένα άρθρο για τις κλειδαριές. Είναι χρήσιμο να το διαβάσετε, είναι πολύ ενδιαφέρον.
Τι βλέπουμε λοιπόν; Βλέπουμε δύο αιτήματα. Συναλλαγή με ALTER TABLE είναι μια συναλλαγή αποκλεισμού. Ξεκίνησε, αλλά δεν τελείωσε, και η εφαρμογή που δημοσίευσε αυτή τη συναλλαγή κάνει άλλα πράγματα κάπου. Και το δεύτερο αίτημα είναι η ενημέρωση. Περιμένει να τελειώσει το alter table πριν συνεχίσει την εργασία του.
Έτσι μπορούμε να μάθουμε ποιος κλείδωσε ποιον, ποιος κρατάει ποιον και μπορούμε να το αντιμετωπίσουμε περαιτέρω.
Η επόμενη ενότητα είναι pg_stat_statements. Όπως είπα, είναι μια ενότητα. Για να το χρησιμοποιήσετε, πρέπει να φορτώσετε τη βιβλιοθήκη του στη διαμόρφωση, να επανεκκινήσετε το PostgreSQL, να εγκαταστήσετε το module (με μία εντολή) και μετά θα έχουμε μια νέα προβολή.
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 ανταποκρίνεται αργά και κάτι πρέπει να γίνει.
Μπορούμε να δούμε τις πιο ενεργές συναλλαγές εγγραφής στη βάση δεδομένων που αλλάζουν δεδομένα σε κοινόχρηστα buffer. Δείτε ποιος ενημερώνει ή διαγράφει δεδομένα εκεί.
Και μπορούμε απλώς να δούμε διαφορετικά στατιστικά στοιχεία για αυτά τα αιτήματα.
Εμείς pg_stat_statements χρησιμοποιείται για τη δημιουργία αναφορών. Επαναφέρουμε τα στατιστικά μία φορά την ημέρα. Ας το συσσωρεύσουμε. Πριν επαναφέρουμε τα στατιστικά στοιχεία την επόμενη φορά, δημιουργούμε μια αναφορά. Εδώ είναι ένας σύνδεσμος για την έκθεση. Μπορείτε να το παρακολουθήσετε.
Τι κάνουμε? Υπολογίζουμε τα συνολικά στατιστικά στοιχεία για όλα τα αιτήματα. Στη συνέχεια, για κάθε ερώτημα, μετράμε την ατομική συνεισφορά του σε αυτό το συνολικό στατιστικό.
Και τι μπορούμε να δούμε; Μπορούμε να δούμε τον συνολικό χρόνο εκτέλεσης όλων των αιτημάτων ενός συγκεκριμένου τύπου στο φόντο όλων των άλλων αιτημάτων. Μπορούμε να δούμε τη χρήση CPU και I/O σε σχέση με τη συνολική εικόνα. Και ήδη για τη βελτιστοποίηση αυτών των αιτημάτων. Δημιουργούμε κορυφαία ερωτήματα με βάση αυτήν την αναφορά και έχουμε ήδη τροφή για σκέψη σχετικά με το τι πρέπει να βελτιστοποιήσουμε.
Τι έχουμε στα παρασκήνια; Υπάρχουν ακόμη μερικές υποβολές που δεν εξέτασα, επειδή ο χρόνος είναι περιορισμένος.
Υπάρχει pgstattuple είναι επίσης μια πρόσθετη ενότητα από το τυπικό πακέτο συνεισφορών. Σας επιτρέπει να αξιολογήσετε bloat τραπέζια, τα λεγόμενα. κατακερματισμός πίνακα. Και αν ο κατακερματισμός είναι μεγάλος, πρέπει να το αφαιρέσετε, χρησιμοποιήστε διαφορετικά εργαλεία. Και λειτουργία pgstattuple λειτουργεί για μεγάλο χρονικό διάστημα. Και όσο περισσότερα τραπέζια, τόσο περισσότερο θα λειτουργεί.
Η επόμενη συνεισφορά είναι pg_buffercache. Σας επιτρέπει να επιθεωρείτε κοινόχρηστα buffer: πόσο εντατικά και για ποιους πίνακες χρησιμοποιούνται οι σελίδες buffer. Και απλά σας επιτρέπει να κοιτάξετε σε κοινόχρηστα buffer και να αξιολογήσετε τι συμβαίνει εκεί.
Η επόμενη ενότητα είναι pgfincore. Σας επιτρέπει να εκτελείτε λειτουργίες πίνακα χαμηλού επιπέδου μέσω μιας κλήσης συστήματος mincore(), δηλαδή σας επιτρέπει να φορτώσετε τον πίνακα σε κοινόχρηστα buffer ή να τον ξεφορτώσετε. Και επιτρέπει, μεταξύ άλλων, να επιθεωρήσουμε την προσωρινή μνήμη σελίδων του λειτουργικού συστήματος, δηλαδή πόσο χώρο καταλαμβάνει ο πίνακας στην προσωρινή μνήμη σελίδας, σε κοινόχρηστα buffer, και απλώς μας επιτρέπει να αξιολογήσουμε τον φόρτο εργασίας του πίνακα.
Η επόμενη ενότητα είναι pg_stat_kcache. Χρησιμοποιεί επίσης την κλήση συστήματος getrusage(). Και το εκτελεί πριν και μετά την εκτέλεση του αιτήματος. Και στα στατιστικά στοιχεία που ελήφθησαν, μας επιτρέπει να υπολογίσουμε πόσο ξόδεψε το αίτημά μας σε I/O δίσκου, δηλαδή λειτουργίες με το σύστημα αρχείων και εξετάζει τη χρήση του επεξεργαστή. Ωστόσο, το module είναι νέο (khe-khe) και για τη δουλειά του απαιτεί PostgreSQL 9.4 και pg_stat_statements, που ανέφερα προηγουμένως.
-
Η δυνατότητα χρήσης στατιστικών είναι χρήσιμη. Δεν χρειάζεστε λογισμικό τρίτων. Μπορείτε να κοιτάξετε, να δείτε, να κάνετε κάτι, να εκτελέσετε.
-
Η χρήση στατιστικών είναι εύκολη, είναι απλή SQL. Συλλέξατε ένα αίτημα, το συνέταξατε, το στείλατε, το κοιτάξατε.
-
Οι στατιστικές βοηθούν στην απάντηση ερωτήσεων. Εάν έχετε ερωτήσεις, στρέφεστε στα στατιστικά στοιχεία - κοιτάξτε, βγάλτε συμπεράσματα, αναλύστε τα αποτελέσματα.
-
Και πειραματιστείτε. Πολλά αιτήματα, πολλά δεδομένα. Μπορείτε πάντα να βελτιστοποιήσετε κάποιο υπάρχον ερώτημα. Μπορείτε να φτιάξετε τη δική σας έκδοση του αιτήματος που σας ταιριάζει καλύτερα από την αρχική και να τη χρησιμοποιήσετε.
παραπομπές
Έγκυροι σύνδεσμοι που βρέθηκαν στο άρθρο, βάσει των οποίων, ήταν στην αναφορά.
Ο συγγραφέας γράφει περισσότερα
(αγγλ.)
Ο Συλλέκτης Στατιστικών
Λειτουργίες διαχείρισης συστήματος
Ενότητες συνεισφοράς
Βοηθητικά προγράμματα SQL και παραδείγματα κώδικα sql
Σας ευχαριστώ όλους για την προσοχή σας!
Πηγή: www.habr.com