Mysql Stat reporting with mysqlreports

 Mysqlreport

A free tool to see what your mysql server is doing  is mysqlreport. It gives current usage and statistics.
Install it with the following commands:
cd ~ (you can install it to any directory, ie /user/local/sbin I use home for simplicity).
wget hackmysql.com/scripts/mysqlreport
chmod 755 mysqlreport

Now you simply run the command.
./mysqlreport –host 127.0.0.1 –port 3306 –user your-user –password

Below I will give a Brief run down on what the values mean.
For greater detail on what the values and stats mean, see the links below.:
To see status values here is a site for you:
http://www.howtoforge.com/getting-mysql-status-values-with-mysqlreport

To see what these crazy stats mean:
http://hackmysql.com/mysqlreportguide

   1 MySQL 5.0.3              uptime 0 0:34:26       Fri Sep  1 19:46:02 2006
   2
   3 __ Key _________________________________________________________________
   4 Buffer used   380.00k of 512.00M  %Used:   0.07
   5   Current      59.32M            %Usage:  11.59
   6 Write hit      97.04%
   7 Read hit       99.58%
   8 
   9 __ Questions ___________________________________________________________
  10 Total          98.06k   47.46/s
  11   DMS          81.23k   39.32/s  %Total:  82.84
  12   QC Hits      16.58k    8.02/s           16.91
  13   COM_QUIT        200    0.10/s            0.20
  14   Com_            131    0.06/s            0.13
  15   -Unknown         82    0.04/s            0.08
  16 Slow 5 s            0    0.00/s            0.00  %DMS:   0.00  Log:  ON
  17 DMS            81.23k   39.32/s           82.84
  18   SELECT       64.44k   31.19/s           65.72         79.33
  19   INSERT       16.75k    8.11/s           17.08         20.61
  20   UPDATE           41    0.02/s            0.04          0.05
  21   REPLACE           0    0.00/s            0.00          0.00
  22   DELETE            0    0.00/s            0.00          0.00
  23 Com_              131    0.06/s            0.13
  24   change_db       119    0.06/s            0.12
  25   show_fields       9    0.00/s            0.01
  26   show_status       2    0.00/s            0.00
  27 
  28 __ SELECT and Sort _____________________________________________________
  29 Scan               38    0.02/s %SELECT:   0.06
  30 Range              14    0.01/s            0.02
  31 Full join           3    0.00/s            0.00
  32 Range check         0    0.00/s            0.00
  33 Full rng join       0    0.00/s            0.00
  34 Sort scan          14    0.01/s
  35 Sort range         26    0.01/s
  36 Sort mrg pass       0    0.00/s
  37
  38 __ Query Cache _________________________________________________________
  39 Memory usage   17.81M of  32.00M  %Used:  55.66
  40 Block Fragmnt  13.05%
  41 Hits           16.58k    8.02/s
  42 Inserts        48.50k   23.48/s
  43 Prunes         33.46k   16.20/s
  44 Insrt:Prune    1.45:1    7.28/s
  45 Hit:Insert     0.34:1
  46
  47 __ Table Locks _________________________________________________________
  48 Waited          1.01k    0.49/s  %Total:   1.24
  49 Immediate      80.04k   38.74/s
  50
  51 __ Tables ______________________________________________________________
  52 Open              107 of 1024    %Cache:  10.45
  53 Opened            118    0.06/s
  54
  55 __ Connections _________________________________________________________
  56 Max used           77 of  600      %Max:  12.83
  57 Total             202    0.10/s
  58
  59 __ Created Temp ________________________________________________________
  60 Disk table         10    0.00/s
  61 Table              26    0.01/s    Size:  4.00M
  62 File                3    0.00/s
  63
  64 __ Threads _____________________________________________________________
  65 Running            55 of   77
  66 Cache               0              %Hit:    0.5
  67 Created           201    0.10/s
  68 Slow                0    0.00/s
  69
  70 __ Aborted _____________________________________________________________
  71 Clients             0    0.00/s
  72 Connects            8    0.00/s
  73
  74 __ Bytes _______________________________________________________________
  75 Sent           38.46M  18.62k/s
  76 Received        7.98M   3.86k/s
  77
  78 __ InnoDB Buffer Pool __________________________________________________
  79 Usage           3.95M of   7.00M  %Used:  56.47
  80 Read hit       99.99%
  81 Pages
  82   Free            195            %Total:  43.53
  83   Data            249                     55.58 %Drty:   0.00
  84   Misc              4                      0.89
  85   Latched           0                      0.00
  86 Reads         574.56k     0.6/s
  87   From file       176     0.0/s            0.03
  88   Ahead Rnd         4     0.0/s
  89   Ahead Sql         2     0.0/s
  90 Writes        160.82k     0.2/s
  91 Flushes         1.04k     0.0/s
  92 Wait Free           0       0/s
  93
  94 __ InnoDB Lock _________________________________________________________
  95 Waits               0       0/s
  96 Current             0
  97 Time acquiring
  98   Total             0 ms
  99   Average           0 ms
  100  Max               0 ms
  101
  102 __ InnoDB Data, Pages, Rows ____________________________________________
  103 Data
  104   Reads           225     0.0/s
  105   Writes          799     0.0/s
  106   fsync           541     0.0/s
  107   Pending
  108     Reads           0
  109     Writes          0
  110     fsync           0
  111
  112 Pages
  113   Created          23     0.0/s
  114   Read            226     0.0/s
  115   Written       1.04k     0.0/s
  116
  117 Rows
  118   Deleted      25.04k     0.0/s
  119   Inserted     25.04k     0.0/s
  120   Read         81.91k     0.1/s
  121   Updated           0       0/s

Line 1 (Report Header):
Contains the MYSQL Version Mysql Server uptime and current date/time.

Line  3-7 (Key Report):
This gives details on keys(indexes). The report doesn't go into great detail about your index's. But it will indicate how much of your key(index) buffer you are using.
  •  Line 4(Buffers used): This shows how much ram has ever been used of the amount of ram that is set for the key(index) buffer. Important to note that mysql only allocates ram when the key buffer is needed. And does not use the entire amount of ram configured for the buffer. This line works as a watermark.
  •  Line 5 (Current): This shows the amount of RAM that is currently being used by the Key Buffer (index). This should be less than or equal to Buffer Used:
  • Line 6 (Write Hit): Keys(Indexes) are Ram Based, and write quickly compared to writes to the HardDisk.  This line indicates writes ram / disk ratio. The higher the % the more ram it's using (typically) this happens mainly with selects. If you are doing updates, inserts, deletes, etc the write hit may be near 0%.   But if the write hit is below 0% you are writing mainly to the disk. This is bad and will cause performance degradation. Key Hit efficiencyt is best determined by what your mysql server is used for. (The DMS sub-report in Line 17 (DMS sub-report) can assist in figuring this out.)
  •  Line 7 (Read Hit):

 

 

 

jim has written 83 articles

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>