MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.
Compatibility:
- MySQL 3.23, 4.0, 4.1, 5.0, 5.1 (full support)
- MySQL 5.4 (not fully tested, partially supported)
- MySQL 6.0 (partial support)
- MariaDB 10.x (partial support)
- Perl 5.6 or later (with perl-doc package)
- Unix/Linux based operating system (tested on Linux, BSD variants, and Solaris variants)
- Windows is not supported at this time
- Unrestricted read access to the MySQL server (OS root access recommended for MySQL < 5.0)
Download the mysqltuner script :
- wget http://mysqltuner.com/mysqltuner.pl
- In order to run it, we must make it executable:
chmod +x mysqltuner.pl - Afterwards, we can run it. You need your MySQL root password for it:
./mysqltuner.pl
Result :
- root@test[~]# ./mysqltuner.pl
- >> MySQLTuner 1.4.4 – Major Hayden <major@mhtx.net>
- >> 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
- [!!] Switch to 64-bit OS – MySQL cannot currently use all of your RAM
Storage Engine Statistics
- [–] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
- [–] Data in MyISAM tables: 762M (Tables: 2237)
- [–] Data in InnoDB tables: 445M (Tables: 1078)
- [–] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
- [!!] Total fragmented tables: 418
- ERROR 1102 (42000) at line 1: Incorrect database name ‘#mysql50#tesing.bak’
Security Recommendations
- [OK] There is no anonymous account in all database users
- [OK] All database users have passwords assigned
- [!!] There is not basic password file list !
Performance Metrics
- [–] Up for: 4d 23h 45m 49s (14M q [33.744 qps], 274K conn, TX: 41B, RX: 3B)
- [–] Reads / Writes: 88% / 12%
- [–] Total buffers: 168.0M global + 2.7M per thread (151 max threads)
- [OK] Maximum possible memory usage: 573.8M (7% of installed RAM)
- [OK] Slow queries: 0% (23/14M)
- [OK] Highest usage of available connections: 27% (42/151)
- [OK] Key buffer size / total MyISAM indexes: 8.0M/140.4M
- [OK] Key buffer hit rate: 97.4% (85M cached / 2M reads)
- [!!] Query cache is disabled
- [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
- [!!] Joins performed without indexes: 10434
- [!!] Temporary tables created on disk: 56% (436K on disk / 771K total)
- [!!] Thread cache is disabled
- [!!] Table cache hit rate: 0% (400 open / 246K opened)
- [OK] Open file limit used: 5% (549/10K)
- [OK] Table locks acquired immediately: 99% (13M immediate / 13M locks)
InnoDB Metrics
- [–] InnoDB is enabled.
- [–] InnoDB BufferPool Size :128.0M
- [–] InnoDB BufferPool Inst :1
- [!!] InnoDB buffer pool / data size: 128.0M/445.2M
- [OK] InnoDB buffer pool instances: 1
- [OK] InnoDB log waits: 0
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
- Set thread_cache_size to 4 as a starting value
Variables to adjust:
- 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 (>= 445M)
- root@test[~]#
