Note: In the MySQL database (that you are trying to monitor), ensure that the user name assigned to the Applications Manager has permission to access the MySQL database from the host where the Applications Manager is running. Otherwise, assign a relevant user name with the privileges to do the same.
Minimum User Privileges : The user should have privileges to execute SELECT, SHOW DATABASES, REPLICATION commands in the MySQL server. Also, to access Applications Manager's MySQL Monitoring feature, machine should be given authorization to the MySQL database.
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;
To create a MySQL database server Monitor, follow the steps given below:
Refer to this page to learn how to add a new MySQL DB monitor using REST API.
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 (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. |
| 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. |
| 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.
