Βελτιστοποίηση βάσης δεδομένων MySQL με το mysqltuner.pl

Το MySQLTuner είναι ένα Script γραμμένο στην γλώσσα perl το οποίο με βάση τα στατιστικά που έχουν μαζέψει τα logs της MySQL δίνει κάποιες σχετικά ασφαλείς υποδείξεις για το τι τιμές μπορούν να αλλαχτούν στο αρχείο ρυθμίσεων της MySQL για παραπάνω απόδοση. Αρχικά το κατεβάζουμε και το κάνουμε εκτελέσιμο:
wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl
Έπειτα το τρέχουμε:
./mysqltuner.pl
Καλό είναι να τρέχει λίγο καιρό η MySQL, όσο περισσότερο, τόσο πιο ακριβείς υποδείξεις θα πάρουμε, αντίθετα αν προσπαθήσουμε να το τρέξουμε μετά από ένα restart της MySQL, θα πάρουμε μήνυμα "MySQL started within last 24 hours – recommendations may be inaccurate."
>>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.68-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 5367)
[--] Data in InnoDB tables: 400M (Tables: 4079)
[--] Data in MEMORY tables: 0B (Tables: 33)
[!!] Total fragmented tables: 4684

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15d 10h 40m 6s (68M q [51.568 qps], 1M conn, TX: 794B, RX: 12B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 78.0M global + 7.5M per thread (800 max threads)
[OK] Maximum possible memory usage: 5.9G (76% of installed RAM)
[OK] Slow queries: 0% (1K/68M)
[OK] Highest usage of available connections: 8% (66/800)
[OK] Key buffer size / total MyISAM indexes: 36.0M/229.4M
[OK] Key buffer hit rate: 98.5% (572M cached / 8M reads)
[OK] Query cache efficiency: 70.8% (38M cached / 54M selects)
[!!] Query cache prunes per day: 723587
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 2M sorts)
[!!] Joins performed without indexes: 109545
[!!] Temporary tables created on disk: 31% (1M on disk / 4M total)
[OK] Thread cache hit rate: 99% (66 created / 1M connections)
[!!] Table cache hit rate: 0% (1K open / 83M opened)
[OK] Open file limit used: 3% (1K/48K)
[OK] Table locks acquired immediately: 99% (26M immediate / 26M locks)
[!!] InnoDB data size / buffer pool: 400.4M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 1024)
    innodb_buffer_pool_size (>= 400M)
Θα πρέπει να διαβάσετε προσεκτικά ότι αποτελέσματα εμφανίζει και ειδικά τις προτάσεις στο τέλος που υποδεικνύουν ακριβώς ποιες μεταβλητές στην ενότητα [mysqld] στο αρχείο ρυθμίσεων πρέπει να αλλάξετε (/etc/mysql/my.cnf σε Debian και Ubuntu). Να θυμάστε ότι μετά από αλλαγές στο my.cnf θα πρέπει να κάνετε restart το service της MySQL. Μετά τις αλλαγές μπορείτε να επιστρέψετε μετά από 2 ή 3 μέρες και να ξανατρέξετε το mysqltuner.pl ώστε να δείτε αν υπάρχουν επιπλέον προτάσεις από τις αρχικές, έτσι κάνετε βήμα προς βήμα βελτιστοποίηση των ρυθμίσεων. Δώστε ιδιαίτερη έμφαση στο πεδίο "Query cache efficiency" και σημειώνετε την μεταβολή του ποσοστού. Αυτή η μέθοδος είναι ιδανική για εκμάθηση, στην περίπτωση που βρίσκατε τυχαία στο Internet κάποια configurations με παραπλήσια χαρακτηριστικά hosting με τα δικά σας και είχατε να αλλάξετε ένα κατεβατό από μεταβλητές που θα μπορούσε να σας μπερδέψει ή δεν θα σας άφηνε χρόνο να διαβάσετε το τι ορίζει η κάθε αλλαγή. Σημείωση: Ό,τι αλλαγές κι αν κάνετε στο my.cnf αρχείο ρυθμίσεων της MySQL σας, εμείς δεν φέρουμε καμμιά ευθύνη αφενός κι αφετέρου σας συστήνουμε ακράδαντα να κάνετε backup τόσο του αρχείου ρυθμίσεων πριν τις αλλαγές όσο και ολόκληρης της βάσης.