← Back to team overview

maria-developers team mailing list archive

Review request : MDEV-573 (mysqlreport update)

 

Hi,

I have been doing modifications to the MariaDB mysqlreport version (which is shipped on the Debian/Ubuntu packages and can be found on the debian/additions/ directory), especially to add stats/informations about MariaDB specific functions but also to fix some bugs and limitations of the tool.

As of today, the script works without throwing a warning and seems to output correct informations on MariaDB 5.5 (either with or without threadpool and binary log activated), MariaDB 5.5 + TokuDB, MySQL 5.1, MySQL 5.5 and it solves some small bugs and issues that could lead warning messages, invalid values returned or truncated values.

The main additions are the AriaDB pagecache stats, binlog cache stats, subquery cache stats, TokuDB stats and Threadpool stats. There are also some "cosmetic" changes/fixes : the addition of values such as the % of disk tables used or the InnoDB read "From file" modified to "From disk" as of the documentation of the status variables states ; values such as the threads cache size or the table cache size that were limited to 3 chars are now limited to 5 chars ; the per seconds stats was truncated to 5 chars max on Questions and Query Cache sections (for example 153.6k/s was truncated to 153.6/s).


The JIRA task : https://mariadb.atlassian.net/browse/MDEV-573
The GIT repository where i put the latest revisions (i only have been using it lately but i commented all the changes and bugfixes along the time on the JIRA task and could provide older revisions/diff if required) : https://github.com/jb-boin/mariadb/blob/5.5.30/debian/additions/mysqlreport


I would like to be sure that the values/stats i added are really useful to monitor (and the calculations of these correct) and that the way informations are shown, named and ordered is clear enough ; for example, the way the test that determines if its a MariaDB or a MySQL server might not be the best possible (it checks if the Aria engine is available). There is still an upstream bug on the "Questions" and the "SELECT and Sort" section, the %Total values are not correctly calculated and lead to values > 100%, i havent got time to give it a look yet and i included in the example output below values hitting that bug.


Here is an example of the output of the latest revision on a server having about all the possible functions enabled (values are mixed from different servers) :

   MariaDB 5.5.30-tokudb-7.0.1 uptime 70 9:4:47    Thu Jul  4 01:48:24 2013

   __ Key _________________________________________________________________
   Buffer used   651.41M of    1.00G   %Used:  63.61
      Current     675.43M              %Usage:  65.96
   Write hit      79.79%
   Read hit       99.99%

   __ Questions ___________________________________________________________
   Total           2.57M       0.4/s
      -Unknown     39.92M       6.6/s  %Total: 1550.4
      DMS          39.78M       6.5/s          1545.3
      Com_          2.71M       0.4/s          105.09
      COM_QUIT        444       0.0/s            0.02
   Slow 4 s          285       0.0/s            0.01  %DMS:   0.00 Log: OFF
   DMS            39.78M       6.5/s          1545.3
      UPDATE       39.76M       6.5/s          1544.5         99.95
      SELECT       21.11k       0.0/s            0.82          0.05
      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_            2.71M       0.4/s          105.09
      show_proces   2.51M       0.4/s           97.33
      admin_comma 152.51k       0.0/s            5.92
      show_status   9.23k       0.0/s            0.36

   __ Rows ________________________________________________________________
   Rows          186.00G    240.3k/s
      Using idx   168.06G    217.1k/s  %Index:  90.35
   Rows/question  158.90

   __ SELECT and Sort _____________________________________________________
   Scan           42.33k       0.0/s %SELECT: 200.53
   Range               0         0/s            0.00
   Full join      19.27k       0.0/s           91.29
   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

   __ Table Locks _________________________________________________________
   Waited          8.86M      11.4/s  %Total:   0.46
   Immediate       1.93G      2.5k/s

   __ Tables ______________________________________________________________
   Open             6843 of   8192    %Cache:  83.53
   Opened         14.14k       0.0/s

   __ Connections _________________________________________________________
   Max used         5403 of  22000      %Max:  24.56
   Total         176.48M     228.0/s

   __ Created Temp ________________________________________________________
   Disk table      7.76k       0.0/s   %Disk:  14.55
   Table          53.38k       0.0/s    Size:  64.0M
   File               12       0.0/s

   __ Threads _____________________________________________________________
   Running           274 of   2788
   Created       181.68k       0.2/s
   Slow                0         0/s
   Threadpool        892 of   1200     %Used:  74.33
      Running         458 of   1200  %Running:  38.17
      Idle            434 of   1200     %Idle:  36.17

   __ Aborted _____________________________________________________________
   Clients         5.03k       0.0/s
   Connects        3.65k       0.0/s

   __ Bytes _______________________________________________________________
   Sent            1.51T      1.9M/s
   Received      445.91G    576.0k/s

   __ InnoDB Buffer Pool __________________________________________________
   Usage          25.97G of   26.00G  %Usage:  99.89
   Read hit      100.00%
   Pages
      Free          1.83k              %Total:   0.11
      Data          1.67M                       98.23  %Drty:   0.07
      Misc          28381                        1.67
      Latched           0                        0.00
   Reads         527.54G    681.5k/s
      From disk   100.00k       0.1/s   %Disk:   0.00
      Ahead Rnd         0         0/s
   Writes          5.21G      6.7k/s
   Flushes        17.35M      22.4/s
   Wait Free           9       0.0/s   %Wait:   0.00

   __ InnoDB Lock _________________________________________________________
   Waits        36660726      47.4/s
   Current             0
   Time acquiring
      Total     627441780 ms
      Average         171 ms
      Max         1537520 ms

   __ InnoDB Data, Pages, Rows ____________________________________________
   Data
      Reads       512.26k       0.7/s
      Writes      181.92M     235.0/s
      fsync         2.07M       2.7/s
      Pending
        Reads           0
        Writes          0
        fsync           0

   Pages
      Created       3.72M       4.8/s
      Read        512.18k       0.7/s
      Written      17.35M      22.4/s

   Rows
      Deleted     175.85M     227.2/s
      Inserted    200.46M     259.0/s
      Read        174.52G    225.4k/s
      Updated     224.40M     289.9/s

   __ Aria Pagecache ______________________________________________________
   Buffer used     3.43M of  512.00M   %Used:   0.67
      Current      20.26M              %Usage:   3.96
   Write hit      47.09%
   Read hit       99.96%

   __ Subquery Cache ______________________________________________________
   Hit ratio      99.91%
   Hits           38.63G    49.9k/s
   Miss           35.97M     46.5/s

   __ Binary Log Cache _____________________________________________________
   Disk use
      Transactional		  0.43%
      Non transactional	  0.50%

   __ TokuDB ______________________________________________________________
   Cachetable      4.00G of    4.00G  %Usage:  99.99
      Miss          1.83M       0.3/s
      Evictions     1.95M       0.3/s



Thanks in advance,
Jean Weisbuch