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 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.
Monitoring MySQL Resources Using innotop
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.
Key Features of Innotop:
-
- Performance Analysis: By monitoring CPU usage, memory consumption, disk I/O, and query execution times, innotop helps to discover bottlenecks and optimize resource utilization.
- Real-Time Monitoring: Innotop delivers live updates on numerous MySQL server metrics, allowing administrators to discover and respond to performance issues as they occur.
- 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.
- Customizable Views: Users may customize the display to meet their individual needs, concentrating on the most important metrics for their use case.
- Historical Data: Innotop may save data for future examination, assisting with trend analysis and capacity planning.
In this blog we are going to discuss the Innotop tool. It’s an open source monitoring tool in MySQL.
To install innotop from the YUM repository, follow these instructions:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | [root@mysql-01 innotop-master]# yum install innotop ============================================================================================================================================================================================================== Package Architecture Version Repository Size ============================================================================================================================================================================================================== Installing: innotop noarch 1.13.0-1.el8 epel 156 k Installing dependencies: perl-TermReadKey x86_64 2.37-7.el8 appstream 40 k Transaction Summary ============================================================================================================================================================================================================== Install 2 Packages Total download size: 196 k Installed size: 563 k Is this ok [y/N]: y Downloading Packages: (1/2): perl-TermReadKey-2.37-7.el8.x86_64.rpm 137 kB/s | 40 kB 00:00 (2/2): innotop-1.13.0-1.el8.noarch.rpm 44 kB/s | 156 kB 00:03 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 45 kB/s | 196 kB 00:04 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : perl-TermReadKey-2.37-7.el8.x86_64 1/2 Installing : innotop-1.13.0-1.el8.noarch 2/2 Running scriptlet: innotop-1.13.0-1.el8.noarch 2/2 Verifying : perl-TermReadKey-2.37-7.el8.x86_64 1/2 Verifying : innotop-1.13.0-1.el8.noarch 2/2 Installed: innotop-1.13.0-1.el8.noarch perl-TermReadKey-2.37-7.el8.x86_64 Complete! |
Verify installation: Check the version of innotop to ensure it is installed.
| 1 2 | [root@mysql-02 innotop-master]# innotop --version innotop Ver 1.13.0 |
Monitoring using Innotop
[root@mysql-02 innotop-master]# innotop -u root –askpass
Enter password :
Switching different modes for checking in Innotop:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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 q Quit innotop k Kill a query's connection r Reverse sort order n Switch to the next connection s Choose sort column p Pause innotop 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 |
Some of the matrices overview used in monitoring of mysql performance:
For Dashboard:
Offers a quick overview of the server’s overall performance and critical metrics.
| 1 2 | Uptime MaxSQL ReplLag QPS Cxns Run Miss Lock Tbls Repl SQL 16m 0.69 1 0.00 0 64 |
InnodDB I/O info:
InnoDB I/O. Information: Provides visibility into InnoDB’s input/output processes, which is critical for monitoring disk activity and performance.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | _________________________ I/O Threads __________________________ Thread Purpose Thread Status 0 insert buffer thread waiting for completed aio requests 1 read thread waiting for completed aio requests 2 read thread waiting for completed aio requests 3 read thread waiting for completed aio requests 4 read thread waiting for completed aio requests 5 write thread waiting for completed aio requests 6 write thread waiting for completed aio requests 7 write thread waiting for completed aio requests 8 write thread waiting for completed aio requests ____________________________ Pending I/O _____________________________ Async Rds Async Wrt IBuf Async Rds Sync I/Os Log Flushes Log I/Os 0 ________________________ File I/O Misc _________________________ OS Reads OS Writes OS fsyncs Reads/Sec Writes/Sec Bytes/Sec 1309 449 213 0.00 0.00 0 _____________________ Log Statistics _____________________ Sequence No. Flushed To Last Checkpoint IO Done IO/Sec 947420121 947420121 947420121 117 0.00 |
Query List:
Displays active inquiries, including execution progress and resource utilization, which is useful for discovering sluggish or troublesome queries.
| 1 2 3 4 5 6 | When Load Cxns QPS Slow Se/In/Up/De% QCacheHit KCacheHit BpsIn BpsOut Now 0.03 1 1.95 0 0/ 0/ 0/ 0 0.00% 100.00% 52.28 6.09k Total 0.00 151 1.13 0 0/ 0/ 0/ 0 0.00% 100.00% 19.88 5.06k Cmd ID State User Host DB Time Query Daemon 8 Waiting on empty q event_sc localhost 19:44 |
InnoDB Buffer:
Displays the current condition and utilization of InnoDB buffer pools, which are essential for effective data caching and retrieval.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | ___________________________ Buffer Pool ____________________________ Size Free Bufs Pages Dirty Pages Hit Rate Memory Add'l Pool 127.99k 129628 1429 0 -- 0 ____________________ Page Statistics _____________________ Reads Writes Created Reads/Sec Writes/Sec Creates/Sec 1285 258 144 0.00 0.00 0.00 ______________________ Insert Buffers ______________________ Inserts Merged Recs Merges Size Free List Len Seg. Size 0 0 1 0 2 ___________________ Adaptive Hash Index ___________________ Size Cells Used Node Heap Bufs Hash/Sec Non-Hash/Sec 540.29k 3 0.00 0.00 |
INNODB LOCK Wait:
Displays transactions that are waiting for locks in InnoDB, which can assist discover bottlenecks caused by lock contention.
| 1 | WThread Waiting Query WWait BThread BRowsMod BAge BWait BStatus Blocking Query |
InnoDB Row Ops:
Displays information on row-level operations in InnoDB, which is important for understanding how data is modified.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | ________________ 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 0 sleeping _____________________________ InnoDB Semaphores _____________________________ Waits Spins Rounds RW Waits RW Spins Sh Waits Sh Spins Signals ResCnt 0 0 0 0 ____________________________ InnoDB Wait Array _____________________________ Thread Time File Line Type Readers Lck Var Waiters Waiting? Ending? |
Innodb Deadlocks:
Shows information regarding deadlocks in the InnoDB storage engine, which may be used to discover and resolve transaction problems.
| 1 2 3 4 5 6 7 | [RO] InnoDB Deadlocks (? for help) localhost, 17m, InnoDB 0s :-), 126.08 QPS, 4/2/0 con/run/cac thds, 8.0.40 ______________________ Deadlock Transactions ______________________ ID Timestring User Host Victim Time Undo LStrcts Query Text ____________________ Deadlock Locks ____________________ ID Waiting Mode DB Table Index Special Ins Intent |
InnoDB locks:
Displays information about all locks on the server, which is important for detecting locking issues and increasing concurrency.
| 1 2 | _____________________________ InnoDB Locks _______________________________ ID Type Waiting Wait Active Mode DB Table Index Ins Intent Special |
Variables & Status:
Displays server variables and status information, which is required for customizing and tweaking server performance.
| 1 2 3 4 5 6 | _______________________________ InnoDB Locks _______________________________ ID Type Waiting Wait Active Mode DB Table Index Ins Intent Special 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 0.98 0 0 9 1 0.1 0 0 0 0.66 0.66 0.98 0 0 9 1 0.1 0 0 0 0.66 0.66 |
Command Summary:
A summary of the commands performed on the server, with a focus on the most common and resource-intensive.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | __________________________ Command Summary __________________________ Name Value Pct Last Incr Pct Com_show_status 340 40.38% 2 40.00% Com_admin_commands 311 36.94% 2 40.00% Com_show_engine_status 83 9.86% 1 20.00% Com_create_table 40 4.75% 0 0.00% Com_show_master_status 29 3.44% 0 0.00% Com_show_processlist 29 3.44% 0 0.00% Com_set_option 6 0.71% 0 0.00% Com_change_db 1 0.12% 0 0.00% Com_create_db 1 0.12% 0 0.00% Com_flush 1 0.12% 0 0.00% Com_show_variables 1 0.12% 0 0.00% Com_alter_db 0 0.00% 0 0.00% Com_alter_event 0 0.00% 0 0.00% Com_alter_function 0 0.00% 0 0.00% Com_alter_instance 0 0.00% 0 0.00% Com_alter_procedure 0 0.00% 0 0.00% Com_alter_resource_group 0 0.00% 0 0.00% |
InnoDB Txns:
Monitors InnoDB transactions, giving information on transaction activity and performance.
| 1 2 3 | History Versions Undo Dirty Buf Used Bufs Txns MaxTxnTime LStrcts 0 0.00% 1.09% 3 00:00 ID User Host Txn Status Time Undo Query Text |
InnoDB FK Err:
Display Foreign key uses.
| 1 | No foreign key error data. |
User Statistics:
Provides data on user activity, allowing you to track and enhance user-specific performance.
| 1 | DB Table Reads Reads Via Idx Changed Chg X Idx |
In conclusion, using the right monitoring tools in MySQL, like Innotop, can significantly improve database performance.
Credit for Innotop tool : https://github.com/innotop/innotop.git
