The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Database Discussions' started by cPanelPeter, Feb 9, 2014.

  1. cPanelPeter

    cPanelPeter Technical Analyst III
    Staff Member

    Joined:
    Sep 23, 2013
    Messages:
    569
    Likes Received:
    15
    Trophy Points:
    18
    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:

    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.
     
  2. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    865
    Likes Received:
    9
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    #2 kernow, Feb 9, 2014
    Last edited: Feb 9, 2014
  3. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    322
    Likes Received:
    10
    Trophy Points:
    18
    Location:
    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 <major@mhtx.net>
     >>  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
     
    #3 eva2000, Feb 9, 2014
    Last edited: Feb 9, 2014
  4. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    865
    Likes Received:
    9
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    #4 kernow, Feb 14, 2014
    Last edited: Feb 14, 2014
  5. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    322
    Likes Received:
    10
    Trophy Points:
    18
    Location:
    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 <major@mhtx.net>
     >>  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)
    
    
     
    #5 eva2000, Mar 1, 2014
    Last edited: Mar 1, 2014
  6. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    865
    Likes Received:
    9
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Ummm.... I don't see support for MySQL 5.6 listed?
     
  7. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    322
    Likes Received:
    10
    Trophy Points:
    18
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    it's just detection support as they removed the version checking temporarily and fixed some variables for mysql 5.5 and 5.6+
     
  8. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    865
    Likes Received:
    9
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Oh I see. Been using tuning-primer.sh instead, which does support current MySQL version and has the edge me thinks..........
     
  9. Palsman

    Palsman Member

    Joined:
    Apr 23, 2013
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Linux Server
    cPanel Access Level:
    Root Administrator
    So what server admin or cPanel recommend to do Mysql optimization?
    Can you please help us?

    Regards,
     
Loading...

Share This Page