Using MySQLTuner to optimize MySQL Performance

The first step of MySQL optimization is from MySQLTuner.

MySQLTuner official website: http://mysqltuner.com/

Download MySQLTuner

# Wget http://mysqltuner.pl/-O mysqltuner. pl
Execute 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: 13 M (Tables: 298)
[--] Data in InnoDB tables: 42 M (Tables: 226)
[--] Data in MEMORY tables: 5 M (Tables: 7)
[!] Total fragmented table: 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 18 h 5 m 33 s (19 M q [5.330 qps], 474 K conn, TX: 71G, RX: 31G)
[--] Reads/Writes: 95%/5%
[--] Binary logging is disabled
[--] Total buffers: 832.0 M global + 9.9 M per thread (512 max threads)
[OK] Maximum reached memory usage: 1.5 GB (43.44% of installed RAM)
[!] Maximum possible memory usage: 5.8 GB (167.05% of installed RAM)
[OK] Slow queries: 0% (0/19 M)
[OK] Highest usage of available connections: 13% (71/512)
[OK] Aborted connections: 0.00% (1/474283)
[OK] Query cache efficiency: 47.1% (15 M cached/32 M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts/104 K sorts)
[!] Temporary tables created on disk: 90% (104 K on disk/115 K total)
[OK] Thread cache hit rate: 99% (162 created/474 K connections)
[OK] Table cache hit rate: 41% (258 open/616 opened)
[OK] Open file limit used: 9% (244/2 K)
[OK] Table locks acquired immediately: 99% (2 M immediate/2 M locks)
 
-------- MyISAM Metrics ------------------------------------------------------
[!] Key buffer used: 24.6% (4 M used/16 M cache)
[OK] Key buffer size/total MyISAM indexes: 16.0 M/3.4 M
[OK] Read Key buffer hit rate: 100.0% (1 M cached/651 reads)
[!] Write Key buffer hit rate: 29.8% (95 K cached/67 K writes)
 
-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool/data size: 128.0 M/42.3 M
[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 (> 32 M)
Max_heap_table_size (> 32 M)

Follow the "Recommendations" section.