mysql 性能调优之mysqlreport

最近数据库这块偶发性的出现单表主键查询缓慢问题，表现为通过主键去查询一张大概100W记录的表时，耗时在500ms左右

用mysqlreport生成报告，让我大吃一惊：

主要发现几个问题：

1、分配的key buffer 384M，基本耗完，达到惊人的99.78%，索引缓存被利用的非常充分，但是其实这么高很危险的，得加大一点

然后read hit又蛮高的，已经超过99.95% 索引从缓存中读取，其余走磁盘读取，如果索引再增加，就会导致其他索引被挤出，会增加访问磁盘索引的可能性

2、qps达到每秒处理75个请求，DMS和QCHITS占了所有questions大部分，其中命中缓存的select占到总的questions的16.4%,已经很不错了

3、所有DMS中select占到51.91%，坚定了我们用myisam的决心，重点优化还是在select方向

4、慢查询日志被关闭了，看不到慢查询的数据

4、scan和range占到所有select里的5%和4%，看来select里全表扫描的蛮多的，sql有优化的余地

5、qc里inserts和prune的比值是99:1  相当于99次插入查询结果，有一次其他查询结果被挤出来，说明qc的内存还是足够用的

但是hits和inserts的比值却少的可怜，说明插入的查询结果，没有被多次查询命中，这里的查询缓存基本没有用处

5、table wait lock 的请求次数达到26.86%，说明DDL语句对查询的锁定还是蛮严重的，得看是否有索引太多，更新缓慢的问题

6、table cache用了94%，要赶紧加大一点

7、conections用了100%，也要加大一点

__ Key _________________________________________________________________
Buffer used   340.72M of 384.00M  %Used:  88.73
  Current     383.14M            %Usage:  99.78
Write hit      94.27%
Read hit       99.95%


__ Questions ___________________________________________________________
Total           1.91G    75.3/s
  DMS           5.76G   226.6/s  %Total: 301.08
  -Unknown      4.29G   169.1/s          224.67
  QC Hits     313.43M    12.3/s           16.40
  Com_        137.15M     5.4/s            7.17
  COM_QUIT    354.83k     0.0/s            0.02
Slow 10 s      50.36k     0.0/s            0.00  %DMS:   0.00  Log: OFF
DMS             5.76G   226.6/s          301.08
  SELECT        2.99G   117.6/s          156.31         51.91
  INSERT        1.42G    55.8/s           74.12         24.62
  UPDATE      894.09M    35.2/s           46.77         15.53
  DELETE      456.57M    18.0/s           23.88          7.93
  REPLACE           0       0/s            0.00          0.00
Com_          137.15M     5.4/s            7.17
  set_option  136.64M     5.4/s            7.15
  show_variab 194.19k     0.0/s            0.01
  show_collat 193.72k     0.0/s            0.01


__ SELECT and Sort _____________________________________________________
Scan          155.98M     6.1/s %SELECT:   5.22
Range         122.98M     4.8/s            4.12
Full join     101.80k     0.0/s            0.00
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan      15.78M     0.6/s
Sort range    283.87M    11.2/s
Sort mrg pass   1.43M     0.1/s


__ Query Cache _________________________________________________________
Memory usage   25.36M of  32.00M  %Used:  79.26
Block Fragmnt  16.56%
Hits          313.43M    12.3/s
Inserts         1.37G    53.8/s
Insrt:Prune   99.10:1    53.2/s
Hit:Insert     0.23:1


  set_option  136.64M     5.4/s            7.15
  show_variab 194.19k     0.0/s            0.01
  show_collat 193.72k     0.0/s            0.01


__ SELECT and Sort _____________________________________________________
Scan          155.98M     6.1/s %SELECT:   5.22
Range         122.98M     4.8/s            4.12
Full join     101.80k     0.0/s            0.00
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan      15.78M     0.6/s
Sort range    283.87M    11.2/s
Sort mrg pass   1.43M     0.1/s


__ Query Cache _________________________________________________________
Memory usage   25.36M of  32.00M  %Used:  79.26
Block Fragmnt  16.56%
Hits          313.43M    12.3/s
Inserts         1.37G    53.8/s
Insrt:Prune   99.10:1    53.2/s
Hit:Insert     0.23:1


__ Table Locks _________________________________________________________
Waited        720.84M    28.4/s  %Total:  26.86
Immediate       1.96G    77.3/s


__ Tables ______________________________________________________________
Open              483 of  512    %Cache:  94.34
Opened          8.55k     0.0/s


__ Connections _________________________________________________________
Max used          501 of  500      %Max: 100.20
Total         355.45k     0.0/s


__ Created Temp ________________________________________________________
Disk table      1.11M     0.0/s
Table          12.36M     0.5/s    Size:  32.0M
File            1.60M     0.1/s


__ Threads _____________________________________________________________
Running             3 of  401
Cached             10 of   16      %Hit:  91.89
Created        28.82k     0.0/s
Slow                2     0.0/s


__ Aborted _____________________________________________________________
Clients         1.24k     0.0/s
Connects          995     0.0/s


__ Bytes _______________________________________________________________
Sent            1.09T   42.8k/s
Received        3.89G   153.0/s


__ InnoDB Buffer Pool __________________________________________________
Usage         304.00k of   8.00M  %Used:   3.71
Read hit       84.42%
Pages
  Free            493            %Total:  96.29
  Data             19                      3.71 %Drty:   0.00
  Misc              0                      0.00
  Latched           0                      0.00
Reads              77     0.0/s
  From file        12     0.0/s           15.58
  Ahead Rnd         1     0.0/s
  Ahead Sql         0       0/s
Writes              0       0/s
Flushes             0       0/s
Wait Free           0       0/s