是一个系统活动报告,类似于Linux性能工具,它与Linux的top命令相仿,并参考mytop工具而设计.
它专门用后监控InnoDB性能和MySQL服务器.主要用于监控事务,死锁,外键,查询活动,复制活动,系统变量的主要统计信息及主机的其他详情。InnoTop被广泛使用,并被当做常用性能监控工具。由于InnoTop使用Perl语言写成,所以需要安装perl环境和相关的工具包。必须安装Time::HiRes,Term::ReadKey,DBI,DBD::mysql这四个包[root@forummysql01 data]# perl -MCPAN -eshellCPAN> install Time::HiRes cdCPAN> install Term::ReadKey CPAN> install DBI CPAN> install DBD::mysql一.下载安装innotop-1.8[root@forummysql01 data]# wget http://innotop.googlecode.com/files/innotop-1.8.0.tar.gz[root@forummysql01 data]# tar xvzf innotop-1.8.0.tar.gz [root@forummysql01 data]# cd innotop-1.8.0[root@forummysql01 data]# perl Makefile.PL [root@forummysql01 data]# make[root@forummysql01 data]# make install二.使用 /usr/local/mysql/bin/mysql -usystem -p'HD_root2010!' -S /data/mysqldata/3306/mysql.sockinnotop -u -p -h -P -S 示例: innotop -usystem -p'HD_root2010!' -P 3306 -S /data/mysqldata/3306/mysql.sock以下是进入的页面状态When Load QPS Slow Se/In/Up/De% QCacheHit KCacheHit BpsIn BpsOut Now 0.05 0.19 0 0/ 0/550/50 0.00% 92.31% 32.67k 1.18kTotal 0.00 0.36 1.24k 20/3838/1473/3297 0.00% 93.88% 36.83k 284.40kCmd ID State User Host DB Time Query其中 Load 是负载 QPS (Query Per Second) 表示每秒的查询率 Slow 代表慢查询的个数 Se/In/Up/De%: 增,删,改,查的比率 QCacheHit:查询缓冲的命中率 KCacheHit:索引的命中率 BpsIn (bytes Per Second in) 每秒钟输入的字节数 BpsOut(bytes Per Second Out)每秒钟输出的字节数 输入?可以进入帮助界面:如下:Switch to a different mode: B InnoDB Buffers L Locks R InnoDB Row Ops C Command Summary M Replication Status S Variables & Status D InnoDB Deadlocks O Open Tables T InnoDB Txns F InnoDB FK Err Q Query List U User Statistics I InnoDB I/O Info Actions: a Toggle the innotop process k Kill a query's connection c Choose visible columns n Switch to the next connection d Change refresh interval p Pause innotop e Explain a thread's query q Quit innotop f Show a thread's full query r Reverse sort order h Toggle the header on and off s Change the display's sort column i Toggle idle processes x Kill a queryOther: TAB Switch to the next server group / Quickly filter what you see ! Show license and warranty = Toggle aggregation # Select/create server groups @ Select/create server connections $ Edit configuration settings \ Clear quick-filtersPress any key to continue各种模式的切换都是:shift+字母B模式:InnoDB Buffers____________________________ Buffer Pool ____________________________Size Free Bufs Pages Dirty Pages Hit Rate Memory Add'l Pool512.00k 17863 495843 0 -- 8.19G 0_____________________ Page Statistics ______________________Reads Writes Created Reads/Sec Writes/Sec Creates/Sec443950 5953289 51893 0.00 0.00 0.00______________________ Insert Buffers ______________________Inserts Merged Recs Merges Size Free List Len Seg. Size _________________ Adaptive Hash Index __________________Size Cells Used Node Heap Bufs Hash/Sec Non-Hash/Sec 0.00 0.00其中:Buffer Pool:Size: 某sql使用的Buffer pool的大小Free Bufs : Innodb_buffer_pool_pages_free的值,空页数。Pages: Innodb_buffer_pool_pages_data的值,包含数据的页数(脏或干净).Dirty Pages: Innodb_buffer_pool_pages_dirty的值,当前的脏页数Hit Rate: 命中率Memory : Innodb_buffer_pool_size的值Add'l Pool: Innodb_additional_mem_pool_size的值Page Statisics(页面统计)Reads: Innodb_pages_read的值,读取的页数Writes:Innodb_pages_written的值,写入的页数Created:Innodb_pages_created的值,创建的页数C模式:Command Summary (show Global status)显示的值按降序排序,变量必须是数字,先按S再输入相关的参数的前缀名:按s建:输入innodb 则:D模式:InnoDB Deadlocks(产生的死锁,和产生死锁的语句)____________________________________________________________________________________ Deadlock Transactions ____________________________________________________________________________________ID Timestring User Host Victim Time Undo LStrcts Query Text 182513696 2012-11-15 15:39:30 apps_oper 192 No 00:00 215 9 insert into category_doc_info (category_id,doc_title,category_show,category_Coordinate) values(23692,'1941',1,2)182519005 2012-11-15 15:39:30 apps_oper 192 Yes 00:00 0 4 delete from category_doc_info where (category_id = 23692) ______________________________________ Deadlock Locks ______________________________________ID Waiting Mode DB Table Index Special Ins Intent182513696 0 X apps category_doc_info INDEX_SEARCH rec but not gap 0182513696 1 S apps category_doc_info INDEX_SEARCH 0182519005 1 X apps category_doc_info INDEX_SEARCH rec but not gap 0其中ID:连接线程ID号Timestring: 死锁发生的时间User:用户名Host:主机M模式:Replication Status(查看复制状态的信息)I模式: InnoDB I/O Info (查看InnoDB对应的I/O的信息)O模式: 查看打开的表Q模式(非常管用的一项):然后e输入thread ID显示执行计划或者按F显示完整的sql语句。