Γεια σου
Το όνομά μου είναι Vanya και είμαι προγραμματιστής Java. Συμβαίνει να δουλεύω πολύ με την PostgreSQL - ρυθμίζοντας τη βάση δεδομένων, βελτιστοποιώντας τη δομή, την απόδοση και παίζω λίγο DBA τα Σαββατοκύριακα.
Πρόσφατα έχω τακτοποιήσει αρκετές βάσεις δεδομένων στις μικροϋπηρεσίες μας και έγραψα μια βιβλιοθήκη java , που διευκολύνει αυτή τη δουλειά, με εξοικονομεί χρόνο και με βοηθά να αποφύγω ορισμένα κοινά λάθη που γίνονται από προγραμματιστές. Είναι αυτή η βιβλιοθήκη για την οποία θα μιλήσουμε σήμερα.
Αποποίηση ευθυνών
Η κύρια έκδοση της PostgreSQL με την οποία δουλεύω είναι η 10. Όλα τα ερωτήματα SQL που χρησιμοποιώ ελέγχονται επίσης στην έκδοση 11. Η ελάχιστη υποστηριζόμενη έκδοση είναι 9.6.
Ιστορικό
Όλα ξεκίνησαν πριν από σχεδόν ένα χρόνο με μια κατάσταση που ήταν περίεργη για μένα: η ανταγωνιστική δημιουργία ενός ευρετηρίου ολοκληρώθηκε με λάθος. Το ίδιο το ευρετήριο, ως συνήθως, παρέμεινε στη βάση δεδομένων σε μη έγκυρη κατάσταση. Η ανάλυση ημερολογίου έδειξε έλλειψη . Και πάμε... Σκάβοντας βαθύτερα, ανακάλυψα ένα σωρό προβλήματα στη διαμόρφωση της βάσης δεδομένων και, σηκώνοντας τα μανίκια μου, άρχισα να τα διορθώνω με μια λάμψη στα μάτια μου.
Πρόβλημα πρώτο - προεπιλεγμένη διαμόρφωση
Μάλλον όλοι έχουν βαρεθεί αρκετά τη μεταφορά για την Postgres, η οποία μπορεί να εκτελεστεί σε καφετιέρα, αλλά... η προεπιλεγμένη διαμόρφωση εγείρει πραγματικά μια σειρά από ερωτήματα. Τουλάχιστον, αξίζει να δώσετε προσοχή maintenance_work_mem, temp_file_limit, statement_timeout и lock_timeout.
Στην περίπτωσή μας maintenance_work_mem ήταν το προεπιλεγμένο 64 MB και temp_file_limit κάτι γύρω στα 2 GB - απλά δεν είχαμε αρκετή μνήμη για να δημιουργήσουμε ένα ευρετήριο σε ένα μεγάλο τραπέζι.
Επομένως, στο pg-index-health Μάζεψα μια σειρά , κατά τη γνώμη μου, οι παράμετροι που πρέπει να ρυθμιστούν για κάθε βάση δεδομένων.
Πρόβλημα δύο - διπλότυπα ευρετήρια
Οι βάσεις δεδομένων μας ζουν σε μονάδες SSD και τις χρησιμοποιούμε HA-Διαμόρφωση με πολλαπλά κέντρα δεδομένων, κύριο κεντρικό υπολογιστή και n- αριθμός αντιγράφων. Ο χώρος στο δίσκο είναι ένας πολύτιμος πόρος για εμάς. δεν είναι λιγότερο σημαντικό από την απόδοση και την κατανάλωση CPU. Επομένως, αφενός χρειαζόμαστε ευρετήρια για γρήγορη ανάγνωση και αφετέρου δεν θέλουμε να βλέπουμε περιττά ευρετήρια στη βάση δεδομένων, αφού καταναλώνουν χώρο και επιβραδύνουν την ενημέρωση δεδομένων.
Και τώρα, έχοντας αποκαταστήσει τα πάντα και έχοντας δει αρκετά , αποφάσισα να οργανώσω μια «μεγάλη» εκκαθάριση. Αποδείχθηκε ότι στους προγραμματιστές δεν αρέσει να διαβάζουν την τεκμηρίωση της βάσης δεδομένων. Δεν τους αρέσει πολύ. Εξαιτίας αυτού, προκύπτουν δύο τυπικά σφάλματα - ένα ευρετήριο που δημιουργήθηκε με μη αυτόματο τρόπο σε ένα πρωτεύον κλειδί και ένα παρόμοιο "μη αυτόματο" ευρετήριο σε μια μοναδική στήλη. Το γεγονός είναι ότι δεν χρειάζονται - η Postgres θα κάνει τα πάντα μόνη της. Τέτοια ευρετήρια μπορούν να διαγραφούν με ασφάλεια και έχουν εμφανιστεί διαγνωστικά για το σκοπό αυτό .
Πρόβλημα τρίτο - τεμνόμενοι δείκτες
Οι περισσότεροι αρχάριοι προγραμματιστές δημιουργούν ευρετήρια σε μία στήλη. Σταδιακά, έχοντας βιώσει διεξοδικά αυτήν την επιχείρηση, οι άνθρωποι αρχίζουν να βελτιστοποιούν τα ερωτήματά τους και να προσθέτουν πιο σύνθετα ευρετήρια που περιλαμβάνουν πολλές στήλες. Έτσι εμφανίζονται τα ευρετήρια στις στήλες A, Α + Β, Α+Β+Γ και ούτω καθεξής. Οι δύο πρώτοι από αυτούς τους δείκτες μπορούν να απορριφθούν με ασφάλεια, καθώς είναι προθέματα του τρίτου. Αυτό εξοικονομεί επίσης πολύ χώρο στο δίσκο και υπάρχουν διαγνωστικά για αυτό .
Πρόβλημα τέσσερα - ξένα κλειδιά χωρίς ευρετήρια
Το Postgres σάς επιτρέπει να δημιουργείτε περιορισμούς ξένων κλειδιών χωρίς να προσδιορίζετε ευρετήριο υποστήριξης. Σε πολλές περιπτώσεις αυτό δεν είναι πρόβλημα, και μπορεί να μην εκδηλωθεί καν... Προς το παρόν...
Το ίδιο συνέβη και με εμάς: απλά κάποια στιγμή μια εργασία, που εκτελείται σύμφωνα με ένα χρονοδιάγραμμα και εκκαθαρίζει τη βάση δεδομένων των παραγγελιών δοκιμής, άρχισε να μας «προστίθεται» από τον κύριο οικοδεσπότη. Η CPU και το IO πήγαν χαμένα, τα αιτήματα επιβραδύνθηκαν και έληξαν, η υπηρεσία ήταν πεντακόσια. Γρήγορη ανάλυση έδειξε ότι ερωτήματα όπως:
delete from <table> where id in (…)Σε αυτήν την περίπτωση, φυσικά, υπήρχε ευρετήριο ανά αναγνωριστικό στον πίνακα προορισμού και πολύ λίγες εγγραφές διαγράφηκαν ανάλογα με τη συνθήκη. Φαινόταν ότι όλα έπρεπε να λειτουργήσουν, αλλά, δυστυχώς, δεν έγινε.
Ο υπέροχος ήρθε στη διάσωση εξηγήστε αναλύστε και είπε ότι εκτός από τη διαγραφή εγγραφών στον πίνακα προορισμού, υπάρχει επίσης έλεγχος ακεραιότητας αναφοράς και σε έναν από τους σχετικούς πίνακες αυτός ο έλεγχος αποτυγχάνει διαδοχική σάρωση λόγω έλλειψης κατάλληλου δείκτη. Έτσι γεννήθηκαν τα διαγνωστικά .
Πρόβλημα πέντε – μηδενική τιμή σε ευρετήρια
Από προεπιλογή, το Postgres περιλαμβάνει μηδενικές τιμές σε ευρετήρια btree, αλλά συνήθως δεν χρειάζονται εκεί. Ως εκ τούτου, προσπαθώ επιμελώς να πετάξω αυτά τα μηδενικά (διαγνωστικά ), δημιουργώντας μερικά ευρετήρια σε μηδενικές στήλες ανά τύπο where <A> is not null. Με αυτόν τον τρόπο μπόρεσα να μειώσω το μέγεθος ενός ευρετηρίου μας από 1877 MB σε 16 KB. Και σε μία από τις υπηρεσίες, το μέγεθος της βάσης δεδομένων μειώθηκε συνολικά κατά 16% (κατά 4.3 GB σε απόλυτους αριθμούς) λόγω της εξαίρεσης των μηδενικών τιμών από τα ευρετήρια. Τεράστια εξοικονόμηση χώρου στο δίσκο με πολύ απλές τροποποιήσεις. 🙂
Πρόβλημα έκτο – έλλειψη πρωτευόντων κλειδιών
Λόγω της φύσης του μηχανισμού μια κατάσταση σαν αυτή είναι πιθανή όταν το μέγεθος του τραπεζιού σας αυξάνεται ραγδαία λόγω μεγάλου αριθμού νεκρών εγγραφών. Πίστευα αφελώς ότι αυτό δεν θα μας απειλούσε, και ότι αυτό δεν θα συμβεί στη βάση μας, γιατί εμείς, ουάου!!!, είμαστε κανονικοί προγραμματιστές... Πόσο ανόητος και αφελής ήμουν...
Μια μέρα, μια υπέροχη μετανάστευση πήρε και ενημέρωσε όλες τις εγγραφές σε έναν μεγάλο και ενεργά χρησιμοποιούμενο πίνακα. Πήραμε +100 GB στο μέγεθος του πίνακα από το μπλε. Ήταν κρίμα, αλλά οι ατυχίες μας δεν τελείωσαν εκεί. Αφού η αυτόματη σκούπα σε αυτό το τραπέζι τελείωσε 15 ώρες αργότερα, έγινε σαφές ότι η φυσική τοποθεσία δεν θα επέστρεφε. Δεν μπορούσαμε να σταματήσουμε την υπηρεσία και να κάνουμε το VACUUM FULL, οπότε αποφασίσαμε να το χρησιμοποιήσουμε . Και μετά αποδείχθηκε ότι pg_repack δεν γνωρίζει πώς να επεξεργάζεται πίνακες χωρίς πρωτεύον κλειδί ή άλλο περιορισμό μοναδικότητας και ο πίνακάς μας δεν είχε πρωτεύον κλειδί. Έτσι γεννήθηκαν τα διαγνωστικά .
Στην έκδοση της βιβλιοθήκης 0.1.5 Προστέθηκε η δυνατότητα συλλογής δεδομένων από διόγκωση πινάκων και ευρετηρίων και έγκαιρης απάντησης σε αυτά.
Προβλήματα επτά και οκτώ - ανεπαρκή ευρετήρια και αχρησιμοποίητα ευρετήρια
Τα ακόλουθα δύο διαγνωστικά είναι: и – εμφανίστηκαν στην τελική τους μορφή σχετικά πρόσφατα. Το θέμα είναι ότι δεν θα μπορούσαν απλώς να ληφθούν και να προστεθούν.
Όπως έγραψα ήδη, χρησιμοποιούμε μια διαμόρφωση με πολλά αντίγραφα και ο φόρτος ανάγνωσης σε διαφορετικούς κεντρικούς υπολογιστές είναι θεμελιωδώς διαφορετικός. Ως αποτέλεσμα, η κατάσταση αποδεικνύεται ότι ορισμένοι πίνακες και ευρετήρια σε ορισμένους κεντρικούς υπολογιστές πρακτικά δεν χρησιμοποιούνται και για ανάλυση πρέπει να συλλέξετε στατιστικά στοιχεία από όλους τους κεντρικούς υπολογιστές στο σύμπλεγμα. Αυτό είναι επίσης απαραίτητο σε κάθε κεντρικό υπολογιστή στο σύμπλεγμα· δεν μπορείτε να το κάνετε μόνο στον κύριο.
Αυτή η προσέγγιση μας επέτρεψε να εξοικονομήσουμε αρκετές δεκάδες gigabyte αφαιρώντας ευρετήρια που δεν χρησιμοποιήθηκαν ποτέ, καθώς και προσθέτοντας ευρετήρια που λείπουν σε πίνακες που χρησιμοποιούνται σπάνια.
Ως συμπέρασμα
Φυσικά, για όλα σχεδόν τα διαγνωστικά μπορείτε να διαμορφώσετε . Με αυτόν τον τρόπο, μπορείτε να εφαρμόσετε γρήγορα ελέγχους στην εφαρμογή σας, αποτρέποντας την εμφάνιση νέων σφαλμάτων και στη συνέχεια να διορθώσετε σταδιακά τα παλιά.
Ορισμένα διαγνωστικά μπορούν να εκτελεστούν σε λειτουργικές δοκιμές αμέσως μετά την κυκλοφορία των μετεγκαταστάσεων βάσης δεδομένων. Και αυτό είναι ίσως ένα από τα πιο ισχυρά χαρακτηριστικά της βιβλιοθήκης μου. Ένα παράδειγμα χρήσης μπορεί να βρεθεί στο .
Είναι λογικό να πραγματοποιούνται έλεγχοι για αχρησιμοποίητα ή λείπουν ευρετήρια, καθώς και για bloat, μόνο σε μια πραγματική βάση δεδομένων. Οι συλλεγόμενες τιμές μπορούν να καταγραφούν σε ή αποστέλλονται στο σύστημα παρακολούθησης.
Πραγματικά το ελπίζω pg-index-health θα είναι χρήσιμο και σε ζήτηση. Μπορείτε επίσης να συμβάλετε στην ανάπτυξη της βιβλιοθήκης αναφέροντας προβλήματα που βρίσκετε και προτείνοντας νέα διαγνωστικά.
Πηγή: www.habr.com