A short guide on how to install mysqltuner on directadmin powered server.
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.
To install the script on your server run this commands as root
wget -O /usr/local/sbin/mysqltuner mysqltuner.pl
chmod 710 /usr/local/sbin/mysqltuner
To run th script after the installation finished, run this:
mysqltuner
You will see something similar to:
[root@server ~]# mysqltuner
>> MySQLTuner 1.4.6 - Major Hayden
>> 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.44-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED -InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 2G (Tables: 3759)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 2
-------- 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: 4s (22 q [5.500 qps], 8 conn, TX: 11K, RX: 1K)
[--] Reads / Writes: 66% / 34%
[--] Binary logging is disabled
[--] Total buffers: 1.4G global + 18.7M per thread (70 max threads)
[OK] Maximum reached memory usage: 2.7G (70% of installed RAM)
[OK] Maximum possible memory usage: 2.7G (70.90% of installed RAM)
[OK] Slow queries: 0% (0/22)
[OK] Highest usage of available connections: 1% (1/70)
[!!] Aborded connections: 12.50% (1/8)
[!!] Key buffer used: 18.2% (195M used / 1B cache)
[OK] Key buffer size / total MyISAM indexes: 1.0G/377.2M
[!!] Read Key buffer hit rate: 33.3% (30 cached / 20 reads)
[!!] Write Key buffer hit rate: 0.0% (10 cached / 10 writes)
[!!] Query cache efficiency: 0.0% (0 cached / 6 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 87% (1 created / 8 connections)
[OK] Table cache hit rate: 1400% (28 open / 2 opened)
[OK] Open file limit used: 0% (25/65K)
[OK] Table locks acquired immediately: 100% (41 immediate / 41 locks)
[!!] Connections aborted: 12%
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is disabled.[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine
-------- Replication Metrics -------------------------------------------------
[--] No replication setup for this server.
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate unclosed connexions and network issues
Your applications are not closing MySQL connections properly
Variables to adjust:
key_buffer_size (~ 195M)
query_cache_limit (> 32M, or use smaller result sets)
[root@server ~]#
For more information please see http://mysqltuner.com/