Skip to main content

PostgreSQL Workload Profiles

The following profiles run customer-representative or benchmarking scenarios using the postgresql workload.

Client/Server Topology Support​

PostgreSQL workload profiles support running the workload on both a single system as well as in a client/server topology. This means that the workload supports operation on a single system or on 2 distinct systems. The client/server topology is typically used when it is desirable to include a network component in the overall performance evaluation. In a client/server topology, one system operates in the 'Client' role making calls to the system operating in the 'Server' role. The Virtual Client instances running on the client and server systems will synchronize with each other before running the workload. In order to support a client/server topology, an environment layout file MUST be supplied to each instance of the Virtual Client on the command line to describe the IP address/location of other Virtual Client instances. An environment layout file is not required for the single system topology.

In the environment layout file provided to the Virtual Client, define the role of the client system/VM as "Client" and the role of the server system(s)/VM(s) as "Server". The spelling of the roles must be exact. The IP addresses of the systems/VMs must be correct as well. The following example illustrates the idea. The name of the client must match the name of the system or the value of the agent ID passed in on the command line.

# Single System (environment layout not required)
./VirtualClient --profile=PERF-POSTGRESQL-HAMMERDB-TPCC.json --system=Juno --timeout=1440

# Multi-System
# On Client Role System...
./VirtualClient --profile=PERF-POSTGRESQL-HAMMERDB-TPCC.json --system=Juno --timeout=1440 --clientId=Client01 --layoutPath=/any/path/to/layout.json

# On Server Role System...
./VirtualClient --profile=PERF-POSTGRESQL-HAMMERDB-TPCC.json --system=Juno --timeout=1440 --clientId=Server01 --layoutPath=/any/path/to/layout.json

# Example contents of the 'layout.json' file:
{
    "clients": [
        {
            "name": "Client01",
            "role": "Client",
            "ipAddress": "10.1.0.1"
        },
        {
            "name": "Server01",
            "role": "Server",
            "ipAddress": "10.1.0.2"
        }
    ]
}

Configuring the TPCC Scenario​

The TPCC scenario uses HammerDB to create and populate a PostgreSQL database with transactional workload patterns. Database creation and population can only be performed on the Server side.

Important: HammerDB will automatically distribute and populate the database tables across the target disks you specify. Make sure to provide the DiskFilter parameter to point to your target disks for optimal performance.

{
  "Type": "HammerDBExecutor",
  "Parameters": 
  {
    "Scenario": "PopulatePostgreSQLDatabase",
    "Action": "PopulateTables",
    "DatabaseName": "$.Parameters.DatabaseName",
    "DiskFilter": "$.Parameters.DiskFilter",
    "Workload": "tpcc",
    "SQLServer": "postgresql",
    "PackageName": "hammerdb",
    "VirtualUsers": "{calculate({LogicalCoreCount})}",
    "WarehouseCount": "{calculate({SystemMemoryMegabytes} * 15 / 800)}",
    "Port": "$.Parameters.Port",
    "Role": "Server"
  }
},

Configuring the TPCH Scenario​

The TPCH scenario uses HammerDB to create and populate a PostgreSQL database with analytical workload patterns (decision support queries). Like TPCC, database creation and population can only be performed on the Server side.

Important: HammerDB will automatically distribute and populate the database tables across the target disks you specify. Make sure to provide the DiskFilter parameter to point to your target disks for optimal performance.

{
  "Type": "HammerDBExecutor",
  "Parameters": 
  {
    "Scenario": "PopulatePostgreSQLDatabase",
    "DatabaseName": "$.Parameters.DatabaseName",
    "DiskFilter": "$.Parameters.DiskFilter",
    "Workload": "tpch",
    "SQLServer": "postgresql",
    "PackageName": "hammerdb",
    "VirtualUsers": "{calculate({LogicalCoreCount})}",
    "ScaleFactor": "10",
    "Port": "$.Parameters.Port",
    "Duration": "$.Parameters.Duration",
    "Role": "Server"
  }
},

Profile Components​

There are a lot of moving parts to this workload that allows for both out-of-box and configurable scenarios. Here are the key components to be aware of.

Dependencies​

  • FormatDisks and MountDisks: Format any unformatted disks on the server, then mount any unmounted disks.
  • DependencyPackageInstallation: On all VMs, download the hammerdb package; on the server-side, also download the postgresql package.
  • LinuxPackageInstallation: Download the python3 package to execute the python scripts provided for installation and configuration of the packages.
  • PostgreSQLServerInstallation and PostgreSQLServerConfiguration: Runs python scripts to install and configure the PostgreSQL server (ie. set up network, database, variables, and users).
  • ApiServer: Starts the API server for Client-Server workloads.

Actions​

  • HammerDBExecutor: Creates and populates the PostgreSQL database using the HammerDB tool. HammerDB automatically handles the distribution of database tables across the target disks specified by the DiskFilter parameter. The database creation and population must be performed on the Server side. Once populated, VC persists the state, and it will not drop or recreate tables. The workload supports both TPCC (transactional) and TPCH (analytical) scenarios.

  • HammerDBServerExecutor: Manages the server-side execution of HammerDB workloads, handling the database operations and serving client requests.

  • HammerDBClientExecutor: Runs the workload from the client-side, generating load against the server database. This executor connects to the server and executes the specified workload (TPCC or TPCH) scenarios.

  • SysbenchClientExecutor: Runs a given workload from the client-side on the server database. Note that this action can run with different arguments from HammerDBExecutor. VC does not support dropping and recreating a new database or table configuration within the same profile or system.

  • SysbenchServerExecutor: Sets the server online for client interaction.

PERF-POSTGRESQL-HAMMERDB-TPCC.json​

Runs the PostgreSQL workload against the HammerDB tool which generates various transactional traffic patterns against a PostgreSQL server using the TPCC (Transaction Processing Performance Council) benchmark.

PERF-POSTGRESQL-HAMMERDB-TPCH.json​

Runs the PostgreSQL workload against the HammerDB tool which generates analytical workload patterns against a PostgreSQL server using the TPCH (Transaction Processing Performance Council Benchmark H) for decision support scenarios.

  • Workload Profile

  • Supported Platform/Architectures

    • linux-x64

  • Supports Disconnected Scenarios

    • No. Internet connection required.

  • Dependencies
    The dependencies defined in the 'Dependencies' section of the profile itself are required in order to run the workload operations effectively.

    • Internet connection.
    • The IP addresses defined in the environment layout (see above) for the Client and Server systems must be correct.
    • The name of the Client and Server instances defined in the environment layout must match the agent/client IDs supplied on the command line (e.g. --agentId) or must match the name of the system as defined by the operating system itself.

    Additional information on components that exist within the 'Dependencies' section of the profile can be found in the following locations:

  • Profile Parameters
    The following parameters can be optionally supplied on the command line to modify the behaviors of the workload. The System Memory (in Megabytes) is represented by the variable "mem" in the formulaic defaults listed below.

    ParameterPurposeDefault value
    DatabaseNameOptional. Provide the name of the database under test."hammerdb_tpcc" or "hammerdb_tpch"
    PortOptional. Provide the port number that PostgreSQL server will listen on.5432
    DiskFilterOptional. Filter to specify target disks for database distribution."osdisk:false&sizegreaterthan:256g"
    DurationOptional. Duration for the workload execution."00:20:00"
    WorkloadRequired. Either "tpcc" or "tpch"N/A
    SQLServerRequired. Normally "postgresql"N/A
    VirtualUsersOptional. Number of virtual users (threads) for the workload setupvCPU
    WarehouseCountOptional. Number of warehouses created (TPCC only).mem*15/800
    ScaleFactorOptional. Scale factor for TPCH workload.10
    SharedMemoryBufferOptional. PostgreSQL shared memory buffer size.mem*85/100

  • Profile Runtimes
    See the 'Metadata' section of the profile for estimated runtimes. These timings represent the length of time required to run a single round of profile actions. These timings can be used to determine minimum required runtimes for the Virtual Client in order to get results. These are often estimates based on the number of system cores.

  • Usage Examples
    The following section provides a few basic examples of how to use the workload profile. Additional usage examples can be found in the 'Usage Scenarios/Examples' link at the top.

    # When running on a single system (environment layout not required)
    ./VirtualClient --profile=PERF-POSTGRESQL-HAMMERDB-TPCC.json --system=Demo --timeout=250 --packageStore="{BlobConnectionString|SAS Uri}"

    # When running in a client/server environment
    ./VirtualClient --profile=PERF-POSTGRESQL-HAMMERDB-TPCC.json --system=Demo --timeout=1440 --clientId=Client01 --layoutPath="/any/path/to/layout.json" --packageStore="{BlobConnectionString|SAS Uri}"
    ./VirtualClient --profile=PERF-POSTGRESQL-HAMMERDB-TPCC.json --system=Demo --timeout=1440 --clientId=Server01 --layoutPath="/any/path/to/layout.json" --packageStore="{BlobConnectionString|SAS Uri}"