Abstract
This article presents a comprehensive and actionable test plan for evaluating Vertica EON mode deployments, with a focus on migrations from Enterprise Edition. It begins with critical considerations for migration, including depot sizing, subcluster strategy, and tuning recommendations. Detailed sections cover configuring and monitoring the Data Collector, measuring load throughput, and troubleshooting depot performance across a variety of metrics such as pinning, access frequency, eviction rates, and depot-read efficiency. The document also includes practical SQL queries and automation scripts, including a robust bash-based solution for capturing and replaying real-world workloads to identify performance gaps. While Vertica plans to include native workload replay functionality in the future, the script in this article can serve as a draft solution compatible with all recent supported versions, enabling users to simulate and analyze real-world query patterns in both Enterprise and EON environments today.
Acknowledgment
I would like to express my sincere gratitude to Sruthi Anumula - OpenText Vertica Product Support Engineer,
for her invaluable assistance in reviewing this blog article. Her thoughtful comments, constructive feedback,
and improvement guidelines significantly enhanced the quality and clarity of this content.
Thank you, Sruthi, for your generous support and contribution to this work.
All SQL queries examples, bash scripts are included in the attached PDF file.
The full workload capture and replay script is available also in GitHub:
👉 https://github.com/mogomo/vertica_workload_replay
Vertica subclusters use case examples:
Included in the PDF Attachment
- Vertica EON Catalog Distribution and Synchronization: Explains how catalog objects are managed across nodes and subclusters, with emphasis on Global Catalog Lock behavior and synchronization frequency.
- Subclusters Use Cases: Describes scenarios for workload isolation and safe cluster scale-down without entering read-only mode.
- Shard Configuration Guidelines: Covers best practices on shard-to-node ratios, shard limits, performance impact, and how to balance scalability vs. overhead.
- Depot Sizing and Tuning: Provides formulas, recommendations, and examples to determine optimal depot size. Also addresses ephemeral storage, partitioning, and performance implications.
- MIGRATE_ENTERPRISE_TO_EON Tests: Offers a checklist and considerations for in-place migrations, hardware mismatches, and post-migration tuning of subclusters and resource pools.
- Managing the Data Collector: Details how to enable/disable the Data Collector, clear memory/disk usage, track storage, and configure retention policies for various components.
- Workload Capture and Replay: Describes the included bash script for collecting successful queries and generating a replay script that compares original vs. replayed metrics (duration, output rows).
- Loading Tests: Provides SQL queries to evaluate load throughput, sort/parse percent, and track streaming activity over time using
load_streams. - Depot Monitoring and Troubleshooting:
- Depot vs. Communal read ratios
- Depot evictions and re-fetch analysis
- Pinned vs. unpinned table byte count and age distribution
- Depot access count heatmaps
- Depot content size groupings
- Depot Query Hints and Configuration Parameters: Explains advanced hints like
/*+DEPOT_FETCH(NONE)*/and configuration settings such asUseDepotForReads,UsePeerToPeerDataTransfer, andAWSStreamingConnectionPercentage. - Monitoring Communal Storage: Shows how to detect bucket leaks, confirm cleanup, and run
clean_communal_storage(true)safely. - Workload Analyzer Overview: Provides SQL to use Vertica's built-in analyzer for recommending tuning actions across observed query patterns.
For further details, sample output, and the full workload capture/replay script, please refer to the attached PDF
file: Comprehensive Vertica EON Migration Test Plan.
Useful Links:
