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.