{"id":2779,"date":"2025-01-22T15:25:58","date_gmt":"2025-01-22T15:25:58","guid":{"rendered":"https:\/\/bynatree.com\/?p=2779"},"modified":"2025-01-22T15:25:58","modified_gmt":"2025-01-22T15:25:58","slug":"real-time-mysql-performance-monitoring-with-innotop-on-centos-8-monitoring-tools-in-mysql","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2025\/01\/22\/real-time-mysql-performance-monitoring-with-innotop-on-centos-8-monitoring-tools-in-mysql\/","title":{"rendered":"Real-Time MySQL Performance Monitoring with Innotop on CentOS 8 – Monitoring Tools in MySQL"},"content":{"rendered":"
\n

In this blog post, we’ll explore real-time MySQL performance monitoring with Innotop on CentOS 8. Using the best monitoring tools in MySQL, you can ensure optimal database performance.<\/h4>\n<\/blockquote>\n

<\/h2>\n

Introduction to Resource and Performance Monitoring Tools in MySQL<\/span><\/h2>\n

MySQL’s resource and performance monitoring tools in MySQL features are crucial for both database administrators and developers. These tools offer extensive insights into system resource use, database performance, and possible bottlenecks. By continually monitoring indicators such as CPU utilization, memory consumption, disk I\/O, and query execution times, they enable proactive administration and optimization of MySQL databases.<\/span><\/p>\n

Monitoring MySQL Resources Using innotop<\/span><\/h2>\n

Innotop is a robust command-line tool created exclusively for real-time monitoring of MySQL and MariaDB servers. Innotop, derived from the popular top command-line program for monitoring system processes, adds comparable capability to MySQL databases by offering a simple and comprehensive interface for watching the database’s performance indicators.<\/span><\/p>\n

Key Features of Innotop:<\/span><\/p>\n

    \n
  • \n
      \n
    • Performance Analysis: By monitoring CPU usage, memory consumption, disk I\/O, and query execution times, innotop helps to discover bottlenecks and optimize resource utilization.<\/span><\/li>\n
    • Real-Time Monitoring: Innotop delivers live updates on numerous MySQL server metrics, allowing administrators to discover and respond to performance issues as they occur.<\/span><\/li>\n
    • Multiple Modes: It offers a variety of display modes, including process lists, InnoDB status, variables, and more, allowing for a deep dive into certain components of the database.<\/span><\/li>\n
    • Customizable Views: Users may customize the display to meet their individual needs, concentrating on the most important metrics for their use case.<\/span><\/li>\n
    • Historical Data: Innotop may save data for future examination, assisting with trend analysis and capacity planning.\u00a0<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n

       <\/p>\n

      In this blog we are going to discuss the Innotop tool. It’s an open source monitoring tool\u00a0in MySQL.<\/span><\/p>\n

      To install innotop from the YUM repository, follow these instructions:<\/span><\/h3>\n
      [root@mysql-01 innotop-master]# yum install innotop\n==============================================================================================================================================================================================================\n Package                                                Architecture                                 Version                                            Repository                                       Size\n==============================================================================================================================================================================================================\nInstalling:\n innotop                                                noarch                                       1.13.0-1.el8                                       epel                                            156 k\nInstalling dependencies:\n perl-TermReadKey                                       x86_64                                       2.37-7.el8                                         appstream                                        40 k\n\nTransaction Summary\n==============================================================================================================================================================================================================\nInstall  2 Packages\n\nTotal download size: 196 k\nInstalled size: 563 k\nIs this ok [y\/N]: y\nDownloading Packages:\n(1\/2): perl-TermReadKey-2.37-7.el8.x86_64.rpm                                                                                                                                 137 kB\/s |  40 kB     00:00\n(2\/2): innotop-1.13.0-1.el8.noarch.rpm                                                                                                                                         44 kB\/s | 156 kB     00:03\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\nTotal                                                                                                                                                                          45 kB\/s | 196 kB     00:04\nRunning transaction check\nTransaction check succeeded.\nRunning transaction test\nTransaction test succeeded.\nRunning transaction\n  Preparing        :                                                                                                                                                                                      1\/1\n  Installing       : perl-TermReadKey-2.37-7.el8.x86_64                                                                                                                                                   1\/2\n  Installing       : innotop-1.13.0-1.el8.noarch                                                                                                                                                          2\/2\n  Running scriptlet: innotop-1.13.0-1.el8.noarch                                                                                                                                                          2\/2\n  Verifying        : perl-TermReadKey-2.37-7.el8.x86_64                                                                                                                                                   1\/2\n  Verifying        : innotop-1.13.0-1.el8.noarch                                                                                                                                                          2\/2\n\nInstalled:\n  innotop-1.13.0-1.el8.noarch                                                                        perl-TermReadKey-2.37-7.el8.x86_64\n\nComplete!<\/pre>\n
      Verify installation: Check the version of innotop to ensure it is installed.<\/span><\/h3>\n
      [root@mysql-02 innotop-master]# innotop --version\ninnotop  Ver 1.13.0\n\n<\/pre>\n
      Monitoring using Innotop<\/b><\/h2>\n
      [root@mysql-02 innotop-master]# innotop -u root –askpass<\/b><\/p>\n
      Enter password :<\/b><\/p>\n
       <\/p>\n
      Switching different modes for checking in Innotop:
      \n<\/b><\/h3>\n
      Switch to a different mode:\n   A  Dashboard         I  InnoDB I\/O Info     Q  Query List\n   B  InnoDB Buffers    K  InnoDB Lock Waits   R  InnoDB Row Ops\n   C  Command Summary   L  Locks               S  Variables & Status\n   D  InnoDB Deadlocks  M  Replication Status  T  InnoDB Txns\n   F  InnoDB FK Err     O  Open Tables         U  User Statistics\nActions:\n   d  Change refresh interval        q  Quit innotop\n   k  Kill a query's connection      r  Reverse sort order\n   n  Switch to the next connection  s  Choose sort column\n   p  Pause innotop                  x  Kill a query\n\nOther:\n TAB  Switch to the next server group   \/  Quickly filter what you see\n   !  Show license and warranty         =  Toggle aggregation\n   #  Select\/create server groups       @  Select\/create server connections\n   $  Edit configuration settings       \\  Clear quick-filters\nPress any key to continue\n<\/pre>\n
      \n
      Some of the matrices overview used in monitoring of mysql performance:\u00a0<\/span><\/h3>\n<\/blockquote>\n
      For Dashboard: <\/span><\/h3>\n
      Offers a quick overview of the server’s overall performance and critical metrics.<\/span><\/p>\n
      Uptime  MaxSQL  ReplLag  QPS   Cxns  Run  Miss  Lock  Tbls  Repl  SQL\n   16m                   0.69     1       0.00     0    64\n<\/pre>\n
      InnodDB I\/O info: <\/span><\/h3>\n
      InnoDB I\/O. Information: Provides visibility into InnoDB’s input\/output processes, which is critical for monitoring disk activity and performance.\u00a0<\/span><\/p>\n
      _________________________ I\/O Threads __________________________\nThread  Purpose               Thread Status\n     0  insert buffer thread  waiting for completed aio requests\n     1  read thread           waiting for completed aio requests\n     2  read thread           waiting for completed aio requests\n     3  read thread           waiting for completed aio requests\n     4  read thread           waiting for completed aio requests\n     5  write thread          waiting for completed aio requests\n     6  write thread          waiting for completed aio requests\n     7  write thread          waiting for completed aio requests\n     8  write thread          waiting for completed aio requests\n\n____________________________ Pending I\/O _____________________________\nAsync Rds  Async Wrt  IBuf Async Rds  Sync I\/Os  Log Flushes  Log I\/Os\n                                                           0\n\n________________________ File I\/O Misc _________________________\nOS Reads  OS Writes  OS fsyncs  Reads\/Sec  Writes\/Sec  Bytes\/Sec\n    1309        449        213       0.00        0.00          0\n\n_____________________ Log Statistics _____________________\nSequence No.  Flushed To  Last Checkpoint  IO Done  IO\/Sec\n947420121     947420121   947420121            117    0.00<\/pre>\n
      Query List: <\/span><\/h3>\n
      Displays active inquiries, including execution progress and resource utilization, which is useful for discovering sluggish or troublesome queries.<\/span><\/p>\n
      When   Load  Cxns  QPS   Slow  Se\/In\/Up\/De%  QCacheHit  KCacheHit  BpsIn  BpsOut\nNow    0.03     1  1.95     0   0\/ 0\/ 0\/ 0       0.00%    100.00%  52.28   6.09k\nTotal  0.00   151  1.13     0   0\/ 0\/ 0\/ 0       0.00%    100.00%  19.88   5.06k\n\nCmd     ID      State               User      Host       DB      Time   Query\nDaemon       8  Waiting on empty q  event_sc  localhost          19:44<\/pre>\n
      InnoDB Buffer: <\/span><\/h3>\n
      Displays the current condition and utilization of InnoDB buffer pools, which are essential for effective data caching and retrieval.<\/span><\/h3>\n
      ___________________________ Buffer Pool ____________________________\nSize     Free Bufs  Pages  Dirty Pages  Hit Rate  Memory  Add'l Pool\n127.99k     129628   1429            0  --             0\n\n____________________ Page Statistics _____________________\nReads  Writes  Created  Reads\/Sec  Writes\/Sec  Creates\/Sec\n 1285     258      144       0.00        0.00         0.00\n\n______________________ Insert Buffers ______________________\nInserts  Merged Recs  Merges  Size  Free List Len  Seg. Size\n                   0       0     1              0          2\n\n___________________ Adaptive Hash Index ___________________\nSize     Cells Used  Node Heap Bufs  Hash\/Sec  Non-Hash\/Sec\n540.29k                           3      0.00          0.00\n<\/pre>\n
      INNODB LOCK Wait: <\/span><\/h3>\n
      Displays transactions that are waiting for locks in InnoDB, which can assist discover bottlenecks caused by lock contention.<\/span><\/p>\n
      WThread  Waiting Query  WWait  BThread  BRowsMod  BAge  BWait  BStatus  Blocking Query\n\n<\/pre>\n
      InnoDB Row Ops: <\/span><\/h3>\n
      Displays information on row-level operations in InnoDB, which is important for understanding how data is modified. <\/span><\/p>\n
      ________________ InnoDB Row Operations _________________\nIns  Upd  Read  Del  Ins\/Sec  Upd\/Sec  Read\/Sec  Del\/Sec\n  0    0     0    0     0.00     0.00      0.00     0.00\n\n___________________ Row Operation Misc ____________________\nQueries Queued  Queries Inside  Rd Views  Main Thread State\n             0               0         0  sleeping\n\n_____________________________ InnoDB Semaphores _____________________________\nWaits  Spins  Rounds  RW Waits  RW Spins  Sh Waits  Sh Spins  Signals  ResCnt\n                             0         0         0         0\n\n____________________________ InnoDB Wait Array _____________________________\nThread  Time  File  Line  Type  Readers  Lck Var  Waiters  Waiting?  Ending?\n<\/pre>\n
      Innodb Deadlocks: <\/span><\/h3>\n
      Shows information regarding deadlocks in the InnoDB storage engine, which may be used to discover and resolve transaction problems.<\/span><\/p>\n
      [RO] InnoDB Deadlocks (? for help)                                                                                                   localhost, 17m, InnoDB 0s :-), 126.08 QPS, 4\/2\/0 con\/run\/cac thds, 8.0.40\n\n______________________ Deadlock Transactions ______________________\nID  Timestring  User  Host  Victim  Time  Undo  LStrcts  Query Text\n\n____________________ Deadlock Locks ____________________\nID  Waiting  Mode  DB  Table  Index  Special  Ins Intent\n\n<\/pre>\n
      InnoDB locks: <\/span><\/h3>\n
      Displays information about all locks on the server, which is important for detecting locking issues and increasing concurrency.<\/span><\/p>\n
      _____________________________ InnoDB Locks _______________________________\nID  Type  Waiting  Wait  Active  Mode  DB  Table  Index  Ins Intent  Special\n\n\n<\/pre>\n
      Variables & Status: <\/span><\/h3>\n
      Displays server variables and status information, which is required for customizing and tweaking server performance.<\/span><\/p>\n
      _______________________________ InnoDB Locks \n_______________________________\nID  Type  Waiting  Wait  Active  Mode  DB  Table  Index  Ins Intent  Special\n              QPS         Commit_PS   Rollback_Commit      Write_Commit         R_W_Ratio          Opens_PS  Table_Cache_Used        Threads_PS Thread_Cache_Used     CXN_Used_Ever      CXN_Used_Now\n             0.98                 0                 0                 9                 1               0.1                 0                 0                 0              0.66              0.66\n             0.98                 0                 0                 9                 1               0.1                 0                 0                 0              0.66              0.66\n\n\n\n<\/pre>\n
      Command Summary: <\/span><\/h3>\n
      A summary of the commands performed on the server, with a focus on the most common and resource-intensive.<\/span><\/p>\n
      __________________________ Command Summary __________________________\nName                                 Value  Pct     Last Incr  Pct\nCom_show_status                        340  40.38%          2  40.00%\nCom_admin_commands                     311  36.94%          2  40.00%\nCom_show_engine_status                  83   9.86%          1  20.00%\nCom_create_table                        40   4.75%          0   0.00%\nCom_show_master_status                  29   3.44%          0   0.00%\nCom_show_processlist                    29   3.44%          0   0.00%\nCom_set_option                           6   0.71%          0   0.00%\nCom_change_db                            1   0.12%          0   0.00%\nCom_create_db                            1   0.12%          0   0.00%\nCom_flush                                1   0.12%          0   0.00%\nCom_show_variables                       1   0.12%          0   0.00%\nCom_alter_db                             0   0.00%          0   0.00%\nCom_alter_event                          0   0.00%          0   0.00%\nCom_alter_function                       0   0.00%          0   0.00%\nCom_alter_instance                       0   0.00%          0   0.00%\nCom_alter_procedure                      0   0.00%          0   0.00%\nCom_alter_resource_group                 0   0.00%          0   0.00%\n\n<\/pre>\n
      InnoDB Txns: <\/span><\/h3>\n
      Monitors InnoDB transactions, giving information on transaction activity and performance.<\/span><\/p>\n
      History  Versions  Undo  Dirty Buf  Used Bufs  Txns  MaxTxnTime  LStrcts\n      0                      0.00%      1.09%     3       00:00\nID  User  Host  Txn Status  Time  Undo  Query Text<\/pre>\n
      InnoDB FK Err: <\/span><\/h3>\n
      Display Foreign key uses.<\/span><\/p>\n
      No foreign key error data.<\/pre>\n
      User Statistics: <\/span><\/h3>\n
      Provides data on user activity, allowing you to track and enhance user-specific performance.<\/span><\/p>\n
      DB  Table  Reads  Reads Via Idx  Changed  Chg X Idx<\/pre>\n
      In conclusion, using the right monitoring tools in MySQL, like Innotop, can significantly improve database performance.<\/p>\n
      \n
      Credit for Innotop tool : https:\/\/github.com\/innotop\/innotop.git<\/b><\/p>\n<\/blockquote>\n
      \u00a0<\/b><\/p>\n","protected":false},"excerpt":{"rendered":"
      In this blog post, we’ll explore real-time MySQL performance monitoring with Innotop on CentOS 8. Using the best monitoring tools in MySQL, you can ensure optimal database performance. Introduction to Resource and Performance Monitoring Tools in MySQL MySQL’s resource and performance monitoring tools in MySQL features are crucial for both database administrators and developers. These…<\/p>\n","protected":false},"author":1,"featured_media":2780,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24],"tags":[89,92,112,116,169,176,190,201,210,211,230,240,301,316,334],"class_list":["post-2779","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-centos-8","tag-centos-performance","tag-database-management","tag-database-performance","tag-innotop","tag-it-operations","tag-linux-tools","tag-monitoring-tools","tag-mysql-monitoring","tag-mysql-performance","tag-open-source-monitoring","tag-performance-tuning","tag-real-time-monitoring","tag-server-monitoring","tag-system-administration","category-24","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2779","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2779"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2779\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2780"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2779"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2779"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2779"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}