/ Register

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.

Previous Thread Next Thread
Loading...
  1. May 11, 2017 #1
    BamaStangGuy

    BamaStangGuy Active Member

    668
    192
    43
    May 25, 2014
    Ratings:
    +272
    Local Time:
    8:23 AM
    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
     
  2. May 11, 2017 #2
    eva2000

    eva2000 Administrator Staff Member

    54,907
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    11:23 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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
     
  3. May 11, 2017 #3
    eva2000

    eva2000 Administrator Staff Member

    54,907
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    11:23 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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
--------------------------------------------------
     
  4. May 12, 2017 #4
    BamaStangGuy

    BamaStangGuy Active Member

    668
    192
    43
    May 25, 2014
    Ratings:
    +272
    Local Time:
    8:23 AM
    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
     
  5. May 12, 2017 #5
    eva2000

    eva2000 Administrator Staff Member

    54,907
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    11:23 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    looking good there :)

    are you accounting for non-innodb table sizes too ?
     
  6. May 12, 2017 #6
    BamaStangGuy

    BamaStangGuy Active Member

    668
    192
    43
    May 25, 2014
    Ratings:
    +272
    Local Time:
    8:23 AM
    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.
     
  7. May 12, 2017 #7
    eva2000

    eva2000 Administrator Staff Member

    54,907
    12,240
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,811
    Local Time:
    11:23 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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. May 14, 2017 #8
    BamaStangGuy

    BamaStangGuy Active Member

    668
    192
    43
    May 25, 2014
    Ratings:
    +272
    Local Time:
    8:23 AM
    Slow Query Log included now :)

    Screenshot 2017-05-14 03.47.00.png
     
Previous Thread Next Thread
Loading...

.