Overview

The article covers the following topics:

  • common use cases where CloudMonix can help with monitoring and automation

  • what happens in the monitoring cycle

  • what is needed to connect to and monitor MySQL databases

  • what metrics can be tracked, visualized and monitored

  • what automated actions can be executed by CloudMonix


Why use CloudMonix for MySQL databases?

Popular usages of CloudMonix include the following examples:

  • Monitoring Uptime and Availability

  • Tracking the number and statuses of connections, sessions and requests

  • Tracking the longest running queries, and overall utilization

  • Executing custom SQL scripts on schedule


Monitoring Cycle

In every monitoring cycle, CloudMonix will attempt to connect to monitored database and retrieve data behind configured metrics. Most of the metrics are retrieved by executing SHOW GLOBAL STATUS command. CloudMonix also will also attempt to open a new connection in every cycle to measure Response Time (if that metric is enabled).


After each monitored cycle is completed, CloudMonix will evaluate and possibly execute any automated actions and auto-scaling operations.


Configuration 

MySQL database monitoring can be configured using the “Add New” button in the dashboard. In order for CloudMonix to connect to and monitor MySQL databases, it needs username/password credentials for each database, as well as server name, port number and database name.


Username & Password

Most of the metrics are retrieved by executing SHOW GLOBAL STATUS command, some are retrieved from the x$statements_with_runtimes_in_95th_percentile view, performance_schema  and information_schema tables. Ensure that the provided credentials have appropriate permissions.


Firewall

CloudMonix monitoring services are hosted in Azure and utilize static IPs to perform monitoring. Ensure that addressed specified in this article are white-listed.


Metrics

Every diagnostic data point that CloudMonix retrieves from the monitored resource is considered a metric in CloudMonix. Refer to the Metrics article to learn more about metrics in general.


CloudMonix retrieves most of the metrics  by executing SHOW GLOBAL STATUS command. Some metrics are captured by directly querying the databases.


CloudMonix provides a default template for monitoring MySQL databases.


The metrics can be added, removed and customized in the Metrics tab in the resource configuration dialog.


Built-in Metrics

ResourceStatus

Identifies the last state of the monitored resource. This is a critical metric that is captured for most types of resources that CloudMonix tracks. It is used for Uptime reports and should not be removed. 

  • Data Type: string

  • Possible values: Ready, Down, Unknown

  • Included in sample profile: no

  • Included in default alerts: no

Statuses are determined according to the following rules:

  • Ready - successfully connected to the resource and retrieved metrics

  • Down - there was an error when trying to open a connection

  • Unknown - can’t connect to the resource (e.g. because of invalid credentials) or can’t capture metrics values


ResponseTime

Tracks resource's response time by measuring how long it took to open an active connection to MySQL database in milliseconds.

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called ResponseTime

  • Included in default alerts: yes, in an alert Slow Response (Warning): Raises an alert if establishing a connection to monitored resource takes over 15 seconds for sustained period of time


DatabaseActiveSessionCount

Tracks the number of open connections to the database. Retrieved by running SHOW STATUS command (Threads_connected).

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called ConnectionsActive

  • Included in default alerts: yes, in an alert Open Sessions Approaching Max Limit (Warning): Number of open connections (sesssions) is approaching maximum limit. The alert must be explicitly enabled.


DatabaseMySqlQueries

Tracks the number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored procedures. Retrieved by running SHOW STATUS command (Questions).

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called QueriesCount

  • Included in default alerts: no


DatabaseMySqlQueryErrors

Tracks count of queries executed with error. Retrieved from performance_schema.events_statements_summary_by_digest table, calculated as a sum of the sum_errors.

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called ErrorsCount

  • Included in default alerts: no


DatabaseMySqlQueryRunTime

Average run time for queries in microseconds. Retrieved from performance_schema.events_statements_summary_by_digest table, calculated as ratio of sum_timer_wait sum to count_star sum, divided by 1’000’000. Available for MySQL versions 5.6.6 or higher.

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called QueryRunTime

  • Included in default alerts: no


DatabaseMySqlReads

Tracks the number of SELECT statements. Retrieved by running SHOW STATUS command (Com_select).

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called ReadsCount

  • Included in default alerts: no


DatabaseMySqlWrites

Tracks the number of INSERT, UPDATE and DELETE statements. Retrieved by running SHOW STATUS command (sum of Com_insert, Com_update, Com_delete values).

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called WritesCount

  • Included in default alerts: no


DatabaseMySqlTopQueries

Tracks top 10 longest running queries. Retrieved from x$statements_with_runtimes_in_95th_percentile view. Query text display is limited to 2000 characters.

  • Data Type: array of objects with the following properties:

    • ExecutionCount (ulong): The total number of times the statement has executed.

    • ErrorCount (ulong): The total number of errors produced by occurrences of the statement.

    • WarningCount (ulong): The total number of warnings produced by occurrences of the statement.

    • TotalLatency (decimal): The total wait time of timed occurrences of the statement, in seconds.

    • MaxLatency (decimal): The maximum single wait time of timed occurrences of the statement, in seconds.

    • AverageLatency (decimal): The average wait time per timed occurrence of the statement, in seconds.

    • RowsSent (ulong): The total number of rows returned by occurrences of the statement.

    • RowsSentAverage (decimal): The average number of rows returned per occurrence of the statement.

    • Text (string): The normalized statement string.

  • Can be accessed only through aggregation using Expressions, see Evaluating data in sets\arrays (advanced) section to learn more.

  • Included in sample profile: yes, tracked as a metric called TopQueries

  • Included in default alerts: no


DatabaseMySqlUtilization

Tracks various system statistics. 

  • Data Type: double

  • Requires selecting Performance Group, e.g. “Aborted”, “Connections”, “Performance” and Performance Variable, e.g. “Performance_schema_rwlock_classes_lost”

  • Included in sample profile: no

  • Included in default alerts: no


DatabaseSize

Tracks the size of the database in megabytes. Retrieved from information_schema.tables table, calculated as a sum of Data_length and Index_length .

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called Size

  • Included in default alerts: yes, in an alert Database Approaching Max Size (Warning): Fires an alert when the size of the database approaches maximum limit. The alert must be explicitly enabled.


DatabaseSqlNumeric

Captures numeric value from custom user-defined SQL statement. This metric is very useful to define alerts based on user-owned data. It is important the user-defined SQL statement can execute quickly. 


Example:

SELECT COUNT(*) FROM Customers

 

The statement should return a single scalar numeric value. Queries returning NULLs, non-numeric data, multiple rows, and etc. will not work properly.


Note: sometimes data from this metric needs to be evaluated side by side with data in other resources. For example, current count of customers may be used as a factor in scaling actions for Azure Web Roles. In such a case, use LinkedMetric to import data from MySQL database metrics into other resources.

  • Data Type: double

  • Included in sample profile: no

  • Included in default alerts: no


DatabaseSqlQuery

Captures a table-based collection of data retrieved via user-defined SQL statement. This metric is very useful to define alerts based on user-owned data.


Note: It is important the user-defined SQL statement can execute quickly. It is also important that the size of the result set is reasonable (no more than a few hundred rows and within 20-30kb). Only one result set can be returned. Furthermore, ensure that column names of the result set do not contain spaces, start with numeric characters, and contain non-alphanumeric characters. Column names should also not duplicate. Column names of the resulting dataset will be converted to C# object properties before being visualized in the dashboard.

  • Data Type: array of objects (rows with properties as defined by the SQL statement)

  • Can be accessed only through aggregation using Expressions, see Evaluating data in sets\arrays (advanced) section to learn more.

  • Included in sample profile: no

  • Included in default alerts: no


Alerts

Users can create alerts based on changes in any value tracked by CloudMonix (including custom metrics). Each resource template includes alerts which are suitable for a given resource. The predefined alerts for MySQL databases are listed in the Metrics section. Refer to the Alerts article to learn more about alerts in general.


Alerts are available during the Trial period or in Professional and Ultimate plans only.


Actions and Automation 


Automation features (Actions) allow users to set up powerful reactive, proactive and scheduled actions and auto-scaling rules. CloudMonix can execute actions and scale adjustments when a specific monitoring condition occurs or according to a schedule. Refer to the Actions article to learn more.


Automation features are available during the Trial period or in the Ultimate plan only.


Built-in Actions 

CustomSqlDatabaseScript

CloudMonix will execute on the target MySQL database the custom SQL script specified in the action definition. That action is especially useful when used in combination with other CloudMonix features, such as metrics or schedules.