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. 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:
    With the permission from cPanel.net folks, I would like to share my mysqlmymonlite.sh CentOS/WHM version bash shell script with cPanel.net folks which can be downloaded at mysqlmymon.com or from cPanel Application Catalog.

    The zip download has a readme.txt file outlining how to use mysqlmymonlite.sh including how to setup a scheduled cron job to save time stamped logs to disk or send stats gathered via email and the zip file has 3 folders for specific versions of the script for:

    • plain CentOS
    • CentOS/WHM
    • Debian 6.x

    Sharing stats publicly:
    Sole purpose for mysqlmymonlite.sh is to allow server owners to quickly gather stats for their own use or to provide these stats publicly on forums etc, so that other users and readers can use these stats to provide server recommendations, optimisation advice or troubleshooting advice. If you're using forums as medium for sharing these stats, it's best to use the forum's custom BBCODE tags for code format [noparse]
    Code:
    
    
    [/noparse] to wrap the output stats for easier reading.

    Code:
    wrap stats output in CODE tags !
    Background:
    I wrote this script as I provide courtesy server optimisation advice to vBulletin clients in the private vB Server config forum and since June 2011 last year have been providing this script to 100s of vBulletin customers to use so they can gather all the required server stats and info I would need to provide optimisation advice. Literally, it only takes less than <12 seconds to gather all the server info and stats needed :)

    To date, vBulletin.com private server optimisation forum was the only place you could download mysqlmymonlite.sh script. However, I have been popping in on cPanel.net's Optimization forum Optimization and seeing the great work Tristan and other folks have been doing in providing optimisation advice and thought everyone here would benefit from using mysqlmymonlite.sh script to gather your server stats and post them in your optimisation request threads here.

    Benefits:
    mysqlmymonlite.sh will:

    1. Save Tristan and other volunteer helpers' time as they no longer need to ask multiple questions in multiple posts to ascertain the nature and stats of a particular WHM/cPanel server environment. The script is intended to provide enough info and stats to help Tristan/folks help you while still masking as much private info about your server's as possible i.e. server hostnames are masked by default.
    2. Save WHM/cPanel users time in gathering all the info and stats required - literally takes less than <12 seconds to gather all info and stats once you run the script. The zip file download has a readme text file which explains how to install/setup and run the script as well as other uses of the script such as setting up a cronjob to run the script and save output to timestamped text log file or to run a cron to email you the stats every hour or whatever time interval you want.

    Menu options:
    • As you can see besides the main run option which grabs all the stats, there's a few other options you can explore for vmstat and standalone mysqltuner, mysqlreport.
    • The showcreate, showindex, vbshowtables options are for MySQL where you can enter a MySQL database name and output to screen or save to text files the database's tables' show create, show index structure and database's table names. Handy when you don't even want to bother loading phpmyadmin or need to compare a database's table creation and index structure - i.e. output to text file 2 databases' index structure and then use a file DIFF compare tool to compare their indexes on each table.
    • The dblist option just spits out a list of all MySQL databases on your server.

    Code:
    ./mysqlmymonlite.sh 
     
    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
    
    Demo sample output:
    Sample output from v0.3.2 of mysqlmymonlite.sh can be see here full sample output or more recent v0.3.4 example excerpts.

    There's even a modified version of mysqltuner and mysqlreport you can use, see sample output for modified mysqltuner.pl.

    WHM folks more favoured tool mysqltuner modified with better explanation of some of the recommendations made.

    Code:
    ./mysqlmymonlite.sh mysqltuner
     
    [root@centos62svr2 tools]# ./mysqlmymonlite.sh mysqltuner
     
    ----------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - v0.3.2
    compiled by George Liu (eva2000) vbtechsupport.com
    ----------------------------
     
    Report Generated:
    Wed Feb 15 10:30:00 EST 2012
     
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    mysqltuner.pl [found]
     
     >>  MySQLTuner 1.2.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
    [!!] Successfully authenticated with no password - SECURITY RISK!
     
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.3.3-MariaDB-rc-mariadb108
    [OK] Operating on 64-bit architecture
     
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB +Federated -InnoDB -ISAM -NDBCluster 
    [OK] Total fragmented tables: 0
     
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 9h 0m 5s (1K q [0.057 qps], 657 conn, TX: 1M, RX: 101K)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 320.0M global + 1.5M per thread (500 max threads)
    [!!] Maximum possible memory usage: 1.1G (108% of installed RAM)
    [OK] Slow queries: 0% (0/1K)
    [OK] Highest usage of available connections: 0% (1/500)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/97.0K
    [!!] Query cache efficiency: 0.0% (0 cached / 588 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 55 sorts)
    [OK] Temporary tables created on disk: 4% (40 on disk / 891 total)
    [OK] Thread cache hit rate: 99% (1 created / 657 connections)
    [OK] Table cache hit rate: 77% (24 open / 31 opened)
    [OK] Open file limit used: 0% (51/20K)
    [OK] Table locks acquired immediately: 100% (21 immediate / 21 locks)
     
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability.
        See notes on accuracy of this recommendation below
        Enable the slow query log to troubleshoot bad queries.
        Dont forget to disable slow query logging after troubleshooting
       - For MySQL 5.0 http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
       - For MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
       - For MySQL 5.5 http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
       - For MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
     
    Variables to adjust:
      *** MySQLs maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
      *** Please note this recommendation isnt entirely accurate. 
          The formula used to calculate max memory usage assumes all queries utilise
          all memory buffers simultaneously. When in reality is 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 dont 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 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_limit (> 1M, or use smaller result sets)
     
     
    Report Complete:
    Wed Feb 15 10:30:01 EST 2012
    ----------------------------
    
    Demo for ./mysqlmymonlite.sh showindex saved to disk at /home/myindexes

    Code:
    [root@localhost ~]# ./mysqlmymonlite.sh showindex
    What is your mysql database name ? 
    sbtest
    
    
    Do you want to display all sbtest tables' indexes ? [y/n]
    y
    
    Do you want save output to text file ? Answering no will output only to screen. [y/n]
    y
    
    Enter directory path where you want to save the text file i.e. /home/username 
    /home/myindexes
    saving to: /home/myindexes/sbtest-sbtest1-indexes.txt
    
    *******************************************************
    sbtest table index list saved at: /home/myindexes
    *******************************************************
    Jun 28  20:51   846    sbtest-sbtest1-indexes.txt
    Jun 28  20:51   413    sbtest-sbtest1-schema.txt
    indexes listing for dbname = sbtest, tablename = sbtest1

    Code:
    [root@localhost ~]# cat /home/myindexes/sbtest-sbtest1-indexes.txt
    +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | sbtest1 |          0 | PRIMARY  |            1 | id          | A         |     1363740 |     NULL | NULL   |      | BTREE      |         |
    | sbtest1 |          1 | k        |            1 | k           | A         |      454580 |     NULL | NULL   |      | BTREE      |         |
    +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    Disclaimer:
    By downloading and using mysqlmymonlite.sh WHM version script you understand and agree to the following terms of use:

    1. WHM/cPanel.net and I (George Liu / eva2000) are not liable for any errors or damages arising from mysqlmymonlite.sh usage. Usage of the script is entirely at your own risk. Not that there would be any damage possible as I have extensively tested the script and it has been used by 100s of vBulletin customers since June 2011. But please reply to this thread if you have bugs to report or contact me.
    2. WHM/cPanel.net and I (George Liu / eva2000) are not obligated to provide support for mysqlmymonlite.sh script itself nor are we obligated to provide optimisation advice. Any advice provided is done out courtesy and of voluntary nature.
    3. Script initially works and tested on CentOS for detecting Apache prefork mpm worker and mod_php, Nginx and Litespeed web servers and may not detect other configurations at present i.e. event/worker mpm or mod_fcgid and mod_ruid2. Support for those may be added in future by me.

    Keeping mysqlmymonlite.sh up to date:
    Currently, 2 methods to get notified for mysqlmymonlite.sh updates.
    1. via Twitter at https://twitter.com/vbtechsupport
    2. mysqlmymonlite.sh has it's own Google+ Page for script updates too


    [video=youtube;2s-ems7Xpi4]http://www.youtube.com/watch?v=2s-ems7Xpi4&hd=1[/video]
     
    #1 eva2000, Apr 5, 2012
    Last edited: Apr 5, 2012
    Masimo likes this.
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,451
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    This should come in very handy! Great job, thanks!
     
  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:
    Thanks Chuck hopefully saves folks alot of time gathering stats :)

    Feedback and suggestions are welcomed via contact form, as I am sure there are better ways to bash script some routines.
     
    #3 eva2000, Apr 5, 2012
    Last edited: Apr 5, 2012
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Thanks for your work on this script. I haven't had a chance to test it yet, but it's appreciated that you are willing to support users for your script.
     
  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:
    You're welcome Tristan :)
     
  6. 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:
    I've been asked what the difference is between my version's mysqltuner and the official one, the modded one i include in mysqlmymonlite.sh elaborates on some of the recommendations made. One is the annoying memory used recommendation as it rarely will hold true for most folks.

    Here's a sample of mysqlmymonlite.sh's mysqltuner option via command line when the mysql memory usage recommendation is triggered.

    Code:
    ./mysqlmymonlite.sh mysqltuner
    Code:
    ----------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - v0.3.6
    compiled by George Liu (eva2000) vbtechsupport.com
    ----------------------------
    
    Report Generated:
    Fri Apr 27 11:13:45 EST 2012
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    mysqltuner.pl [found]
    
     >>  MySQLTuner 1.2.1 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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.2.12-MariaDB-mariadb115
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster 
    [OK] Total fragmented tables: 0
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3s (33 q [11.000 qps], 16 conn, TX: 60K, RX: 1K)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 40.0M global + 928.0K per thread (2000 max threads)
    [!!] Maximum possible memory usage: 1.8G (184% of installed RAM)
    [OK] Slow queries: 0% (0/33)
    [OK] Highest usage of available connections: 0% (1/2000)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/91.0K
    [!!] Query cache efficiency: 0.0% (0 cached / 8 selects)
    [OK] Query cache prunes per day: 0
    [OK] Temporary tables created on disk: 0% (0 on disk / 11 total)
    [OK] Thread cache hit rate: 93% (1 created / 16 connections)
    [OK] Table cache hit rate: 53% (8 open / 15 opened)
    [OK] Open file limit used: 0% (19/10K)
    [OK] Table locks acquired immediately: 100% (18 immediate / 18 locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability.
        See notes on accuracy of this recommendation below
        Enable the slow query log to troubleshoot bad queries.
        Don't forget to disable slow query logging after troubleshooting
       - For MySQL 5.0 http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
       - For MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
       - For MySQL 5.5 http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
       - For MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
    
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
      *** Please note this recommendation isn't entirely accurate. 
          The formula used to calculate max memory usage assumes all queries utilise
          all memory buffers simultaneously. When in reality is it's 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 don't be too concerned with this warning.
    
          It's 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 it's 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_limit (> 512K, or use smaller result sets)
    
    
    Report Complete:
    Fri Apr 27 11:13:45 EST 2012
    ----------------------------
     
    #6 eva2000, Apr 26, 2012
    Last edited: Apr 26, 2012
  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:
    Sample for mysqlreport output

    Code:
    ./mysqlmymonlite.sh mysqlreport
    Code:
    ----------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - v0.3.6
    compiled by George Liu (eva2000) vbtechsupport.com
    ----------------------------
    
    Report Generated:
    Fri Apr 27 12:10:33 EST 2012
    
    ----------------------------
    mysqlreport output
    ----------------------------
    mysqlreport [found]
    MySQL 5.2.12-MariaDB-ma  uptime 0 0:56:51       Fri Apr 27 12:10:33 2012
    
    __ Key _________________________________________________________________
    Buffer used         0 of  16.00M  %Used:   0.00
      Current       2.92M            %Usage:  18.24
    Write hit       0.00%
    Read hit        0.00%
    
    __ Questions ___________________________________________________________
    Total              76     0.0/s
      COM_QUIT         31     0.0/s  %Total:  40.79
      Com_             27     0.0/s           35.53
      DMS              21     0.0/s           27.63
      -Unknown          3     0.0/s            3.95
    Slow 1 s            0       0/s            0.00  %DMS:   0.00  Log: OFF
    DMS                21     0.0/s           27.63
      SELECT           21     0.0/s           27.63        100.00
      REPLACE           0       0/s            0.00          0.00
      DELETE            0       0/s            0.00          0.00
      INSERT            0       0/s            0.00          0.00
      UPDATE            0       0/s            0.00          0.00
    Com_               27     0.0/s           35.53
      show_variab      12     0.0/s           15.79
      show_status       6     0.0/s            7.89
      show_databa       4     0.0/s            5.26
    
    __ SELECT and Sort _____________________________________________________
    Scan               27     0.0/s %SELECT: 128.57
    Range               0       0/s            0.00
    Full join           0       0/s            0.00
    Range check         0       0/s            0.00
    Full rng join       0       0/s            0.00
    Sort scan           1     0.0/s
    Sort range          0       0/s
    Sort mrg pass       0       0/s
    
    __ Query Cache _________________________________________________________
    Memory usage   16.96k of   8.00M  %Used:   0.21
    Block Fragmnt 100.00%
    Hits                0       0/s
    Inserts             1     0.0/s
    Insrt:Prune       1:1       0/s
    Hit:Insert     0.00:1
    
    __ Table Locks _________________________________________________________
    Waited              0       0/s  %Total:   0.00
    Immediate          19     0.0/s
    
    __ Tables ______________________________________________________________
    Open               23 of 1536    %Cache:   1.50
    Opened             30     0.0/s
    
    __ Connections _________________________________________________________
    Max used            1 of 2000      %Max:   0.05
    Total              33     0.0/s
    
    __ Created Temp ________________________________________________________
    Disk table          0       0/s
    Table              27     0.0/s    Size:  16.0M
    File                0       0/s
    
    __ Threads _____________________________________________________________
    Running             1 of    1
    Cached              0 of   64      %Hit:  96.97
    Created             1     0.0/s
    Slow                0       0/s
    
    __ Aborted _____________________________________________________________
    Clients             0       0/s
    Connects            0       0/s
    
    __ Bytes _______________________________________________________________
    Sent          121.24k    35.5/s
    Received        4.35k     1.3/s
    
    Report Complete:
    Fri Apr 27 12:10:33 EST 2012
    ----------------------------
     
  8. canhfbf

    canhfbf Registered

    Joined:
    May 4, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Great job. Thanks!!
     
  9. 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:
    thanks canhfbf, hope the script saves you and other admins alot of time in gathering server usage stats - only takes <12 seconds to gather :)
     
  10. -GR-

    -GR- Active Member

    Joined:
    May 2, 2012
    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    George, I think it is great that you provided the script for Cpanel users and I appreciate all the work you have done over the years helping people tune their setups.

    I don't use vbulletin anymore so I miss seeing your posts on optimizing different setups.
     
  11. 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:
    Thanks for your kind words. For me it's all about saving time, shouldn't take ages to gather all server stats you need - so mysqlmymonlite.sh was born :)
     
  12. 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:
    Update:
    Extend mysqlmymonlite.sh with own commands
    http://mysqlmymon.com/#custom


    v0.3.8 added the ability to customise and extend mysqlmymonlite.sh with end user custom SSH telnet command line support. A new menu option called 'custom' which can be called via ./mysqlmymonlite.sh custom.

    This option allows you to create a separate includes file name it incopt.inc and place it in same direct mysqlmymonlite.sh is located in.

    You can add your own custom SSH bash shell commands to this incopt.inc file to further extend mysqlmymonlite.sh's functionality and usefulness. Invoke and run these custom commands via:

    Code:
        ./mysqlmymonlite.sh custom
    For instance you could add iostats command to incopt.inc file as follows:

    Code:
        echo ""
        echo "iostat -x 10 5"
        iostat -x 10 5
    
    then run custom command and output iostats output

    Code:
    ./mysqlmymonlite.sh custom
    
    iostat -x 10 5
    Linux 2.6.32-220.7.1.el6.x86_64  05/08/2012      _x86_64_        (2 CPU)
    
    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
               0.14    0.00    0.14    0.03    0.00   99.69
    
    Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
    sda               0.08     1.98    0.21    0.37     8.09    18.42    46.01     0.01   12.47   1.67   0.10
    scd0              0.00     0.00    0.00    0.00     0.00     0.00     8.00     0.00    0.43   0.43   0.00
    dm-0              0.00     0.00    0.28    2.31     8.03    18.42    10.22     0.47  181.06   0.37   0.10
    dm-1              0.00     0.00    0.00    0.00     0.02     0.00     8.00     0.00    2.73   1.66   0.00
    
    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
               0.00    0.00    0.00    0.00    0.00  100.00
    
    Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
    sda               0.00     0.70    0.10    0.30     1.60     7.20    22.00     0.00    1.75   1.75   0.07
    scd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    dm-0              0.00     0.00    0.00    0.90     0.00     7.20     8.00     0.00    0.11   0.11   0.01
    dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    
    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
               0.00    0.00    0.00    0.00    0.00  100.00
    
    Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
    sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    scd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    
    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
               0.00    0.00    0.00    0.00    0.00  100.00
    
    Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
    sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    scd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    
    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
               0.05    0.00    0.10    0.00    0.00   99.85
    
    Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
    sda               0.00     0.10    0.00    0.60     0.00     5.60     9.33     0.00    0.67   0.17   0.01
    scd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    dm-0              0.00     0.00    0.00    0.70     0.00     5.60     8.00     0.00    0.71   0.14   0.01
    dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
    Full details are explained in readme.txt file included in download zip file. If you're familiar with bash shell scripting and normal SSH telnet environment commands, you can get pretty creative with what you add to incopt.inc file :)
     
  13. 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:
  14. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,451
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Great job on this. This clip should make understanding how to use it, that much easier. :)
     
  15. 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:
    Thanks Chuck, should take some of the confusion out of installation and usage and also highlight some of the other useful options i.e. grabing MySQL database table indexes to view and compare (for checking missing indexes etc)

    Also for example output on web site http://mysqlmymon.com/ - each sample output I added some links to documentation on various sites to explain what the output is displaying.
     
    #15 eva2000, Jun 18, 2012
    Last edited: Jun 18, 2012
  16. 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:
    Change Log:

    July 3rd, 2012:
    mysqlmymonlite.sh v0.4.2 / v0.4.2 WHM / v0.4.2 Debian

    i.e.

    Code:
    ./mysqlmymonlite.sh pschema               
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.4.2 mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Tue Jul 3 12:11:00 EST 2012
    ---------------------------------------
    Process List
    +-----------+----------------+----------------------------------------+
    | THREAD_ID | PROCESSLIST_ID | NAME                                   |
    +-----------+----------------+----------------------------------------+
    |         0 |              0 | thread/sql/main                        |
    |         1 |              0 | thread/innodb/io_handler_thread        |
    |         2 |              0 | thread/innodb/io_handler_thread        |
    |         3 |              0 | thread/innodb/io_handler_thread        |
    |         4 |              0 | thread/innodb/io_handler_thread        |
    |         5 |              0 | thread/innodb/io_handler_thread        |
    |         6 |              0 | thread/innodb/io_handler_thread        |
    |         7 |              0 | thread/innodb/srv_lock_timeout_thread  |
    |         8 |              0 | thread/innodb/srv_error_monitor_thread |
    |         9 |              0 | thread/innodb/srv_monitor_thread       |
    |        10 |              0 | thread/innodb/srv_master_thread        |
    |        11 |              0 | thread/sql/signal_handler              |
    |        49 |             38 | thread/sql/one_connection              |
    +-----------+----------------+----------------------------------------+
    
    ---------------------------------------
    PERFORMANCE_SCHEMA Var status
    +---------------------------------------------------+---------+
    | Variable_name                                     | Value   |
    +---------------------------------------------------+---------+
    | performance_schema                                | ON      |
    | performance_schema_events_waits_history_long_size | 10000   |
    | performance_schema_events_waits_history_size      | 10      |
    | performance_schema_max_cond_classes               | 80      |
    | performance_schema_max_cond_instances             | 1000    |
    | performance_schema_max_file_classes               | 50      |
    | performance_schema_max_file_handles               | 32768   |
    | performance_schema_max_file_instances             | 10000   |
    | performance_schema_max_mutex_classes              | 200     |
    | performance_schema_max_mutex_instances            | 1000000 |
    | performance_schema_max_rwlock_classes             | 30      |
    | performance_schema_max_rwlock_instances           | 1000000 |
    | performance_schema_max_table_handles              | 100000  |
    | performance_schema_max_table_instances            | 50000   |
    | performance_schema_max_thread_classes             | 50      |
    | performance_schema_max_thread_instances           | 1000    |
    +---------------------------------------------------+---------+
    
    ---------------------------------------
    PERFORMANCE_SCHEMA Status
    +------------------------------------------+-------+
    | Variable_name                            | Value |
    +------------------------------------------+-------+
    | Performance_schema_cond_classes_lost     | 0     |
    | Performance_schema_cond_instances_lost   | 0     |
    | Performance_schema_file_classes_lost     | 0     |
    | Performance_schema_file_handles_lost     | 0     |
    | Performance_schema_file_instances_lost   | 0     |
    | Performance_schema_locker_lost           | 0     |
    | Performance_schema_mutex_classes_lost    | 0     |
    | Performance_schema_mutex_instances_lost  | 0     |
    | Performance_schema_rwlock_classes_lost   | 0     |
    | Performance_schema_rwlock_instances_lost | 0     |
    | Performance_schema_table_handles_lost    | 0     |
    | Performance_schema_table_instances_lost  | 0     |
    | Performance_schema_thread_classes_lost   | 0     |
    | Performance_schema_thread_instances_lost | 0     |
    +------------------------------------------+-------+
    
    ---------------------------------------
    PERFORMANCE_SCHEMA Engine status
    +--------------------+--------------------------------------------------------+-----------+
    | Type               | Name                                                   | Status    |
    +--------------------+--------------------------------------------------------+-----------+
    | performance_schema | events_waits_current.row_size                          | 136       |
    | performance_schema | events_waits_current.row_count                         | 3000      |
    | performance_schema | events_waits_history.row_size                          | 120       |
    | performance_schema | events_waits_history.row_count                         | 10000     |
    | performance_schema | events_waits_history.memory                            | 1200000   |
    | performance_schema | events_waits_history_long.row_size                     | 120       |
    | performance_schema | events_waits_history_long.row_count                    | 10000     |
    | performance_schema | events_waits_history_long.memory                       | 1200000   |
    | performance_schema | (pfs_mutex_class).row_size                             | 248       |
    | performance_schema | (pfs_mutex_class).row_count                            | 200       |
    | performance_schema | (pfs_mutex_class).memory                               | 49600     |
    | performance_schema | (pfs_rwlock_class).row_size                            | 296       |
    | performance_schema | (pfs_rwlock_class).row_count                           | 30        |
    | performance_schema | (pfs_rwlock_class).memory                              | 8880      |
    | performance_schema | (pfs_cond_class).row_size                              | 216       |
    | performance_schema | (pfs_cond_class).row_count                             | 80        |
    | performance_schema | (pfs_cond_class).memory                                | 17280     |
    | performance_schema | (pfs_thread_class).row_size                            | 136       |
    | performance_schema | (pfs_thread_class).row_count                           | 50        |
    | performance_schema | (pfs_thread_class).memory                              | 6800      |
    | performance_schema | (pfs_file_class).row_size                              | 240       |
    | performance_schema | (pfs_file_class).row_count                             | 50        |
    | performance_schema | (pfs_file_class).memory                                | 12000     |
    | performance_schema | mutex_instances.row_size                               | 136       |
    | performance_schema | mutex_instances.row_count                              | 1000000   |
    | performance_schema | mutex_instances.memory                                 | 136000000 |
    | performance_schema | rwlock_instances.row_size                              | 200       |
    | performance_schema | rwlock_instances.row_count                             | 1000000   |
    | performance_schema | rwlock_instances.memory                                | 200000000 |
    | performance_schema | cond_instances.row_size                                | 88        |
    | performance_schema | cond_instances.row_count                               | 1000      |
    | performance_schema | cond_instances.memory                                  | 88000     |
    | performance_schema | threads.row_size                                       | 504       |
    | performance_schema | threads.row_count                                      | 1000      |
    | performance_schema | threads.memory                                         | 504000    |
    | performance_schema | file_instances.row_size                                | 624       |
    | performance_schema | file_instances.row_count                               | 10000     |
    | performance_schema | file_instances.memory                                  | 6240000   |
    | performance_schema | (pfs_file_handle).row_size                             | 8         |
    | performance_schema | (pfs_file_handle).row_count                            | 32768     |
    | performance_schema | (pfs_file_handle).memory                               | 262144    |
    | performance_schema | events_waits_summary_by_thread_by_event_name.row_size  | 48        |
    | performance_schema | events_waits_summary_by_thread_by_event_name.row_count | 360000    |
    | performance_schema | events_waits_summary_by_thread_by_event_name.memory    | 17280000  |
    | performance_schema | (pfs_table_share).row_size                             | 488       |
    | performance_schema | (pfs_table_share).row_count                            | 50000     |
    | performance_schema | (pfs_table_share).memory                               | 24400000  |
    | performance_schema | (pfs_table).row_size                                   | 72        |
    | performance_schema | (pfs_table).row_count                                  | 100000    |
    | performance_schema | (pfs_table).memory                                     | 7200000   |
    | performance_schema | performance_schema.memory                              | 394468704 |
    +--------------------+--------------------------------------------------------+-----------+
    
    ---------------------------------------
    PERFORMANCE_SCHEMA setup_instruments
    +------------------------------------------------------------+---------+-------+
    | NAME                                                       | ENABLED | TIMED |
    +------------------------------------------------------------+---------+-------+
    | wait/synch/mutex/sql/PAGE::lock                            | YES     | YES   |
    | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync                | YES     | YES   |
    | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active              | YES     | YES   |
    | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_pool                | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_des_key_file                     | YES     | YES   |
    | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index             | YES     | YES   |
    | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_prep_xids         | YES     | YES   |
    | wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_index           | YES     | YES   |
    | wait/synch/mutex/sql/Delayed_insert::mutex                 | YES     | YES   |
    | wait/synch/mutex/sql/hash_filo::lock                       | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_active_mi                        | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_connection_count                 | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_crypt                            | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_delayed_create                   | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_delayed_insert                   | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_delayed_status                   | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_error_log                        | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_gdl                              | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_global_system_variables          | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_manager                          | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_prepared_stmt_count              | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_rpl_status                       | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_server_started                   | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_status                           | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_system_variables_hash            | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_table_share                      | YES     | YES   |
    | wait/synch/mutex/sql/THD::LOCK_thd_data                    | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_user_conn                        | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_uuid_generator                   | YES     | YES   |
    | wait/synch/mutex/sql/LOG::LOCK_log                         | YES     | YES   |
    | wait/synch/mutex/sql/Master_info::data_lock                | YES     | YES   |
    | wait/synch/mutex/sql/Master_info::run_lock                 | YES     | YES   |
    | wait/synch/mutex/sql/Master_info::sleep_lock               | YES     | YES   |
    | wait/synch/mutex/sql/Slave_reporting_capability::err_lock  | YES     | YES   |
    | wait/synch/mutex/sql/Relay_log_info::data_lock             | YES     | YES   |
    | wait/synch/mutex/sql/Relay_log_info::log_space_lock        | YES     | YES   |
    | wait/synch/mutex/sql/Relay_log_info::run_lock              | YES     | YES   |
    | wait/synch/mutex/sql/Relay_log_info::sleep_lock            | YES     | YES   |
    | wait/synch/mutex/sql/Query_cache::structure_guard_mutex    | YES     | YES   |
    | wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data             | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_error_messages                   | YES     | YES   |
    | wait/synch/mutex/sql/LOG_INFO::lock                        | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_thread_count                     | YES     | YES   |
    | wait/synch/mutex/sql/HA_DATA_PARTITION::LOCK_auto_inc      | YES     | YES   |
    | wait/synch/mutex/mysys/BITMAP::mutex                       | YES     | YES   |
    | wait/synch/mutex/mysys/IO_CACHE::append_buffer_lock        | YES     | YES   |
    | wait/synch/mutex/mysys/IO_CACHE::SHARE_mutex               | YES     | YES   |
    | wait/synch/mutex/mysys/KEY_CACHE::cache_lock               | YES     | YES   |
    | wait/synch/mutex/mysys/LOCK_alarm                          | YES     | YES   |
    | wait/synch/mutex/mysys/my_thread_var::mutex                | YES     | YES   |
    | wait/synch/mutex/mysys/THR_LOCK_charset                    | YES     | YES   |
    | wait/synch/mutex/mysys/THR_LOCK_heap                       | YES     | YES   |
    | wait/synch/mutex/mysys/THR_LOCK_isam                       | YES     | YES   |
    | wait/synch/mutex/mysys/THR_LOCK_lock                       | YES     | YES   |
    | wait/synch/mutex/mysys/THR_LOCK_malloc                     | YES     | YES   |
    | wait/synch/mutex/mysys/THR_LOCK::mutex                     | YES     | YES   |
    | wait/synch/mutex/mysys/THR_LOCK_myisam                     | YES     | YES   |
    | wait/synch/mutex/mysys/THR_LOCK_net                        | YES     | YES   |
    | wait/synch/mutex/mysys/THR_LOCK_open                       | YES     | YES   |
    | wait/synch/mutex/mysys/THR_LOCK_threads                    | YES     | YES   |
    | wait/synch/mutex/mysys/TMPDIR_mutex                        | YES     | YES   |
    | wait/synch/mutex/mysys/THR_LOCK_myisam_mmap                | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_audit_mask                       | YES     | YES   |
    | wait/synch/mutex/sql/Cversion_lock                         | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_event_queue                      | YES     | YES   |
    | wait/synch/mutex/sql/Event_scheduler::LOCK_scheduler_state | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_user_locks                       | YES     | YES   |
    | wait/synch/mutex/sql/MDL_map::mutex                        | YES     | YES   |
    | wait/synch/mutex/sql/MDL_wait::LOCK_wait_status            | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_open                             | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_slave_list                       | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_xid_cache                        | YES     | YES   |
    | wait/synch/mutex/sql/LOCK_plugin                           | YES     | YES   |
    | wait/synch/mutex/csv/tina                                  | YES     | YES   |
    | wait/synch/mutex/csv/TINA_SHARE::mutex                     | YES     | YES   |
    | wait/synch/mutex/myisam/MI_SORT_INFO::mutex                | YES     | YES   |
    | wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock          | YES     | YES   |
    | wait/synch/mutex/myisam/MI_CHECK::print_msg                | YES     | YES   |
    | wait/synch/mutex/myisammrg/MYRG_INFO::mutex                | YES     | YES   |
    | wait/synch/mutex/memory/HP_SHARE::intern_lock              | YES     | YES   |
    | wait/synch/mutex/blackhole/blackhole                       | YES     | YES   |
    | wait/synch/mutex/archive/archive_mutex                     | YES     | YES   |
    | wait/synch/mutex/archive/ARCHIVE_SHARE::mutex              | YES     | YES   |
    | wait/synch/mutex/innodb/commit_threads_m                   | YES     | YES   |
    | wait/synch/mutex/innodb/commit_cond_mutex                  | YES     | YES   |
    | wait/synch/mutex/innodb/innobase_share_mutex               | YES     | YES   |
    | wait/synch/mutex/innodb/prepare_commit_mutex               | YES     | YES   |
    | wait/synch/mutex/innodb/autoinc_mutex                      | YES     | YES   |
    | wait/synch/mutex/innodb/btr_search_enabled_mutex           | YES     | YES   |
    | wait/synch/mutex/innodb/buf_pool_mutex                     | YES     | YES   |
    | wait/synch/mutex/innodb/buf_pool_zip_mutex                 | YES     | YES   |
    | wait/synch/mutex/innodb/cache_last_read_mutex              | YES     | YES   |
    | wait/synch/mutex/innodb/dict_foreign_err_mutex             | YES     | YES   |
    | wait/synch/mutex/innodb/dict_sys_mutex                     | YES     | YES   |
    | wait/synch/mutex/innodb/file_format_max_mutex              | YES     | YES   |
    | wait/synch/mutex/innodb/fil_system_mutex                   | YES     | YES   |
    | wait/synch/mutex/innodb/flush_list_mutex                   | YES     | YES   |
    | wait/synch/mutex/innodb/log_flush_order_mutex              | YES     | YES   |
    | wait/synch/mutex/innodb/hash_table_mutex                   | YES     | YES   |
    | wait/synch/mutex/innodb/ibuf_bitmap_mutex                  | YES     | YES   |
    | wait/synch/mutex/innodb/ibuf_mutex                         | YES     | YES   |
    | wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex      | YES     | YES   |
    | wait/synch/mutex/innodb/kernel_mutex                       | YES     | YES   |
    | wait/synch/mutex/innodb/log_sys_mutex                      | YES     | YES   |
    | wait/synch/mutex/innodb/mem_pool_mutex                     | YES     | YES   |
    | wait/synch/mutex/innodb/mutex_list_mutex                   | YES     | YES   |
    | wait/synch/mutex/innodb/purge_sys_bh_mutex                 | YES     | YES   |
    | wait/synch/mutex/innodb/recv_sys_mutex                     | YES     | YES   |
    | wait/synch/mutex/innodb/rseg_mutex                         | YES     | YES   |
    | wait/synch/mutex/innodb/rw_lock_list_mutex                 | YES     | YES   |
    | wait/synch/mutex/innodb/rw_lock_mutex                      | YES     | YES   |
    | wait/synch/mutex/innodb/srv_dict_tmpfile_mutex             | YES     | YES   |
    | wait/synch/mutex/innodb/srv_innodb_monitor_mutex           | YES     | YES   |
    | wait/synch/mutex/innodb/srv_misc_tmpfile_mutex             | YES     | YES   |
    | wait/synch/mutex/innodb/srv_monitor_file_mutex             | YES     | YES   |
    | wait/synch/mutex/innodb/syn_arr_mutex                      | YES     | YES   |
    | wait/synch/mutex/innodb/trx_doublewrite_mutex              | YES     | YES   |
    | wait/synch/mutex/innodb/trx_undo_mutex                     | YES     | YES   |
    | wait/synch/mutex/sql/tz_LOCK                               | YES     | YES   |
    | wait/synch/rwlock/sql/CRYPTO_dynlock_value::lock           | YES     | YES   |
    | wait/synch/rwlock/sql/LOCK_grant                           | YES     | YES   |
    | wait/synch/rwlock/sql/LOGGER::LOCK_logger                  | YES     | YES   |
    | wait/synch/rwlock/sql/LOCK_sys_init_connect                | YES     | YES   |
    | wait/synch/rwlock/sql/LOCK_sys_init_slave                  | YES     | YES   |
    | wait/synch/rwlock/sql/LOCK_system_variables_hash           | YES     | YES   |
    | wait/synch/rwlock/sql/Query_cache_query::lock              | YES     | YES   |
    | wait/synch/rwlock/sql/LOCK_dboptions                       | YES     | YES   |
    | wait/synch/rwlock/sql/MDL_lock::rwlock                     | YES     | YES   |
    | wait/synch/rwlock/sql/MDL_context::LOCK_waiting_for        | YES     | YES   |
    | wait/synch/rwlock/myisam/MYISAM_SHARE::key_root_lock       | YES     | YES   |
    | wait/synch/rwlock/myisam/MYISAM_SHARE::mmap_lock           | YES     | YES   |
    | wait/synch/rwlock/innodb/btr_search_latch                  | YES     | YES   |
    | wait/synch/rwlock/innodb/dict_operation_lock               | YES     | YES   |
    | wait/synch/rwlock/innodb/fil_space_latch                   | YES     | YES   |
    | wait/synch/rwlock/innodb/checkpoint_lock                   | YES     | YES   |
    | wait/synch/rwlock/innodb/trx_i_s_cache_lock                | YES     | YES   |
    | wait/synch/rwlock/innodb/trx_purge_latch                   | YES     | YES   |
    | wait/synch/rwlock/innodb/index_tree_rw_lock                | YES     | YES   |
    | wait/synch/rwlock/innodb/dict_table_stats                  | YES     | YES   |
    | wait/synch/rwlock/sql/THR_LOCK_servers                     | YES     | YES   |
    | wait/synch/rwlock/sql/THR_LOCK_udf                         | YES     | YES   |
    | wait/synch/cond/sql/PAGE::cond                             | YES     | YES   |
    | wait/synch/cond/sql/TC_LOG_MMAP::COND_active               | YES     | YES   |
    | wait/synch/cond/sql/TC_LOG_MMAP::COND_pool                 | YES     | YES   |
    | wait/synch/cond/sql/MYSQL_BIN_LOG::COND_prep_xids          | YES     | YES   |
    | wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond             | YES     | YES   |
    | wait/synch/cond/sql/MYSQL_RELAY_LOG::update_cond           | YES     | YES   |
    | wait/synch/cond/sql/Query_cache::COND_cache_status_changed | YES     | YES   |
    | wait/synch/cond/sql/COND_manager                           | YES     | YES   |
    | wait/synch/cond/sql/COND_rpl_status                        | YES     | YES   |
    | wait/synch/cond/sql/COND_server_started                    | YES     | YES   |
    | wait/synch/cond/sql/Delayed_insert::cond                   | YES     | YES   |
    | wait/synch/cond/sql/Delayed_insert::cond_client            | YES     | YES   |
    | wait/synch/cond/sql/Item_func_sleep::cond                  | YES     | YES   |
    | wait/synch/cond/sql/Master_info::data_cond                 | YES     | YES   |
    | wait/synch/cond/sql/Master_info::start_cond                | YES     | YES   |
    | wait/synch/cond/sql/Master_info::stop_cond                 | YES     | YES   |
    | wait/synch/cond/sql/Master_info::sleep_cond                | YES     | YES   |
    | wait/synch/cond/sql/Relay_log_info::data_cond              | YES     | YES   |
    | wait/synch/cond/sql/Relay_log_info::log_space_cond         | YES     | YES   |
    | wait/synch/cond/sql/Relay_log_info::start_cond             | YES     | YES   |
    | wait/synch/cond/sql/Relay_log_info::stop_cond              | YES     | YES   |
    | wait/synch/cond/sql/Relay_log_info::sleep_cond             | YES     | YES   |
    | wait/synch/cond/sql/TABLE_SHARE::cond                      | YES     | YES   |
    | wait/synch/cond/sql/User_level_lock::cond                  | YES     | YES   |
    | wait/synch/cond/sql/COND_thread_count                      | YES     | YES   |
    | wait/synch/cond/sql/COND_thread_cache                      | YES     | YES   |
    | wait/synch/cond/sql/COND_flush_thread_cache                | YES     | YES   |
    | wait/synch/cond/mysys/COND_alarm                           | YES     | YES   |
    | wait/synch/cond/mysys/IO_CACHE_SHARE::cond                 | YES     | YES   |
    | wait/synch/cond/mysys/IO_CACHE_SHARE::cond_writer          | YES     | YES   |
    | wait/synch/cond/mysys/my_thread_var::suspend               | YES     | YES   |
    | wait/synch/cond/mysys/THR_COND_threads                     | YES     | YES   |
    | wait/synch/cond/sql/Event_scheduler::COND_state            | YES     | YES   |
    | wait/synch/cond/sql/COND_queue_state                       | YES     | YES   |
    | wait/synch/cond/sql/MDL_context::COND_wait_status          | YES     | YES   |
    | wait/synch/cond/myisam/MI_SORT_INFO::cond                  | YES     | YES   |
    | wait/synch/cond/innodb/commit_cond                         | YES     | YES   |
    | wait/io/file/sql/map                                       | YES     | YES   |
    | wait/io/file/sql/binlog                                    | YES     | YES   |
    | wait/io/file/sql/binlog_index                              | YES     | YES   |
    | wait/io/file/sql/relaylog                                  | YES     | YES   |
    | wait/io/file/sql/relaylog_index                            | YES     | YES   |
    | wait/io/file/sql/casetest                                  | YES     | YES   |
    | wait/io/file/sql/dbopt                                     | YES     | YES   |
    | wait/io/file/sql/des_key_file                              | YES     | YES   |
    | wait/io/file/sql/ERRMSG                                    | YES     | YES   |
    | wait/io/file/sql/select_to_file                            | YES     | YES   |
    | wait/io/file/sql/file_parser                               | YES     | YES   |
    | wait/io/file/sql/FRM                                       | YES     | YES   |
    | wait/io/file/sql/global_ddl_log                            | YES     | YES   |
    | wait/io/file/sql/load                                      | YES     | YES   |
    | wait/io/file/sql/LOAD_FILE                                 | YES     | YES   |
    | wait/io/file/sql/log_event_data                            | YES     | YES   |
    | wait/io/file/sql/log_event_info                            | YES     | YES   |
    | wait/io/file/sql/master_info                               | YES     | YES   |
    | wait/io/file/sql/misc                                      | YES     | YES   |
    | wait/io/file/sql/partition                                 | YES     | YES   |
    | wait/io/file/sql/pid                                       | YES     | YES   |
    | wait/io/file/sql/query_log                                 | YES     | YES   |
    | wait/io/file/sql/relay_log_info                            | YES     | YES   |
    | wait/io/file/sql/send_file                                 | YES     | YES   |
    | wait/io/file/sql/slow_log                                  | YES     | YES   |
    | wait/io/file/sql/tclog                                     | YES     | YES   |
    | wait/io/file/sql/trigger_name                              | YES     | YES   |
    | wait/io/file/sql/trigger                                   | YES     | YES   |
    | wait/io/file/sql/init                                      | YES     | YES   |
    | wait/io/file/mysys/proc_meminfo                            | YES     | YES   |
    | wait/io/file/mysys/charset                                 | YES     | YES   |
    | wait/io/file/mysys/cnf                                     | YES     | YES   |
    | wait/io/file/csv/metadata                                  | YES     | YES   |
    | wait/io/file/csv/data                                      | YES     | YES   |
    | wait/io/file/csv/update                                    | YES     | YES   |
    | wait/io/file/myisam/data_tmp                               | YES     | YES   |
    | wait/io/file/myisam/dfile                                  | YES     | YES   |
    | wait/io/file/myisam/kfile                                  | YES     | YES   |
    | wait/io/file/myisam/log                                    | YES     | YES   |
    | wait/io/file/myisammrg/MRG                                 | YES     | YES   |
    | wait/io/file/innodb/innodb_data_file                       | YES     | YES   |
    | wait/io/file/innodb/innodb_log_file                        | YES     | YES   |
    | wait/io/file/innodb/innodb_temp_file                       | YES     | YES   |
    +------------------------------------------------------------+---------+-------+
    
    ---------------------------------------
    PERFORMANCE_SCHEMA setup_timers
    +-------------+-----------------+------------------+----------------+
    | TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
    +-------------+-----------------+------------------+----------------+
    | CYCLE       |      3349467661 |                1 |           7418 |
    | NANOSECOND  |      1000000000 |                1 |          11567 |
    | MICROSECOND |         1000000 |                1 |          11520 |
    | MILLISECOND |            1041 |                1 |          11770 |
    | TICK        |             102 |                1 |           2256 |
    +-------------+-----------------+------------------+----------------+
    +------+------------+
    | NAME | TIMER_NAME |
    +------+------------+
    | wait | CYCLE      |
    +------+------------+
    
    ---------------------------------------
    InnoDB Engine Mutex List
    +--------+----------------+-------------+
    | Type   | Name           | Status      |
    +--------+----------------+-------------+
    | InnoDB | buf0buf.c:1166 | os_waits=44 |
    | InnoDB | fil0fil.c:1608 | os_waits=1  |
    | InnoDB | log0log.c:832  | os_waits=7  |
    +--------+----------------+-------------+
    
    ---------------------------------------
    Event Waits Summary - All
    +-----------------------------------------------+-----------+------------+
    | EVENT_NAME                                    | WAIT_MS   | COUNT_STAR |
    +-----------------------------------------------+-----------+------------+
    | wait/io/file/innodb/innodb_log_file           | 2315.8433 |       1044 |
    | wait/io/file/innodb/innodb_log_file           |  177.9255 |          7 |
    | wait/synch/rwlock/innodb/checkpoint_lock      |  165.0938 |         12 |
    | wait/io/file/innodb/innodb_data_file          |  143.1848 |        150 |
    | wait/io/file/innodb/innodb_log_file           |  109.6981 |          3 |
    | wait/io/file/innodb/innodb_data_file          |   79.5981 |        197 |
    | wait/io/file/sql/FRM                          |   60.8595 |        429 |
    | wait/synch/mutex/innodb/mutex_list_mutex      |   44.7356 |      16546 |
    | wait/synch/mutex/innodb/rw_lock_list_mutex    |   44.4718 |      16464 |
    | wait/synch/mutex/innodb/flush_list_mutex      |   22.6376 |       8395 |
    | wait/io/file/myisam/kfile                     |   19.3142 |         71 |
    | wait/synch/mutex/innodb/flush_list_mutex      |   17.6950 |       6454 |
    | wait/synch/rwlock/innodb/checkpoint_lock      |   13.5572 |          2 |
    | wait/synch/mutex/innodb/buf_pool_mutex        |   11.6377 |        188 |
    | wait/synch/mutex/innodb/buf_pool_mutex        |   11.3442 |       3829 |
    | wait/synch/mutex/innodb/fil_system_mutex      |    6.6077 |       2371 |
    | wait/synch/mutex/innodb/buf_pool_mutex        |    3.3445 |        128 |
    | wait/synch/mutex/innodb/log_sys_mutex         |    3.0194 |        918 |
    | wait/synch/rwlock/innodb/fil_space_latch      |    2.1868 |        756 |
    | wait/synch/mutex/innodb/kernel_mutex          |    1.5821 |        458 |
    | wait/synch/mutex/innodb/log_sys_mutex         |    1.1328 |        397 |
    | wait/io/file/myisam/dfile                     |    1.1081 |         43 |
    | wait/synch/mutex/innodb/fil_system_mutex      |    0.9816 |        309 |
    | wait/synch/mutex/innodb/fil_system_mutex      |    0.8764 |        188 |
    | wait/synch/mutex/innodb/flush_list_mutex      |    0.8683 |        188 |
    | wait/synch/mutex/innodb/buf_pool_mutex        |    0.8517 |        292 |
    | wait/synch/mutex/sql/LOCK_plugin              |    0.6744 |        230 |
    | wait/synch/mutex/innodb/trx_doublewrite_mutex |    0.5484 |        148 |
    | wait/synch/mutex/innodb/kernel_mutex          |    0.5033 |        181 |
    | wait/synch/mutex/innodb/log_flush_order_mutex |    0.5027 |        195 |
    +-----------------------------------------------+-----------+------------+
    
    ---------------------------------------
    Event Waits Summary - wait/io/file only
    +--------------------------------------+-----------+------------+
    | EVENT_NAME                           | WAIT_MS   | COUNT_STAR |
    +--------------------------------------+-----------+------------+
    | wait/io/file/innodb/innodb_log_file  | 2315.8433 |       1044 |
    | wait/io/file/innodb/innodb_log_file  |  177.9255 |          7 |
    | wait/io/file/innodb/innodb_data_file |  143.1848 |        150 |
    | wait/io/file/innodb/innodb_log_file  |  109.6981 |          3 |
    | wait/io/file/innodb/innodb_data_file |   79.5981 |        197 |
    | wait/io/file/sql/FRM                 |   60.8595 |        429 |
    | wait/io/file/myisam/kfile            |   19.3142 |         71 |
    | wait/io/file/myisam/dfile            |    1.1081 |         43 |
    | wait/io/file/sql/casetest            |    0.1842 |         10 |
    | wait/io/file/mysys/charset           |    0.0913 |          3 |
    | wait/io/file/sql/ERRMSG              |    0.0677 |          5 |
    | wait/io/file/sql/pid                 |    0.0561 |          3 |
    | wait/io/file/sql/query_log           |    0.0410 |          4 |
    | wait/io/file/sql/query_log           |    0.0341 |          3 |
    | wait/io/file/sql/global_ddl_log      |    0.0141 |          2 |
    | wait/io/file/myisam/log              |    0.0000 |          0 |
    | wait/io/file/mysys/proc_meminfo      |    0.0000 |          0 |
    | wait/io/file/mysys/charset           |    0.0000 |          0 |
    | wait/io/file/mysys/cnf               |    0.0000 |          0 |
    | wait/io/file/csv/metadata            |    0.0000 |          0 |
    | wait/io/file/csv/data                |    0.0000 |          0 |
    | wait/io/file/csv/update              |    0.0000 |          0 |
    | wait/io/file/myisam/data_tmp         |    0.0000 |          0 |
    | wait/io/file/myisam/dfile            |    0.0000 |          0 |
    | wait/io/file/myisam/kfile            |    0.0000 |          0 |
    | wait/io/file/sql/init                |    0.0000 |          0 |
    | wait/io/file/sql/query_log           |    0.0000 |          0 |
    | wait/io/file/sql/LOAD_FILE           |    0.0000 |          0 |
    | wait/io/file/sql/log_event_data      |    0.0000 |          0 |
    | wait/io/file/sql/log_event_info      |    0.0000 |          0 |
    +--------------------------------------+-----------+------------+
     
    #16 eva2000, Jul 2, 2012
    Last edited: Jul 3, 2012
  17. AaRAdyOcU

    AaRAdyOcU Registered

    Joined:
    Sep 23, 2009
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Great job..
     
  18. xman888

    xman888 Well-Known Member

    Joined:
    Nov 23, 2005
    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    6
    Hello eva2000,

    Thank you so much for this.

    Guys at cpanel, I have now installed the software: mysqlmonlite.sh - I am wondering if I can pay someone at cpanel to look at my VPS, can come up with some solutions to reduce the intermittent high server loads that occurs.

    Can someone buzz me back how I can engage your services?

    Kind regards,
    Anthony
     
  19. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
  20. chae

    chae Well-Known Member

    Joined:
    Apr 19, 2003
    Messages:
    145
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Auckland, New Zealand
    mysqlmymonlite.sh & mysqltuner option

    Hi Yah eva2000, just inherited a server which at times sees mysql loads shooting up to well over 100% CPU. Loaded on your wonderful script when I run the following: ./mysqlmymonlite.sh run I get the output with a detailed breakdown of each section as expected but when I run the mysqltuner part there's no report generated ? Am I doing something wrong ?? Many thanks in advance...

    [root /root/tool]# ./mysqlmymonlite.sh mysqltuner

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

    Report Generated:
    Wed Mar 27 09:45:16 NZDT 2013

    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    mysqltuner.pl [found]

    >> MySQLTuner 1.2.1 mod - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Modified by George Liu (eva2000) at vbtechsupport.com
    >> Run with '--help' for additional options and output filtering
    [[0;31m!![0m] The --forcemem option is required for remote connections

    Report Complete:
    Wed Mar 27 09:45:16 NZDT 2013
    ----------------------------
    [root /root/tool]#
     
Loading...

Share This Page