[Case # 90317] - Warning on using mysqltuner and MySQL 5.6!

cPanelPeter

Technical Analyst III
Staff member
Sep 23, 2013
575
20
143
cPanel Access Level
Root Administrator
Twitter
Hello,

We get a number of requests for MySQL optimization and/or tuning. Many people use the mysqltuner.pl script to help them in doing so.

One of our analysts (cPanelKaden) recently did some research and discovered that mysqltuner doesn't fully support any MySQL versions past 5.1. From the README file:

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)
* Perl 5.6 or later
* 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)
The last time that mysqltuner.pl script was updated no their github site was 3 years ago.

If you are running mysqltuner.pl on MySQL 5.6, then it will make recommendations that no longer exist on 5.6.
This will in turn break your MySQL so that it won't even start.

It's highly recommended that you do not use mysqltuner.pl any longer in order to optimize/tune your MySQL configuration.

Case # 90317 has been created as of today to remove mysqltuner.pl from cPanel distribution. This of course does not stop someone from downloading mysqltuner from their website and running it.
 

eva2000

Well-Known Member
Aug 14, 2001
339
15
318
Brisbane, Australia
cPanel Access Level
Root Administrator
Twitter
Well mysqltuner has never really been updated beyond MySQL 5.1 and it's recommendations have never 100% been correct. My mysqlmymonlite.sh script uses a modified version of mysqltuner and it works fine with MariaDB 5.5. But some issues it seems recognising MariaDB 10.0.7 (MySQL 5.6 version of MariaDB).

mysqltuner thinks i am on MySQL 4.x hehe.

Code:
[!!] Your MySQL version 10.0.7-MariaDB is EOL software!  Upgrade soon!
Code:
/root/tools/mysqlmymonlite.sh mysqltuner

-------------------------------------------------------------
System MySQL monitoring stats
mysqlmymonlite.sh - 0.5.2 mysqlmymon.com
compiled by George Liu (eva2000) vbtechsupport.com
-------------------------------------------------------------

Report Generated:
Mon Feb 10 13:03:11 EST 2014

-------------------------------------------------
mysqltuner output
-------------------------------------------------

 >>  MySQLTuner 1.2.1 mod - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
 >>  Modified by George Liu (eva2000) at [url=http://vbtechsupport.com/]vbtechsupport.com[/url]
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[!!] Your MySQL version 10.0.7-MariaDB is EOL software!  Upgrade soon!
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 3K (Tables: 27)
[--] Data in InnoDB tables: 0B (Tables: 2)
[--] Data in CSV tables: 0B (Tables: 2)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 27

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7h 5m 16s (694K q [27.231 qps], 808 conn, TX: 90M, RX: 3B)
[--] Reads / Writes: 0% / 100%
[--] Total buffers: 144.0M global + 352.0K per thread (300 max threads)
[OK] Maximum possible memory usage: 247.1M (24% of installed RAM)
[OK] Slow queries: 0% (0/694K)
[OK] Highest usage of available connections: 1% (4/300)
[OK] Key buffer size / total MyISAM indexes: 32.0M/124.0K
[OK] Key buffer hit rate: 100.0% (654K cached / 0 reads)
[OK] Temporary tables created on disk: 3% (11 on disk / 296 total)
[OK] Thread cache hit rate: 99% (4 created / 808 connections)
[!!] Table cache hit rate: 5% (83 open / 1K opened)
[OK] Open file limit used: 0% (62/8K)
[OK] Table locks acquired immediately: 100% (73 immediate / 73 locks)
[OK] InnoDB data size / buffer pool: 0B/48.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Upgrade MySQL to version 4+ to utilize query caching
    Increase table_cache gradually to avoid file descriptor limits
    Upgrade to MySQL 4.1+ to use concurrent MyISAM inserts

Variables to adjust:
    table_cache (> , table_cache hit rate <20%)


Report Complete:
Mon Feb 10 13:03:11 EST 2014
----------------------------
issue at https://github.com/major/MySQLTuner-perl/issues/34
 
Last edited:

kernow

Well-Known Member
Jul 23, 2004
970
35
178
cPanel Access Level
Root Administrator
Last edited:

eva2000

Well-Known Member
Aug 14, 2001
339
15
318
Brisbane, Australia
cPanel Access Level
Root Administrator
Twitter
FYI, mysqltuner.pl 1.3 is out with MySQL 5.6 and MariaDB 10 support https://github.com/major/MySQLTuner-perl :)

Code:
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)
MariaDB 10 https://github.com/major/MySQLTuner-perl/commit/aba4d3b444800ed06310e89e239282b88d657fa4

only partial support for new versions as version checking was temporarily removed https://github.com/major/MySQLTuner-perl/commit/bdca23452c113591f77ff411544d51ba578e5778

Code:
 >>  MySQLTuner 1.3.0 mod - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
 >>  Run with '--help' for additional options and output filtering
[!!] Currently running unsupported MySQL version 10.0.8-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -ARCHIVE +Aria +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MyISAM 
[--] Data in MyISAM tables: 1G (Tables: 11)
[--] Data in InnoDB tables: 208K (Tables: 13)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[OK] Total fragmented tables: 0

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4m 40s (57 q [0.204 qps], 8 conn, TX: 22K, RX: 2K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 176.0M global + 800.0K per thread (300 max threads)
[OK] Maximum possible memory usage: 410.4M (41% of installed RAM)
[OK] Slow queries: 0% (0/57)
[OK] Highest usage of available connections: 0% (1/300)
[!!] Key buffer size / total MyISAM indexes: 32.0M/300.5M
[!!] 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 / 6 total)
[OK] Thread cache hit rate: 87% (1 created / 8 connections)
[OK] Table cache hit rate: 311% (81 open / 26 opened)
[OK] Open file limit used: 0% (36/8K)
[OK] Table locks acquired immediately: 100% (73 immediate / 73 locks)
[OK] InnoDB buffer pool / data size: 48.0M/208.0K
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate

Variables to adjust:
    key_buffer_size (> 300.5M)
    query_cache_limit (> 512K, or use smaller result sets)
 
Last edited: