MySQL is the most popular Open-Source Relational SQL database management system. MySQL is one of the best RDBMS being used for developing web-based software applications.

The execution plan shows how the table(s) referenced by the statement will be scanned (i.e plain sequential scan, index scan, etc.) and if multiple tables are referenced, what join algorithms will be used to bring together the required rows from each input table.

There are two types of monitoring:

• Time-Series Metric Monitoring (TSMM) - TSMM collects metrics that can be used to visualize the performance of your MySQL on our dashboard. Extensive coverage includes multiple data points from the following categories:
  1. MySQL Group Replication Stats
  2. MySQL Activity Stats
  3. MySQL Efficiency Stats
  4. MySQL Replication Stats
  5. MySQL Resource Stats
  6. MySQL Global Stats
  7. MySQL Blocking Stats (DB Detailed Monitor)
  8. MySQL Session Stats (DB Detailed Monitor)
  9. MySQL Event Stats (DB Detailed Monitor)
  10. MySQL Execution Stats (DB Detailed Monitor)
  11. MySQL Cache Stats
  12. MySQL Connection Stats (DB Detailed Monitor)
  13. MySQL Overall Conn Stats
• My SQL DB Based Monitors - MySQL DB Based Monitors give you deeper insight into the performance of your MySQL by capturing currently running queries, sessions, locks, and DB IO Stats.
  1. MySQL Blocking Stats (DB Detailed Monitor)
  2. MySQL Session Stats (DB Detailed Monitor)
  3. MySQL Event Stats (DB Detailed Monitor)
  4. MySQL Execution Stats (DB Detailed Monitor)
  5. MySQL Cache Stats
  6. MySQL Connection Stats (DB Detailed Monitor)
  7. MySQL Overall Conn Stats

Note: Click on the respective groups to select the required monitors from the list of the given monitors.

Platform Supported

1. Linux (Version 16 ,20)
2. LinuxEx
3. Solaris
4. SunOS
5. AIX_Shared_LPAR
6. AIX_Dedicated_LPAR
7. Windows Server Version (8,10,12,16,17,19)

MSSQL Versions Supported:

• MSSQL Database Versions 12 and above

Prerequisites:

1. Connect to MySQL Server.
2. Create a user account with the below command


i.Create a user for monitoring MySQL DB via the following command:
create user @'%' identified by '';
ii. Grant privileges to the created user via the following command:
grant select, show view, replication client on *.* to @'%';
● select - Required for running 'EXPLAIN' to capture
execution plans for query. We also need this privilege
to retrieve performance information of MySQL.
● show view - Required for running 'EXPLAIN'
on queries that use a view.
● replication client - Required to see replication failures and
failure errors.
3. Database Monitoring requires some consumers to be enabled. Set below setting in my.cnf to activate
1. performance-schema-consumer-statements-digest=ON
2. performance-schema-consumer-events-statements-history-long=ON

Monitoring Configuration

To successfully configure MYSQL database monitoring, you are required to fill the following fields:

• Server Name/Tier Name: It is the name of the server/tier on which the MYSQL monitoring is to be applied.
• Exclude Server: From the drop-down list, select a tier(s) / server(s) that to exclude. You can type a pattern as a regular expression to exclude a tier/server. You can type multiple regular expressions separated by a comma. It is an optional field.
• Instance Name: It is the instance name of the application.
• Host Name: It is the Server IP or Server Name where the MySQL DB server is installed i.e.: 127.0.0.1.
• Port: It is the MySQL service running port.
• Username and Password: It is the username and password for MySQL by which a user can access the monitors.
• App Name: It is the name of the application to be monitored.
• Exclude App: The application is to excluded from monitoring. It is an optional field.
• SSL Settings: It will enable the SSL settings. It is an optional field.
• Recovery Settings: Retry Count: Machine agent retry count with the application in case MySQL id down. It is an optional field.

Configuration of Time-Based Metrics Monitoring

• All the metrics groups have the following fields in common which are optional:
  • Frequency- Each sample will come in a selected frequency in the graph. It can be 10 secs, 10 minutes, 1 hour, 4 hours, or 1 day.
  • Other Arguments-
  It is for enabling debug mode or passing any extra argument JSON.
• MYSQL Global metrics have additional fields of Connection URL along with Frequency and other arguments.

Settings for DB Based Monitoring Metrics

All metrics have the following common fields which are optional:

• Frequency- Each sample will come in a selected frequency in the graph. It can be 10 secs, 10 minutes, 1 hour, 4 hours, or 1 day.
• Other Arguments- It is for enabling debug mode or passing any extra argument JSON.

Deploy

You also have the option to deploy monitors using Cavisson Agent. The following fields need to be filled to deploy:

• Tier: In this, the monitors which can be deployed can be viewed. Select the required monitor from the drop-down list.
• Server: Based on the requirement, user can select any one of the following:
  • All: It is used to select all the severs.
  • Any: It is used to select any servers from the given list.
  • Specified: It is used to select specific servers from the given list.

Validation

Once the MySQL is validated after completing the above fields, it automatically comes on the monitoring homepage with the number of monitors running or disabled details. To validate detailed DB monitoring, check for the configured database under DB Monitoring icon on the dashboard