注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

成长...

坚持,一定要坚持!

 
 
 

日志

 
 

InnoDB Monitor  

2009-08-22 01:27:08|  分类: Mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

用InnoDB monitor 可以监控死锁的情况等

InnoDB引擎提供了一个monitor,可以通过monitor一窥其内部的一些统计信息,也可以说是了解InnoDB引擎的一个很好的窗口。

我们最熟悉的,应当就是show innodb status命令,可以直接在客户端输出很多的信息。其实InnoDB monitor一共有四种模式,show innodb status只是其一种模式的直接展现,并且只能交互式开启,无法自动循环捕获信息。另外还有一种适合四种模式的开启方式,则是通过创建一张特殊的innodb表来开启,开启后会按照固定的时间间隔循环,输出信息到log-error参数指定的错误日志文件中,通过drop对应的表,可以停止monitor。

四种monitor分别是:

  • innodb_monitor:create table innodb_monitor(x int) engine=innodb;
  • innodb_lock_monitor:create table innodb_lock_monitor(x int) engine=innodb;
  • innodb_table_monitor:create table innodb_table_monitor(x int) engine=innodb;
  • innodb_tablespace_monitor:create table innodb_tablespace_monitor(x int) engine=innodb;

根据我在5.1.36版本中实际观察到的结果,innodb_monitor/innodb_lock_monitor开启后的执行周期是16s参考手册上说是15s),而innodb_table_monitor/innodb_tablespace_monitor的执行周期是64s。开启monitor后因为是持续周期性的运行的,在不需要的时候一定要记得drop相关表来停止monitor。如果在开启monitor的中间服务器有重启,monitor不会自动重启,并且在下次启动monitor之前,必须先执行停止操作。

其中innodb_monitor/innodb_lock_monitor两种监视器的输出结果基本类似,后者会有更多关于锁的信息,而前一个实际上就是show innodb status。innodb_table_monitor则会将系统中所有innodb的表的一些结构和内部信息输出,而innodb_tablespace_monitor则输出的是tablespace的信息,注意该monitor输出的只是共享表空间的信息,如果使用innodb_file_per_table为每个表使用独立的表空间,则这些表空间的信息是不会包含在输出中的。

以下是一些简单的示例:
innodb_monitor/innodb_lock_monitor:

=====================================  090805 22:24:48 INNODB MONITOR OUTPUT  =====================================  Per second averages calculated from the last 19 seconds  ----------  SEMAPHORES  ----------  OS WAIT ARRAY INFO: reservation count 312921, signal count 308229  Mutex spin waits 0, rounds 18209349, OS waits 111906  RW-shared spins 287775, OS waits 142204; RW-excl spins 175036, OS waits 19318  ------------  TRANSACTIONS  ------------  Trx id counter 0 121675664  Purge done for trx's n:o < 0 121675662 undo n:o < 0 0  History list length 10  LIST OF TRANSACTIONS FOR EACH SESSION:  ---TRANSACTION 0 121462143, not started, process no 8452, OS thread id 1160767840  mysql tables in use 1, locked 1  MySQL thread id 8056144, query id 78206864 localhost root  ---TRANSACTION 0 137229, not started, process no 8452, OS thread id 1158199648  MySQL thread id 50, query id 377 Has read all relay log; waiting for the slave I/O thread to update it  --------  FILE I/O  --------  I/O thread 0 state: waiting for i/o request (insert buffer thread)  I/O thread 1 state: waiting for i/o request (log thread)  I/O thread 2 state: waiting for i/o request (read thread)  I/O thread 3 state: waiting for i/o request (write thread)  Pending normal aio reads: 0, aio writes: 0,   ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0  Pending flushes (fsync) log: 0; buffer pool: 0  34 OS file reads, 80820900 OS file writes, 1263117 OS fsyncs  0.00 reads/s, 0 avg bytes/read, 1.16 writes/s, 0.63 fsyncs/s  -------------------------------------  INSERT BUFFER AND ADAPTIVE HASH INDEX  -------------------------------------  Ibuf: size 1, free list len 0, seg size 2,  0 inserts, 0 merged recs, 0 merges  Hash table size 8850487, node heap has 233 buffer(s)  0.11 hash searches/s, 0.42 non-hash searches/s  ---  LOG  ---  Log sequence number 4 3697502095  Log flushed up to   4 3697502095  Last checkpoint at  4 3697502095  0 pending log writes, 0 pending chkp writes  79595438 log i/o's done, 0.47 log i/o's/second  ----------------------  BUFFER POOL AND MEMORY  ----------------------  Total memory allocated 4851752298; in additional pool allocated 13195520  Dictionary memory allocated 145784  Buffer pool size   262144  Free buffers       193334  Database pages     68577  Modified db pages  0  Pending reads 0  Pending writes: LRU 0, flush list 0, single page 0  Pages read 70, created 120513, written 2829967  0.00 reads/s, 0.21 creates/s, 0.84 writes/s  Buffer pool hit rate 1000 / 1000  --------------  ROW OPERATIONS  --------------  0 queries inside InnoDB, 0 queries in queue  1 read views open inside InnoDB  Main thread process no. 8452, id 1157658976, state: waiting for server activity  Number of rows inserted 12233742, updated 57497659, deleted 1, read 69720050  0.05 inserts/s, 0.05 updates/s, 0.00 deletes/s, 0.05 reads/s  ----------------------------  END OF INNODB MONITOR OUTPUT  ============================  

innodb_table_monitor:

===========================================  090805 22:26:56 INNODB TABLE MONITOR OUTPUT  ===========================================  --------------------------------------  TABLE: name SYS_FOREIGN, id 0 11, columns 7, indexes 3, appr.rows 0    COLUMNS: ID: DATA_VARCHAR prtype 1835012 len 0; FOR_NAME: DATA_VARCHAR prtype 1835012 len 0;  REF_NAME: DATA_VARCHAR prtype 1835012 len 0; N_COLS: DATA_INT len 4;  DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR:  DATA_SYS prtype 258 len 7;   INDEX: name ID_IND, id 0 11, fields 1/6, uniq 1, type 3     root page 46, appr.key vals 0, leaf pages 1, size pages 1     FIELDS:  ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS    INDEX: name FOR_IND, id 0 12, fields 1/2, uniq 2, type 0     root page 47, appr.key vals 0, leaf pages 1, size pages 1     FIELDS:  FOR_NAME ID    INDEX: name REF_IND, id 0 13, fields 1/2, uniq 2, type 0     root page 48, appr.key vals 0, leaf pages 1, size pages 1     FIELDS:  REF_NAME ID  ...省略若干输出  --------------------------------------  TABLE: name test/test, id 0 81, columns 4, indexes 1, appr.rows 3    COLUMNS: i: DATA_INT DATA_BINARY_TYPE len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6;  DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;    INDEX: name GEN_CLUST_INDEX, id 0 23, fields 0/4, uniq 1, type 1     root page 3, appr.key vals 3, leaf pages 1, size pages 1     FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR i  -----------------------------------  END OF INNODB TABLE MONITOR OUTPUT  ==================================  

innodb_tablespace_monitor:

================================================  090805 22:28:16 INNODB TABLESPACE MONITOR OUTPUT  ================================================  FILE SPACE INFO: id 0  size 65536, free limit 6208, free extents 89  not full frag extents 6: used pages 69, full frag extents 0  first seg id not used 0 1067667  SEGMENT id 0 1067666 space 0; page 903; res 1 used 1; full ext 0  fragm pages 1; free extents 0; not full extents 0: pages 0  ...省略若干输出  SEGMENT id 0 144216 space 0; page 1307; res 1 used 1; full ext 0  fragm pages 1; free extents 0; not full extents 0: pages 0  NUMBER of file segments: 37  Validating tablespace  Validation ok  ---------------------------------------  END OF INNODB TABLESPACE MONITOR OUTPUT  =======================================  
  评论这张
 
阅读(1057)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017