MySQL Database  / MariaDB Monitoring


Note: In the MySQL database (that you are trying to monitor), ensure that the user name assigned to Applications Manager has the permission to access the MySQL database from the host where Applications Manager is running. Else, give a relevant user who has the privileges to do the same.

Creating a new MySQL monitor

Minimum User Privileges : The user should have privileges to execute SELECT, SHOW DATABASES, REPLICATION commands in the MySQL server. Also, Applications Manager machine should be allowed to access the MySQL database server.

For enabling the privileges, execute the below commands in the remote MySQL Server

INSERT INTO user (Host,User) VALUES('<host>','<user>');
GRANT SELECT,SHOW DATABASES,REPLICATION CLIENT ON *.* TO '<user>'@'<host>';
FLUSH PRIVILEGES;

(Host -> Applications Manager machine)

Prerequisites for monitoring MySQL DB metrics: Click here

Using the REST API to add a new MySQL DB monitor: Click here

To create a MySQL database server Monitor, follow the steps given below:

  1. Click on New Monitor link.
  2. Select MySQL DB Server.
  3. Enter the IP Address or hostname of the host.
  4. Enter the Subnet Mask of the network.
  5. Check the 'Force SSL' box to force an SSL connection.
  6. Enter the port number in which MySQL is running.
  7. Enter the polling interval time in minutes.
  8. If you are adding a new monitor from an Admin Server, select a Managed Server.
  9. Provide the user name and password of user who has permission to access the MySQL database.
  10. 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.
  11. 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.
  12. Click Add Monitor(s). This discovers MySQL database server from the network and starts monitoring them.

Troubleshoot: Having trouble in monitoring MySQL database server? Refer to the online troubleshooting section.

Supported Versions

  • MySQL 3.23.x
  • MySQL 4.x, 4.1.x
  • MySQL 5.x, 5.1.x, 5.5.x, 5.6.x, 5.7.x
  • MySQL 8.x
  • All Versions of MariaDB

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 (This performance data is not available for MySQL versions 3.23.x)
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.

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.
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.

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).

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.