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:
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.
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:
Menu options:
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.
Demo for ./mysqlmymonlite.sh showindex saved to disk at /home/myindexes
indexes listing for dbname = sbtest, tablename = sbtest1
Disclaimer:
By downloading and using mysqlmymonlite.sh WHM version script you understand and agree to the following terms of use:
Keeping mysqlmymonlite.sh up to date:
Currently, 2 methods to get notified for mysqlmymonlite.sh updates.
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 !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:
- 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.
- 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 psmemSample 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
----------------------------
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
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 | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+By downloading and using mysqlmymonlite.sh WHM version script you understand and agree to the following terms of use:
- 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.
- 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.
- 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.
- via Twitter at GEORGE LIU (@vbtechsupport) | Twitter
- mysqlmymonlite.sh has it's own Google+ Page for script updates too
Last edited by a moderator:
