MySQL Tuner on FreeBSD 9.2

I just know if mysqltuner is available via ports.

Quick heads up :

# cd /usr/ports/databases/mysqltuner

# make install

# whereis mysqltuner.pl
mysqltuner.pl: /usr/local/bin/mysqltuner.pl

# /usr/local/bin/mysqltuner.pl

>>  MySQLTuner 1.2.0 – Major Hayden <[email protected]>
>>  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:

——– General Statistics ————————————————–
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] InnoDB is enabled but isn’t being used
[OK] Total fragmented tables: 0

——– Security Recommendations  ——————————————-
[OK] All database users have passwords assigned

——– Performance Metrics ————————————————-
[–] Up for: 1h 21m 43s (54 q [0.011 qps], 12 conn, TX: 28K, RX: 1K)
[–] Reads / Writes: 84% / 16%
[–] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (5% of installed RAM)
[OK] Slow queries: 0% (0/54)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/98.0K
[!!] Key buffer hit rate: 50.0% (2 cached / 1 reads)
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 6 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 85% (41 open / 48 opened)
[OK] Open file limit used: 0% (48/11K)
[OK] Table locks acquired immediately: 100% (50 immediate / 50 locks)
[!!] Connections aborted: 25%

——– Recommendations —————————————————–
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
MySQL started within last 24 hours – recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)

Interesting…. as I see this job is not just set and forget but must be running regularly to identified a running process.

P.S : my.cnf location can be seen from ps command

# ps -ax | grep mysql
96928 ??  Is       0:00.01 /bin/sh /usr/local/bin/mysqld_safe –defaults-extra-file=/var/db/mysql/my.cnf –user=mysql –datadir=/var/db/mysql –pid-file=/var/db/mysql/
97024 ??  I        0:00.91 /usr/local/libexec/mysqld –defaults-extra-file=/var/db/mysql/my.cnf –basedir=/usr/local –datadir=/var/db/mysql –plugin-dir=/usr/local/li

or if its not exist you can copy one from share

# ls /usr/local/share/mysql | grep my-
my-huge.cnf
my-innodb-heavy-4G.cnf
my-large.cnf
my-medium.cnf
my-small.cnf