Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Quick Links


Categories

In this Discussion

› › mysqlmymonlite - MySQL optimization tool (by eva2000)
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

mysqlmymonlite - MySQL optimization tool (by eva2000)

MikePTMikePT Moderator, Patron Provider, Veteran
edited May 2017 in General

I've just found this gem...

https://forums.cpanel.net/threads/mysqlmymonlite-sh-server-stats-gathering-tool-for-cpanel-server.268011/

Official link: http://mysqlmymon.com/

--
What is mysqlmymonlite.sh ?

mysqlmymonlite.sh is a MySQL optimization tool - a lite version of more extensive featured mysqlmymon.sh - a MySQL and system monitoring script written by George Liu (eva2000) for quick stats check for apache, nginx, litespeed web server and mysql stats for the following operating systems:

CentOS WHM/Cpanel
CentOS

Debian 6.x systems

Thank you @eva2000 for being part of this community.
I known you from Centminmod, and now I see you've done this as well.
You are, basically, a hero. :-)

+1 kudos for you.

Thanked by 5Hybrid raindog308 eva2000 inthecloudblog totoybilbobaggins

Comments

  • raindog308raindog308 Administrator, Veteran

    "Thus mysqlmymonlite.sh was born and is used mainly for vBulletin server optimisation requests on public forums providing nginx, apache, litespeed web server, openlitespeed web server, mysql, cpu, memory, disk usage stats and general overview of server status in order to understand a vB client's server environment and loads. All this info takes less than <12 seconds to gather once the script is run."

    So is this primarily a "thank you for contacting support/posting in the help forum, to better help you, please post the output of mysqlmymonlite.sh"?

    I'm trying to place it in my mind compared to mysqltuner.pl.

  • eva2000eva2000 Veteran
    edited June 2017

    MikePT said: Thank you @eva2000 for being part of this community. I known you from Centminmod, and now I see you've done this as well. You are, basically, a hero. :-)

    +1 kudos for you.

    Glad you like. The script was created back in 2011 so it's been 6+ yrs now. I just got tired of back and forth questions I see and experienced with new clients or unknown systems for troubleshooting or optimisation advice - what server specs? server config ? server stats ? they have etc.

    Especially on a public forum when someone asks for optimisation or troubleshooting advice i.e. I have high cpu usage please help etc etc. It shouldn't take several posts or back and forth to get a basic idea of a server setup. Just run mysqlmymonlite.sh and usually within less than <12 seconds you'd have enough info to go off of :)

    Centmin Mod users are also encouraged to use it as I have a dedicated thread for discussing mysqlmymonlite.sh at https://community.centminmod.com/threads/mysqlmymonlite-sh-addon.65/

    raindog308 said: So is this primarily a "thank you for contacting support/posting in the help forum, to better help you, please post the output of mysqlmymonlite.sh"?

    I'm trying to place it in my mind compared to mysqltuner.pl.

    Yeah that's essentially why mysqlmymonlite.sh was created. It provides an actual and more detailed context to the statistics and output gathered as opposed to mysqltuner.pl which is just mysql specific. The mysqlmymonlite.sh also bundles a slightly modified version of mysqltuner.pl in there too for folks use to using it. Modified just to elaborate and expand on some of the recommendations that it outputs.

    ./mysqlmymonlite.sh mysqltuner

-------------------------------------------------------------
System MySQL monitoring stats
mysqlmymonlite.sh - 0.5.5 mysqlmymon.com
compiled by George Liu (eva2000) centminmod.com
-------------------------------------------------------------

Report Generated:
Wed May 3 13:07:52 UTC 2017

-------------------------------------------------
mysqltuner output
-------------------------------------------------
 >>  MySQLTuner 1.7.1 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Modified by George Liu (eva2000) at https://centminmod.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.22-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(0B)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[!!] Log file /var/log/mysqld.log is empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[OK] /var/log/mysqld.log doesn't contain any warning.
[OK] /var/log/mysqld.log doesn't contain any error.
[--] 0 start(s) detected in /var/log/mysqld.log
[--] 0 shutdown(s) detected in /var/log/mysqld.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 2h 19m 12s (214 q [0.002 qps], 83 conn, TX: 634K, RX: 21K)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory     : 2.0G
[--] Max MySQL memory    : 386.7M
[--] Other process memory: 157.9M
[--] Total buffers: 152.0M global + 801.0K per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 152.8M (7.46% of installed RAM)
[OK] Maximum possible memory usage: 386.7M (18.88% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/214)
[OK] Highest usage of available connections: 0% (1/300)
[OK] Aborted connections: 0.00%  (0/83)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 73 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 1% (1 on disk / 60 total)
[OK] Thread cache hit rate: 98% (1 created / 83 connections)
[OK] Table cache hit rate: 93% (82 open / 88 opened)
[OK] Open file limit used: 0% (59/262K)
[OK] Table locks acquired immediately: 100% (52 immediate / 52 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 2 thread(s).
[--] Using default value is good enough for your version (10.1.22-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/124.0K
[!!] Read Key buffer hit rate: 81.8% (22 cached / 4 reads)
[!!] Write Key buffer hit rate: 50.0% (8 cached / 4 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is disabled.
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 8.0M/1B
[OK] Aria pagecache hit rate: 98.4% (61 cached / 1 reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Performance should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 512K, or use smaller result sets)
    performance_schema = ON enable PFS

Report Complete:
Wed May 3 13:07:53 UTC 2017
----------------------------

    You can also extend mysqlmymonlite.sh with your own commands specific to your systems too via

    ./mysqlmymonlite.sh custom

    This option allows you to create a separate includes file name it incopt.inc and place it in same directory 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

    Thanked by 1MikePT
  • MikePTMikePT Moderator, Patron Provider, Veteran

    I've used this script and works pretty well, helped a bit tunning MySQL, good job, will be using it many times.

  • eva2000eva2000 Veteran

    Glad to hear :)

    There's also a mysqlreport tool command tool to complement mysqltuner command

    ./mysqlmymonlite.sh mysqlreport

-------------------------------------------------------------
System MySQL monitoring stats
mysqlmymonlite.sh - 0.5.5 mysqlmymon.com
compiled by George Liu (eva2000) centminmod.com
-------------------------------------------------------------

Report Generated:
Wed May 31 22:18:02 UTC 2017

----------------------------
mysqlreport output
----------------------------

MariaDB 10.1.23-MariaDB     uptime 2 16:1:55    Wed May 31 22:18:02 2017

__ Key _________________________________________________________________
Buffer used         0 of  768.00M   %Used:   0.00
  Current     143.66M              %Usage:  18.71
Write hit       0.00%
Read hit        0.00%

__ Questions ___________________________________________________________
Total              32       0.0/s
  Com_             12       0.0/s  %Total:  37.50
  COM_QUIT         12       0.0/s           37.50
  DMS              10       0.0/s           31.25
  -Unknown          2       0.0/s            6.25
Slow 1 s            0         0/s            0.00  %DMS:   0.00 Log:
DMS                10       0.0/s           31.25
  SELECT           10       0.0/s           31.25        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_               12       0.0/s           37.50
  show_variab       6       0.0/s           18.75
  show_status       3       0.0/s            9.38
  show_databa       2       0.0/s            6.25

__ Rows ________________________________________________________________
Rows            3.58k       0.0/s
  Using idx         3       0.0/s  %Index:   0.08
Rows/question  111.88

__ SELECT and Sort _____________________________________________________
Scan               16       0.0/s %SELECT: 160.00
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           0         0/s
Sort range          0         0/s
Sort mrg pass       0         0/s

__ Query Cache _________________________________________________________
Memory usage   17.62k of  256.00M  %Usage:   0.01
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          21       0.0/s

__ Tables ______________________________________________________________
Open               11 of   8192    %Cache:   0.13
Opened             17       0.0/s

__ Connections _________________________________________________________
Max used            1 of   1000      %Max:   0.10
Total              14       0.0/s

__ Created Temp ________________________________________________________
Disk table          0         0/s   %Disk:   0.00
Table              16       0.0/s    Size:   1.0G
File                6       0.0/s

__ Threads _____________________________________________________________
Running             1 of      1
Created             1       0.0/s
Slow                0         0/s
Cached              0 of    384      %Hit:  92.86

__ Aborted _____________________________________________________________
Clients             0         0/s
Connects            0         0/s

__ Bytes _______________________________________________________________
Sent          117.19k       0.5/s
Received        3.44k       0.0/s

__ InnoDB Buffer Pool __________________________________________________
Usage           4.66M of    8.00G  %Usage:   0.06
Read hit       64.45%
Pages
  Free        523.98k              %Total:  99.94
  Data            298                        0.06  %Drty:   0.00
  Misc              0                        0.00
  Latched           0                        0.00
Reads             841       0.0/s
  From disk       299       0.0/s   %Disk:  35.55
  Ahead Rnd         0         0/s
Writes              1       0.0/s
Flushes             1       0.0/s
Wait Free           0         0/s   %Wait:   0.00

__ InnoDB Lock _________________________________________________________
Waits               0         0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads           318       0.0/s
  Writes            5       0.0/s
  fsync             5       0.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created           0         0/s
  Read            298       0.0/s
  Written           1       0.0/s

Rows
  Deleted           0         0/s
  Inserted          0         0/s
  Read              0         0/s
  Updated           0         0/s

__ Aria Pagecache ______________________________________________________
Buffer used         0 of    1.00G   %Used:   0.00
  Current      42.30M              %Usage:   4.13
Write hit       0.00%
Read hit        0.00%

Report Complete:
Wed May 31 22:18:02 UTC 2017
----------------------------
    Thanked by 3inthecloudblog MikePT simpleguy8288
  • WindsOfChangeWindsOfChange Member

    Where can I get a copy of this or where can I buy a copy of mysqlmon.sh ?

  • emghemgh Member

    @WindsOfChange said:
    Where can I get a copy of this or where can I buy a copy of mysqlmon.sh ?

    Just use your nearest time travel machine

    Thanked by 2Frameworks ariq01
  • gdnotmegdnotme Member

    holy necro batman

    Thanked by 2tjn Frameworks
  • MikePTMikePT Moderator, Patron Provider, Veteran
    edited July 2023

    Maybe @eva2000 can update us? :)

    I noticed the website is experiencing an SSL redirect issue, albeit, I don't think the project is dead yet. I am in fact interested in giving it another try.

  • MikePTMikePT Moderator, Patron Provider, Veteran

    Re-opened the thread, waiting for @eva2000 to update us here :)

  • szymonpszymonp Member

    @emgh said:

    @WindsOfChange said:
    Where can I get a copy of this or where can I buy a copy of mysqlmon.sh ?

    Just use your nearest time travel machine

    https://web.archive.org/web/20220518153621/https://mysqlmymon.com/download/mysqlmymonlite.zip

    the wayback machine is kinda like time travel 😉

  • LeviLevi Member

    To me, seems eva is inactive and abandoned his/her projects. Centmin was updated in May (beta version). Stable release is insanely old.

  • MikePTMikePT Moderator, Patron Provider, Veteran
    edited July 2023

    @LTniger said:
    To me, seems eva is inactive and abandoned his/her projects. Centmin was updated in May (beta version). Stable release is insanely old.

    He posted here recently. I hope all is well :(

    Edit: https://github.com/centminmod/centminmod

    Last change, June 9th.

    Thanked by 2eva2000 WindsOfChange
  • emghemgh Member

    @MikePT said:

    @LTniger said:
    To me, seems eva is inactive and abandoned his/her projects. Centmin was updated in May (beta version). Stable release is insanely old.

    He posted here recently. I hope all is well :(

    Edit: https://github.com/centminmod/centminmod

    Last change, June 9th.

    @eva2000

    Thanked by 1MikePT
  • eva2000eva2000 Veteran
    edited July 2023

    @MikePT fixed the mysqlmymon.com redirect issue but that mysqlmymonlite script hasn't been updated in years lol

    @LTniger said: Centmin was updated in May (beta version). Stable release is insanely old.

    If you're unsure, ask on official Centmin Mod forums https://community.centminmod.com/

    Make sure you're tracking the right branches as Centmin Mod has stable 124.00stable https://github.com/centminmod/centminmod/tree/124.00stable and beta branches https://community.centminmod.com/threads/centmin-mod-124-00stable-130-00beta01-releases.22673/. All active development updates go into beta https://github.com/centminmod/centminmod/tree/130.00beta01

    124.00stable Commit history was June 10, 2023.

    130.00beta01 Commit history https://github.com/centminmod/centminmod/commits/130.00beta01 where last commit was made July 12, 2023 so ~3 days ago

    Also all commits get posted on official Centmin Mod Githut commits forum too https://community.centminmod.com/forums/centmin-mod-github-commits.41/ so folks who are not comfortable using Github, can discuss commits on the forums too so you can take the forum RSS feed for tracking too if you need to follow Centmin Mod changes :)

    Basically, the flow of commits goes, 130.00beta01 for latest, 124.00stable and then 124.00stable to master merges usually done once a week.

    Centmin Mod is far from inactive :)

    Thanked by 2MikePT Shamli
  • aliletalilet Member

    I believe the script won't work with MariaDB?

  • eva2000eva2000 Veteran

    @alilet said: I believe the script won't work with MariaDB?

    You mean mysqlmymonlite.sh, it did work with MariaDB on cPanel way back but as I said I haven't tested or updated it in years for cPanel.

  • MikePTMikePT Moderator, Patron Provider, Veteran

    @eva2000 said:

    @alilet said: I believe the script won't work with MariaDB?

    You mean mysqlmymonlite.sh, it did work with MariaDB on cPanel way back but as I said I haven't tested or updated it in years for cPanel.

    Thank you for bringing the website back online! It saddens me to know that the project is no longer active. The friendly way it worked overall made it excellent.

  • eva2000eva2000 Veteran

    @MikePT said: Thank you for bringing the website back online! It saddens me to know that the project is no longer active. The friendly way it worked overall made it excellent.

    Yeah the script evolved to one built into my Centmin Mod LEMP stack for cminfo command https://community.centminmod.com/threads/cminfo-command-explained.11399/ :)

    Thanked by 2MikePT WindsOfChange
  • MikePTMikePT Moderator, Patron Provider, Veteran

    @eva2000 said:

    @MikePT said: Thank you for bringing the website back online! It saddens me to know that the project is no longer active. The friendly way it worked overall made it excellent.

    Yeah the script evolved to one built into my Centmin Mod LEMP stack for cminfo command https://community.centminmod.com/threads/cminfo-command-explained.11399/ :)

    Interesting. Thank you. I guess we can close this for now. Glad to see you here!

    Thanked by 1eva2000
This discussion has been closed.