mysqlreport分析


MySQL 5.0.77-log         uptime 3 17:23:3       Mon Apr 18 22:57:51 2011
 
__ Key _________________________________________________________________
Buffer used    52.33M of  64.00M  %Used:  81.76
  Current      11.94M            %Usage:  18.65
Write hit      98.77%
Read hit       99.21%
 
__ Questions ___________________________________________________________
Total          43.02M   133.7/s
  QC Hits      18.91M    58.8/s  %Total:  43.97
  DMS          17.89M    55.6/s           41.60
  Com_          4.93M    15.3/s           11.46
  COM_QUIT      1.28M     4.0/s            2.97
  +Unknown        347     0.0/s            0.00
Slow 2 s      661.26k     2.1/s            1.54  %DMS:   3.70  Log:  ON
DMS            17.89M    55.6/s           41.60
  SELECT       16.40M    51.0/s           38.12         91.64
  INSERT      992.85k     3.1/s            2.31          5.55
  UPDATE      267.91k     0.8/s            0.62          1.50
  DELETE      235.19k     0.7/s            0.55          1.31
  REPLACE           0       0/s            0.00          0.00
Com_            4.93M    15.3/s           11.46
  set_option    2.56M     7.9/s            5.94
  change_db     1.28M     4.0/s            2.97
  begin       894.75k     2.8/s            2.08
 
__ SELECT and Sort _____________________________________________________
Scan          571.49k     1.8/s %SELECT:   3.49
Range           2.26M     7.0/s           13.75
Full join          13     0.0/s            0.00
Range check         0       0/s            0.00
Full rng join      44     0.0/s            0.00
Sort scan      27.70k     0.1/s
Sort range    240.09k     0.7/s
Sort mrg pass     147     0.0/s
 
__ Query Cache _________________________________________________________
Memory usage  240.04M of 512.00M  %Used:  46.88
Block Fragmnt  16.18%
Hits           18.91M    58.8/s
Inserts        13.04M    40.5/s
Insrt:Prune    6.28:1    34.1/s
Hit:Insert     1.45:1
 
__ Table Locks _________________________________________________________
Waited             36     0.0/s  %Total:   0.00
Immediate      22.44M    69.7/s
 
__ Tables ______________________________________________________________
Open              681 of 4096    %Cache:  16.63
Opened          3.77k     0.0/s
 
__ Connections _________________________________________________________
Max used           52 of 1000      %Max:   5.20
Total           1.28M     4.0/s
 
__ Created Temp ________________________________________________________
Disk table      1.21k     0.0/s
Table         128.73k     0.4/s    Size:   1.0G
File              271     0.0/s
 
__ Threads _____________________________________________________________
Running             1 of    6
Cached             11 of   16      %Hit:  99.97
Created           406     0.0/s
Slow                0       0/s
 
__ Aborted _____________________________________________________________
Clients            51     0.0/s
Connects            4     0.0/s
 
__ Bytes _______________________________________________________________
Sent           41.02G  127.5k/s
Received        7.58G   23.6k/s
 
__ InnoDB Buffer Pool __________________________________________________
Usage           1.00G of   1.00G  %Used: 100.00
Read hit       99.99%
Pages
  Free              1            %Total:   0.00
  Data         55.35k                     84.46 %Drty:   0.16
  Misc          10184                     15.54
  Latched                                  0.00
Reads          91.53G  284.5k/s
  From file     8.25M    25.6/s            0.01
  Ahead Rnd    301736     0.9/s
  Ahead Sql    388983     1.2/s
Writes         11.05M    34.3/s
Flushes       733.22k     2.3/s
Wait Free           0       0/s
 
__ InnoDB Lock _________________________________________________________
Waits             111     0.0/s
Current             0
Time acquiring
  Total         29594 ms
  Average         266 ms
  Max            6305 ms
 
__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads        11.33M    35.2/s
  Writes        1.30M     4.0/s
  fsync       659.22k     2.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0
 
Pages
  Created      14.53k     0.0/s
  Read         31.46M    97.8/s
  Written     733.22k     2.3/s
 
Rows
  Deleted     151.24k     0.5/s
  Inserted      1.39M     4.3/s
  Read        376.04G    1.2M/s
  Updated     263.93k     0.8/s
上面是MYSQLREPORT的输出信息，里面标注红色的是有疑问的地方。
1：服务器的TEMP_TABLE_SIZE=1000了，怎么还有Disk table      1.21k     呢？2：INNODB_BUFFOR_SIZE=1000M。USAGE全用了，是不是要调大这个值？3：现在系统里是INNODB和MYISAM混用的，此系统是读多写少的，怎么样配置参数比较合适？

解决方案 »

  1.   

    show global status like '%Innodb_buffer_pool%';如下| Innodb_buffer_pool_pages_data | 50752 | 
    | Innodb_buffer_pool_pages_dirty | 437 | 
    | Innodb_buffer_pool_pages_flushed | 768803 | 
    | Innodb_buffer_pool_pages_free | 0 | 
    | Innodb_buffer_pool_pages_misc | 14784 | 
    | Innodb_buffer_pool_pages_total | 65536 | 
    | Innodb_buffer_pool_read_ahead_rnd | 334932 | 
    | Innodb_buffer_pool_read_ahead_seq | 430981 | 
    | Innodb_buffer_pool_read_requests | 98792359561 | 
    | Innodb_buffer_pool_reads | 9186489 | 
    | Innodb_buffer_pool_wait_free | 0 | 
    | Innodb_buffer_pool_write_requests | 11662806 |
      

  2.   

    2：INNODB_BUFFOR_SIZE=1000M。USAGE全用了，是不是要调大这个值？ 
    Innodb_buffer_pool_read_requests | 98792359561 | 
     Innodb_buffer_pool_reads | 9186489 | mysql> select (98792359561-9186489)*100/98792359561;
    +---------------------------------------+
    | (98792359561-9186489)*100/98792359561 |
    +---------------------------------------+
    |                               99.9907 |
    +---------------------------------------+
    1 row in set (0.00 sec)
    缓存命中率已经非常高了 不需要加大Innodb_buffer_pool
      

  3.   

    1：服务器的TEMP_TABLE_SIZE=1000了，怎么还有Disk table 1.21k 呢？http://wenku.baidu.com/view/f4950318964bcf84b9d57bdc.html
      

  4.   

    内存    InnoDB既缓存数据，又缓存索引，并将其缓存于一个很大的缓冲池中。可以通过STATUS比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率，InnoDB缓冲池命中率应不小于99%。  命中率=innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_read_ahead + innodb_buffer_pool_reads)    平均每次读取的字节数= innodb_data_read / innodb_data_reads注：innodb_buffer_pool_read_requests：从缓冲池中读取页的次数         Innodb_buffer_pool_read_ahead：预读的次数         Innodb_buffer_pool_reads：从磁盘读取页的次数         Innodb_data_read：读入的字节数         Innodb_data_reads：发起读取请求的次数，每次读取可能需要读取多个页