mysqlmymonlite.sh server stats gathering tool for cPanel Server

eva2000

Well-Known Member
Aug 14, 2001
346
19
318
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 [CODE - /CODE] 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 Workarounds and 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 <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
 >>  Run with '--help' for additional options and output filtering
[!!] 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 GEORGE LIU (@vbtechsupport) | Twitter
  2. mysqlmymonlite.sh has it's own Google+ Page for script updates too


 
Last edited by a moderator:

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
43
348
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.
 

eva2000

Well-Known Member
Aug 14, 2001
346
19
318
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 <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
 >>  Run with '--help' for additional options and output filtering

-------- 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
----------------------------
 
Last edited:

eva2000

Well-Known Member
Aug 14, 2001
346
19
318
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
----------------------------
 

-GR-

Active Member
May 2, 2012
42
0
56
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.
 

eva2000

Well-Known Member
Aug 14, 2001
346
19
318
Brisbane, Australia
cPanel Access Level
Root Administrator
Twitter
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.
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 :)
 

eva2000

Well-Known Member
Aug 14, 2001
346
19
318
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 :)
 

eva2000

Well-Known Member
Aug 14, 2001
346
19
318
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.
 
Last edited:

eva2000

Well-Known Member
Aug 14, 2001
346
19
318
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 |
+--------------------------------------+-----------+------------+
 
Last edited:

xman888

Well-Known Member
Nov 23, 2005
61
1
158
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
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
43
348
somewhere over the rainbow
cPanel Access Level
Root Administrator

chae

Well-Known Member
Apr 19, 2003
145
0
166
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 <[email protected]>
>> 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]#