[email protected] [/]# perl mysqltuner.pl
>> MySQLTuner 1.5.1 - Major Hayden <
[email protected]>
>> Bug reports, feature requests, and downloads at
http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.42-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 9G (Tables: 7613)
[--] Data in InnoDB tables: 952M (Tables: 6438)
[--] Data in MEMORY tables: 0B (Tables: 40)
[!!] Total fragmented tables: 746
-------- Security Recommendations -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] User '
[email protected]' has user name as password.
[!!] User '
[email protected]%' hasn't specific host restriction.
[!!] User '
[email protected]%' hasn't specific host restriction.
[!!] User '
[email protected]%' hasn't specific host restriction.
[--] There is 605 basic passwords in the list.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 7h 20m 19s (114M q [250.309 qps], 4M conn, TX: 266B, RX: 28B)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is disabled
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum reached memory usage: 544.8M (3.42% of installed RAM)
[OK] Maximum possible memory usage: 583.2M (3.67% of installed RAM)
[OK] Slow queries: 0% (703/114M)
[!!] Highest connection usage: 90% (137/151)
[OK] Aborted connections: 0.01% (564/4319956)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (26K temp sorts / 10M sorts)
[!!] Joins performed without indexes: 424659
[!!] Temporary tables created on disk: 61% (3M on disk / 5M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 26M opened)
[OK] Open file limit used: 0% (449/50K)
[OK] Table locks acquired immediately: 99% (99M immediate / 99M locks)
-------- MyISAM Metrics -----------------------------------------------------
[OK] Key buffer used: 99.2% (8M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.5G
[OK] Read Key buffer hit rate: 98.3% (1B cached / 17M reads)
[!!] Write Key buffer hit rate: 73.6% (52M cached / 13M writes)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/953.0M
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB Used buffer: 100.00% (8192 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 96.72% (2082148445 hits/ 2152841006 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1886234 writes)
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB 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
Set up a Secure Password for
[email protected] ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for
[email protected]% to
[email protected]
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
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
Set thread_cache_size to 4 as a starting value
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
http://bit.ly/1mi7c4C
Beware that open_files_limit (50000) variable
should be greater that table_open_cache ( 400)
Variables to adjust:
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_open_cache (> 400)
innodb_buffer_pool_size (>= 952M) if possible.
