Select Page

MYSQL tuning with mysqlreport

MYSQL tuning with mysqlreport

mysql.logo I found this interesting tool (beside tuning-primer.sh) while trying to optimizing my server setting for #Joomla!

mysqlreport

mysqlreport makes an easy-to-read report of important MySQL status values. Unlike SHOW STATUS which simply dumps over 100 values to screen in one long list, mysqlreport interprets, formats, and then nicely presents the values in report readable by humans. Numerous example reports are available at the mysqlreport web page.
The benefit of mysqlreport is that it allows you to very quickly see a wide array of performance indicators for your MySQL server which would otherwise need to be calculated by hand from all the various SHOW STATUS values. For example, the Index Read Ratio is an important value but it is not present in SHOW STATUS; it is an inferred value (the ratio of Key_reads to Key_read_requests).

Grab it at http://hackmysql.com/mysqlreport

How to run it (more options), it require PERL to run.

# ./mysqlreport –user xxxxx–password xxxxxx| more

Running mysqlreport against my host, gave the following results:

  • very good table lock 0.02%
  • very good read ratio 99.93%
  • good query cache, but could be reduce to 40Mb to avoid wasting memory resource

if you need something more professional and can afford it, you can try Mysql Enterprise (free for 30 days, enough to tune any small server bottleneck)

 

 

MySQL 5.0.41-log         uptime 4 10:56:4       Fri Jan  2 22:45:47 2009

__ Key _________________________________________________________________
Buffer used     2.44M of   5.00M  %Used:  48.75
  Current       2.97M            %Usage:  59.38
Write hit      47.41%
Read hit       99.93%

__ Questions ___________________________________________________________
Total           4.07M    10.6/s
  QC Hits       1.93M     5.0/s  %Total:  47.35
  DMS         973.13k     2.5/s           23.89
  Com_        936.64k     2.4/s           23.00
  COM_QUIT    249.35k     0.6/s            6.12
  -Unknown     14.78k     0.0/s            0.36
Slow 5 s      123.77k     0.3/s            3.04  %DMS:  12.72  Log:  ON
DMS           973.13k     2.5/s           23.89
  SELECT      589.04k     1.5/s           14.46         60.53
  UPDATE      135.53k     0.4/s            3.33         13.93
  INSERT      125.80k     0.3/s            3.09         12.93
  DELETE      119.91k     0.3/s            2.94         12.32
  REPLACE       2.85k     0.0/s            0.07          0.29
Com_          936.64k     2.4/s           23.00
  set_option  411.63k     1.1/s           10.11
  change_db   230.65k     0.6/s            5.66
  show_tables  68.89k     0.2/s            1.69

__ SELECT and Sort _____________________________________________________
Scan          205.15k     0.5/s %SELECT:  34.83
Range          27.27k     0.1/s            4.63
Full join      13.73k     0.0/s            2.33
Range check         8     0.0/s            0.00
Full rng join   4.46k     0.0/s            0.76
Sort scan      76.29k     0.2/s
Sort range    110.20k     0.3/s
Sort mrg pass       0       0/s

__ Query Cache _________________________________________________________
Memory usage   25.86M of  70.00M  %Used:  36.94
Block Fragmnt  16.52%

Hits            1.93M     5.0/s
Inserts       533.75k     1.4/s
Insrt:Prune   13.29:1     1.3/s
Hit:Insert     3.61:1

__ Table Locks _________________________________________________________
Waited            269     0.0/s  %Total:   0.02
Immediate       1.71M     4.5/s

__ Tables ______________________________________________________________
Open             1482 of 2000    %Cache:  74.10
Opened         44.50k     0.1/s

__ Connections _________________________________________________________
Max used           16 of   25      %Max:  64.00
Total         250.45k     0.7/s

__ Created Temp ________________________________________________________
Disk table     65.75k     0.2/s
Table         198.32k     0.5/s    Size: 120.0M
File                5     0.0/s

__ Threads _____________________________________________________________
Running             1 of    3
Cached             13 of   32      %Hit:  99.99
Created            16     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients         2.20k     0.0/s
Connects        3.41k     0.0/s

__ Bytes _______________________________________________________________
Sent            1.48G    3.8k/s
Received      757.33M    2.0k/s

__ InnoDB Buffer Pool __________________________________________________
Usage           7.98M of   8.00M  %Used:  99.80
Read hit       99.80%

Pages
  Free              1            %Total:   0.20
  Data            510                     99.61 %Drty:   0.00
  Misc              1                      0.20
  Latched           0                      0.00
Reads           1.03M     2.7/s
  From file     2.10k     0.0/s            0.20
  Ahead Rnd        79     0.0/s
  Ahead Sql         6     0.0/s
Writes         45.01k     0.1/s
Flushes        12.42k     0.0/s
Wait Free           0       0/s

__ 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         2.30k     0.0/s
  Writes       23.18k     0.1/s
  fsync        14.15k     0.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created           5     0.0/s
  Read          2.60k     0.0/s
  Written      12.42k     0.0/s

Rows
  Deleted         843     0.0/s
  Inserted      2.07k     0.0/s
  Read        107.49k     0.3/s
  Updated       2.83k     0.0/s

About The Author

I worked with various Insurances companies across Switzerland on online applications handling billion premium volumes. I love to continuously spark my creativity in many different and challenging open-source projects fueled by my great passion for innovation and blockchain technology.In my technical role as a senior software engineer and Blockchain consultant, I help to define and implement innovative solutions in the scope of both blockchain and traditional products, solutions, and services. I can support the full spectrum of software development activities, starting from analyzing ideas and business cases and up to the production deployment of the solutions.I'm the Founder and CEO of Disruptr GmbH.

Categories