网站搜索

安装Innotop来监控MySQL服务器性能


Innotop是一个优秀的命令行程序,类似于“top命令”,用于监控在InnoDB引擎下运行的本地和远程MySQL服务器。 Innotop 具有许多功能和不同类型的模式/选项,这有助于监控 MySQL 性能的不同方面,还可以帮助数据库管理员找出 MySQL 服务器出了什么问题。

例如,Innotop有助于监控mysql复制状态用户统计查询列表InnoDB缓冲区InnoDB I/O信息打开表锁定表等,它定期刷新数据,所以你可以看到更新的结果。

Innotop 具有强大的功能和灵活性,不需要任何额外的配置,只需从终端运行“innotop”命令即可执行。

安装Innotop(MySQL监控)

默认情况下,innotop 软件包不包含在 Linux 发行版中,例如 RHELCentOS FedoraScientific Linux。您需要通过启用第三方 epel 存储库并使用 yum 命令来安装它,如下所示。

yum install innotop
样本输出
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: centos.mirror.net.in
 * epel: epel.mirror.net.in
 * epel-source: epel.mirror.net.in
 * extras: centos.mirror.net.in
 * updates: centos.mirror.net.in
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package innotop.noarch 0:1.9.0-3.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==========================================================================================================
 Package			Arch		Version			Repository		Size
==========================================================================================================
Installing:
 innotop                        noarch          1.9.0-3.el6             epel                    149 k

Transaction Summary
==========================================================================================================
Install       1 Package(s)

Total download size: 149 k
Installed size: 489 k
Is this ok [y/N]: y
Downloading Packages:
innotop-1.9.0-3.el6.noarch.rpm                                                      | 149 kB    00:00     
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : innotop-1.9.0-3.el6.noarch							1/1 
  Verifying  : innotop-1.9.0-3.el6.noarch                                                       1/1 

Installed:
  innotop.noarch 0:1.9.0-3.el6                                                                                                                                 

Complete!

要启动innotop,只需输入“innotop”并指定选项-u用户名)和- p密码)分别从命令行输入,然后按 Enter 键。

innotop -u root -p 'tecm1nt'

连接到 MySQL 服务器后,您应该会看到类似于以下屏幕的内容。

[RO] Dashboard (? for help)                                                                    localhost, 61d, 254.70 QPS, 5/2/200 con/run/cac thds, 5.1.61-log
Uptime  MaxSQL  ReplLag  Cxns  Lock  QPS     QPS  Run  Run  Tbls  Repl   SQL
   61d                      4     0  254.70  _         _     462  Off 1
创拓帮助

按“?”获取命令行选项和用法的摘要。

Switch to a different mode:
   A  Dashboard         I  InnoDB I/O Info     Q  Query List
   B  InnoDB Buffers    K  InnoDB Lock Waits   R  InnoDB Row Ops
   C  Command Summary   L  Locks               S  Variables & Status
   D  InnoDB Deadlocks  M  Replication Status  T  InnoDB Txns
   F  InnoDB FK Err     O  Open Tables         U  User Statistics

Actions:
   d  Change refresh interval        p  Pause innotop
   k  Kill a query's connection      q  Quit innotop
   n  Switch to the next connection  x  Kill a query

Other:
 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-filters
Press any key to continue

本节包含 innotop 使用的屏幕截图。使用大写键在模式之间切换。

用户统计

此模式显示按读取排序的用户统计信息索引统计信息

CXN        When   Load  QPS    Slow  QCacheHit  KCacheHit  BpsIn    BpsOut 
localhost  Total  0.00  1.07k   697      0.00%     98.17%  476.83k  242.83k
查询列表

此模式显示 SHOW FULL PROCESSLIST 的输出,类似于 mytop 的查询列表模式。此功能不显示 InnoDB 信息,对于一般用途最有用。

When   Load  Cxns  QPS   Slow  Se/In/Up/De%             QCacheHit  KCacheHit  BpsIn    BpsOut
Now    0.05     1  0.20     0   0/200/450/100               0.00%    100.00%  882.54   803.24
Total  0.00   151  0.00     0  31/231470/813290/188205      0.00%     99.97%    1.40k    0.22

Cmd      ID      State               User      Host           DB      Time      Query
Connect      25  Has read all relay  system u                         05:26:04
InnoDB I/O 信息

此模式显示InnoDB的I/O统计挂起的I/OI/O线程文件I/O 默认情况下日志统计表。

____________________ I/O Threads ____________________
Thread  Purpose               Thread Status          
     0  insert buffer thread  waiting for i/o request
     1  log thread            waiting for i/o request
     2  read thread           waiting for i/o request
     3  write thread          waiting for i/o request

____________________________ Pending I/O _____________________________
Async Rds  Async Wrt  IBuf Async Rds  Sync I/Os  Log Flushes  Log I/Os
        0          0               0          0            0         0

________________________ File I/O Misc _________________________
OS Reads  OS Writes  OS fsyncs  Reads/Sec  Writes/Sec  Bytes/Sec
      26          3          3       0.00        0.00          0

_____________________ Log Statistics _____________________
Sequence No.  Flushed To  Last Checkpoint  IO Done  IO/Sec
0 5543709     0 5543709   0 5543709              8    0.00
InnoDB 缓冲区

在本节中,您将看到有关InnoDB缓冲池页面统计插入缓冲区自适应哈希索引的信息>。数据从SHOW INNODB STATUS获取。

__________________________ Buffer Pool __________________________
Size  Free Bufs  Pages  Dirty Pages  Hit Rate  Memory  Add'l Pool
 512        492     20            0  --        16.51M     841.38k

____________________ Page Statistics _____________________
Reads  Writes  Created  Reads/Sec  Writes/Sec  Creates/Sec
   20       0        0       0.00        0.00         0.00

______________________ Insert Buffers ______________________
Inserts  Merged Recs  Merges  Size  Free List Len  Seg. Size
      0            0       0     1              0          2

__________________ Adaptive Hash Index ___________________
Size    Cells Used  Node Heap Bufs  Hash/Sec  Non-Hash/Sec
33.87k                           0      0.00          0.00
InnoDB 行操作

在这里,您将看到InnoDB行操作行操作杂项信号量等待数组表的输出默认情况下。

________________ InnoDB Row Operations _________________
Ins  Upd  Read  Del  Ins/Sec  Upd/Sec  Read/Sec  Del/Sec
  0    0     0    0     0.00     0.00      0.00     0.00

________________________ Row Operation Misc _________________________
Queries Queued  Queries Inside  Rd Views  Main Thread State          
             0               0         1  waiting for server activity

_____________________________ InnoDB Semaphores _____________________________
Waits  Spins  Rounds  RW Waits  RW Spins  Sh Waits  Sh Spins  Signals  ResCnt
    2      0      41         1         1         2         4        5       5

____________________________ InnoDB Wait Array _____________________________
Thread  Time  File  Line  Type  Readers  Lck Var  Waiters  Waiting?  Ending?
命令总结

命令摘要模式显示所有cmd_summary表,如下所示。

_____________________ Command Summary _____________________
Name                    Value     Pct     Last Incr  Pct   
Com_update              11980303  65.95%          2  33.33%
Com_insert               3409849  18.77%          1  16.67%
Com_delete               2772489  15.26%          0   0.00%
Com_select                   507   0.00%          0   0.00%
Com_admin_commands           411   0.00%          1  16.67%
Com_show_table_status        392   0.00%          0   0.00%
Com_show_status              339   0.00%          2  33.33%
Com_show_engine_status       164   0.00%          0   0.00%
Com_set_option               162   0.00%          0   0.00%
Com_show_tables               92   0.00%          0   0.00%
Com_show_variables            84   0.00%          0   0.00%
Com_show_slave_status         72   0.00%          0   0.00%
Com_show_master_status        47   0.00%          0   0.00%
Com_show_processlist          43   0.00%          0   0.00%
Com_change_db                 27   0.00%          0   0.00%
Com_show_databases            26   0.00%          0   0.00%
Com_show_charsets             24   0.00%          0   0.00%
Com_show_collations           24   0.00%          0   0.00%
Com_alter_table               12   0.00%          0   0.00%
Com_show_fields               12   0.00%          0   0.00%
Com_show_grants               10   0.00%          0   0.00%
变量和状态

此部分计算统计信息,例如每秒查询,并以多种不同模式显示它们。

QPS     Commit_PS     Rlbck_Cmt  Write_Commit     R_W_Ratio      Opens_PS   Tbl_Cch_Usd    Threads_PS  Thrd_Cch_Usd CXN_Used_Ever  CXN_Used_Now
  0             0             0      18163174             0             0             0             0             0          1.99          1.32
  0             0             0      18163180             0             0             0             0             0          1.99          1.32
  0             0             0      18163188             0             0             0             0             0          1.99          1.32
  0             0             0      18163192             0             0             0             0             0          1.99          1.32
  0             0             0      18163217             0             0             0             0             0          1.99          1.32
  0             0             0      18163265             0             0             0             0             0          1.99          1.32
  0             0             0      18163300             0             0             0             0             0          1.99          1.32
  0             0             0      18163309             0             0             0             0             0          1.99          1.32
  0             0             0      18163321             0             0             0             0             0          1.99          1.32
  0             0             0      18163331             0             0             0             0             0          1.99          1.32
复制状态

在此模式下,您将看到Slave SQL StatusSlave I/O StatusMaster Status的输出。前两部分显示从属状态从属I/O线程状态,最后一部分显示主状态

_______________________ Slave SQL Status _______________________
Master        On?  TimeLag  Catchup  Temp  Relay Pos  Last Error
172.16.25.125  Yes    00:00     0.00     0   41295853            

____________________________________ Slave I/O Status _____________________________________
Master        On?  File              Relay Size  Pos       State                           
172.16.25.125  Yes  mysql-bin.000025      39.38M  41295708  Waiting for master to send event

____________ Master Status _____________
File              Position  Binlog Cache
mysql-bin.000010  10887846         0.00%
非交互方式

您可以非交互方式运行“innotop”。

innotop --count 5 -d 1 -n
uptime	max_query_time	time_behind_master	connections	locked_count	qps	spark_qps	run	spark_run	open	slave_running	longest_sql
61d			2	0	0.000363908088893752				64	Yes 	
61d			2	0	4.96871146980749	_		_	64	Yes 	
61d			2	0	3.9633543857494	^_		__	64	Yes 	
61d			2	0	3.96701862656428	^__		___	64	Yes 	
61d			2	0	3.96574802684297	^___		____	64	Yes
监控远程数据库

要监视远程系统上的远程数据库,请使用以下命令并使用特定的用户名密码主机名

innotop -u username -p password -h hostname

有关“innotop”用法和选项的更多信息,请通过在终端上点击“man innotop”查看手册页。

参考链接

创拓主页

另请阅读

  1. RHEL/CentOS/Fedora 中的 Mtop(MySQL 数据库监控)