Welcome to Centmin Mod Community
Become a Member

Featured mysqlmymonlite.sh Addon

Discussion in 'Add Ons' started by eva2000, May 26, 2014.

  1. seopolis

    seopolis New Member

    9
    0
    1
    Aug 23, 2014
    Ratings:
    +0
    Local Time:
    9:46 PM
    5.5
    Yes, I have edited a few values there.
     
  2. eva2000

    eva2000 Administrator Staff Member

    44,702
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,794
    Local Time:
    4:46 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
  3. eva2000

    eva2000 Administrator Staff Member

    44,702
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,794
    Local Time:
    4:46 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    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 mysqlmymonlite 0.5.3 released.http://mysqlmymon.com/March 4th, 2015:…
     
  4. YuchiRO

    YuchiRO Member

    100
    6
    18
    Jan 12, 2015
    Ratings:
    +8
    Local Time:
    1:46 AM
    5.5.4
    I had a same problem.

    How to upgrade to 0.5.3. I didnt find this version on web.
     
  5. eva2000

    eva2000 Administrator Staff Member

    44,702
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,794
    Local Time:
    4:46 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
  6. YuchiRO

    YuchiRO Member

    100
    6
    18
    Jan 12, 2015
    Ratings:
    +8
    Local Time:
    1:46 AM
    5.5.4
    i delete all files in tool directory and re-download. Follow install video and i got problem:

    [root@tool]# chmod +x mysqlmymonlite.sh
    [root@tool]# ./mysqlmymonlite.sh run
    -bash: ./mysqlmymonlite.sh: /bin/bash^M: bad interpreter: No such file or directory
    [root@tool]# ./mysqlmymonlite.sh
    -bash: ./mysqlmymonlite.sh: /bin/bash^M: bad interpreter: No such file or directory
    [root@tool]#

    [root@tool]# ls -lh
    total 176K
    drwxr-xr-x 2 root root 4.0K Mar 4 02:26 centos
    drwxr-xr-x 2 root root 4.0K Mar 4 2015 centos_whm
    -rw-r--r-- 1 root root 13K Mar 4 2015 changelog.txt
    -rw-r--r-- 1 root root 14K Mar 4 2015 changelog_vb_bbcode.txt
    -rw-r--r-- 1 root root 3.5K Mar 4 2015 config.ini
    drwxr-xr-x 2 root root 4.0K Mar 4 2015 debian
    -rwxr-xr-x 1 root root 58K Mar 4 2015 mysqlmymonlite.sh
    -rw-r--r-- 1 root root 51K Mar 3 18:48 mysqlmymonlite.zip
    -rw-r--r-- 1 root root 13K Aug 8 2013 readme.txt
     
  7. eva2000

    eva2000 Administrator Staff Member

    44,702
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,794
    Local Time:
    4:46 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    my bad updated zip with fixed unix line endings

    web site at mysqlmymonlite.sh & mysqlmymon.sh server and mysql stats gathering tool also has fix for it too if you encounter this

     
  8. YuchiRO

    YuchiRO Member

    100
    6
    18
    Jan 12, 2015
    Ratings:
    +8
    Local Time:
    1:46 AM
    5.5.4
    [root@ tool]# ./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 03:00:45 UTC 2015

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

    >> MySQLTuner 1.3.0 mod - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at MySQLTuner-perl by major
    >> Modified by George Liu (eva2000) at vbtechsupport.com
    >> Run with '--help' for additional options and output filtering
    [OK] Logged in using credentials passed on the command line
    [OK] Currently running supported MySQL version 5.5.42-MariaDB
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED -InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 210M (Tables: 108)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 3

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

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 5m 50s (347K q [87.987 qps], 4K conn, TX: 1B, RX: 45M)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 88.0M global + 800.0K per thread (300 max threads)
    [OK] Maximum possible memory usage: 322.4M (4% of installed RAM)
    [OK] Slow queries: 0% (454/347K)
    [OK] Highest usage of available connections: 4% (12/300)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/67.2M
    [OK] Key buffer hit rate: 100.0% (430M cached / 12K reads)
    [OK] Query cache efficiency: 38.1% (168K cached / 443K selects)
    [!!] Query cache prunes per day: 1977138
    [OK] Sorts requiring temporary tables: 6% (3K temp sorts / 55K sorts)
    [!!] Temporary tables created on disk: 32% (38K on disk / 117K total)
    [OK] Thread cache hit rate: 99% (12 created / 4K connections)
    [OK] Table cache hit rate: 116% (182 open / 156 opened)
    [OK] Open file limit used: 3% (300/8K)
    [OK] Table locks acquired immediately: 100% (148K immediate / 148K locks)
    Use of uninitialized value $mystat{"Innodb_log_waits"} in concatenation (.) or
    string at /root/mysqltuner.pl line 1011 (#1)
    (W uninitialized) An undefined value was used as if it were already
    defined. It was interpreted as a "" or a 0, but maybe it was a mistake.
    To suppress this warning assign a defined value to your variables.

    To help you figure out what was undefined, perl will try to tell you the
    name of the variable (if any) that was undefined. In some cases it cannot
    do this, so it also tells you what operation you used the undefined value
    in. Note, however, that perl optimizes your program and the operation
    displayed in the warning may not necessarily appear literally in your
    program. For example, "that $foo" is usually optimized into "that "
    . $foo, and the warning will refer to the concatenation (.) operator,
    even though there is no . in your program.

    [OK] InnoDB log waits:
    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries.
    Do not forget to disable slow query logging after troubleshooting
    - For MySQL 5.0 MySQL :: MySQL 5.0 Reference Manual :: 5.2.4 The Slow Query Log
    - For MySQL 5.1 MySQL :: MySQL 5.1 Reference Manual :: 5.2.5 The Slow Query Log
    - For MySQL 5.5 MySQL :: MySQL 5.5 Reference Manual :: 5.2.5 The Slow Query Log
    - For MySQL 5.6 MySQL :: MySQL 5.6 Reference Manual :: 5.2.5 The Slow Query Log
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses

    Variables to adjust:
    query_cache_size (> 16M)
    tmp_table_size (> 32M, increase tmp_table_size)
    max_heap_table_size (> 32M, increase max_heap_table_size)



    Report Complete:
    Wed Mar 4 03:00:45 UTC 2015


    How to fix these warning... ?
    query_cache_size increase to 32Mb ?
    tmp_table_size increase to 64Mb ?
    max_heap_table_size increase to 64Mb ?

    My vps 8core and 8Gb. Max online at the same time is 150.

    Regards
     
  9. eva2000

    eva2000 Administrator Staff Member

    44,702
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,794
    Local Time:
    4:46 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Might want to wrap output in CODE tags for easier reading on forums.

    However, I only provide the tool as is, optimisation of your server/mysql is left up yourself or other members of the forum providing advice.
     
  10. eva2000

    eva2000 Administrator Staff Member

    44,702
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,794
    Local Time:
    4:46 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    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
    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 <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
    >>  Run with '--help' for additional options and output filtering
    [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
    ----------------------------
     
  11. eva2000

    eva2000 Administrator Staff Member

    44,702
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,794
    Local Time:
    4:46 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    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

    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 :)
     
  12. eva2000

    eva2000 Administrator Staff Member

    44,702
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,794
    Local Time:
    4:46 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    updated mysqltuner.pl from 1.40 mod to 1.43 mod version which supports MySQL 5.6/MariaDB 10 innodb buffer pool instances etc

    for existing mysqlmymonlite.sh users, just remove old mysqltuner.pl from /root/mysqltuner.pl and re-run mysqlmymonlite.sh to download latest version

    Code:
    rm -rf /root/mysqltuner.pl
    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:
    Mon Jun 15 15:22:18 UTC 2015
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    
    >>  MySQLTuner 1.4.3 mod - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
    >>  Run with '--help' for additional options and output filtering
    [!!] Currently running unsupported MySQL version 10.0.19-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
    [--] Data in InnoDB tables: 1M (Tables: 1)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [!!] Total fragmented tables: 1
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There is no anonymous account in all database users
    [OK] All database users have passwords assigned
    [--] There is 605 basic passwords in the list.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 51m 51s (3K q [0.581 qps], 1K conn, TX: 719K, RX: 567K)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 144.0M global + 1.0M per thread (300 max threads)
    [OK] Maximum possible memory usage: 453.4M (44% of installed RAM)
    [OK] Slow queries: 0% (0/3K)
    [OK] Highest usage of available connections: 0% (1/300)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/122.0K
    [!!] Key buffer hit rate: 93.3% (30 cached / 2 reads)
    [!!] Query cache efficiency: 0.0% (0 cached / 2K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8 sorts)
    [OK] Temporary tables created on disk: 0% (0 on disk / 70 total)
    [OK] Thread cache hit rate: 99% (1 created / 1K connections)
    [OK] Table cache hit rate: 415% (83 open / 20 opened)
    [OK] Open file limit used: 0% (58/8K)
    [OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
    [OK] InnoDB buffer pool / data size: 48.0M/1.5M
    [OK] InnoDB log waits: 0
    [--] MySQL version: 10.0.19-MariaDB is greater than MySQL 5.6.6(innodb_buffer_pool_instances is present)
    [OK] InnoDB buffer pool instances is lower than 64.
    [--] Buffer Pool Size: 48M
    [--] Buffer Pool Inst: 8
    [OK] InnoDB buffer pool instances is configurated for managing around 1Go Buffer pool size.
    -------- 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)
    
    
    Report Complete:
    Mon Jun 15 15:22:22 UTC 2015
    ----------------------------
     
  13. quicksalad

    quicksalad Member

    159
    9
    18
    May 31, 2015
    Ratings:
    +13
    Local Time:
    12:16 AM
    I get
    Code:
    [root@dd01 tool]# ./mysqlmymonlite.sh
    #: bad interpreter: No such file or directory##########
    
    in centos folder its working fine, is it permission issue?
     
  14. eva2000

    eva2000 Administrator Staff Member

    44,702
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,794
    Local Time:
    4:46 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    need to run from where you moved or placed the mysqlmymonlite.sh file and make sure to chmod 0755 it i.e. is it at /root/tools or /root/tool ?
     
  15. quicksalad

    quicksalad Member

    159
    9
    18
    May 31, 2015
    Ratings:
    +13
    Local Time:
    12:16 AM
    I moved it at /root/tool and checked the permission, it's already 0755 but I'm getting error mentioned above.
     
  16. eva2000

    eva2000 Administrator Staff Member

    44,702
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,794
    Local Time:
    4:46 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    what does first 2 lines in mysqlmymonlite.sh look like ?

    Code:
    head -n2 mysqlmymonlite.sh
    also see notes on official site at mysqlmymonlite.sh & mysqlmymon.sh server and mysql stats gathering tool

     
  17. quicksalad

    quicksalad Member

    159
    9
    18
    May 31, 2015
    Ratings:
    +13
    Local Time:
    12:16 AM
    Please see below @eva2000
    Code:
    [root@dd01 tool]# head -n2 mysqlmymonlite.sh
    # GRANT ALL PRIVILEGES ON *.* TO 'userinfo'@'localhost' IDENTIFIED BY 'enteryour
    # nginx.conf is located, you can define it in this variable to override the scri 
    I already did
    Code:
    dos2unix mysqlmymonlite.sh
    still I'm getting the same error, but with centos folder ./mysqlmymonlite.sh command work

    Thanks
     
  18. eva2000

    eva2000 Administrator Staff Member

    44,702
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,794
    Local Time:
    4:46 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    you do not have the correct and complete contents in the file

    head -n2 first 2 lines of the correct file are
    Code:
    head -n2 mysqlmymonlite.sh
    #!/bin/bash
    #######################################################
    if you copy and pasted contents into a file, you need to make sure to copy the entire contents.

    when you have for first 2 lines, are the 35th line of the full contents
    Code:
    grep -n '# GRANT ALL PRIVILEGES ON' mysqlmymonlite.sh
    35:# GRANT ALL PRIVILEGES ON *.* TO 'userinfo'@'localhost' IDENTIFIED BY 'enteryourpassword';
     
  19. quicksalad

    quicksalad Member

    159
    9
    18
    May 31, 2015
    Ratings:
    +13
    Local Time:
    12:16 AM
    @eva2000 is it okay to use mysqlmonlite.sh at centos folder?
     
  20. eva2000

    eva2000 Administrator Staff Member

    44,702
    10,193
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,794
    Local Time:
    4:46 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    that is the one to use for Centmin Mod and yes can run from that folder just note that if you setup cronjobs etc where the path is