Applications Manager's MySQL Monitoring capabilities allow users to efficiently track the performance and health of their MySQL databases in real time. Key features include real-time monitoring of key performance indicators such as query execution time, database connections, server resource utilization, and more.
In this help document, you will learn how to create a MySQL database monitor, along with the list of parameters that are monitored by Applications Manager.
Supported versions: MySQL 5.0.15 and above, MariaDB 10.4 and above
Prerequisites for monitoring MySQL metrics: Click here
Rest API to add a new MySQL performance monitor: Click here
To create a MySQL database server Monitor, follow the steps given below:
Note: For the MySQL Driver, the MySQL jar file should be located in the AppManager_home/working/mysql/MMMySQLDriver/ path.
To view detailed performance metrics of a MySQL server, click the corresponding monitor listed in the Availability tab. These metrics are categorized into six different tabs for better understanding.
This tab provides information into the overall performance of the MySQL server.
| Parameter | Description |
|---|---|
| Monitor Information | |
| Name | Denotes the name of MySQL server monitor. |
| Health | Denotes the health (Clear, Warning, Critical) of the MySQL server. |
| Type | Denotes the type you are monitoring. |
| MySQL Version | Specifies the version of the database server. |
| Port | Specifies the port number at which the database server is running. |
| Base Directory | Specifies the directory in which the database server is installed. |
| Data Directory | Specifies the directory in the hard disk of the system where the data for the database server is stored. |
| Host Name | Specifies the host at which the database server is running. |
| Host OS | Specifies the OS of the host where the database server is running. |
| Last Alarm | Specifies the last alarm that was generated for the database server. |
| Last Polled at | Specifies the time at which the last poll was performed. |
| Next Poll at | Specifies the time at which the next poll is scheduled. |
| Availability | Shows the current status of the server - available or not available. |
| Connection Time | |
| Connection Time | The time taken to connect to the MySQL database server from Applications Manager server. |
| Connection Time Out | Specifies the maximum time taken by the application to connect to MySQL Server |
| Request Statistics | |
| Request Rate | Number of request received in one second. |
| Bytes Received Rate | Number of bytes received in one second. |
| Bytes Sent Rate | Number of bytes sent in one second. |
| Connection Statistics | |
| Open Connections | The number of connections opened at present in the MySql Server. |
| Aborted Connections | Number of tries to connect to the MySQL server that failed. |
| Aborted Clients | Number of clients aborted by MySQL server. |
| Open Connections in (%) | The ratio of connections opened with max connections. |
| Thread Details | |
| Threads Used | Number of threads processing the request. |
| Threads in Cache | Number of threads currently placed in the thread cache. |
| Thread Cache Size | Specifies the cache size in the MySQL server. |
| Table Lock Statistics | |
| Immediate Locks | Number of times a table lock for the table is acquired immediately. |
| Locks Wait | Number of times a table lock could not be acquired after waiting. |
| Key Efficiency | |
| Key Hitrate | Percentage of key read requests that resulted in actual key reads from the key buffer. |
| Key Buffer Used | Amount of allocated key buffer in use. |
| Key Buffer Size | Size of the buffer used for index blocks. Also known as the key cache. |
| Query Statistics | |
| Queries Inserted/Min | No. of Insert Queries executed per minute |
| Queries Deleted/Min | No. of Delete Queries executed per minute |
| Queries Updated/Min | No. of Update Queries executed per minute |
| Queries Selected/Min | No. of Select Queries executed per minute |
| Query Cache Hitrate | |
| Query Cache Hitrate | Ratio of queries that were cached and queries that were not cached. |
| Query Cache Size | Amount of memory allocated for caching query results. |
| Query Cache Limit | Maximum amount of memory for storing cache results. |
| Parameter | Description |
|---|---|
| Database Details | |
| Database Name | Name of the database instance. |
| Database Size | Size of the various databases in the MySQL server (in MB). |
| Parameter | Description |
|---|---|
| Replication Details | |
| 1. Master-Slave Replication | |
| Replication Status | The status of Slave process in MySQL Server |
| Slave IO Running | Status of the Slave IO Process in MySQL Server. Possible values are Yes or No. |
| Slave SQL Running | Status of the Slate SQL Process in MySQL Server. Possible values are Yes or No. |
| Last Error | The last error occured when Slave is synching the data from master. |
| Master Host | The hostname or IP number of the master replication server. |
| Master Port | The TCP/IP port number that the master is listening on. |
| Master User | The username of the account that the slave thread uses for authentication when it connects to the master. |
| Time Behind Master | This indicates of how “late” the slave is behind the Master. |
| 2. Group Replication | |
| State | Defines the state of the Member. Possible values are ONLINE, OFFLINE, ERROR, RECOVERY, UNREACHABLE. |
| Connection State | The status of connection whether it is active/Idle or no Longer exists or trying to connect. Possible values are ON, OFF, CONNECTING. |
| Count Conflicts Detected | The number of transactions that did not pass the conflict detection check. |
| Count Transactions In Queue | The number of transactions in the queue pending conflict detection checks. Once the transactions have been checked for conflicts, if they pass the check, they are queued to be applied as well. |
| Count Transactions Rollback | The number of transactions that this member originated that were rolled back after being sent to the replication group. |
| Last Error | The error message which has triggered lastly. |
| Last Error TimeStamp | The Timestamp of last Error. |
| Replication Applier | Gives the status of Replication Applier Thread is Active/Idle and Dead. Possible values are ON, OFF. |
| Member ID | The member server UUID. This has a different value for each member in the group. This also serves as a key because it is unique to each member. |
| Host Name | The Name of the Host of this Member. |
| Role | This metric defines the member's role in the group. Possible values are PRIMARY, SECONDARY. |
| Port | The port Number on which the Member is Running. |
| Replication Mode | The mode of Replication, which the group is using. Possible values are single-primary, multi-primary. |
| Group Name | The name group that the Member (MySQL server) belongs to. |
| Channel Name | The name of the Group Replication Channel. |
| Primary Host | Host Name of the Primary Member in the Group. |
| Primary Port | Port Number of the Primary Member in the Group. |
Note: Ensure that the Slave Replication Server is added as a monitor in Applications Manager for MySQL Master-Slave Replication monitoring.
| Parameter | Description |
|---|---|
| TOP QUERIES BY CPU | |
| Avg. CPU Time | The average CPU time taken to execute the query (in millisecond). |
| Total CPU Time | The cumulative total amount of CPU time that has been spent running this query (in millisecond). |
| Max CPU | The maximum amount of time that was taken by the CPU to run this query (in millisecond). |
| Query | The SQL query which consumed the maximum CPU time. |
| DB Name | The database under which this query has been executed. |
| Last Execution Time | The last time at which the query executed. |
Note : The TOP QUERIES BY CPU data will be displayed only for MySQL versions 5.7 & above.
| Parameter | Description |
|---|---|
| Session Details | |
| PID | Displays the process ID of the session running in the MySQL server. |
| Status | Displays the status of the process in the MySQL server. |
| User | The login name of the user executing the process. |
| DB Name | Displays the name of the database currently being used by the process. |
| Program Name | The name of the program that has established the session. |
| Memory Usage | The amount of memory that has been utilized by the process (in kilobytes). |
| CPU Time | The cumulative CPU time taken for the process running in the MySQL Server (in millisecond). |
| Lock Latency | Amount of time taken to establish a lock for the established session (in millisecond). |
| Command | For foreground threads, the type of command the thread is executing on behalf of the client, or Sleep if the session is idle. |
| Query | The query the thread is executing, or NULL if it is not executing any statement. |
| Last Query | The last query executed by the thread, if there is no currently executing statement or wait. |
| Last Query CPU Time | The CPU time taken for the last query to be executed (in millisecond). |
Note : The Session Details data will be displayed only for MySQL versions 5.7 & above.
This tab provides information about the system variables maintained by the MySQL server. These system variables indicate how the server is configured.
You can also view realtime and historical data of any of the attributes present in the 'Configuration Information' section in the Configuration tab. Click on any attribute under the Configuration tab. This will open up a new window named 'History Data' that provides more information about these attributes.
There are two tabs in the History Data window - History Report and Global View.
History Report: This tab provides historical reports of the attribute selected based on the time period chosen. You can also use the Select Attribute drop-down box and view reports for other attributes.
Global View: This tab displays the current values of the attribute selected, across multiple monitors. To view information about other attributes present in the monitor, use the Select Attribute drop-down box and change the attribute.
If you want to view data of multiple attributes, click the Customize Columns link present at the top left corner of the window. This will take you to the Edit Global Viewscreen. In this screen, you can change the monitor type using the Filter by Monitor Type drop-down box, select the metrics to be displayed, and show monitors on a monitor basis or a monitor group basis. After you select your options, click the Show Report button to view those information in the Global View tab.
The View Process List option present on the right side under the Show Status section gives you information on the current threads that are running in the MySQL server.
| Parameter | Description |
|---|---|
| Auto Increment Increment | Controls the increment value between successive AUTO_INCREMENT values. |
| Auto Increment Offset | Allows to specify an offset or starting value for the AUTO_INCREMENT column. |
| Autocommit | Determines whether each SQL statement is automatically committed as a separate transaction or not. |
| Automatic SP Privileges | Enable/Disable this setting to adjust the behavior based on your security and access control requirements. |
| Back Log | Determines the size of the listen queue for incoming TCP/IP connections. It represents the maximum number of connections that can be queued by the MySQL server when it is busy handling other connections. |
| Base Directory | Specifies the base directory for the MySQL installation. It represents the directory that contains the MySQL server binaries, libraries, and other essential files. |
| Binlog Cache Size | Determines the size of the cache used for binary log events. |
| Bulk Insert Buffer Size | Determines the size of the buffer used for bulk insert operations. |
| Connect Timeout | Specifies the maximum time that MySQL waits for a connection to be established (in seconds). |
| Data Directory | Represents the path to the MySQL data directory. |
| Delayed Insert Limit | Specifies the maximum number of rows allowed in a single INSERT DELAYED statement. |
| Delayed Insert Timeout | Specifies the maximum number of rows allowed in a single INSERT DELAYED statement. |
| Delayed Queue Size | Determines the maximum number of rows that can be in the delayed insert queue. |
| Expire Logs Days | Specifies the number of days for which these binary log files should be retained. |
| General Log File | Defines the name of the file where the general query log is written. |
| Host Name | Represents the name of the machine or server hosting the MySQL database instance. |
| Identity | Refers to the concept of an auto-incrementing column. This is commonly used to create primary keys or other unique identifiers in a table. |
| Init Slave | Used in MySQL replication to initialize a slave server from a master server. Allows to specify a file containing the data snapshot from the master server. |
| Innodb Additional Memory Pool Size | Determines the size of the additional memory pool that InnoDB uses for managing various internal data structures. |
| Innodb Autoinc Lock Mode | Allows you to configure how InnoDB locks the table or index when generating new auto-increment values.The available modes include traditional, consecutive, and interleaved, each with different performance characteristics and trade-offs. |
| Innodb Buffer Pool Size | Specifies the size of the InnoDB buffer pool, which is a memory area used to cache frequently accessed data and indexes from InnoDB tables. |
| Innodb Change Buffering | Controls the change buffering mechanism in InnoDB. |
| Innodb Data File Path | Specifies the location and size of the InnoDB data files. |
| Innodb Data Home Dir | Determines the base directory path where InnoDB stores its data files. |
| Innodb Flush Log at trx Commit | Determines the level of durability and reliability for committed transactions in InnoDB. |
| Innodb IO Capacity | This option sets the maximum I/O capacity for InnoDB background tasks, such as flushing pages from the buffer pool or writing the redo log to disk. |
| Innodb Lock Wait Timeout | Specifies the maximum amount of time that a transaction will wait to acquire a lock before throwing an error (in seconds). |
| Innodb Log Buffer Size | Determines the size of the buffer used to hold changes made to the InnoDB redo log before they are written to disk. |
| Innodb Log File Size | Specifies the size of each InnoDB log file. |
| Innodb Log Group Home Dir | Specifies the directory path where InnoDB places its redo log files. |
| Interactive Timeout | Defines the number of seconds of inactivity after which an interactive MySQL session is automatically terminated. |
| Join Buffer Size | Determines the size of the buffer used for joins which do not require a temporary table. |
| Key Buffer Size | Sets the size of the buffer used for caching index blocks for MyISAM tables. |
| Key Cache Age Threshold | Determines the minimum number of requests for an index block before it can be considered for eviction from the key cache. |
| Key Cache Block Size | Determines the size of each block in the key cache for MyISAM tables. |
| Key Cache Division Limit | Sets the maximum number of cache divisions for the key cache in MyISAM tables. |
| Large Page Size | Enables the use of large pages (also known as huge pages) for InnoDB buffer pool and MyISAM key cache. |
| Lock Wait Timeout | Defines the maximum amount of time that a transaction or query will wait to acquire a lock before timing out and throwing an error (in seconds). |
| Long Query Time | Specifies the threshold duration that determines when a query is considered a "long query" by the MySQL server (in seconds). |
| Max Allowed Packet | Determines the maximum size for a single network packet or SQL statement that can be sent to or received from the MySQL server (in bytes). |
| Max Binlog Cache Size | Specifies the maximum amount of memory that can be used by the binary log cache (in bytes). |
| Max Binlog Size | Maximum size for each binary log file in MySQL (in bytes). |
| Max Connect Errors | Defines the maximum number of consecutive failed connection attempts allowed before the MySQL server blocks new connection requests from that host. |
| Max Connections | Determines the maximum number of simultaneous client connections allowed to the MySQL server. |
| Max Delayed Threads | Sets the maximum number of threads that can be used for processing delayed inserts in MySQL. |
| Max Error Count | Specifies the maximum number of errors that can occur during the execution of a multi-row INSERT statement or LOAD DATA statement before the operation is aborted. |
| Max Heap Table Size | Defines the maximum size for individual in-memory heap tables in MySQL (in bytes). |
| Max Insert Delayed Threads | Determines the maximum number of threads that can be used for processing delayed inserts for the DELAYED insert method. |
| Max Join Size | Specifies the maximum size in bytes for the result of a join operation in MySQL. |
| Max SP Recursion Depth | Sets the maximum recursion depth for stored procedures in MySQL. It determines the number of nested stored procedure calls that are allowed. |
| Max Temp Tables | Specifies the maximum number of temporary tables that can be created by a single SQL query in MySQL. |
| Max User Connections | Defines the maximum number of simultaneous connections allowed for a single MySQL user account. |
| Max Write Lock Count | Determines the maximum number of write locks that can be acquired by a single transaction in MySQL. |
| Myisam Sort Buffer Size | Sets the size of the buffer used for sorting data during index creation or MyISAM table repair operations. |
| Net Buffer Length | Determines the maximum size of data packets that can be sent or received in a single network request. |
| Open Files Limit | Specifies the maximum number of files that MySQL can open simultaneously. This includes data files, log files, and other internal files. |
| Port | Defines the network port number on which the MySQL server listens for incoming connections. |
| Preload Buffer Size | Sets the size of the buffer used to preload indexes for MyISAM tables. |
| Profiling History Size | Determines the number of profiling data records that are retained by the MySQL server. |
| Query Alloc Block Size | Sets the block size used for memory allocation when parsing SQL queries in MySQL. |
| Query Cache Limit | Determines the maximum size in bytes for an individual query result that can be stored in the query cache. |
| Query Cache Size | Sets the total amount of memory allocated for the query cache in MySQL. |
| Query Cache Type | Specifies the behavior and mode of the query cache in MySQL. |
| Query Prealloc Size | Defines the size of the initial memory allocation block for query parsing in MySQL. |
| Range Alloc Block Size | Sets the block size used for memory allocation when executing range scans in MySQL. |
| Read Buffer Size | Determines the size of the buffer used for sequential table scans in MySQL. |
| Read rnd Buffer Size | Specifies the size of the buffer used for random reads during queries that don't use an index. |
| SQL Auto is null | Determines the behavior of MySQL when inserting or updating columns that have no explicit value specified |
| SQL Big Selects | Controls whether MySQL allows the execution of SELECT statements that potentially return large result sets. |
| SQL Buffer Result | Determines whether the MySQL server buffers the entire result set of a query in memory before returning it to the client. |
| SQL Log Bin | Enables or disables the binary logging feature in MySQL. |
| SQL Log Off | Used to temporarily disable logging of statements in MySQL. |
| SQL Quote Show Create | Determines whether the SHOW CREATE TABLE statement should enclose table and column names in backticks (`) to ensure proper quoting. |
| SQL Safe Updates | the system variable that controls the behavior of UPDATE and DELETE statements. |
| SQL Select Limit | Determines the maximum number of rows that can be returned by a SELECT statement in MySQL. |
| SQL Warnings | Controls whether warning messages generated by MySQL are displayed. |
| Shared Memory | It is an interprocess communication mechanism used for sharing data between multiple MySQL server instances running on the same host. |
| Shared Memory Base Name | Specifies the base name for the shared memory segments used by the MySQL server instances. |
| Slow Launch Time | Sets the threshold time for considering a client connection as a "slow launch" (in seconds). |
| Slow Query Log File | Specifies the file name and path where the slow query log is written. |
| Socket | Specifies the path to the Unix socket file that is used for local client-server communication. |
| Sort Buffer Size | Determines the size of the buffer used for sorting operations. |
| Storage Engine | Specifies the default storage engine to be used for newly created tables. |
| Table Open Cache | Defines the number of open tables that can be cached in memory. |
| Temp Table Size | Specifies the maximum size of internal in-memory temporary tables. |
| Thread Cache Size | Determines the number of threads that are cached for reuse rather than being terminated after each client connection. |
| Version | Represents the version number of the MySQL server. |
| Version Compile OS | Represents the OS platform where the MySQL software was built. |
| Wait Timeout | Specifies the number of seconds a connection can remain idle before it is automatically closed by the server. |
| Warning Count | Represents the number of warnings generated during the execution of the most recent SQL statement. |
