Download the mysqltuner script from github:

[html]
wget http://github.com/major/MySQLTuner-perl/archive/master.zip
[/html]

Unzip the file and then run the perl script:

[html]
perl mysqltuner.pl
[/html]

This will give an out put similar to the below which includes recommendations at the end:

[html]
Use of implicit split to @_ is deprecated at mysqltuner.pl line 3243 (#1)
(D deprecated) It makes a lot of work for the compiler when you clobber
a subroutine’s argument list, so it’s better if you assign the results
of a split() explicitly to an array (or list).

>> MySQLTuner 1.6.12 – Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with ‘–help’ for additional options and output filtering
[OK] Logged in using credentials.

[–] Skipped version check for MySQLTuner script
Warning: bad ps syntax, perhaps a bogus ‘-‘? See http://procps.sf.net/faq.html
Warning: bad ps syntax, perhaps a bogus ‘-‘? See http://procps.sf.net/faq.html
[!!] Your MySQL version 5.0.32-Debian_7etch6-log is EOL software! Upgrade soon!
[OK] Operating on 32-bit architecture with less than 2GB RAM

——– Storage Engine Statistics —————————————————————–
[–] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 163M (Tables: 105)
[!!] InnoDB is enabled but isn’t being used
[!!] Total fragmented tables: 1

——– Security Recommendations ——————————————————————
[OK] There are no anonymous accounts for any database users
[!!] No more password checks for MySQL version <=5.1 [!!] MySQL version <=5.1 are deprecated and end of support. -------- CVE Security Recommendations -------------------------------------------------------------- [!!] CVE-2007-2691 : Candidate [!!] CVE-2007-2691 : Candidate [!!] CVE-2007-2692 : Candidate [!!] CVE-2007-2692 : Candidate [!!] CVE-2007-3780 : Candidate [!!] CVE-2007-3781 : Candidate [!!] CVE-2007-3782 : Candidate [!!] CVE-2007-5969 : Candidate [!!] CVE-2007-5969 : Candidate [!!] CVE-2007-6303 : Candidate [!!] CVE-2007-6304 : Candidate [!!] CVE-2008-3963 : Candidate [!!] CVE-2009-4028 : Candidate [!!] CVE-2009-4484 : Candidate [!!] CVE-2009-4484 : Candidate [!!] CVE-2009-5026 : Candidate [!!] CVE-2010-3833 : Candidate [!!] CVE-2010-3834 : Candidate [!!] CVE-2010-3836 : Candidate [!!] CVE-2010-3837 : Candidate [!!] CVE-2010-3838 : Candidate [!!] 21 CVE(s) found for your MySQL release. -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 124d 23h 19m 23s (51M q [4.761 qps], 91K conn, TX: 478M, RX: 2G) [--] Reads / Writes: 98% / 2% [--] Binary logging is enabled (GTID MODE: OFF) [--] Physical Memory : 2.0G [--] Max MySQL memory : 320.5M Warning: bad ps syntax, perhaps a bogus '-'? See http://procps.sf.net/faq.html [--] Other process memory: 68.1M [--] Total buffers: 58.0M global + 2.6M per thread (100 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 207.6M (10.26% of installed RAM) [OK] Maximum possible memory usage: 320.5M (15.84% of installed RAM) Warning: bad ps syntax, perhaps a bogus '-'? See http://procps.sf.net/faq.html [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (8/51M) [OK] Highest usage of available connections: 56% (57/100) [OK] Aborted connections: 0.00% (1/91441) [OK] Query cache efficiency: 66.0% (33M cached / 51M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8M sorts) [OK] No joins without indexes [OK] Temporary tables created on disk: 17% (10K on disk / 60K total) [!!] Table cache hit rate: 0% (64 open / 6K opened) [OK] Open file limit used: 12% (130/1K) [OK] Table locks acquired immediately: 99% (17M immediate / 17M locks) [OK] Binlog cache memory access: 100.00% ( 1 Memory / 1 Total) -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 11.8% (1M used / 16M cache) [OK] Key buffer size / total MyISAM indexes: 16.0M/29.7M [OK] Read Key buffer hit rate: 100.0% (114M cached / 24K reads) [!!] Write Key buffer hit rate: 92.6% (2M cached / 164K writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is disabled. -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] This is a standalone server. -------- Recommendations --------------------------------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB Run OPTIMIZE TABLE to defragment tables for better performance 21 CVE(s) found for your MySQL release. Consider upgrading your version ! Enable the slow query log to troubleshoot bad queries Increase table_cache gradually to avoid file descriptor limits Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C Beware that open_files_limit (1024) variable should be greater than table_cache ( 64) Upgrade to MySQL 5.5+ to use asynchronous write Variables to adjust: table_cache (> 64)
[/html]

*This was run on a very old development server for testing purposes.

Written by Matt Cooper
Hi, I'm Matt Cooper. I started this blog to pretty much act as a brain dump area for things I learn from day to day. You can contact me at: matt@matthewc424.sg-host.com.