MySQL Database / MariaDB Monitoring

Overview

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.

Creating a new MySQL database monitor

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:

Click on New Monitor link.
Select MySQL DB Server.
Enter the IP Address or hostname of the host.
Enter the Subnet Mask of the network while adding the monitor.
Check the 'Force SSL' box to force an SSL connection. For establishing an SSL connection, ensure to use the hostname specified in the Common Name (CN) field of the imported certificate.
Enter the port number in which MySQL is running.
Enter the polling interval time in minutes.
If you are adding a new monitor from the Central Server, select a Probe Server.
Provide the user name and password of user who has permission to access the MySQL database.
Specify the database name. Please note that the Database name must be valid. Also, the database name is associated with the user name. Hence, provide the database name corresponding to the user name given in the above field.
Choose the MySQL Driver from the Driver option to connect to the MySQL DB server (Default value: MariaDB Driver).
Note: For the MySQL Driver, the MySQL jar file should be located in the AppManager_home/working/mysql/MMMySQLDriver/ path.
Choose the Monitor Group from the combo box with which you want to associate MySQL database server Monitor (optional). You can choose multiple groups to associate your monitor.
Click Add Monitor(s). This discovers MySQL database server from the network and starts monitoring them.

Monitored Parameters

Availability tab gives the availability history for the past 24 hours or 30 days.
Performance tab gives the health status and events for the past 24 hours or 30 days.
List view enables you to perform bulk admin configurations.

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.

Overview

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.
Open Files Utilization
Open Files Used(%)	The percentage of file descriptors currently in use by the MySQL server.

Database

Parameter	Description
Database Details
Database Name	Name of the database instance.
Database Size	Size of the various databases in the MySQL server (in MB).

Replication

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.
Replication Lag From Original Source	The time delay between when a transaction is committed on the primary (original source) node and when that same transaction becomes available on the current node.
Replication Lag From Immediate Source	The time delay between when a transaction is committed on the immediate upstream node and when it becomes available on the current node.
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.

Performance

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.

Session

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.

Configuration

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.

Configuration Metrics

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.

MySQL Database / MariaDB Monitoring

Overview

Creating a new MySQL database monitor

Monitored Parameters

Overview

Database

Replication

Performance

Session

Configuration

Configuration Metrics

Loved by customers all over the world

"Standout Tool With Extensive Monitoring Capabilities"

"I like Applications Manager because it helps us to detect issues present in our servers and SQL databases."

Carlos Rivero

Trusted by over 6000+ businesses globally