借助 MySQLTuner 優化 MySQL 性能

MySQLTuner 是一個 Perl 腳本,可以用來分析您的 MySQL 性能,並且基於收集到的信息給出相應的優化建議,下面我們來看一篇MySQL優化從的教程。

從MySQLTuner開始MySQL優化的第一步。

MySQLTuner官網：http://mysqltuner.com/

下載MySQLTuner

# wget http://mysqltuner.pl/ -O mysqltuner.pl
執行MySQLTuner

# perl mysqltuner.pl
 >>  MySQLTuner 1.6.5 - Major Hayden
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.44-MariaDB
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 13M (Tables: 298)
[--] Data in InnoDB tables: 42M (Tables: 226)
[--] Data in MEMORY tables: 5M (Tables: 7)
[!!] Total fragmented tables: 245
 
-------- Security Recommendations  -------------------------------------------
[!!] User '@ip28.hichina.com' is an anonymous account.
[!!] User '@localhost' is an anonymous account.
[!!] User '@ip28.hichina.com' has no password set.
[!!] User '@localhost' has no password set.
[!!] User '@ip28.hichina.com' has user name as password.
[!!] User '@localhost' has user name as password.
[!!] There is no basic password file list!
 
-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 41d 18h 5m 33s (19M q [5.330 qps], 474K conn, TX: 71G, RX: 31G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Total buffers: 832.0M global + 9.9M per thread (512 max threads)
[OK] Maximum reached memory usage: 1.5G (43.44% of installed RAM)
[!!] Maximum possible memory usage: 5.8G (167.05% of installed RAM)
[OK] Slow queries: 0% (0/19M)
[OK] Highest usage of available connections: 13% (71/512)
[OK] Aborted connections: 0.00%  (1/474283)
[OK] Query cache efficiency: 47.1% (15M cached / 32M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 104K sorts)
[!!] Temporary tables created on disk: 90% (104K on disk / 115K total)
[OK] Thread cache hit rate: 99% (162 created / 474K connections)
[OK] Table cache hit rate: 41% (258 open / 616 opened)
[OK] Open file limit used: 9% (244/2K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
 
-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 24.6% (4M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/3.4M
[OK] Read Key buffer hit rate: 100.0% (1M cached / 651 reads)
[!!] Write Key buffer hit rate: 29.8% (95K cached / 67K writes)
 
-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/42.3M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 42.16% (3453 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 100.00% (143223128 hits/ 143225011 total)
[OK] InnoDB Write log efficiency: 99.34% (48546483 hits/ 48867619 total)
[OK] InnoDB log waits: 0.00% (0 waits / 321136 writes)
 
-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.
 
-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.
 
-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Remove Anonymous User accounts - there are 2 anonymous accounts.
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)

關注「Recommendations」的部分。