Ο ιστότοπος είναι αργός: τι να κοιτάξετε σε MySQL και PostgreSQL

Το «ο ιστότοπος είναι αργός» σχεδόν πάντα καταλήγει στη βάση δεδομένων. Ο έλεγχος παίρνει λίγα λεπτά αν γίνει με τη σωστή σειρά: πρώτα ό,τι φαίνεται κατά την ίδια την επιβράδυνση, μετά ό,τι συσσωρεύεται επί εβδομάδες.

Κοιτάξτε ενώ συμβαίνει το πρόβλημα

Ο βασικός κανόνας: η πιο πολύτιμη πληροφορία είναι διαθέσιμη ακριβώς όσο όλα είναι αργά. Η πρώτη εντολή για MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

Για PostgreSQL:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

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

Συνδέσεις

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

Αν το Max_used_connections πλησιάζει το max_connections, η εφαρμογή παίρνει κατά περιόδους άρνηση «too many connections». Η αύξηση του ορίου δεν είναι το πρώτο που κάνετε: κάθε σύνδεση παίρνει μνήμη, και η αύξηση σε διακομιστή που ήδη στερείται μνήμης απλώς επιταχύνει το ταξίδι στον χώρο ανταλλαγής. Πρώτα βρείτε γιατί δεν αποδεσμεύονται οι συνδέσεις: συνήθως ένα μακρύ ερώτημα ή η απουσία δεξαμενής συνδέσεων στην πλευρά της εφαρμογής.

Αξίζει επίσης να το δείτε από την άλλη πλευρά: μια απότομη άνοδος των συνδέσεων μπορεί να προκύπτει όχι από αυξανόμενη δημοτικότητα αλλά από bots που σφυροκοπούν μια βαριά σελίδα — μια αναζήτηση, ένα φίλτρο καταλόγου, μια γεννήτρια αναφορών.

Αργά ερωτήματα

Χωρίς αρχείο αργών ερωτημάτων δεν υπάρχει πού να πάτε παραπέρα. MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Μία μέρα αργότερα:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL, στο postgresql.conf:

log_min_duration_statement = 1000

Έπειτα η επέκταση pg_stat_statements, που δείχνει απευθείας τον συνολικό χρόνο ανά ερώτημα:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Προσοχή: σημασία δεν έχει το μακρύτερο ερώτημα αλλά εκείνο με τον μεγαλύτερο συνολικό χρόνο. Ένα ερώτημα 50 χιλιοστών του δευτερολέπτου που τρέχει χίλιες φορές το λεπτό κάνει περισσότερη ζημιά από μια αναφορά δέκα δευτερολέπτων μία φορά τη μέρα.

Η ρύθμιση που ξεχνιέται πιο συχνά

Για MySQL και MariaDB είναι το μέγεθος της δεξαμενής buffer του InnoDB. Η προεπιλογή είναι 128 μεγαμπάιτ και δεν έχει αλλάξει εδώ και δεκαετίες. Σε διακομιστή όπου η βάση καταλαμβάνει αρκετά γιγαμπάιτ, αυτό σημαίνει συνεχή ανάγνωση από τον δίσκο αντί από τη μνήμη.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Λογικός γνώμονας για αποκλειστικό διακομιστή βάσης είναι το μισό της RAM· για διακομιστή που τρέχει και διακομιστή ιστού και PHP, το ένα τέταρτο, με το βλέμμα στο να μην αρχίσει η ανταλλαγή. Αυτή η μία ρύθμιση δίνει συνήθως περισσότερα από μια εβδομάδα βελτιστοποίησης ερωτημάτων.

Δύο πράγματα που τρώνε σιωπηλά τον δίσκο

Τα δυαδικά αρχεία της MySQL. Χρειάζονται για αναπαραγωγή και ανάκτηση σε χρονικό σημείο, μεγαλώνουν συνεχώς και από προεπιλογή ενίοτε δεν αφαιρούνται ποτέ. Για έλεγχο:

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

Αν δεν έχετε αναπαραγωγή και δεν χρησιμοποιείτε ανάκτηση σε χρονικό σημείο, ορίστε τη διατήρηση σε μερικές λογικές μέρες.

Αρχεία WAL της PostgreSQL με ξεχασμένη υποδοχή αναπαραγωγής. Λιγότερο γνωστή και πιο επικίνδυνη περίπτωση: αν υπάρχει υποδοχή αναπαραγωγής αλλά ο καταναλωτής της δεν συνδέεται πια, η PostgreSQL είναι υποχρεωμένη να κρατά κάθε αρχείο από τη στιγμή της αποσύνδεσης — και θα τα κρατά μέχρι να τελειώσει ο δίσκος.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

Μια ανενεργή υποδοχή που δεν χρειάζεται κανείς πρέπει να καταργηθεί. Αυτή είναι μία από τις λίγες καταστάσεις όπου μια βάση δεδομένων οδηγεί εγγυημένα έναν δίσκο στο μηδέν.

Μέγεθος και αύξηση

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

Χρήσιμος δεν είναι τόσο ο τρέχων αριθμός όσο ο ρυθμός με τον οποίο αλλάζει. Μια βάση που διπλασιάστηκε σε έναν μήνα χωρίς αντίστοιχη άνοδο της κίνησης σημαίνει συνήθως ότι κάποιος πίνακας καταγραφής γράφεται και δεν καθαρίζεται ποτέ — συνεδρίες, ιστορικό εργασιών, το ημερολόγιο συμβάντων ενός πρόσθετου.

Ασφάλεια σε δύο γραμμές

Μιας και έχετε ανοιχτή την κονσόλα της βάσης, αξίζει να ελέγξετε δύο πράγματα. Πρώτον, αν η βάση είναι προσβάσιμη από έξω (ss -tulpn | grep 3306): σχεδόν ποτέ δεν πρέπει να βλέπει προς τα έξω. Δεύτερον, λογαριασμούς που μπορούν να συνδεθούν από οποιαδήποτε διεύθυνση:

mysql -e "SELECT user, host FROM mysql.user"

Εγγραφές με host = '%' σημαίνουν «συνδέσεις από παντού». Αν είναι ανοιχτή και η θύρα, ένας κωδικός είναι το μόνο που στέκεται ανάμεσα στη βάση και στο διαδίκτυο.

Αντίγραφα ασφαλείας

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

Για την καθημερινή παρακολούθηση αρκούν τρεις αριθμοί: το πλήθος των συνδέσεων, ο αριθμός των αργών ερωτημάτων ανά ημέρα και το μέγεθος της βάσης. Και οι τρεις αλλάζουν αργά και προβλέψιμα, και κάθε απότομη κίνηση σε έναν από αυτούς αξίζει μια ματιά. Πώς μοιάζουν σε μία σελίδα το δείχνει η επίδειξη παρακάτω.