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.

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Discussion in 'Workarounds and Optimization' started by eva2000, Apr 5, 2012.

Tags:
  1. Facundo

    Facundo Member

    Joined:
    Sep 8, 2009
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    51
    If you recived this error:
    Code:
    t/10connect.t ........................ Bailout called.  Further testing stopped:  ERROR: Access denied for user 'root'@'localhost' (using password: NO)
    FAILED--Further testing stopped: ERROR: Access denied for user 'root'@'localhost' (using password: NO)
    You can fix with this command from root shell:
    Code:
    # /usr/local/cpanel/scripts/perlinstaller DBD::mysql
     
    #41 Facundo, Oct 29, 2014
    Last edited by a moderator: Oct 30, 2014
  2. david.cocos

    david.cocos Member

    Joined:
    May 19, 2014
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello,

    I have tried to install mysqlmymonlite the centos_whm version, and when I try to use ./mysqlmymonlite mysqltuner it gives me the error: "The --forcemem option is required for remote connections".
    How can I run past this?
     
  3. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    330
    Likes Received:
    12
    Trophy Points:
    318
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    did you set a mysql USER/PASS variable and did you change MYSQLHOST at all from default localhost ? mysqltuner should only prompt for forcemem if MYSQLHOST is set to a remote mysql server other than localhost/127.0.0.1

    mysqltuner.pl code

    Code:
    	# Are we being asked to connect to a remote server?
    	if ($opt{host} ne 0) {
    		chomp($opt{host});
    		$opt{port} = ($opt{port} eq 0)? 3306 : $opt{port} ;
    		# If we're doing a remote connection, but forcemem wasn't specified, we need to exit
    		if ($opt{'forcemem'} eq 0) {
    			badprint "The --forcemem option is required for remote connections\n";
    			exit;
    		}
    		infoprint "Performing tests on $opt{host}:$opt{port}\n";
    		$remotestring = " -h $opt{host} -P $opt{port}";
    		$doremote = 1;
    	}
    
     
    #43 eva2000, Nov 11, 2014
    Last edited: Nov 11, 2014
  4. david.cocos

    david.cocos Member

    Joined:
    May 19, 2014
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I have put the mysql username and password in the settings file (config.ini), and mysql is on the same server with everything else.
     
  5. katmai

    katmai Well-Known Member

    Joined:
    Mar 13, 2006
    Messages:
    530
    Likes Received:
    0
    Trophy Points:
    166
    Location:
    Brno, Czech Republic
    had the same issue. this worked. thanks.
     
  6. caisc

    caisc Well-Known Member

    Joined:
    Oct 5, 2011
    Messages:
    70
    Likes Received:
    2
    Trophy Points:
    58
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Many thanks for such brilliant work and offering it for free to the community !
    I just installed it on one of our client server who has issues with mysql performance and it showed up some vital stats clearly pointing to the bottlenecks.
     
  7. bruceleeon

    bruceleeon Active Member

    Joined:
    Jul 20, 2004
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    156
    Same issue here, MySQL is localhost

    [!!] The --forcemem option is required for remote connections

    I have the same issue... were you able to resolve?
     
  8. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    330
    Likes Received:
    12
    Trophy Points:
    318
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    strange indeed.. what does full output look like ?

    I tried this on fresh WHM 11.48 + MySQL 5.6.23 and works fine

    mysqltuner
    Code:
    /root/tools/mysqlmymonlite.sh mysqltuner
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.2 WHM mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Mon Mar 2 17:39:32 CET 2015
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    
     >>  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
    [OK] Currently running supported MySQL version 5.6.23
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 74M (Tables: 297)
    [--] Data in InnoDB tables: 9M (Tables: 121)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in MEMORY tables: 505K (Tables: 2)
    [!!] Total fragmented tables: 127
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 6h 21m 24s (237K q [2.170 qps], 25K conn, TX: 335M, RX: 31M)
    [--] Reads / Writes: 93% / 7%
    [--] Total buffers: 11.5G global + 5.0M per thread (800 max threads)
    [OK] Maximum possible memory usage: 15.4G (49% of installed RAM)
    [OK] Slow queries: 0% (0/237K)
    [OK] Highest usage of available connections: 0% (7/800)
    [OK] Key buffer size / total MyISAM indexes: 4.9G/24.5M
    [OK] Key buffer hit rate: 100.0% (7M cached / 2K reads)
    [OK] Query cache efficiency: 85.8% (137K cached / 160K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
    [!!] Temporary tables created on disk: 31% (17K on disk / 55K total)
    [OK] Thread cache hit rate: 99% (7 created / 25K connections)
    [OK] Table cache hit rate: 97% (1K open / 1K opened)
    [OK] Open file limit used: 0% (990/262K)
    [OK] Table locks acquired immediately: 99% (63K immediate / 63K locks)
    [OK] InnoDB buffer pool / data size: 6.0G/9.5M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    
    
    Report Complete:
    Mon Mar 2 17:39:33 CET 2015
    ----------------------------
    
    mysqlreport
    Code:
    /root/tools/mysqlmymonlite.sh mysqlreport
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.2 WHM mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Mon Mar 2 17:39:26 CET 2015
    
    ----------------------------
    mysqlreport output
    ----------------------------
    
    MySQL   5.6.23              uptime 1 6:21:17    Mon Mar  2 17:39:26 2015
    
    __ Key _________________________________________________________________
    Buffer used     2.81M of    4.88G   %Used:   0.06
      Current     959.52M              %Usage:  19.19
    Write hit      40.34%
    Read hit       99.96%
    
    __ Questions ___________________________________________________________
    Total         237.14k       2.2/s
      QC Hits     137.70k       1.3/s  %Total:  58.07
      Com_         49.84k       0.5/s           21.02
      COM_QUIT     25.77k       0.2/s           10.87
      DMS          24.47k       0.2/s           10.32
      -Unknown        638       0.0/s            0.27
    Slow 10 s           0         0/s            0.00  %DMS:   0.00 Log:
    DMS            24.47k       0.2/s           10.32
      SELECT       22.75k       0.2/s            9.59         92.99
      DELETE          587       0.0/s            0.25          2.40
      INSERT          530       0.0/s            0.22          2.17
      UPDATE          484       0.0/s            0.20          1.98
      REPLACE         115       0.0/s            0.05          0.47
    Com_           49.84k       0.5/s           21.02
      set_option   21.36k       0.2/s            9.01
      change_db    13.82k       0.1/s            5.83
      show_fields  13.15k       0.1/s            5.54
    
    __ Rows ________________________________________________________________
    Rows           16.85M     154.2/s
      Using idx    16.12M     147.5/s  %Index:  95.62
    Rows/question   71.08
    
    __ SELECT and Sort _____________________________________________________
    Scan           18.90k       0.2/s %SELECT:  83.09
    Range             862       0.0/s            3.79
    Full join          63       0.0/s            0.28
    Range check         0         0/s            0.00
    Full rng join       0         0/s            0.00
    Sort scan       1.37k       0.0/s
    Sort range         49       0.0/s
    Sort mrg pass       0         0/s
    
    __ Query Cache _________________________________________________________
    Memory usage    5.91M of   96.00M  %Usage:   6.15
    Block Fragmnt   8.30%
    Hits          137.70k       1.3/s
    Inserts         1.46k       0.0/s
    Insrt:Prune   1.46k:1       0.0/s
    Hit:Insert    94.25:1
    
    __ Table Locks _________________________________________________________
    Waited              2       0.0/s  %Total:   0.00
    Immediate      63.34k       0.6/s
    
    __ Tables ______________________________________________________________
    Open             1033 of  10000    %Cache:  10.33
    Opened          1.06k       0.0/s
    
    __ Connections _________________________________________________________
    Max used            7 of    800      %Max:   0.88
    Total          25.78k       0.2/s
    
    __ Created Temp ________________________________________________________
    Disk table     17.54k       0.2/s   %Disk:  45.88
    Table          38.22k       0.3/s    Size: 512.0M
    File                6       0.0/s
    
    __ Threads _____________________________________________________________
    Running             1 of      2
    Created             7       0.0/s
    Slow                0         0/s
    Cached              5 of    256      %Hit:  99.97
    
    __ Aborted _____________________________________________________________
    Clients             0         0/s
    Connects           44       0.0/s
    
    __ Bytes _______________________________________________________________
    Sent          335.84M      3.1k/s
    Received       31.57M     288.9/s
    
    __ InnoDB Buffer Pool __________________________________________________
    Usage          15.28M of    6.00G  %Usage:   0.25
    Read hit       99.67%
    Pages
      Free        392.24k              %Total:  99.75
      Data            969                        0.25  %Drty:   0.00
      Misc              9                        0.00
      Latched           0                        0.00
    Reads         293.18k       2.7/s
      From disk       964       0.0/s   %Disk:   0.33
      Ahead Rnd         0         0/s
    Writes            622       0.0/s
    Wait Free           0         0/s   %Wait:   0.00
    Flushes           146       0.0/s
    
    __ InnoDB Lock _________________________________________________________
    Waits               0         0/s
    Current             0
    Time acquiring
      Total             0 ms
      Average           0 ms
      Max               0 ms
    
    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
      Reads           985       0.0/s
      Writes          343       0.0/s
      fsync            91       0.0/s
      Pending
        Reads           0
        Writes          0
        fsync           0
    
    Pages
      Created           6       0.0/s
      Read            963       0.0/s
      Written         146       0.0/s
    
    Rows
      Deleted           6       0.0/s
      Inserted          4       0.0/s
      Read          1.93M      17.7/s
      Updated          14       0.0/s
    
    Report Complete:
    Mon Mar 2 17:39:26 CET 2015
    ----------------------------
    
     
  9. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    330
    Likes Received:
    12
    Trophy Points:
    318
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
  10. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    330
    Likes Received:
    12
    Trophy Points:
    318
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    Also updated my modified mysqltuner.pl version from 1.3.0 to 1.4.0. To update for mysqlmymonlite.sh, just delete /root/mysqltuner.pl and re-run

    Code:
    ./mysqlmymonlite.sh mysqltuner
    
    yes MariaDB 10 is listed unsupported due to perl code's version match against it, but it should work fine
    Code:
    [!!] Currently running unsupported MySQL version 10.0.17-MariaDB
    I'm no perl coder, so if anyone has the fix here's the section of code in line 473 of mysqltuner.pl

    Code:
    # Checks for supported or EOL'ed MySQL versions
    my ($mysqlvermajor,$mysqlverminor);
    sub validate_mysql_version {
        ($mysqlvermajor,$mysqlverminor) = $myvar{'version'} =~ /(\d+)\.(\d+)/;
        if (!mysql_version_ge(5)) {
            badprint "Your MySQL version ".$myvar{'version'}." is EOL software!  Upgrade soon!\n";
        } elsif (mysql_version_ge(6)) {
            badprint "Currently running unsupported MySQL version ".$myvar{'version'}."\n";
        } else {
            goodprint "Currently running supported MySQL version ".$myvar{'version'}."\n";
        }
    }
    
    Code:
     /root/tools/mysqlmymonlite.sh mysqltuner
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.3 mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Wed Mar 4 04:38:01 UTC 2015
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    mysqltuner.pl [found]
    
    >>  MySQLTuner 1.4.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
    [OK] Logged in using credentials passed on the command line
    [!!] Currently running unsupported MySQL version 10.0.17-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [!!] 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: 16h 44m 20s (494 q [0.008 qps], 203 conn, TX: 1M, RX: 54K)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 144.0M global + 800.0K per thread (300 max threads)
    [OK] Maximum possible memory usage: 378.4M (36% of installed RAM)
    [OK] Slow queries: 0% (0/494)
    [OK] Highest usage of available connections: 0% (1/300)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/122.0K
    [!!] Query cache efficiency: 0.0% (0 cached / 156 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10 sorts)
    [OK] Temporary tables created on disk: 4% (11 on disk / 243 total)
    [OK] Thread cache hit rate: 99% (2 created / 203 connections)
    [OK] Table cache hit rate: 431% (82 open / 19 opened)
    [OK] Open file limit used: 0% (58/8K)
    [OK] Table locks acquired immediately: 100% (78 immediate / 78 locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        MySQL started within last 24 hours - recommendations may be inaccurate
    Variables to adjust:
        query_cache_limit (> 512K, or use smaller result sets)
    
    
    Report Complete:
    Wed Mar 4 04:38:01 UTC 2015
    ----------------------------
     
    #50 eva2000, Mar 3, 2015
    Last edited: Mar 3, 2015
  11. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    330
    Likes Received:
    12
    Trophy Points:
    318
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    Also don't forget you can extend mysqlmymonlite.sh with your own custom commands and stats gather output outlined at mysqlmymon.com/#custom

    For example adding Memcached server statistics output provided you have installed memcached-tool command

    1. create a incopt.inc file in same directory as mysqlmymonlite.sh and place in it the 3 lines
    Code:
        echo ""
        echo "memcached-tool 127.0.0.1:11211 stats"
        memcached-tool 127.0.0.1:11211 stats
    2. then run mysqlmymonlite.sh custom command to see output
    Code:
    ./mysqlmymonlite.sh custom   
    
    memcached-tool 127.0.0.1:11211 stats
    #127.0.0.1:11211   Field       Value
             accepting_conns           1
                   auth_cmds           0
                 auth_errors           0
                       bytes           0
                  bytes_read           7
               bytes_written           0
                  cas_badval           0
                    cas_hits           0
                  cas_misses           0
                   cmd_flush           0
                     cmd_get           0
                     cmd_set           0
                   cmd_touch           0
                 conn_yields           0
       connection_structures           6
           crawler_reclaimed           0
            curr_connections           5
                  curr_items           0
                   decr_hits           0
                 decr_misses           0
                 delete_hits           0
               delete_misses           0
           evicted_unfetched           0
                   evictions           0
           expired_unfetched           0
                    get_hits           0
                  get_misses           0
                  hash_bytes      524288
           hash_is_expanding           0
            hash_power_level          16
                   incr_hits           0
                 incr_misses           0
                    libevent 2.0.22-stable
              limit_maxbytes     8388608
         listen_disabled_num           0
           lrutail_reflocked           0
                malloc_fails           0
                         pid         887
                pointer_size          64
                   reclaimed           0
                reserved_fds          20
               rusage_system    1.689743
                 rusage_user    1.638750
       slab_reassign_running           0
                 slabs_moved           0
                     threads           4
                        time  1425446357
           total_connections           6
                 total_items           0
                  touch_hits           0
                touch_misses           0
                      uptime       62737
                     version      1.4.22
    You can get very creative if you know shell or SSH command line well to add your own commands or stats gather to incopt.inc file :)
     
  12. Tobias Karlsson

    Tobias Karlsson Active Member

    Joined:
    Jul 29, 2015
    Messages:
    43
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Sweden
    cPanel Access Level:
    Root Administrator
    When i run this i only end up whit a empty mysqlmymonlite_stats.txt file. What could i be doing wrong?
     
  13. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    330
    Likes Received:
    12
    Trophy Points:
    318
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    what's exact command you're running ? and did you edit mysqlmymonlite.sh for mysql root password ?
     
  14. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    330
    Likes Received:
    12
    Trophy Points:
    318
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    mysqlmymonlite.sh 0.5.4 has been released. You can download from here. Install video here.

    Changes of note are:
    • update for defaults-extra-file support for mysql 5.6
    • update mysqltuner.pl 1.5.1 mod version with MariaDB 10 and Aria Engine support. I no longer need to patch mysqltuner.pl for Aria Engine support as upstream master has added support for it alone with other goodies. There's still some mods I added for warning/messages to clarify them a bit more i.e. memory usage :)
    Examples for mysqlmymolite.sh 0.5.4 mysqlreport and mysqltuner outputs

    Code:
     ./mysqlmymonlite.sh         
    please see help for all options available
    ./mysqlmymonlite.sh --help
    
    install mysqlmymonlite.sh at /root
    chmod +x mysqlmymonlite.sh
    
    ./mysqlmymonlite.sh --help
    ./mysqlmymonlite.sh check
    ./mysqlmymonlite.sh run
    ./mysqlmymonlite.sh mysql
    ./mysqlmymonlite.sh vmstat
    ./mysqlmymonlite.sh showcreate
    ./mysqlmymonlite.sh showindex
    ./mysqlmymonlite.sh vbshowtables
    ./mysqlmymonlite.sh dblist
    ./mysqlmymonlite.sh mysqlreport
    ./mysqlmymonlite.sh mysqlfullreport
    ./mysqlmymonlite.sh mysqltuner
    ./mysqlmymonlite.sh psmem
    ./mysqlmymonlite.sh pschema
    Code:
    ./mysqlmymonlite.sh mysqlreport
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.4 mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Tue Aug 25 14:30:16 UTC 2015
    
    ----------------------------
    mysqlreport output
    ----------------------------
    MariaDB 10.0.21-MariaDB     uptime 14 17:58:6   Tue Aug 25 14:30:17 2015
    
    __ Key _________________________________________________________________
    Buffer used    60.00k of   32.00M   %Used:   0.18
      Current       5.85M              %Usage:  18.28
    Write hit      58.86%
    Read hit       99.64%
    
    __ Questions ___________________________________________________________
    Total           1.32k       0.0/s
      DMS             971       0.0/s  %Total:  73.45
      -Unknown        275       0.0/s           20.80
      QC Hits         246       0.0/s           18.61
      Com_            222       0.0/s           16.79
      COM_QUIT        158       0.0/s           11.95
    Slow 1 s            0         0/s            0.00  %DMS:   0.00 Log:
    DMS               971       0.0/s           73.45
      SELECT          788       0.0/s           59.61         81.15
      UPDATE          144       0.0/s           10.89         14.83
      INSERT           34       0.0/s            2.57          3.50
      DELETE            5       0.0/s            0.38          0.51
      REPLACE           0         0/s            0.00          0.00
    Com_              222       0.0/s           16.79
      show_fields      60       0.0/s            4.54
      show_status      32       0.0/s            2.42
      set_option       29       0.0/s            2.19
    
    __ Rows ________________________________________________________________
    Rows           17.72k       0.0/s
      Using idx       610       0.0/s  %Index:   3.44
    Rows/question   13.40
    
    __ SELECT and Sort _____________________________________________________
    Scan              159       0.0/s %SELECT:  20.18
    Range               2       0.0/s            0.25
    Full join           0         0/s            0.00
    Range check         0         0/s            0.00
    Full rng join       0         0/s            0.00
    Sort scan          16       0.0/s
    Sort range          3       0.0/s
    Sort mrg pass       0         0/s
    
    __ Query Cache _________________________________________________________
    Memory usage   17.15k of   16.00M  %Usage:   0.10
    Block Fragmnt 100.00%
    Hits              246       0.0/s
    Inserts           365       0.0/s
    Insrt:Prune     365:1       0.0/s
    Hit:Insert     0.67:1
    
    __ Table Locks _________________________________________________________
    Waited              0         0/s  %Total:   0.00
    Immediate         711       0.0/s
    
    __ Tables ______________________________________________________________
    Open               82 of   2048    %Cache:   4.00
    Opened             29       0.0/s
    
    __ Connections _________________________________________________________
    Max used            1 of    300      %Max:   0.33
    Total             160       0.0/s
    
    __ Created Temp ________________________________________________________
    Disk table         60       0.0/s   %Disk:  44.12
    Table             136       0.0/s    Size:  32.0M
    File                6       0.0/s
    
    __ Threads _____________________________________________________________
    Running             1 of      1
    Created             1       0.0/s
    Slow                0         0/s
    Cached              0 of     64      %Hit:  99.38
    
    __ Aborted _____________________________________________________________
    Clients             0         0/s
    Connects            0         0/s
    
    __ Bytes _______________________________________________________________
    Sent            1.04M       0.8/s
    Received      388.59k       0.3/s
    
    __ InnoDB Buffer Pool __________________________________________________
    Usage           2.48M of   47.98M  %Usage:   5.18
    Read hit       73.77%
    Pages
      Free          2.91k              %Total:  94.82
      Data            159                        5.18  %Drty:   0.00
      Misc              0                        0.00
      Latched           0                        0.00
    Reads             610       0.0/s
      From disk       160       0.0/s   %Disk:  26.23
      Ahead Rnd         0         0/s
    Writes              1       0.0/s
    Wait Free           0         0/s   %Wait:   0.00
    Flushes             1       0.0/s
      LRU               0         0/s  %Flush:   0.00
    
    __ InnoDB Lock _________________________________________________________
    Waits               0         0/s
    Current             0
    Time acquiring
      Total             0 ms
      Average           0 ms
      Max               0 ms
    Trx history          0
    
    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
      Reads           173       0.0/s
      Writes            5       0.0/s
      fsync             5       0.0/s
      Pending
        Reads           0
        Writes          0
        fsync           0
    
    Pages
      Created           0         0/s
      Read            159       0.0/s
      Written           1       0.0/s
    
    Rows
      Deleted           0         0/s
      Inserted          0         0/s
      Read              0         0/s
      Updated           0         0/s
    
    __ Aria Pagecache ______________________________________________________
    Buffer used    16.00k of    8.00M   %Used:   0.20
      Current     336.00k              %Usage:   4.10
    Write hit     100.00%
    Read hit       88.06%
    
    Report Complete:
    Tue Aug 25 14:30:17 UTC 2015
    ----------------------------
    Code:
    ./mysqlmymonlite.sh mysqltuner
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.4 mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Tue Aug 25 14:30:27 UTC 2015
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    >>  MySQLTuner 1.5.1 - 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
    [OK] Currently running supported MySQL version 10.0.21-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
    [!!] InnoDB is enabled but isn't being used
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There is no anonymous account in all database users
    [OK] All database users have passwords assigned
    [!!] There is not basic password file list !
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 14d 17h 58m 16s (1K q [0.001 qps], 175 conn, TX: 1M, RX: 392K)
    [--] Reads / Writes: 81% / 19%
    [--] Binary logging is disabled
    [--] Total buffers: 152.0M global + 1.0M per thread (300 max threads)
    [OK] Maximum reached memory usage: 153.0M (14.94% of installed RAM)
    [OK] Maximum possible memory usage: 461.4M (45.06% of installed RAM)
    [OK] Slow queries: 0% (0/1K)
    [OK] Highest usage of available connections: 0% (1/300)
    [OK] Aborted connections: 0.00%  (0/175)
    [OK] Query cache efficiency: 23.6% (246 cached / 1K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 19 sorts)
    [!!] Temporary tables created on disk: 40% (60 on disk / 147 total)
    [OK] Thread cache hit rate: 99% (1 created / 175 connections)
    [OK] Table cache hit rate: 282% (82 open / 29 opened)
    [OK] Open file limit used: 0% (58/8K)
    [OK] Table locks acquired immediately: 100% (711 immediate / 711 locks)
    
    -------- MyISAM Metrics -----------------------------------------------------
    [!!] Key buffer used: 18.3% (6M used / 33M cache)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/122.0K
    [OK] Read Key buffer hit rate: 99.6% (1K cached / 7 reads)
    [!!] Write Key buffer hit rate: 58.9% (457 cached / 188 writes)
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is disabled.
    [!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine
    
    -------- AriaDB Metrics -----------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
    
    Report Complete:
    Tue Aug 25 14:30:27 UTC 2015
    ----------------------------
     
  15. Masimo

    Masimo Well-Known Member

    Joined:
    Nov 19, 2015
    Messages:
    65
    Likes Received:
    2
    Trophy Points:
    8
    Location:
    Tbilisi
    cPanel Access Level:
    Root Administrator
    My report is not complete.

     
  16. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    330
    Likes Received:
    12
    Trophy Points:
    318
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    @Masimo what version of mysql you using ?
     
  17. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    330
    Likes Received:
    12
    Trophy Points:
    318
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    Updated mysqltuner.pl to 1.6.4 bundle for mysqlmymonlite.sh. To update just remove old version and rerun mysqltuner option

    Code:
    rm /root/mysqltuner.pl
    ./mysqlmymonlite.sh mysqltuner
    Code:
    /root/tools/mysqlmymonlite.sh mysqltuner
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.4 mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Wed Feb 3 10:36:33 UTC 2016
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    mysqltuner.pl [found]
     >>  MySQLTuner 1.6.4 - 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
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 10.0.23-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM 
    [--] Data in InnoDB tables: 2G (Tables: 24)
    [!!] Total fragmented tables: 24
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [!!] There is no basic password file list!
    
    -------- CVE Security Recommendations  ---------------------------------------
    [--] Skipped due to --cvefile option undefined
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6h 52m 35s (491 q [0.020 qps], 188 conn, TX: 985K, RX: 52K)
    [--] Reads / Writes: 100% / 0%
    [--] Binary logging is disabled
    [--] Total buffers: 416.0M global + 1.0M per thread (300 max threads)
    [OK] Maximum reached memory usage: 417.0M (40.73% of installed RAM)
    [OK] Maximum possible memory usage: 725.4M (70.84% of installed RAM)
    [OK] Slow queries: 0% (0/491)
    [OK] Highest usage of available connections: 0% (1/300)
    [OK] Aborted connections: 0.53%  (1/188)
    [!!] Query cache efficiency: 0.0% (0 cached / 199 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10 sorts)
    [OK] Temporary tables created on disk: 0% (0 on disk / 132 total)
    [OK] Thread cache hit rate: 99% (1 created / 188 connections)
    [OK] Table cache hit rate: 246% (106 open / 43 opened)
    [OK] Open file limit used: 0% (58/8K)
    [OK] Table locks acquired immediately: 100% (90 immediate / 90 locks)
    
    -------- MyISAM Metrics ------------------------------------------------------
    [!!] Key buffer used: 18.3% (6M used / 33M cache)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/122.0K
    [OK] Read Key buffer hit rate: 95.0% (40 cached / 2 reads)
    
    -------- InnoDB Metrics ------------------------------------------------------
    [--] InnoDB is enabled.
    [!!] InnoDB buffer pool / data size: 192.0M/2.7G
    [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
    [OK] InnoDB Used buffer: 91.67% (11263 used/ 12287 total)
    [!!] InnoDB Read buffer efficiency: 31.01% (5209 hits/ 16799 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
    
    -------- ThreadPool Metrics --------------------------------------------------
    [--] ThreadPool stat is disabled.
    
    -------- AriaDB Metrics ------------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- TokuDB Metrics ------------------------------------------------------
    [--] TokuDB is disabled.
    
    -------- Galera Metrics ------------------------------------------------------
    [--] Galera is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
    Variables to adjust:
        query_cache_limit (> 512K, or use smaller result sets)
        innodb_buffer_pool_size (>= 2G) if possible.
        innodb_buffer_pool_instances (=1)
    
    Report Complete:
    Wed Feb 3 10:36:33 UTC 2016
    ----------------------------
    
     
  18. Masimo

    Masimo Well-Known Member

    Joined:
    Nov 19, 2015
    Messages:
    65
    Likes Received:
    2
    Trophy Points:
    8
    Location:
    Tbilisi
    cPanel Access Level:
    Root Administrator
    5.6.25
     
  19. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    330
    Likes Received:
    12
    Trophy Points:
    318
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    strange looks to me working for me

    Code:
    /mysqlmymonlite.sh mysqltuner
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.4 WHM mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Wed Feb 3 03:58:26 PST 2016
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    >>  MySQLTuner 1.6.4 - 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
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.6.28
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 4G (Tables: 984)
    [--] Data in InnoDB tables: 32M (Tables: 255)
    [--] Data in MEMORY tables: 0B (Tables: 27)
    [!!] Total fragmented tables: 9
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [!!] There is no basic password file list!
    
    -------- CVE Security Recommendations  ---------------------------------------
    [--] Skipped due to --cvefile option undefined
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 28d 2h 26m 24s (197K q [0.081 qps], 76K conn, TX: 176M, RX: 23M)
    [--] Reads / Writes: 82% / 18%
    [--] Binary logging is disabled
    [--] Total buffers: 792.0M global + 1.5M per thread (500 max threads)
    [OK] Maximum reached memory usage: 801.0M (80.43% of installed RAM)
    [!!] Maximum possible memory usage: 1.5G (154.83% of installed RAM)
    [OK] Slow queries: 0% (0/197K)
    [OK] Highest usage of available connections: 1% (6/500)
    [OK] Aborted connections: 0.01%  (8/76552)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
    [OK] Temporary tables created on disk: 16% (164K on disk / 1M total)
    [OK] Thread cache hit rate: 99% (6 created / 76K connections)
    [OK] Table cache hit rate: 83% (2K open / 3K opened)
    [OK] Open file limit used: 18% (3K/16K)
    [OK] Table locks acquired immediately: 99% (36K immediate / 36K locks)
    
    -------- MyISAM Metrics ------------------------------------------------------
    [!!] Key buffer used: 18.9% (50M used / 268M cache)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/1.0G
    [OK] Read Key buffer hit rate: 98.9% (306K cached / 3K reads)
    [!!] Write Key buffer hit rate: 46.6% (14K cached / 7K writes)
    
    -------- InnoDB Metrics ------------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 256.0M/33.0M
    [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
    [!!] InnoDB Used buffer: 6.49% (1064 used/ 16383 total)
    [OK] InnoDB Read buffer efficiency: 99.95% (2278557 hits/ 2279620 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 4 writes)
    
    -------- ThreadPool Metrics --------------------------------------------------
    [--] ThreadPool stat is disabled.
    
    -------- AriaDB Metrics ------------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- TokuDB Metrics ------------------------------------------------------
    [--] TokuDB is disabled.
    
    -------- Galera Metrics ------------------------------------------------------
    [--] Galera is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
      *** Please note this recommendation is not entirely accurate.
          The formula used to calculate max memory usage assumes all queries utilise
          all memory buffers simultaneously. When in reality it is very rare for a
          query to engage & utilise all memory buffers simultaneously.
    
          Formula also assumes all predefined max_connections are reached.
          You could have set max_connections = 1000 and in a whole year
          of usage never hit beyond 50 max_used_connections. So your real MySQL
          memory usage is only 1/20th of theorectical max memory usage reported.
    
          So real max memory usage will never reach this peak.
          So do not be too concerned with this warning.
    
          It is better to monitor your real MySQL max_used_connection and MySQL
          memory usage over time and adjust accordingly.
    
          You can use tools such as Cacti, Munin or mysqlmymonlite.sh at mysqlmymon.com to
          monitor your MySQL memory usage over time.
    
          MySQL performs optimally when its required amount of memory is met.
          Reducing and starving MySQL memory allocation to adhere to this
          artificial max memory warning - of which in reality will never be reached,
          will only reduce MySQL performance in many cases ***
    
    
        query_cache_size (>= 8M)
        innodb_buffer_pool_instances (=1)
    
    Report Complete:
    Wed Feb 3 03:58:27 PST 2016
    ----------------------------
     
  20. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    330
    Likes Received:
    12
    Trophy Points:
    318
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    @Masimo make sure you have correct mysql root user/pass details and if they're same in /root/.my.cnf
     
Loading...

Share This Page