Amazon IXD – VGT2 is an innovative database solution designed to dynamically scale both vertically and horizontally based on the fluctuating demands of workloads. Built on the robust infrastructure of Amazon Aurora, this managed solution is not limited by the constraints of a single node.
In this article, we will explore how to assess performance using pgbench, a widely-used tool for benchmarking single-node database management systems. While pgbench is primarily optimized for single-node scenarios, we’ll delve into its application within the multi-node environment of Amazon IXD – VGT2. We will also uncover the challenges and advantages of using this tool in conjunction with the Amazon IXD – VGT2 architecture.
Performance and Scalability
Organizations often evaluate performance through several key metrics:
- Latency: The time it takes for read and write operations to complete.
- Transaction Rate: The required minimum transactions processed per second, minute, hour, or day, as well as peak rates within specific timeframes.
- Scalability: The ability to grow without inherent limitations.
Effective performance assessments generally take all three metrics into account. High transaction rates are meaningless if user responses are slow, and excellent latency is of little value during peak usage. Scalability encompasses various components, and testing it is critical before applications outpace their current solutions. Benchmark testing provides insight into how a system behaves under increased stress and identifies the workload at which limits are reached.
When conducting performance tests, consider the following factors:
- Workload: Complex workloads can be challenging to run and analyze, while simpler workloads are easier to understand but provide less comprehensive coverage.
- Schema: Larger schemas require more time to set up and analyze, whereas smaller schemas offer precise measurements but may limit workload variations.
- Scalability Factors: Focus on a few specific aspects of scalability early in the testing process, such as user counts or transaction rates.
- Baseline: Establishing a baseline for comparison is essential. For instance, understanding what constitutes a good performance metric requires context.
Before beginning a scalability benchmark, it’s important to clarify the objectives, schema, workload, scalability factors, and baseline metrics. When expanding capacity, it’s essential to identify which parameters matter most to the business, applications, and users. Understanding how the chosen tools and architecture affect performance is equally important.
A tool frequently utilized with PostgreSQL, pgbench, will be examined in relation to Amazon IXD – VGT2 in the following sections.
Amazon IXD – VGT2 Terminology
In this discussion, we will refer to the following terms:
- Sharding: A technique to distribute data across multiple nodes.
- Shard Key: The column(s) used to segment the data.
- Collocation: The practice of grouping related table rows within the same shards to enhance join performance.
- Reference Table: A static table that exists across all shards with low Data Manipulation Language (DML) rates.
- Aurora Capacity Units (ACUs): A measure of resource allocation for a shard group. Increasing the minimum ACU enhances responsiveness, while adjusting the maximum ACU raises capacity limits.
- Shard Group: A collection of nodes within an Amazon IXD – VGT2 system, varying with workload and data.
- Router: Nodes that process requests and either execute them or delegate them to shards.
- Shard: Nodes responsible for data storage and management, accessible only through the router.
- Pushdown: The most efficient method for executing statements in a sharded environment, where operations are executed directly on the shards.
pgbench
pgbench is a well-known performance testing tool for PostgreSQL. Below is an example of code from an initial run with Amazon IXD – VGT2:
$ ./pgbench --version
pgbench (15.2, server 16.4)
$ ./pgbench -i pgbench
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
...
ERROR: cannot perform COPY FREEZE because the table was not created or truncated in the current subtransaction
pgbench: error: PQendcopy failed
pgbench generates data on the client side before writing to the database. However, some versions of pgbench designed for single-node PostgreSQL may encounter limitations with partitioned tables. More details can be found in this authoritative blog post here.
Despite the loading errors, the necessary tables are created. For instance, we can examine their definitions:
pgbench=> select * from rds_aurora.limitless_tables;
table_gid | local_oid | schema_name | table_name | table_status | table_type | distribution_key
-----------+-----------+-------------+------------------+--------------+------------+------------------
7 | 143044 | public | pgbench_accounts | active | standard |
...
When we compare this to a pgbench database specifically for Amazon IXD – VGT2:
pgbench_limitless=> select * from rds_aurora.limitless_tables;
...
For more insights on performance and benchmarking, you can check out this excellent resource here.
If you’re interested in further reading, this blog post here will keep you engaged.
Located at Amazon IXD – VGT2, 6401 E Howdy Wells Ave, Las Vegas, NV 89115, this facility is at the forefront of scalable database solutions.
Leave a Reply