{"id":11467,"date":"2019-03-14T08:22:27","date_gmt":"2019-03-14T08:22:27","guid":{"rendered":"http:\/\/www.appservgrid.com\/paw92\/?p=11467"},"modified":"2019-03-14T08:22:27","modified_gmt":"2019-03-14T08:22:27","slug":"install-innotop-to-monitor-mysql-server-performance","status":"publish","type":"post","link":"https:\/\/www.appservgrid.com\/paw92\/index.php\/2019\/03\/14\/install-innotop-to-monitor-mysql-server-performance\/","title":{"rendered":"Install Innotop to Monitor MySQL Server Performance"},"content":{"rendered":"

Innotop<\/strong>\u00a0is an excellent command line program, similar to \u2018top command<\/a>\u2018 to monitor local and remote MySQL servers running under\u00a0InnoDB<\/strong>\u00a0engine. Innotop comes with many features and different types of modes\/options, which helps to monitor different aspects of MySQL performance and also helps database administrator to find out what\u2019s wrong going with MySQL server.<\/p>\n

For example,\u00a0Innotop<\/strong>\u00a0helps in monitoring\u00a0mysql replication status<\/strong>,\u00a0user statistics<\/strong>,\u00a0query list<\/strong>,\u00a0InnoDB buffers<\/strong>,\u00a0InnoDB I\/O information<\/strong>,\u00a0open tables<\/strong>,\u00a0lock tables,<\/strong>\u00a0etc, it refreshes its data regularly, so you could see updated results.<\/p>\n

\n

\"Install<\/a><\/p>\n

Innotop MySQL Server Monitoring<\/p>\n<\/div>\n

Innotop<\/strong>\u00a0comes with great features and flexibility and doesn\u2019t needs any extra configuration and it can be executed by just running \u2018innotop<\/strong>\u2018 command from the terminal.<\/p>\n

Installing Innotop (MySQL Monitoring)<\/h3>\n

By default\u00a0innotop<\/strong>\u00a0package is not included in Linux distributions such as\u00a0RHEL<\/strong>,\u00a0CentOS<\/strong>,\u00a0Fedora<\/strong>\u00a0and\u00a0Scientific Linux<\/strong>. You need to install it by enabling third party\u00a0epel repository<\/a>\u00a0and using yum command as shown below.<\/p>\n

# yum install innotop<\/pre>\n
Sample Output<\/h5>\n
Loaded plugins: fastestmirror\r\nLoading mirror speeds from cached hostfile\r\n * base: centos.mirror.net.in\r\n * epel: epel.mirror.net.in\r\n * epel-source: epel.mirror.net.in\r\n * extras: centos.mirror.net.in\r\n * updates: centos.mirror.net.in\r\nSetting up Install Process\r\nResolving Dependencies\r\n--> Running transaction check\r\n---> Package innotop.noarch 0:1.9.0-3.el6 will be installed\r\n--> Finished Dependency Resolution\r\n\r\nDependencies Resolved\r\n\r\n==========================================================================================================\r\n Package\t\t\tArch\t\tVersion\t\t\tRepository\t\tSize\r\n==========================================================================================================\r\nInstalling:\r\n innotop                        noarch          1.9.0-3.el6             epel                    149 k\r\n\r\nTransaction Summary\r\n==========================================================================================================\r\nInstall       1 Package(s)\r\n\r\nTotal download size: 149 k\r\nInstalled size: 489 k\r\nIs this ok [y\/N]: y\r\nDownloading Packages:\r\ninnotop-1.9.0-3.el6.noarch.rpm                                                      | 149 kB    00:00     \r\nRunning rpm_check_debug\r\nRunning Transaction Test\r\nTransaction Test Succeeded\r\nRunning Transaction\r\n  Installing : innotop-1.9.0-3.el6.noarch\t\t\t\t\t\t\t1\/1 \r\n  Verifying  : innotop-1.9.0-3.el6.noarch                                                       1\/1 \r\n\r\nInstalled:\r\n  innotop.noarch 0:1.9.0-3.el6                                                                                                                                 \r\n\r\nComplete!<\/pre>\n
To start\u00a0innotop<\/strong>, simply type \u201cinnotop<\/strong>\u201d and specify options\u00a0-u<\/strong>\u00a0(username<\/strong>) and\u00a0-p<\/strong>\u00a0(password<\/strong>) respectively, from the command line and press Enter.<\/center><\/p>\n
# innotop -u root -p 'tecm1nt'<\/pre>\n
Once you\u2019ve connected to\u00a0MySQL<\/strong>\u00a0server, you should see something similar to the following screen.<\/p>\n
[RO] Dashboard (? for help)                                                                    localhost, 61d, 254.70 QPS, 5\/2\/200 con\/run\/cac thds, 5.1.61-log\r\nUptime  MaxSQL  ReplLag  Cxns  Lock  QPS     QPS  Run  Run  Tbls  Repl   SQL\r\n   61d                      4     0  254.70  _         _     462  Off 1<\/pre>\n
Innotop Help<\/h5>\n
Press \u201c?<\/strong>\u201d to get the summary of command line options and usage.<\/p>\n
Switch to a different mode:\r\n   A  Dashboard         I  InnoDB I\/O Info     Q  Query List\r\n   B  InnoDB Buffers    K  InnoDB Lock Waits   R  InnoDB Row Ops\r\n   C  Command Summary   L  Locks               S  Variables & Status\r\n   D  InnoDB Deadlocks  M  Replication Status  T  InnoDB Txns\r\n   F  InnoDB FK Err     O  Open Tables         U  User Statistics\r\n\r\nActions:\r\n   d  Change refresh interval        p  Pause innotop\r\n   k  Kill a query's connection      q  Quit innotop\r\n   n  Switch to the next connection  x  Kill a query\r\n\r\nOther:\r\n TAB  Switch to the next server group   \/  Quickly filter what you see\r\n   !  Show license and warranty         =  Toggle aggregation\r\n   #  Select\/create server groups       @  Select\/create server connections\r\n   $  Edit configuration settings       \\  Clear quick-filters\r\nPress any key to continue<\/pre>\n
This section contains screen shots of\u00a0innotop<\/strong>\u00a0usage. Use Upper-case keys to switch between modes.<\/p>\n
User Statistics<\/h5>\n
This mode displays\u00a0user statistics<\/strong>\u00a0and\u00a0index statistics<\/strong>\u00a0sorted by reads.<\/p>\n
CXN        When   Load  QPS    Slow  QCacheHit  KCacheHit  BpsIn    BpsOut \r\nlocalhost  Total  0.00  1.07k   697      0.00%     98.17%  476.83k  242.83k<\/pre>\n
Query List<\/h5>\n
This mode displays the output from\u00a0SHOW FULL PROCESSLIST<\/strong>, similar to\u00a0mytop\u2019s query list<\/a>\u00a0mode. This feature doesn\u2019t display\u00a0InnoDB<\/strong>\u00a0information and it\u2019s most useful for general usage.<\/p>\n
When   Load  Cxns  QPS   Slow  Se\/In\/Up\/De%             QCacheHit  KCacheHit  BpsIn    BpsOut\r\nNow    0.05     1  0.20     0   0\/200\/450\/100               0.00%    100.00%  882.54   803.24\r\nTotal  0.00   151  0.00     0  31\/231470\/813290\/188205      0.00%     99.97%    1.40k    0.22\r\n\r\nCmd      ID      State               User      Host           DB      Time      Query\r\nConnect      25  Has read all relay  system u                         05:26:04<\/pre>\n
InnoDB I\/O Info<\/h5>\n
This mode displays\u00a0InnoDB\u2019s I\/O statistics<\/strong>,\u00a0pending I\/O<\/strong>,\u00a0I\/O threads<\/strong>,\u00a0file I\/O<\/strong>\u00a0and\u00a0log statistics<\/strong>\u00a0tables by default.<\/p>\n
____________________ I\/O Threads ____________________\r\nThread  Purpose               Thread Status          \r\n     0  insert buffer thread  waiting for i\/o request\r\n     1  log thread            waiting for i\/o request\r\n     2  read thread           waiting for i\/o request\r\n     3  write thread          waiting for i\/o request\r\n\r\n____________________________ Pending I\/O _____________________________\r\nAsync Rds  Async Wrt  IBuf Async Rds  Sync I\/Os  Log Flushes  Log I\/Os\r\n        0          0               0          0            0         0\r\n\r\n________________________ File I\/O Misc _________________________\r\nOS Reads  OS Writes  OS fsyncs  Reads\/Sec  Writes\/Sec  Bytes\/Sec\r\n      26          3          3       0.00        0.00          0\r\n\r\n_____________________ Log Statistics _____________________\r\nSequence No.  Flushed To  Last Checkpoint  IO Done  IO\/Sec\r\n0 5543709     0 5543709   0 5543709              8    0.00<\/pre>\n
InnoDB Buffers<\/h5>\n
This section, you will see information about the\u00a0InnoDB buffer pool<\/strong>,\u00a0page statistics<\/strong>,\u00a0insert buffer<\/strong>, and\u00a0adaptive hash index<\/strong>. The data fetches from\u00a0SHOW INNODB STATUS<\/strong>.<\/p>\n
__________________________ Buffer Pool __________________________\r\nSize  Free Bufs  Pages  Dirty Pages  Hit Rate  Memory  Add'l Pool\r\n 512        492     20            0  --        16.51M     841.38k\r\n\r\n____________________ Page Statistics _____________________\r\nReads  Writes  Created  Reads\/Sec  Writes\/Sec  Creates\/Sec\r\n   20       0        0       0.00        0.00         0.00\r\n\r\n______________________ Insert Buffers ______________________\r\nInserts  Merged Recs  Merges  Size  Free List Len  Seg. Size\r\n      0            0       0     1              0          2\r\n\r\n__________________ Adaptive Hash Index ___________________\r\nSize    Cells Used  Node Heap Bufs  Hash\/Sec  Non-Hash\/Sec\r\n33.87k                           0      0.00          0.00<\/pre>\n
InnoDB Row Ops<\/h5>\n
Here, you will see the output of\u00a0InnoDB row operations<\/strong>,\u00a0row operation misc<\/strong>,\u00a0semaphores<\/strong>, and\u00a0wait array<\/strong>\u00a0tables by default.<\/p>\n
________________ InnoDB Row Operations _________________\r\nIns  Upd  Read  Del  Ins\/Sec  Upd\/Sec  Read\/Sec  Del\/Sec\r\n  0    0     0    0     0.00     0.00      0.00     0.00\r\n\r\n________________________ Row Operation Misc _________________________\r\nQueries Queued  Queries Inside  Rd Views  Main Thread State          \r\n             0               0         1  waiting for server activity\r\n\r\n_____________________________ InnoDB Semaphores _____________________________\r\nWaits  Spins  Rounds  RW Waits  RW Spins  Sh Waits  Sh Spins  Signals  ResCnt\r\n    2      0      41         1         1         2         4        5       5\r\n\r\n____________________________ InnoDB Wait Array _____________________________\r\nThread  Time  File  Line  Type  Readers  Lck Var  Waiters  Waiting?  Ending?<\/pre>\n
Command Summary<\/h5>\n
The command summary mode displays all the\u00a0cmd_summary<\/strong>\u00a0table, which looks similar to the below.<\/p>\n
_____________________ Command Summary _____________________\r\nName                    Value     Pct     Last Incr  Pct   \r\nCom_update              11980303  65.95%          2  33.33%\r\nCom_insert               3409849  18.77%          1  16.67%\r\nCom_delete               2772489  15.26%          0   0.00%\r\nCom_select                   507   0.00%          0   0.00%\r\nCom_admin_commands           411   0.00%          1  16.67%\r\nCom_show_table_status        392   0.00%          0   0.00%\r\nCom_show_status              339   0.00%          2  33.33%\r\nCom_show_engine_status       164   0.00%          0   0.00%\r\nCom_set_option               162   0.00%          0   0.00%\r\nCom_show_tables               92   0.00%          0   0.00%\r\nCom_show_variables            84   0.00%          0   0.00%\r\nCom_show_slave_status         72   0.00%          0   0.00%\r\nCom_show_master_status        47   0.00%          0   0.00%\r\nCom_show_processlist          43   0.00%          0   0.00%\r\nCom_change_db                 27   0.00%          0   0.00%\r\nCom_show_databases            26   0.00%          0   0.00%\r\nCom_show_charsets             24   0.00%          0   0.00%\r\nCom_show_collations           24   0.00%          0   0.00%\r\nCom_alter_table               12   0.00%          0   0.00%\r\nCom_show_fields               12   0.00%          0   0.00%\r\nCom_show_grants               10   0.00%          0   0.00%<\/pre>\n
Variables & Status<\/h5>\n
This section calculates statistics, like\u00a0queries per second<\/strong>, and displays them out in number of different modes.<\/p>\n
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\r\n  0             0             0      18163174             0             0             0             0             0          1.99          1.32\r\n  0             0             0      18163180             0             0             0             0             0          1.99          1.32\r\n  0             0             0      18163188             0             0             0             0             0          1.99          1.32\r\n  0             0             0      18163192             0             0             0             0             0          1.99          1.32\r\n  0             0             0      18163217             0             0             0             0             0          1.99          1.32\r\n  0             0             0      18163265             0             0             0             0             0          1.99          1.32\r\n  0             0             0      18163300             0             0             0             0             0          1.99          1.32\r\n  0             0             0      18163309             0             0             0             0             0          1.99          1.32\r\n  0             0             0      18163321             0             0             0             0             0          1.99          1.32\r\n  0             0             0      18163331             0             0             0             0             0          1.99          1.32<\/pre>\n
Replication Status<\/h5>\n
In this mode, you will see the output of\u00a0Slave SQL Status<\/strong>,\u00a0Slave I\/O Status<\/strong>\u00a0and\u00a0Master Status<\/strong>. The first two section shows the\u00a0slave status<\/strong>\u00a0and\u00a0slave I\/O thread status<\/strong>\u00a0and the last section shows\u00a0Master status<\/strong>.<\/p>\n
_______________________ Slave SQL Status _______________________\r\nMaster        On?  TimeLag  Catchup  Temp  Relay Pos  Last Error\r\n172.16.25.125  Yes    00:00     0.00     0   41295853            \r\n\r\n____________________________________ Slave I\/O Status _____________________________________\r\nMaster        On?  File              Relay Size  Pos       State                           \r\n172.16.25.125  Yes  mysql-bin.000025      39.38M  41295708  Waiting for master to send event\r\n\r\n____________ Master Status _____________\r\nFile              Position  Binlog Cache\r\nmysql-bin.000010  10887846         0.00%<\/pre>\n
Non-Interactively<\/h5>\n
You can run \u201cinnotop<\/strong>\u201d in non-interactively.<\/p>\n
# innotop --count 5 -d 1 -n<\/pre>\n
uptime\tmax_query_time\ttime_behind_master\tconnections\tlocked_count\tqps\tspark_qps\trun\tspark_run\topen\tslave_running\tlongest_sql\r\n61d\t\t\t2\t0\t0.000363908088893752\t\t\t\t64\tYes \t\r\n61d\t\t\t2\t0\t4.96871146980749\t_\t\t_\t64\tYes \t\r\n61d\t\t\t2\t0\t3.9633543857494\t^_\t\t__\t64\tYes \t\r\n61d\t\t\t2\t0\t3.96701862656428\t^__\t\t___\t64\tYes \t\r\n61d\t\t\t2\t0\t3.96574802684297\t^___\t\t____\t64\tYes<\/pre>\n
Monitor Remote Database<\/h5>\n
To monitor a remote database on a remote system, use the following command using a particular\u00a0username<\/strong>,\u00a0password<\/strong>\u00a0and\u00a0hostname<\/strong>.<\/p>\n
# innotop -u username -p password -h hostname<\/pre>\n
For more information about \u2018innotop<\/strong>\u2018 usage and options, see the man pages by hitting \u201cman innotop<\/strong>\u201d on a terminal.<\/p>\n
Reference Links<\/h3>\n
Innotop Homepage<\/a><\/p>\n
Read Also<\/strong>\u00a0:<\/p>\n
    \n
  1. Mtop (MySQL Database Monitoring) in RHEL\/CentOS\/Fedora<\/a><\/li>\n<\/ol>\n
    Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
    Innotop\u00a0is an excellent command line program, similar to \u2018top command\u2018 to monitor local and remote MySQL servers running under\u00a0InnoDB\u00a0engine. Innotop comes with many features and different types of modes\/options, which helps to monitor different aspects of MySQL performance and also helps database administrator to find out what\u2019s wrong going with MySQL server. For example,\u00a0Innotop\u00a0helps in … <\/p>\n
    Continue reading “Install Innotop to Monitor MySQL Server Performance”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-11467","post","type-post","status-publish","format-standard","hentry","category-linux"],"_links":{"self":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11467","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/comments?post=11467"}],"version-history":[{"count":1,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11467\/revisions"}],"predecessor-version":[{"id":11468,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11467\/revisions\/11468"}],"wp:attachment":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/media?parent=11467"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/categories?post=11467"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/tags?post=11467"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}