Workload Fingerprint

Overview

The Workload Fingerprint feature in DBtune provides intelligent query-level insights to help users identify, prioritize, and tune performance-critical SQL workloads. It groups related SQL statements and defines a stable, comparable way to measure their performance in variable environments.

Workload Fingerprint automatically analyzes queries executed in the PostgreSQL database and groups them based on average query runtime (AQR) and how frequently each query runs. Using this data, DBtune automatically recommends queries that should be reviewed or used for tuning on specific query sets.

You can either:

1. Use DBtune's automatic recommendations, or

2. Create and select their own custom fingerprints, custom set of queries.

What happens when a fingerprint is selected?

When you run a tuning session against a fingerprint, DBtune evaluates candidate configuration changes and can adjust database tuning parameters appropriate for the chosen workload. Example parameters include:

• shared_buffers controls memory for caching data pages.

• work_mem for per-operation memory for sorts/hash operations.

• random_page_cost and effective_io_concurrency control I/O planner and costs/parallel I/O tuning.

• maintenance_work_mem, max_parallel_workers_per_gather, and other version-specific parameters.

The exact parameters DBtune proposes depend on the workload characteristics, PostgreSQL version, and observed behavior. The selection heuristics and ranking may evolve over time.

Key benefits

1. Automatically identifies high-impact queries based on query runtime and call count.

2. Recommendeds fingerprints for review and tuning.

3. Allows users to create custom fingerprints to target specific workloads such as OLTP, OLAP, or mixed.

4. Both recommended and custom fingerprints integrate seamlessly with DBtune tuning sessions, allowing users to observe the performance impact of configuration changes in near real time. Improvements are not guaranteed and depend on the workload and system bottlenecks.

How to use fingerprints in the dashboard

Option 1: Use recommended fingerprints

Step 1:

Navigate to the "Tuning" tab in the DBtune dashboard. Click on the "Recommend" button:

Step 2:

Select recommended queries by a time range.

Step 3:

Here, DBtune will automatically:

1. Analyze the recent workload.

2. Select queries that are most relevant based on AQR and call count.

You can observe the following columns, Query, AQR (average query runtime), Calls (number of times the query runs), Total duration (AQR x Calls), Runtime coverage (% of total workload runtime contributed by the specific query).

Step 4:

Review the recommended queries and click "Start tuning" to start the tuning session.

Step 5:

Using the recommended fingerprint as a performance benchmark, DBtune begins to test and optimize relevant tuning parameters for memory, I/O, parallelism, and others.

As the tuning session runs, DBtune tests multiple configurations and plots their performance against the baseline. The goal is to find the configuration that results in the lowest AQR for recommended fingerprint, although improvements are not guaranteed for every workload.

Step 6:

After a tuning session completes, as indicated by the green Finished status, DBtune highlights the best-performing configuration it discovered. You can then review the detailed performance measurements by hovering over the 'best' configuration.

Option 2: Create a custom fingerprint

Step 1:

Go to the Fingerprints tab in the dashboard and click the "Create" button:

Step 2:

Select the queries you want to include, give your fingerprint a meaningful name, and click "Create."

Step 3:

Navigate back to the "Tuning" tab. Select your custom fingerprint from the list as shown below. Click "Start tuning" to start the tuning session.

Step 4:

DBtune will now begin a tuning session using your custom fingerprint as the performance benchmark, following the same iterative process used for recommended fingerprints.

Step 5:

Once the optimal configuration is applied, you can verify its impact by observing the AQR metrics for your fingerprint during subsequent workloads.