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

cPanelPeter · Feb 9, 2014

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.

kernow · Feb 9, 2014

Thanks for the info. What recommendations that no longer exist on 5.6 does it make?
Do you know if there is a simular problem using tuning-primer.sh ?
EDIT
Looks like tuning-primer is OK with 5.6 https://launchpad.net/mysql-tuning-primer/+announcement/8353

eva2000 · Feb 9, 2014

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

kernow · Feb 14, 2014

It should also be noted that with Mysql 5.6 the default storage engine is InnoDB MySQL :: MySQL 5.5 Reference Manual :: 14.3.1.2 InnoDB as the Default MySQL Storage Engine unless you specify otherwise in my.cnf
And if you upgrade to 5.6 remember if your my.cnf says;
table_cache (value)
change it to read :
table_open_cache
Otherwise mysql will fail to start!!!!

eva2000 · Mar 1, 2014

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)

kernow · Mar 2, 2014

Ummm.... I don't see support for MySQL 5.6 listed?

eva2000 · Mar 4, 2014

it's just detection support as they removed the version checking temporarily and fixed some variables for mysql 5.5 and 5.6+

kernow · Mar 4, 2014

Oh I see. Been using tuning-primer.sh instead, which does support current MySQL version and has the edge me thinks..........

Palsman · Oct 11, 2015

So what server admin or cPanel recommend to do Mysql optimization?
Can you please help us?

Regards,

cPanelMichael · Oct 12, 2015

Hello

You may find this thread helpful:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

Thread starter	Similar threads	Forum	Replies	Date
	Follow internal case number reference or database?	Databases	4	Sep 24, 2019
R	[case CPANEL-12822,case CPANEL-13105] phpMyAdmin max_input_vars error	Databases	32	Apr 20, 2017
L	[case CPANEL-9751] Phpmyadmin - PHP MySQL library version	Databases	12	Apr 11, 2017
L	check LOWER_CASE_TABLE_NAMES to 1	Databases	1	May 23, 2015
A	[Case 185925] check_mysql sends a notification that databases are broken when they cannot be locked	Databases	19	May 22, 2015

Search

Search

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

cPanelPeter

Senior Technical Analyst

kernow

Well-Known Member

eva2000

Well-Known Member

kernow

Well-Known Member

eva2000

Well-Known Member

kernow

Well-Known Member

eva2000

Well-Known Member

kernow

Well-Known Member

Palsman

Member

cPanelMichael

Administrator