Όπως συμβαίνει με όλες τις σχεσιακές βάσεις δεδομένων, η MySQL μπορεί να αποδειχθεί ένα περίπλοκο θηρίο, το οποίο μπορεί να σταματήσει αμέσως, αφήνοντας τις εφαρμογές σας εν κινήσει και την επιχείρησή σας στη γραμμή.
Η αλήθεια είναι ότι τα κοινά λάθη αποτελούν τα περισσότερα προβλήματα απόδοσης της MySQL. Για να βεβαιωθείτε ότι ο διακομιστής MySQL διατηρείται σε κορυφαία ταχύτητα, παρέχοντας σταθερή και συνεπή απόδοση, είναι σημαντικό να εξαλείψετε αυτά τα λάθη, τα οποία συχνά κρύβονται από κάποια λεπτότητα στο φόρτο εργασίας σας ή σε μια παγίδα διαμόρφωσης.
Ευτυχώς, πολλά ζητήματα απόδοσης MySQL αποδεικνύονται ότι έχουν παρόμοιες λύσεις, καθιστώντας την αντιμετώπιση προβλημάτων και συντονίζοντας τη MySQL μια διαχειρίσιμη εργασία.
Ακολουθούν 10 συμβουλές για να αποκτήσετε εξαιρετική απόδοση από το MySQL.
Συμβουλή απόδοσης MySQL Νο. 1: Προφίλ του φόρτου εργασίας σας
Ο καλύτερος τρόπος για να καταλάβετε πώς ο διακομιστής σας ξοδεύει το χρόνο του είναι να σχεδιάσετε το φόρτο εργασίας του διακομιστή. Με τον προσδιορισμό του φόρτου εργασίας σας, μπορείτε να εκθέσετε τα πιο ακριβά ερωτήματα για περαιτέρω συντονισμό. Εδώ, ο χρόνος είναι η πιο σημαντική μέτρηση, επειδή όταν εκδίδετε ένα ερώτημα εναντίον του διακομιστή, σας ενδιαφέρει πολύ λίγο για οτιδήποτε εκτός από το πόσο γρήγορα ολοκληρώνεται.
Ο καλύτερος τρόπος για να σχεδιάσετε το φόρτο εργασίας σας είναι με ένα εργαλείο όπως το πρόγραμμα ανάλυσης ερωτημάτων του MySQL Enterprise Monitor ή το pt-query-digest από το Percona Toolkit. Αυτά τα εργαλεία συλλαμβάνουν ερωτήματα που εκτελεί ο διακομιστής και επιστρέφουν έναν πίνακα εργασιών που ταξινομούνται μειώνοντας τη σειρά του χρόνου απόκρισης, διοχετεύοντας αμέσως τις πιο ακριβές και χρονοβόρες εργασίες στην κορυφή, ώστε να μπορείτε να δείτε πού να εστιάσετε τις προσπάθειές σας.
Τα εργαλεία προφίλ φόρτου εργασίας ομαδοποιούν παρόμοια ερωτήματα, επιτρέποντάς σας να δείτε τα ερωτήματα που είναι αργά, καθώς και τα ερωτήματα που είναι γρήγορα αλλά εκτελούνται πολλές φορές.
Συμβουλή απόδοσης MySQL No. 2: Κατανοήστε τους τέσσερις βασικούς πόρους
Για να λειτουργήσει, ένας διακομιστής βάσης δεδομένων χρειάζεται τέσσερις βασικούς πόρους: CPU, μνήμη, δίσκο και δίκτυο. Εάν κάποιο από αυτά είναι αδύναμο, ακανόνιστο ή υπερφορτωμένο, τότε ο διακομιστής βάσης δεδομένων είναι πολύ πιθανό να έχει χαμηλή απόδοση.
Η κατανόηση των βασικών πόρων είναι σημαντική σε δύο συγκεκριμένους τομείς: επιλογή υλικού και αντιμετώπιση προβλημάτων.
Όταν επιλέγετε υλικό για MySQL, βεβαιωθείτε ότι τα εξαρτήματα έχουν καλή απόδοση. Εξίσου σημαντικό, ισορροπήστε τους αρκετά καλά μεταξύ τους. Συχνά, οι οργανισμοί θα επιλέξουν διακομιστές με γρήγορους επεξεργαστές και δίσκους, αλλά που είναι λιμοκτονούμενοι για μνήμη. Σε ορισμένες περιπτώσεις, η προσθήκη μνήμης είναι ένας φθηνός τρόπος αύξησης της απόδοσης κατά παραγγελίες μεγέθους, ειδικά σε φόρτους εργασίας που είναι συνδεδεμένοι στο δίσκο. Αυτό μπορεί να φαίνεται αντίθετο, αλλά σε πολλές περιπτώσεις οι δίσκοι υπερχρησιμοποιούνται επειδή δεν υπάρχει αρκετή μνήμη για τη συγκράτηση του συνόλου δεδομένων εργασίας του διακομιστή.
Ένα άλλο καλό παράδειγμα αυτής της ισορροπίας αφορά τους CPU. Στις περισσότερες περιπτώσεις, η MySQL θα έχει καλή απόδοση με γρήγορους επεξεργαστές, επειδή κάθε ερώτημα εκτελείται σε ένα νήμα και δεν μπορεί να παραλληλιστεί μεταξύ των CPU.
Όσον αφορά την αντιμετώπιση προβλημάτων, ελέγξτε την απόδοση και τη χρήση και των τεσσάρων πόρων, με προσεκτικό μάτι για να διαπιστώσετε εάν έχουν καλή απόδοση ή απλώς τους ζητείται να κάνουν πάρα πολύ δουλειά. Αυτή η γνώση μπορεί να βοηθήσει στην επίλυση προβλημάτων γρήγορα.
Συμβουλή απόδοσης MySQL No. 3: Μην χρησιμοποιείτε το MySQL ως ουρά
Ουρές και μοτίβα πρόσβασης που μοιάζουν με ουρές μπορούν να γλιστρήσουν στην εφαρμογή σας χωρίς να το γνωρίζετε. Για παράδειγμα, εάν ορίσετε την κατάσταση ενός στοιχείου έτσι ώστε μια συγκεκριμένη διαδικασία εργαζομένου να μπορεί να το διεκδικήσει πριν ενεργήσει σε αυτό, τότε δημιουργείτε ακούσια μια ουρά. Ένα χαρακτηριστικό παράδειγμα είναι η επισήμανση των μηνυμάτων ως αποστολής, η αποστολή τους και η επισήμανση ως απεσταλμένων.
Οι ουρές προκαλούν προβλήματα για δύο σημαντικούς λόγους: Σειριοποιούν το φόρτο εργασίας σας, αποτρέποντας την εκτέλεση εργασιών παράλληλα και συχνά οδηγούν σε έναν πίνακα που περιέχει εργασίες σε διαδικασία καθώς και ιστορικά δεδομένα από εργασίες που υποβλήθηκαν σε επεξεργασία εδώ και πολύ καιρό. Και οι δύο προσθέτουν καθυστέρηση στην εφαρμογή και φορτώνουν στη MySQL.
Συμβουλή απόδοσης MySQL Νο. 4: Φιλτράρετε πρώτα τα αποτελέσματα με τα φθηνότερα
Ένας πολύ καλός τρόπος βελτιστοποίησης της MySQL είναι να κάνετε πρώτα φθηνή, ανακριβή εργασία και μετά τη σκληρή, ακριβή εργασία στο μικρότερο, προκύπτον σύνολο δεδομένων.
Για παράδειγμα, ας υποθέσουμε ότι ψάχνετε κάτι μέσα σε μια δεδομένη ακτίνα ενός γεωγραφικού σημείου. Το πρώτο εργαλείο στην εργαλειοθήκη πολλών προγραμματιστών είναι ο τύπος μεγάλου κύκλου (Haversine) για τον υπολογισμό της απόστασης κατά μήκος της επιφάνειας μιας σφαίρας. Το πρόβλημα με αυτήν την τεχνική είναι ότι ο τύπος απαιτεί πολλές τριγωνομετρικές λειτουργίες, οι οποίες απαιτούν πολύ CPU. Οι υπολογισμοί μεγάλου κύκλου τείνουν να εκτελούνται αργά και κάνουν την χρήση CPU της μηχανής να ανεβαίνει στα ύψη.
Πριν εφαρμόσετε τον τύπο του μεγάλου κύκλου, μειώστε τα αρχεία σας σε ένα μικρό υποσύνολο του συνόλου και περικόψτε το σύνολο που προκύπτει σε έναν ακριβή κύκλο. Ένα τετράγωνο που περιέχει τον κύκλο (ακριβώς ή ανακριβώς) είναι ένας εύκολος τρόπος για να γίνει αυτό. Με αυτόν τον τρόπο, ο κόσμος έξω από την πλατεία δεν χτυπιέται ποτέ με όλες αυτές τις δαπανηρές λειτουργίες trig.
Συμβουλή απόδοσης MySQL Νο. 5: Μάθετε τις δύο παγίδες θανάτου κλιμάκωσης
Η επεκτασιμότητα δεν είναι τόσο ασαφής όσο πιστεύετε. Στην πραγματικότητα, υπάρχουν ακριβείς μαθηματικοί ορισμοί της επεκτασιμότητας που εκφράζονται ως εξισώσεις. Αυτές οι εξισώσεις επισημαίνουν γιατί τα συστήματα δεν κλιμακώνονται όσο πρέπει.
Ακολουθήστε τον Universal Scalability Law, έναν ορισμό που είναι βολικός για την έκφραση και τον ποσοτικό προσδιορισμό των χαρακτηριστικών κλιμάκωσης ενός συστήματος. Εξηγεί τα προβλήματα κλιμάκωσης σε σχέση με δύο βασικά κόστη: τη σειριοποίηση και το crosstalk.
Οι παράλληλες διεργασίες που πρέπει να σταματήσουν για να πραγματοποιηθεί κάτι σε σειρά περιορίζονται εγγενώς στην επεκτασιμότητά τους. Ομοίως, εάν οι παράλληλες διαδικασίες πρέπει να συνομιλούν μεταξύ τους όλη την ώρα για να συντονίζουν την εργασία τους, περιορίζουν ο ένας τον άλλον.
Αποφύγετε τη σειριοποίηση και το crosstalk και η εφαρμογή σας θα κλιμακωθεί πολύ καλύτερα. Σε τι μεταφράζεται αυτό μέσα στο MySQL; Διαφέρει, αλλά μερικά παραδείγματα θα ήταν η αποφυγή αποκλειστικών κλειδαριών στις σειρές. Οι ουρές, σημείο 3 παραπάνω, τείνουν να έχουν χαμηλή κλίμακα για αυτόν τον λόγο.
Συμβουλή απόδοσης MySQL No. 6: Μην εστιάζετε υπερβολικά στη διαμόρφωση
Τα DBA τείνουν να ξοδεύουν μεγάλο χρονικό διάστημα τροποποιώντας τις διαμορφώσεις. Το αποτέλεσμα συνήθως δεν είναι μεγάλη βελτίωση και μερικές φορές μπορεί να είναι πολύ επιζήμιο. Έχω δει πολλούς «βελτιστοποιημένους» διακομιστές που συνετρίβησαν συνεχώς, εξαντλήθηκαν η μνήμη και δεν είχαν καλή απόδοση όταν ο φόρτος εργασίας έγινε λίγο πιο έντονος.
Οι προεπιλογές που αποστέλλονται με το MySQL είναι ενιαίου μεγέθους και καθόλου ξεπερασμένες, αλλά δεν χρειάζεται να ρυθμίσετε τα πάντα. Είναι καλύτερα να διορθώσετε τις βασικές αρχές και να αλλάξετε άλλες ρυθμίσεις μόνο εάν χρειάζεται. Στις περισσότερες περιπτώσεις, μπορείτε να λάβετε το 95% της μέγιστης απόδοσης του διακομιστή, ορίζοντας περίπου 10 επιλογές σωστά. Οι λίγες καταστάσεις όπου αυτό δεν ισχύει θα είναι εξαιρετικές περιπτώσεις μοναδικές για τις περιστάσεις σας.
Στις περισσότερες περιπτώσεις, τα εργαλεία "συντονισμού" του διακομιστή δεν συνιστώνται επειδή τείνουν να παρέχουν οδηγίες που δεν έχουν νόημα για συγκεκριμένες περιπτώσεις. Μερικοί έχουν ακόμη επικίνδυνες, ανακριβείς συμβουλές που κωδικοποιούνται σε αυτές - όπως οι αναλογίες επιτυχίας της προσωρινής μνήμης και οι τύποι κατανάλωσης μνήμης. Αυτά δεν ήταν ποτέ σωστά και έχουν γίνει ακόμη λιγότερο σωστά καθώς έχει περάσει ο καιρός.
Συμβουλή απόδοσης MySQL No. 7: Προσέξτε για ερωτήματα σελιδοποίησης
Οι εφαρμογές που κάνουν σελιδοποίηση τείνουν να φέρουν τον διακομιστή στα γόνατά του. Στην εμφάνιση μιας σελίδας αποτελεσμάτων, με έναν σύνδεσμο για μετάβαση στην επόμενη σελίδα, αυτές οι εφαρμογές συνήθως ομαδοποιούνται και ταξινομούνται με τρόπους που δεν μπορούν να χρησιμοποιήσουν ευρετήρια και χρησιμοποιούν ΟΡΙΟ και αντισταθμίζεται που αναγκάζουν τον διακομιστή να κάνει πολλή δουλειά δημιουργώντας και μετά απορρίπτοντας σειρές.
Βελτιστοποιήσεις μπορούν συχνά να βρεθούν στο ίδιο το περιβάλλον εργασίας χρήστη. Αντί να εμφανίζετε τον ακριβή αριθμό σελίδων στα αποτελέσματα και τους συνδέσμους σε κάθε σελίδα ξεχωριστά, μπορείτε απλώς να εμφανίσετε έναν σύνδεσμο προς την επόμενη σελίδα. Μπορείτε επίσης να αποτρέψετε τα άτομα να μεταβαίνουν σε σελίδες πολύ μακριά από την πρώτη σελίδα.
Από την πλευρά του ερωτήματος, αντί να το χρησιμοποιήσετε ΟΡΙΟ με αντισταθμίζεται, μπορείτε να επιλέξετε μία ακόμη σειρά από ό, τι χρειάζεστε, και όταν ο χρήστης κάνει κλικ στο σύνδεσμο "επόμενη σελίδα", μπορείτε να ορίσετε αυτήν την τελική σειρά ως σημείο εκκίνησης για το επόμενο σύνολο αποτελεσμάτων. Για παράδειγμα, εάν ο χρήστης είδε μια σελίδα με τις σειρές 101 έως 120, θα μπορούσατε επίσης να επιλέξετε τη σειρά 121. για να αποδώσετε την επόμενη σελίδα, θα ζητήσετε από τον διακομιστή για σειρές μεγαλύτερες ή ίσες με 121, όριο 21.
Συμβουλή απόδοσης MySQL No. 8: Αποθηκεύστε τα στατιστικά με ανυπομονησία, προειδοποιήστε απρόθυμα
Η παρακολούθηση και η προειδοποίηση είναι απαραίτητα, αλλά τι συμβαίνει στο τυπικό σύστημα παρακολούθησης; Αρχίζει να στέλνει ψευδώς θετικά και οι διαχειριστές του συστήματος ρυθμίζουν κανόνες φιλτραρίσματος email για να σταματήσουν τον θόρυβο. Σύντομα το σύστημα παρακολούθησής σας είναι εντελώς άχρηστο.
Θέλω να σκεφτώ την παρακολούθηση με δύο τρόπους: καταγραφή μετρήσεων και ειδοποίηση. Είναι πολύ σημαντικό να καταγράψετε και να αποθηκεύσετε όλες τις μετρήσεις που μπορείτε, επειδή θα χαρείτε να τις έχετε όταν προσπαθείτε να καταλάβετε τι άλλαξε στο σύστημα. Κάποια μέρα, θα εμφανιστεί ένα παράξενο πρόβλημα και θα λατρέψετε τη δυνατότητα να δείξετε ένα γράφημα και να δείξετε μια αλλαγή στον φόρτο εργασίας του διακομιστή.
Αντίθετα, υπάρχει μια τάση να προειδοποιείτε πάρα πολύ. Οι άνθρωποι συχνά προειδοποιούν για πράγματα όπως το buffer ratio ratio ή τον αριθμό των προσωρινών πινάκων που δημιουργούνται ανά δευτερόλεπτο. Το πρόβλημα είναι ότι δεν υπάρχει καλό όριο για μια τέτοια αναλογία. Το σωστό όριο δεν διαφέρει μόνο από διακομιστή σε διακομιστή, αλλά από ώρα σε ώρα καθώς ο φόρτος εργασίας σας αλλάζει.
Ως αποτέλεσμα, προειδοποιήστε με φειδώ και μόνο σε συνθήκες που υποδηλώνουν ένα συγκεκριμένο, εφικτό πρόβλημα. Η χαμηλή αναλογία επίσκεψης buffer δεν είναι ενεργή, ούτε υποδηλώνει πραγματικό πρόβλημα, αλλά ένας διακομιστής που δεν ανταποκρίνεται σε μια προσπάθεια σύνδεσης είναι ένα πραγματικό πρόβλημα που πρέπει να επιλυθεί.
Συμβουλή απόδοσης MySQL No. 9: Μάθετε τους τρεις κανόνες ευρετηρίου
Η ευρετηρίαση είναι πιθανώς το πιο παρεξηγημένο θέμα στις βάσεις δεδομένων, επειδή υπάρχουν τόσοι πολλοί τρόποι για να μπερδευτείτε σχετικά με το πώς λειτουργούν τα ευρετήρια και πώς τα χρησιμοποιεί ο διακομιστής. Χρειάζεται πολλή προσπάθεια για να κατανοήσουμε πραγματικά τι συμβαίνει.
Τα ευρετήρια, όταν έχουν σχεδιαστεί σωστά, εξυπηρετούν τρεις σημαντικούς σκοπούς σε έναν διακομιστή βάσης δεδομένων:
- Τα ευρετήρια επιτρέπουν στον διακομιστή να βρει ομάδες γειτονικών γραμμών αντί μεμονωμένων σειρών. Πολλοί άνθρωποι πιστεύουν ότι ο σκοπός ενός ευρετηρίου είναι η εύρεση μεμονωμένων σειρών, αλλά η εύρεση μεμονωμένων σειρών οδηγεί σε τυχαίες λειτουργίες δίσκου, κάτι που είναι αργό. Είναι πολύ καλύτερο να βρείτε ομάδες σειρών, όλες ή περισσότερες από τις οποίες είναι ενδιαφέρουσες, παρά να βρείτε σειρές μία κάθε φορά.
- Τα ευρετήρια επιτρέπουν στον διακομιστή να αποφύγει την ταξινόμηση διαβάζοντας τις σειρές με την επιθυμητή σειρά. Η ταξινόμηση είναι δαπανηρή. Η ανάγνωση σειρών στην επιθυμητή σειρά είναι πολύ πιο γρήγορη.
- Τα ευρετήρια επιτρέπουν στον διακομιστή να ικανοποιεί ολόκληρα ερωτήματα μόνο από το ευρετήριο, αποφεύγοντας την ανάγκη πρόσβασης στον πίνακα καθόλου. Αυτό είναι γνωστό ως ευρετήριο κάλυψης ή ερώτημα μόνο για ευρετήριο.
Εάν μπορείτε να σχεδιάσετε τα ευρετήρια και τα ερωτήματά σας για να εκμεταλλευτείτε αυτές τις τρεις ευκαιρίες, μπορείτε να κάνετε τα ερωτήματά σας πολλές παραγγελίες μεγέθους γρηγορότερα.
Συμβουλή απόδοσης MySQL No. 10: Αξιοποιήστε την εμπειρία των συνομηλίκων σας
Μην προσπαθήσετε να το πάτε μόνοι. Εάν προβληματίζεστε για ένα πρόβλημα και κάνετε ό, τι φαίνεται λογικό και λογικό για εσάς, αυτό είναι υπέροχο. Αυτό θα λειτουργήσει περίπου 19 φορές από τα 20. Την άλλη φορά, θα κατεβείτε μια τρύπα κουνελιού που θα είναι πολύ δαπανηρή και χρονοβόρα, ακριβώς επειδή η λύση που προσπαθείτε φαίνεται να έχει πολύ νόημα.
Δημιουργήστε ένα δίκτυο πόρων που σχετίζονται με τη MySQL - και αυτό ξεπερνά τα σύνολα εργαλείων και τους οδηγούς αντιμετώπισης προβλημάτων. Υπάρχουν μερικοί εξαιρετικά πεπειραμένοι άνθρωποι που παραμονεύουν σε λίστες αλληλογραφίας, φόρουμ, ιστότοπους Q&A και ούτω καθεξής. Τα συνέδρια, οι εμπορικές εκθέσεις και οι τοπικές εκδηλώσεις ομάδων χρηστών παρέχουν πολύτιμες ευκαιρίες για την απόκτηση πληροφοριών και την οικοδόμηση σχέσεων με συναδέλφους που μπορούν να σας βοηθήσουν.
Για όσους αναζητούν εργαλεία για να συμπληρώσουν αυτές τις συμβουλές, μπορείτε να δείτε τον Οδηγό διαμόρφωσης Percona για MySQL, τον Σύμβουλο ερωτήσεων Percona για MySQL και τα πρόσθετα παρακολούθησης Percona. (Σημείωση: Θα χρειαστεί να δημιουργήσετε έναν λογαριασμό Percona για να αποκτήσετε πρόσβαση σε αυτούς τους δύο πρώτους συνδέσμους. Είναι δωρεάν.) Ο οδηγός διαμόρφωσης μπορεί να σας βοηθήσει να δημιουργήσετε ένα βασικό αρχείο my.cnf για έναν νέο διακομιστή που είναι ανώτερος από τα δείγματα αρχείων που αποστέλλονται με το υπηρέτης. Ο σύμβουλος ερωτήσεων θα αναλύσει το SQL σας για να εντοπίσει δυνητικά κακά μοτίβα όπως ερωτήματα σελιδοποίησης (No. 7). Τα Πρόσθετα παρακολούθησης Percona είναι ένα σύνολο πρόσθετων παρακολούθησης και γραφημάτων που σας βοηθούν να αποθηκεύετε στατιστικά με ανυπομονησία και να ειδοποιείτε απρόθυμα (No. 8). Όλα αυτά τα εργαλεία είναι ελεύθερα διαθέσιμα.
