首页 > 数据库 > 如何使用mysqlreport(下)

如何使用mysqlreport(下)

2009年12月13日 2,516 views 发表评论 阅读评论

41 Hits           16.58k    8.02/s   #select命中的次数.越高越好
  42 Inserts        48.50k   23.48/s  
  43 Prunes         33.46k   16.20/s           #mysql5.1没看到了.prune太高可能内存不够 ,.
  44 Insrt:Prune    1.45:1    7.28/s           #Qcache_inserts:Qcache_lowmem_prunes, QC volatility:qc易变性,在一个稳定的qc中,Insert将大于prune,在一个易变的qc中,两边可能对等,或者prune更大.A volatile QC 有两个可能,
 one, the QC size is too small so MySQL has to keep pruning and inserting queries, or two, MySQL is trying to cache everything to a self-defeating end. 
第一种,size过小,增大即可,对于第2种比较普遍,因为mysql …..
MySQL does try to cache nearly everything it can when the QC is enabled with the default type 1. Type 1 means (quoting the MySQL manual): “Cache all query results except for those that begin with SELECT SQL_NO_CACHE.”
A better way to enable the QC is with type 2 “DEMAND”: “Cache results only for queries that begin with SELECT SQL_CACHE.”  不可取,开发人员要很清楚要缓存什么?

  45 Hit:Insert     0.34:1  # QC effectiveness.应该hit远大于Insert为佳. Ideally, the MySQL server should insert a bunch of stable queries into the QC, then get a lot more hits on them. 
例如,如果1:1,这意味着一个query,缓存一次,就被替换出去了.完全违背了query cache的理念.
所以除了以上,虽然hit对比questions很高,但实际上,qc的效率很差的 ,需要强制缓存…demand caching
  46
  47 __ Table Locks _________________________________________________________
  48 Waited          1.01k    0.49/s  %Total:   1.24           #要等待才能获取的锁,等待总不是一件好事.  百分比应越低越好. 很高可能index无或不佳,slow查询多
  49 Immediate      80.04k   38.74/s                           #马上能获取的锁
  50
  51 __ Tables ______________________________________________________________
  52 Open              107 of 1024    %Cache:  10.45      #当前打开的表数目of tablecache(可能打开的表) ,table cache的利用率 前两个值相除 .如果接近100%,还是增大table_cache变量的好.
  53 Opened            118    0.06/s   #up以来打开的表数目
the rate of opening tables can also help determine if table_cache is too low. Generally, it is nice to have this value less than 1/s. However, a busy and well running MySQL server can, for example, be opening 7 tables/s and running at 100% table cache.
  54
  55 __ Connections _________________________________________________________
  56 Max used           77 of  600      %Max:  12.83
  57 Total             202    0.10/s   一般5/s以下..
一般最大连接,默认的100足够,如果很高的数值,可能问题出在别的方面了.like slow queries, poor indexing, or even slow DNS resolution.One often sees MySQL servers with very high max_connection for no good reason. The default value is 100 and this works for even extremely busy, well-optimized servers. 
  58
  59 __ Created Temp ________________________________________________________
  60 Disk table         10    0.00/s          #越小越好 tmp_table_size.不够了,就会创建在disk上.
  61 Table              26    0.01/s    Size:  4.00M   #tmp_table_size=4M;
  62 File                3    0.00/s
  63
  64 __ Threads _____________________________________________________________
  65 Running            55 of   77          # Threads_running    Threads_connected 
  66 Cache               0(0个cche,太少了)              %Hit:    0.5   #cache threads,方便mysql客户端连接; 0.5太低了,每次新连接就要来创建新thread.
服务器应缓存多少线程以便重新使用。当客户端断开连接时,如果线程少于thread_cache_size,则客户端的线程被放入缓存。当请求线程时如果允许可以从缓存中重新利用线程,并且只有当缓存空了时才会创建新线程。如果新连接很多,可以增加该变量以提高性能。(
When the number of connections/s to MySQL exceeds the thread cache (set by the system variable thread_cache_size) MySQL starts to thread thrash: it goes crazy creating threads to keep up with the demand for new connections. When this happens, the thread cache hit rate drops

 如果大量新连接,Threads_created value持续增长,那么考虑增大thread_cache_size吧.http://jeremy.zawodny.com/blog/archives/000173.html
 root@localhost (none)>show status like '%Threads%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 31    |
| Threads_connected      | 3     |
| Threads_created        | 143   |
| Threads_running        | 2     |
+------------------------+-------+
  67 Created           201    0.10/s     #看看57行,总共才202个连接,要创建201个啊....
  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 __________________________________________________5.0.2以上可用.
  79 Usage           3.95M of   7.00M  %Used:  56.47   #对比myisam,innodb还保存了数据.(myisam的key buffer仅缓存index),避免 running out of buffer pool space.超过key buffer,只是影响表索引的使用的性能问题;但是超过innodb buffer pool space,可能导致很多问题,因为nearly everything relies on the buffer pool .文中有叙述一个自动增长的配置,但打不开了. configure an auto-extending buffer pool    
  80 Read hit       99.99%                             #接近100%为佳,否则有些read是读磁盘.  the percentage of buffer pool page reads (hits) from RAM (verses from hard disk)
  81 Pages                                             # 具体innodb buffer的内容 报告
  82   Free            195            %Total:  43.53    #顾名思义了.;   %total 占总计的百分比. 和79行是反的.
  83   Data            249                     55.58 %Drty:   0.00       data具体什么数据不易查;    %drty脏数据占比.更新了,但未刷新到磁盘.
  84   Misc              4                      0.89         #忽略.官方解释:"The number of pages that are busy because they have been allocated for administrative overhead such as row locks or the adaptive hash index."
  85   Latched           0                      0.00         #忽略 官方解释:"These are pages currently being read or written or that cannot be flushed or removed for some other reason."
  86 Reads         574.56k     0.6/s                         #从内存中读.a metric of the number of buffer pool reads from RAM .高是好事.
  87   From file       176     0.0/s            0.03         #how many buffer pool page reads from hard disk.
  88   Ahead Rnd         4     0.0/s             # The number of random read-aheads initiated by InnoDB. This happens when a query scans a large portion of a table but in random order."
  89   Ahead Sql         2     0.0/s             # 预读,在 顺序full table scan情况下.
  90 Writes        160.82k     0.2/s             #like 86;对innodb buffer的写数量,以及速率; 如果updates,insert高,会比较高;
  91 Flushes         1.04k     0.0/s             #the number of buffer pool page-flush requests.
  92 Wait Free           0       0/s             #应该为0,等待pages flush以释放空间 .因为no clean pages 可用.
  93
  94 __ InnoDB Lock _________________________________________________________
  95 Waits               0       0/s            #"The number of times a row lock had to be waited for." Zero is best
  96 Current             0                  #"The number of row locks currently being waited for." Zero is best.
  97 Time acquiring
  98   Total             0 ms            #顾名思义.
  99   Average           0 ms
  100  Max               0 ms
  101
  102 __ InnoDB Data, Pages, Rows ____________________________________________这个很有用,判断Innodb的吞吐率;
  103 Data
  104   Reads           225     0.0/s              #how many times InnoDB has read data. 未能确定何种数据.
  105   Writes          799     0.0/s              # total number of data writes done by InnoDB
  106   fsync           541     0.0/s              #total number of file system syncs.In other words: how many times InnoDB has saved data from RAM back to hard-disk. 比读和写要少.
  107   Pending                                    #当前pending的report ,须为0
  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
 » 转载保留版权:老陈 » 《如何使用mysqlreport(下)》
 » 如果喜欢可以: 点此订阅本站
分类: 数据库 标签: , ,
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.