mysqlmymonlite.sh server stats gathering tool for cPanel Server

Facundo · Oct 29, 2014

If you recived this error:

Code:

t/10connect.t ........................ Bailout called.  Further testing stopped:  ERROR: Access denied for user 'root'@'localhost' (using password: NO)
FAILED--Further testing stopped: ERROR: Access denied for user 'root'@'localhost' (using password: NO)

You can fix with this command from root shell:

Code:

# /usr/local/cpanel/scripts/perlinstaller DBD::mysql

david.cocos · Nov 11, 2014

Hello,

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

eva2000 · Nov 11, 2014

david.cocos said:
Hello,

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

did you set a mysql USER/PASS variable and did you change MYSQLHOST at all from default localhost ? mysqltuner should only prompt for forcemem if MYSQLHOST is set to a remote mysql server other than localhost/127.0.0.1

mysqltuner.pl code

Code:

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

david.cocos · Nov 14, 2014

eva2000 said:
did you set a mysql USER/PASS variable and did you change MYSQLHOST at all from default localhost ? mysqltuner should only prompt for forcemem if MYSQLHOST is set to a remote mysql server other than localhost/127.0.0.1

mysqltuner.pl code

Code:

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

I have put the mysql username and password in the settings file (config.ini), and mysql is on the same server with everything else.

katmai · Dec 29, 2014

Facundo said:

If you recived this error:

Code:

t/10connect.t ........................ Bailout called.  Further testing stopped:  ERROR: Access denied for user 'root'@'localhost' (using password: NO)
FAILED--Further testing stopped: ERROR: Access denied for user 'root'@'localhost' (using password: NO)

You can fix with this command from root shell:

Code:

# /usr/local/cpanel/scripts/perlinstaller DBD::mysql

had the same issue. this worked. thanks.

caisc · Jan 16, 2015

Many thanks for such brilliant work and offering it for free to the community !
I just installed it on one of our client server who has issues with mysql performance and it showed up some vital stats clearly pointing to the bottlenecks.

bruceleeon · Mar 2, 2015

Same issue here, MySQL is localhost

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

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

eva2000 · Mar 2, 2015

strange indeed.. what does full output look like ?

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

mysqltuner

Code:

/root/tools/mysqlmymonlite.sh mysqltuner

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

Report Generated:
Mon Mar 2 17:39:32 CET 2015

-------------------------------------------------
mysqltuner output
-------------------------------------------------

 >>  MySQLTuner 1.3.0 mod - Major Hayden <[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
[OK] Currently running supported MySQL version 5.6.23
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 74M (Tables: 297)
[--] Data in InnoDB tables: 9M (Tables: 121)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 505K (Tables: 2)
[!!] Total fragmented tables: 127

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 6h 21m 24s (237K q [2.170 qps], 25K conn, TX: 335M, RX: 31M)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 11.5G global + 5.0M per thread (800 max threads)
[OK] Maximum possible memory usage: 15.4G (49% of installed RAM)
[OK] Slow queries: 0% (0/237K)
[OK] Highest usage of available connections: 0% (7/800)
[OK] Key buffer size / total MyISAM indexes: 4.9G/24.5M
[OK] Key buffer hit rate: 100.0% (7M cached / 2K reads)
[OK] Query cache efficiency: 85.8% (137K cached / 160K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 31% (17K on disk / 55K total)
[OK] Thread cache hit rate: 99% (7 created / 25K connections)
[OK] Table cache hit rate: 97% (1K open / 1K opened)
[OK] Open file limit used: 0% (990/262K)
[OK] Table locks acquired immediately: 99% (63K immediate / 63K locks)
[OK] InnoDB buffer pool / data size: 6.0G/9.5M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses


Report Complete:
Mon Mar 2 17:39:33 CET 2015
----------------------------

mysqlreport

Code:

/root/tools/mysqlmymonlite.sh mysqlreport

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

Report Generated:
Mon Mar 2 17:39:26 CET 2015

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

MySQL   5.6.23              uptime 1 6:21:17    Mon Mar  2 17:39:26 2015

__ Key _________________________________________________________________
Buffer used     2.81M of    4.88G   %Used:   0.06
  Current     959.52M              %Usage:  19.19
Write hit      40.34%
Read hit       99.96%

__ Questions ___________________________________________________________
Total         237.14k       2.2/s
  QC Hits     137.70k       1.3/s  %Total:  58.07
  Com_         49.84k       0.5/s           21.02
  COM_QUIT     25.77k       0.2/s           10.87
  DMS          24.47k       0.2/s           10.32
  -Unknown        638       0.0/s            0.27
Slow 10 s           0         0/s            0.00  %DMS:   0.00 Log:
DMS            24.47k       0.2/s           10.32
  SELECT       22.75k       0.2/s            9.59         92.99
  DELETE          587       0.0/s            0.25          2.40
  INSERT          530       0.0/s            0.22          2.17
  UPDATE          484       0.0/s            0.20          1.98
  REPLACE         115       0.0/s            0.05          0.47
Com_           49.84k       0.5/s           21.02
  set_option   21.36k       0.2/s            9.01
  change_db    13.82k       0.1/s            5.83
  show_fields  13.15k       0.1/s            5.54

__ Rows ________________________________________________________________
Rows           16.85M     154.2/s
  Using idx    16.12M     147.5/s  %Index:  95.62
Rows/question   71.08

__ SELECT and Sort _____________________________________________________
Scan           18.90k       0.2/s %SELECT:  83.09
Range             862       0.0/s            3.79
Full join          63       0.0/s            0.28
Range check         0         0/s            0.00
Full rng join       0         0/s            0.00
Sort scan       1.37k       0.0/s
Sort range         49       0.0/s
Sort mrg pass       0         0/s

__ Query Cache _________________________________________________________
Memory usage    5.91M of   96.00M  %Usage:   6.15
Block Fragmnt   8.30%
Hits          137.70k       1.3/s
Inserts         1.46k       0.0/s
Insrt:Prune   1.46k:1       0.0/s
Hit:Insert    94.25:1

__ Table Locks _________________________________________________________
Waited              2       0.0/s  %Total:   0.00
Immediate      63.34k       0.6/s

__ Tables ______________________________________________________________
Open             1033 of  10000    %Cache:  10.33
Opened          1.06k       0.0/s

__ Connections _________________________________________________________
Max used            7 of    800      %Max:   0.88
Total          25.78k       0.2/s

__ Created Temp ________________________________________________________
Disk table     17.54k       0.2/s   %Disk:  45.88
Table          38.22k       0.3/s    Size: 512.0M
File                6       0.0/s

__ Threads _____________________________________________________________
Running             1 of      2
Created             7       0.0/s
Slow                0         0/s
Cached              5 of    256      %Hit:  99.97

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

__ Bytes _______________________________________________________________
Sent          335.84M      3.1k/s
Received       31.57M     288.9/s

__ InnoDB Buffer Pool __________________________________________________
Usage          15.28M of    6.00G  %Usage:   0.25
Read hit       99.67%
Pages
  Free        392.24k              %Total:  99.75
  Data            969                        0.25  %Drty:   0.00
  Misc              9                        0.00
  Latched           0                        0.00
Reads         293.18k       2.7/s
  From disk       964       0.0/s   %Disk:   0.33
  Ahead Rnd         0         0/s
Writes            622       0.0/s
Wait Free           0         0/s   %Wait:   0.00
Flushes           146       0.0/s

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

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads           985       0.0/s
  Writes          343       0.0/s
  fsync            91       0.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created           6       0.0/s
  Read            963       0.0/s
  Written         146       0.0/s

Rows
  Deleted           6       0.0/s
  Inserted          4       0.0/s
  Read          1.93M      17.7/s
  Updated          14       0.0/s

Report Complete:
Mon Mar 2 17:39:26 CET 2015
----------------------------

eva2000 · Mar 3, 2015

heads up

mysqlmymonlite.sh 0.5.3 released today to fix that pesky --forcemem prompt - latest download at mysqlmymonlite.sh & mysqlmymon.sh server and mysql stats gathering tool

G+ Announcememt https://plus.google.com/b/108811075440113919897/108811075440113919897/posts/N1He4fD6wh3

eva2000 · Mar 3, 2015

Also updated my modified mysqltuner.pl version from 1.3.0 to 1.4.0. To update for mysqlmymonlite.sh, just delete /root/mysqltuner.pl and re-run

Code:

./mysqlmymonlite.sh mysqltuner

yes MariaDB 10 is listed unsupported due to perl code's version match against it, but it should work fine

Code:

[!!] Currently running unsupported MySQL version 10.0.17-MariaDB

I'm no perl coder, so if anyone has the fix here's the section of code in line 473 of mysqltuner.pl

Code:

# Checks for supported or EOL'ed MySQL versions
my ($mysqlvermajor,$mysqlverminor);
sub validate_mysql_version {
    ($mysqlvermajor,$mysqlverminor) = $myvar{'version'} =~ /(\d+)\.(\d+)/;
    if (!mysql_version_ge(5)) {
        badprint "Your MySQL version ".$myvar{'version'}." is EOL software!  Upgrade soon!\n";
    } elsif (mysql_version_ge(6)) {
        badprint "Currently running unsupported MySQL version ".$myvar{'version'}."\n";
    } else {
        goodprint "Currently running supported MySQL version ".$myvar{'version'}."\n";
    }
}

Code:

 /root/tools/mysqlmymonlite.sh mysqltuner

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

Report Generated:
Wed Mar 4 04:38:01 UTC 2015

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

>>  MySQLTuner 1.4.0 mod - Major Hayden <[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
[OK] Logged in using credentials passed on the command line
[!!] Currently running unsupported MySQL version 10.0.17-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 16h 44m 20s (494 q [0.008 qps], 203 conn, TX: 1M, RX: 54K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 144.0M global + 800.0K per thread (300 max threads)
[OK] Maximum possible memory usage: 378.4M (36% of installed RAM)
[OK] Slow queries: 0% (0/494)
[OK] Highest usage of available connections: 0% (1/300)
[OK] Key buffer size / total MyISAM indexes: 32.0M/122.0K
[!!] Query cache efficiency: 0.0% (0 cached / 156 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10 sorts)
[OK] Temporary tables created on disk: 4% (11 on disk / 243 total)
[OK] Thread cache hit rate: 99% (2 created / 203 connections)
[OK] Table cache hit rate: 431% (82 open / 19 opened)
[OK] Open file limit used: 0% (58/8K)
[OK] Table locks acquired immediately: 100% (78 immediate / 78 locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
    query_cache_limit (> 512K, or use smaller result sets)


Report Complete:
Wed Mar 4 04:38:01 UTC 2015
----------------------------

eva2000 · Mar 3, 2015

Also don't forget you can extend mysqlmymonlite.sh with your own custom commands and stats gather output outlined at mysqlmymon.com/#custom

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

1. create a incopt.inc file in same directory as mysqlmymonlite.sh and place in it the 3 lines

Code:

    echo ""
    echo "memcached-tool 127.0.0.1:11211 stats"
    memcached-tool 127.0.0.1:11211 stats

2. then run mysqlmymonlite.sh custom command to see output

Code:

./mysqlmymonlite.sh custom   

memcached-tool 127.0.0.1:11211 stats
#127.0.0.1:11211   Field       Value
         accepting_conns           1
               auth_cmds           0
             auth_errors           0
                   bytes           0
              bytes_read           7
           bytes_written           0
              cas_badval           0
                cas_hits           0
              cas_misses           0
               cmd_flush           0
                 cmd_get           0
                 cmd_set           0
               cmd_touch           0
             conn_yields           0
   connection_structures           6
       crawler_reclaimed           0
        curr_connections           5
              curr_items           0
               decr_hits           0
             decr_misses           0
             delete_hits           0
           delete_misses           0
       evicted_unfetched           0
               evictions           0
       expired_unfetched           0
                get_hits           0
              get_misses           0
              hash_bytes      524288
       hash_is_expanding           0
        hash_power_level          16
               incr_hits           0
             incr_misses           0
                libevent 2.0.22-stable
          limit_maxbytes     8388608
     listen_disabled_num           0
       lrutail_reflocked           0
            malloc_fails           0
                     pid         887
            pointer_size          64
               reclaimed           0
            reserved_fds          20
           rusage_system    1.689743
             rusage_user    1.638750
   slab_reassign_running           0
             slabs_moved           0
                 threads           4
                    time  1425446357
       total_connections           6
             total_items           0
              touch_hits           0
            touch_misses           0
                  uptime       62737
                 version      1.4.22

You can get very creative if you know shell or SSH command line well to add your own commands or stats gather to incopt.inc file

Tobias Karlsson · Jul 31, 2015

When i run this i only end up whit a empty mysqlmymonlite_stats.txt file. What could i be doing wrong?

eva2000 · Aug 22, 2015

Tobias Karlsson said:
When i run this i only end up whit a empty mysqlmymonlite_stats.txt file. What could i be doing wrong?

what's exact command you're running ? and did you edit mysqlmymonlite.sh for mysql root password ?

eva2000 · Aug 25, 2015

mysqlmymonlite.sh 0.5.4 has been released. You can download from here. Install video here.

Changes of note are:

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

Examples for mysqlmymolite.sh 0.5.4 mysqlreport and mysqltuner outputs

Code:

 ./mysqlmymonlite.sh         
please see help for all options available
./mysqlmymonlite.sh --help

install mysqlmymonlite.sh at /root
chmod +x mysqlmymonlite.sh

./mysqlmymonlite.sh --help
./mysqlmymonlite.sh check
./mysqlmymonlite.sh run
./mysqlmymonlite.sh mysql
./mysqlmymonlite.sh vmstat
./mysqlmymonlite.sh showcreate
./mysqlmymonlite.sh showindex
./mysqlmymonlite.sh vbshowtables
./mysqlmymonlite.sh dblist
./mysqlmymonlite.sh mysqlreport
./mysqlmymonlite.sh mysqlfullreport
./mysqlmymonlite.sh mysqltuner
./mysqlmymonlite.sh psmem
./mysqlmymonlite.sh pschema

Code:

./mysqlmymonlite.sh mysqlreport

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

Report Generated:
Tue Aug 25 14:30:16 UTC 2015

----------------------------
mysqlreport output
----------------------------
MariaDB 10.0.21-MariaDB     uptime 14 17:58:6   Tue Aug 25 14:30:17 2015

__ Key _________________________________________________________________
Buffer used    60.00k of   32.00M   %Used:   0.18
  Current       5.85M              %Usage:  18.28
Write hit      58.86%
Read hit       99.64%

__ Questions ___________________________________________________________
Total           1.32k       0.0/s
  DMS             971       0.0/s  %Total:  73.45
  -Unknown        275       0.0/s           20.80
  QC Hits         246       0.0/s           18.61
  Com_            222       0.0/s           16.79
  COM_QUIT        158       0.0/s           11.95
Slow 1 s            0         0/s            0.00  %DMS:   0.00 Log:
DMS               971       0.0/s           73.45
  SELECT          788       0.0/s           59.61         81.15
  UPDATE          144       0.0/s           10.89         14.83
  INSERT           34       0.0/s            2.57          3.50
  DELETE            5       0.0/s            0.38          0.51
  REPLACE           0         0/s            0.00          0.00
Com_              222       0.0/s           16.79
  show_fields      60       0.0/s            4.54
  show_status      32       0.0/s            2.42
  set_option       29       0.0/s            2.19

__ Rows ________________________________________________________________
Rows           17.72k       0.0/s
  Using idx       610       0.0/s  %Index:   3.44
Rows/question   13.40

__ SELECT and Sort _____________________________________________________
Scan              159       0.0/s %SELECT:  20.18
Range               2       0.0/s            0.25
Full join           0         0/s            0.00
Range check         0         0/s            0.00
Full rng join       0         0/s            0.00
Sort scan          16       0.0/s
Sort range          3       0.0/s
Sort mrg pass       0         0/s

__ Query Cache _________________________________________________________
Memory usage   17.15k of   16.00M  %Usage:   0.10
Block Fragmnt 100.00%
Hits              246       0.0/s
Inserts           365       0.0/s
Insrt:Prune     365:1       0.0/s
Hit:Insert     0.67:1

__ Table Locks _________________________________________________________
Waited              0         0/s  %Total:   0.00
Immediate         711       0.0/s

__ Tables ______________________________________________________________
Open               82 of   2048    %Cache:   4.00
Opened             29       0.0/s

__ Connections _________________________________________________________
Max used            1 of    300      %Max:   0.33
Total             160       0.0/s

__ Created Temp ________________________________________________________
Disk table         60       0.0/s   %Disk:  44.12
Table             136       0.0/s    Size:  32.0M
File                6       0.0/s

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

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

__ Bytes _______________________________________________________________
Sent            1.04M       0.8/s
Received      388.59k       0.3/s

__ InnoDB Buffer Pool __________________________________________________
Usage           2.48M of   47.98M  %Usage:   5.18
Read hit       73.77%
Pages
  Free          2.91k              %Total:  94.82
  Data            159                        5.18  %Drty:   0.00
  Misc              0                        0.00
  Latched           0                        0.00
Reads             610       0.0/s
  From disk       160       0.0/s   %Disk:  26.23
  Ahead Rnd         0         0/s
Writes              1       0.0/s
Wait Free           0         0/s   %Wait:   0.00
Flushes             1       0.0/s
  LRU               0         0/s  %Flush:   0.00

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

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

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

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

__ Aria Pagecache ______________________________________________________
Buffer used    16.00k of    8.00M   %Used:   0.20
  Current     336.00k              %Usage:   4.10
Write hit     100.00%
Read hit       88.06%

Report Complete:
Tue Aug 25 14:30:17 UTC 2015
----------------------------

Code:

./mysqlmymonlite.sh mysqltuner

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

Report Generated:
Tue Aug 25 14:30:27 UTC 2015

-------------------------------------------------
mysqltuner output
-------------------------------------------------
>>  MySQLTuner 1.5.1 - Major Hayden <[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
[OK] Currently running supported MySQL version 10.0.21-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] There is not basic password file list !

-------- Performance Metrics -------------------------------------------------
[--] Up for: 14d 17h 58m 16s (1K q [0.001 qps], 175 conn, TX: 1M, RX: 392K)
[--] Reads / Writes: 81% / 19%
[--] Binary logging is disabled
[--] Total buffers: 152.0M global + 1.0M per thread (300 max threads)
[OK] Maximum reached memory usage: 153.0M (14.94% of installed RAM)
[OK] Maximum possible memory usage: 461.4M (45.06% of installed RAM)
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 0% (1/300)
[OK] Aborted connections: 0.00%  (0/175)
[OK] Query cache efficiency: 23.6% (246 cached / 1K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 19 sorts)
[!!] Temporary tables created on disk: 40% (60 on disk / 147 total)
[OK] Thread cache hit rate: 99% (1 created / 175 connections)
[OK] Table cache hit rate: 282% (82 open / 29 opened)
[OK] Open file limit used: 0% (58/8K)
[OK] Table locks acquired immediately: 100% (711 immediate / 711 locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.3% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/122.0K
[OK] Read Key buffer hit rate: 99.6% (1K cached / 7 reads)
[!!] Write Key buffer hit rate: 58.9% (457 cached / 188 writes)

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

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

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

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)

Report Complete:
Tue Aug 25 14:30:27 UTC 2015
----------------------------

Masimo · Feb 1, 2016

My report is not complete.

[[email protected] mysqlmymonlite]# sh /root/tools/mysqlmymonlite.sh mysqltuner

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

Report Generated:
Mon Feb 1 11:35:11 2016

-------------------------------------------------
mysqltuner output
-------------------------------------------------

Report Complete:
Mon Feb 1 11:38:24 2016
----------------------------

eva2000 · Feb 3, 2016

@Masimo what version of mysql you using ?

eva2000 · Feb 3, 2016

Updated mysqltuner.pl to 1.6.4 bundle for mysqlmymonlite.sh. To update just remove old version and rerun mysqltuner option

Code:

rm /root/mysqltuner.pl
./mysqlmymonlite.sh mysqltuner

Code:

/root/tools/mysqlmymonlite.sh mysqltuner

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

Report Generated:
Wed Feb 3 10:36:33 UTC 2016

-------------------------------------------------
mysqltuner output
-------------------------------------------------
mysqltuner.pl [found]
 >>  MySQLTuner 1.6.4 - Major Hayden <[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
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.0.23-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM 
[--] Data in InnoDB tables: 2G (Tables: 24)
[!!] Total fragmented tables: 24

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

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6h 52m 35s (491 q [0.020 qps], 188 conn, TX: 985K, RX: 52K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Total buffers: 416.0M global + 1.0M per thread (300 max threads)
[OK] Maximum reached memory usage: 417.0M (40.73% of installed RAM)
[OK] Maximum possible memory usage: 725.4M (70.84% of installed RAM)
[OK] Slow queries: 0% (0/491)
[OK] Highest usage of available connections: 0% (1/300)
[OK] Aborted connections: 0.53%  (1/188)
[!!] Query cache efficiency: 0.0% (0 cached / 199 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 132 total)
[OK] Thread cache hit rate: 99% (1 created / 188 connections)
[OK] Table cache hit rate: 246% (106 open / 43 opened)
[OK] Open file limit used: 0% (58/8K)
[OK] Table locks acquired immediately: 100% (90 immediate / 90 locks)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.3% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/122.0K
[OK] Read Key buffer hit rate: 95.0% (40 cached / 2 reads)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 192.0M/2.7G
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[OK] InnoDB Used buffer: 91.67% (11263 used/ 12287 total)
[!!] InnoDB Read buffer efficiency: 31.01% (5209 hits/ 16799 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

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

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

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

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
    query_cache_limit (> 512K, or use smaller result sets)
    innodb_buffer_pool_size (>= 2G) if possible.
    innodb_buffer_pool_instances (=1)

Report Complete:
Wed Feb 3 10:36:33 UTC 2016
----------------------------

Masimo · Feb 3, 2016

eva2000 said:
@Masimo what version of mysql you using ?

5.6.25

eva2000 · Feb 3, 2016

strange looks to me working for me

Code:

/mysqlmymonlite.sh mysqltuner

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

Report Generated:
Wed Feb 3 03:58:26 PST 2016

-------------------------------------------------
mysqltuner output
-------------------------------------------------
>>  MySQLTuner 1.6.4 - Major Hayden <[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
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.28
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 4G (Tables: 984)
[--] Data in InnoDB tables: 32M (Tables: 255)
[--] Data in MEMORY tables: 0B (Tables: 27)
[!!] Total fragmented tables: 9

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

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 28d 2h 26m 24s (197K q [0.081 qps], 76K conn, TX: 176M, RX: 23M)
[--] Reads / Writes: 82% / 18%
[--] Binary logging is disabled
[--] Total buffers: 792.0M global + 1.5M per thread (500 max threads)
[OK] Maximum reached memory usage: 801.0M (80.43% of installed RAM)
[!!] Maximum possible memory usage: 1.5G (154.83% of installed RAM)
[OK] Slow queries: 0% (0/197K)
[OK] Highest usage of available connections: 1% (6/500)
[OK] Aborted connections: 0.01%  (8/76552)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[OK] Temporary tables created on disk: 16% (164K on disk / 1M total)
[OK] Thread cache hit rate: 99% (6 created / 76K connections)
[OK] Table cache hit rate: 83% (2K open / 3K opened)
[OK] Open file limit used: 18% (3K/16K)
[OK] Table locks acquired immediately: 99% (36K immediate / 36K locks)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.9% (50M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/1.0G
[OK] Read Key buffer hit rate: 98.9% (306K cached / 3K reads)
[!!] Write Key buffer hit rate: 46.6% (14K cached / 7K writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 256.0M/33.0M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[!!] InnoDB Used buffer: 6.49% (1064 used/ 16383 total)
[OK] InnoDB Read buffer efficiency: 99.95% (2278557 hits/ 2279620 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 4 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

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

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

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

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
  *** Please note this recommendation is not entirely accurate.
      The formula used to calculate max memory usage assumes all queries utilise
      all memory buffers simultaneously. When in reality it is very rare for a
      query to engage & utilise all memory buffers simultaneously.

      Formula also assumes all predefined max_connections are reached.
      You could have set max_connections = 1000 and in a whole year
      of usage never hit beyond 50 max_used_connections. So your real MySQL
      memory usage is only 1/20th of theorectical max memory usage reported.

      So real max memory usage will never reach this peak.
      So do not be too concerned with this warning.

      It is better to monitor your real MySQL max_used_connection and MySQL
      memory usage over time and adjust accordingly.

      You can use tools such as Cacti, Munin or mysqlmymonlite.sh at mysqlmymon.com to
      monitor your MySQL memory usage over time.

      MySQL performs optimally when its required amount of memory is met.
      Reducing and starving MySQL memory allocation to adhere to this
      artificial max memory warning - of which in reality will never be reached,
      will only reduce MySQL performance in many cases ***


    query_cache_size (>= 8M)
    innodb_buffer_pool_instances (=1)

Report Complete:
Wed Feb 3 03:58:27 PST 2016
----------------------------

eva2000 · Feb 3, 2016

@Masimo make sure you have correct mysql root user/pass details and if they're same in /root/.my.cnf

Thread starter	Similar threads	Forum	Replies	Date
L	Server response time	Workarounds and Optimization	1	May 26, 2020
J	Server Resources reaching limit when multiple users accessing Wordpress Admin	Workarounds and Optimization	2	May 5, 2020
A	my server is not optimize	Workarounds and Optimization	7	Feb 24, 2020
M	Server Automation	Workarounds and Optimization	1	Feb 20, 2020
	Server getting Crushed, where should I start optimizing? Apache or PHP-FPM?	Workarounds and Optimization	2	Feb 10, 2020

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Member

Member

Well-Known Member

Member

Well-Known Member

Well-Known Member

Active Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Active Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member