Join the community today
Register Now

Server Stats / Health Script I Have Been Working On

Discussion in 'System Administration' started by BamaStangGuy, May 11, 2017.

  1. BamaStangGuy

    BamaStangGuy Active Member

    475
    137
    43
    May 25, 2014
    Ratings:
    +181
    Local Time:
    5:24 PM
    For my servers and my clients servers. Long way to go but eventually I will be able to setup a server and based on output it will tell me immediately what I need to change / adjust.

    Screenshot 2017-05-11 07.17.03.png
     
    • Like Like x 2
  2. eva2000

    eva2000 Administrator Staff Member

    31,019
    6,923
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,429
    Local Time:
    9:24 AM
    Nginx 1.13.x
    MariaDB 5.5
    very nice, you should also look at https://community.centminmod.com/threads/mysqlmymonlite-sh-addon.65/

    Code (Text):
    /root/tools/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 3 12:57:07 UTC 2017
    
    ----------------------------
    mysqlreport output
    ----------------------------
    
    MariaDB 10.1.22-MariaDB     uptime 1 2:8:26     Wed May  3 12:57:07 2017
    
    __ Key _________________________________________________________________
    Buffer used     4.00k of   32.00M   %Used:   0.01
      Current       5.85M              %Usage:  18.27
    Write hit      50.00%
    Read hit       66.67%
    
    __ Questions ___________________________________________________________
    Total              78       0.0/s
      Com_             30       0.0/s  %Total:  38.46
      COM_QUIT         29       0.0/s           37.18
      DMS              23       0.0/s           29.49
      -Unknown          4       0.0/s            5.13
    Slow 1 s            0         0/s            0.00  %DMS:   0.00 Log:
    DMS                23       0.0/s           29.49
      SELECT           22       0.0/s           28.21         95.65
      UPDATE            1       0.0/s            1.28          4.35
      REPLACE           0         0/s            0.00          0.00
      DELETE            0         0/s            0.00          0.00
      INSERT            0         0/s            0.00          0.00
    Com_               30       0.0/s           38.46
      show_variab      11       0.0/s           14.10
      show_status       5       0.0/s            6.41
      show_databa       4       0.0/s            5.13
    
    __ Rows ________________________________________________________________
    Rows            7.13k       0.1/s
      Using idx         9       0.0/s  %Index:   0.13
    Rows/question   91.42
    
    __ SELECT and Sort _____________________________________________________
    Scan               24       0.0/s %SELECT: 109.09
    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.15k of   16.00M  %Usage:   0.10
    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          48       0.0/s
    
    __ Tables ______________________________________________________________
    Open               12 of   2048    %Cache:   0.59
    Opened             18       0.0/s
    
    __ Connections _________________________________________________________
    Max used            1 of    300      %Max:   0.33
    Total              31       0.0/s
    
    __ Created Temp ________________________________________________________
    Disk table          0         0/s   %Disk:   0.00
    Table              24       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:  96.77
    
    __ Aborted _____________________________________________________________
    Clients             0         0/s
    Connects            0         0/s
    
    __ Bytes _______________________________________________________________
    Sent          234.57k       2.5/s
    Received        7.68k       0.1/s
    
    __ InnoDB Buffer Pool __________________________________________________
    Usage           2.45M of   47.98M  %Usage:   5.11
    Read hit       63.00%
    Pages
      Free          2.91k              %Total:  94.89
      Data            157                        5.11  %Drty:   0.00
      Misc              0                        0.00
      Latched           0                        0.00
    Reads             427       0.0/s
      From disk       158       0.0/s   %Disk:  37.00
      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           175       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            157       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    8.00M   %Used:   0.00
      Current     352.00k              %Usage:   4.30
    Write hit       0.00%
    Read hit        0.00%
    
    Report Complete:
    Wed May 3 12:57:08 UTC 2017
    ----------------------------
    

    Code (Text):
    /root/tools/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 <major@mhtx.net>
     >>  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
    ----------------------------
    


    I bake it into my forum private stats pages :D

    upload_2017-5-11_22-48-8.png

    Yes forums still use MariaDB 5.5 series for better single threaded performance :)

    upload_2017-5-11_22-52-22.png
     
    • Like Like x 1
  3. eva2000

    eva2000 Administrator Staff Member

    31,019
    6,923
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,429
    Local Time:
    9:24 AM
    Nginx 1.13.x
    MariaDB 5.5
    Also might want to look at disk health especially for SSDs ;)

    Code (Text):
    ./ssd-writes.sh                         
    --------------------------------------------------
     SSD Status:   /dev/sda
    --------------------------------------------------
     On time:      4,327 hr
    --------------------------------------------------
     Data written:
               MB: 491,927.751
               GB: 480.398
               TB: .469
    --------------------------------------------------
     Mean write rate:
            MB/hr: 113.687
    --------------------------------------------------
     Drive health: 99 %
        P/E Count: 5
    --------------------------------------------------
    --------------------------------------------------
     SSD Status:   /dev/sdb
    --------------------------------------------------
     On time:      4,327 hr
    --------------------------------------------------
     Data written:
               MB: 490,572.451
               GB: 479.074
               TB: .467
    --------------------------------------------------
     Mean write rate:
            MB/hr: 113.374
    --------------------------------------------------
     Drive health: 99 %
        P/E Count: 5
    --------------------------------------------------
    
     
    • Like Like x 2
  4. BamaStangGuy

    BamaStangGuy Active Member

    475
    137
    43
    May 25, 2014
    Ratings:
    +181
    Local Time:
    5:24 PM
    Bit more progress.

    Screenshot 2017-05-12 02.54.38.png Screenshot 2017-05-12 02.54.45.png Screenshot 2017-05-12 02.54.52.png
     
    • Like Like x 1
  5. eva2000

    eva2000 Administrator Staff Member

    31,019
    6,923
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,429
    Local Time:
    9:24 AM
    Nginx 1.13.x
    MariaDB 5.5
    looking good there :)

    are you accounting for non-innodb table sizes too ?
     
  6. BamaStangGuy

    BamaStangGuy Active Member

    475
    137
    43
    May 25, 2014
    Ratings:
    +181
    Local Time:
    5:24 PM
    No, just looking up InnoDB total size. Since all I use is InnoDB with XenForo (ES excludes MyISAM search table) I don't really worry about anything else. I also exlude all default MySQL created tables and phpMyAdmin.

    Mostly, its been fun learning how to retrieve all the information through PHP. Ideally, I'd like to create an API so that I can pull all this data from all of my servers I manage into one location.
     
    • Like Like x 1
  7. eva2000

    eva2000 Administrator Staff Member

    31,019
    6,923
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,429
    Local Time:
    9:24 AM
    Nginx 1.13.x
    MariaDB 5.5
    i see.. it is indeed fun... same with me via ssh command line for mysql data/stats retrieval - just no need to worry about php security hehe
     
  8. BamaStangGuy

    BamaStangGuy Active Member

    475
    137
    43
    May 25, 2014
    Ratings:
    +181
    Local Time:
    5:24 PM
    Slow Query Log included now :)

    Screenshot 2017-05-14 03.47.00.png
     
    • Like Like x 2