Workload Analysis
This page is part of MariaDB's SkySQL Documentation.
The parent of this page is: SkySQL Interfaces
Topics on this page:
Overview
Capacity planning, troubleshooting and problem analysis, optimization, and proactive quality assurance activities all depend on an ability to understand typical workload patterns.
Available as a Technical Preview on MariaDB SkySQL, Workload Analysis applies thousands of observations per week to a deep learning model to identify discrete workload patterns.
With Workload Analysis, interactive visualizations provide graphical representation of distinct workloads, their key metrics, and transitions between workloads as they change over time.
Workload patterns are often cyclical and may shift based on application traffic, workload variations, high I/O operations such as backups and batch processing, application defects, time-of-day and day-of-week, and seasonality. Understanding the typical workload patterns enables you to identify the types of atypical workloads that commonly trigger acute service problems.
Workload Analysis cuts through the complexity of understanding these variations while also reducing the impact of human bias and making the analysis process repeatable.
Note
Workload Analysis is currently a Technical Preview.
Available Demo
A demonstration of Workload Analysis is immediately available with pre-learned data.
Enabling Workload Analysis
The process of enabling Workload Analysis and modeling your workloads can take several weeks.
Prerequisites
As a Technical Preview, Workload Analysis currently has several requirements:
Since Workload Analysis is a time-consuming process, you should review the Workload Analysis demo to confirm that this feature is right for you.
Workload Analysis is currently available for Single Node Transactions and Replicated Transactions topologies on GCP.
Database services must remain Running, since several weeks are required to learn your workload, and a database cannot be stopped or deleted.
Real workload patterns must be present to produce meaningful results.
Enable and Access
If all prerequisites are met:
Log in to MariaDB SkySQL.
Allowlist your IP for access to SkySQL Monitoring and SkySQL Workload Analysis.
From the "Services" page ("Your Services" link in the main menu), click on the desired service to view the Service Details page.
Use the "Request Workload Analysis" link. Workload Analysis is available for Single Node Transactions and Replicated Transactions topologies on GCP. The process of enabling Workload Analysis and modeling your database workloads will take several weeks.
Once you receive notification that your Workload Analysis is ready, return to MariaDB SkySQL and click on the "Workload Analysis" link in the SkySQL main menu (left navigation) to access this feature.
Features and Interfaces
Workload Analysis is in Technical Preview. Interfaces are subject to change.
Workload Analysis must be enabled and your service must undergo workload modeling before the analysis can be accessed.
A demo is immediately available which contains pre-learned data.
Analysis List
Workload Analysis is available once enabled and after several weeks of workload modeling. Upon selection of an available database service or data set, you will be provided a tabbed interface to access various data products related to the dataset:
Overview Tab
Charts in the Overview tab are overlaid with a metric to show how it changes with workload patterns. Available metrics include:
Metric | Description |
|---|---|
QRT | Query Response Time, or the amount of time queries take to execute. |
TPS | Number of transactions processed per second. |
QPS | Number of queries processed per second. |
Innodb_data_read | Number of bytes read by the InnoDB storage engine. |
Innodb_data_write | Number of bytes written by the InnoDB storage engine. |
Metrics Tab
Workload Analysis tracks the MariaDB Enterprise Server status variable values over time to build deep insights into how current conditions compare to historical conditions.
The Metrics tab uses colored bands to represent workload patterns, with sparklines (graphs) to show metrics. Mouse-over provides specific values. Time period is selected by calendar.
Default view sorts charts alphabetically by metric name.
Bundles view categorizes metrics:
Query Throughput
Query Performance
Resources
Connections
InnoDB Storage Engine
Caching
Correlated view statistically groups metrics, allowing you to investigate underlying relationships between status variables.
Custom view allows you to select specific metrics by name.
Similarity Tab
Similar workloads have similar characteristics, such as the number of connections or the number of inserts. Optimizations for one distinct workload may result in optimizing for similar workloads. Workloads that become dissimilar may indicate that they are on the verge of changing, providing you with the opportunity to optimize for workload changes before they occur.
The Similarity tab shows how workload patterns compare to each other using colored bands. Mouse-over provides specific values, including percentage of similarity. Time period is selected by calendar.
The Show Similarity Matrix switch shows workload patterns numbered on the x and y axes and a matrix with percentages of similarity.
Percentiles Tab
Distribution of values within a metric can provide insights into how a particular feature performs under certain workloads.
The Percentiles tab shows the percentile distribution of metric values for all distinct workloads in the selected period, allowing you to easily evaluate a metric within a single workload and to compare it to other workloads. Mouse-over provides specific values. Time period is selected by calendar.
A chart is included for each of the 100+ status variables used as key metrics, with colored bars for each distinct workload. The size of the bar relates the minimum and maximum values the metric holds for the workload. The bar is divided into sections, each representing a distinct value held by the metric. A white dot indicates the average for the period.
Spread Tab
Observing the range of values recorded for a particular status variable can provide insights to optimization.
The Spread tab shows the variation between workloads in a range, allowing you to visually identify which workloads cluster near minimum, maximum, and mean values.
The Spread tab displays charts for each status variable used as a key metric. Each chart shows a dot for a value recorded for the given status variable within the selected week. The dots have the same color as the workload in which they occurred. Hovering the mouse over a dot shows the workload in which it occurs and provides the specific value.
Similarity is indicated by the size of a range of metrics, with closer metrics having more similarity. If the range is large, there may be an opportunity to proactively mitigate issues by increasing resource limits. If the range is small, there may be an opportunity to reallocate resources for more efficient utilization.
Feature Comparison Tab
Exploring the relationships between metrics in different workloads can provide you with insights in optimizing your database service.
The Feature Comparison tab allows selection of two status variables from the 100+ key metrics and two workloads, then plots how the metrics correlate in the given workloads.
Feature Comparison represents workloads using colored bands. Mouse-over provides specific values. Time period is selected by calendar.
Charts include:
Scatter plot
Histogram
Density heat map
Key Features Tab
Workload Analysis identifies workloads using 100+ status variables identified as key metrics. Distinct values or changes in these key metrics are used in identifying workload patterns. You may find this useful in understanding irregularities, troubleshooting issues, and in anticipating changes.
The Key Features tab provides listings for each workload pattern with up to ten key metrics that distinguish the workload from others. A colored wheel is sectioned to represent each workload pattern. Clicking on a workload displays key metrics for that workload and metrics charts. Time period is selected by calendar.