Skip to main content

Usage

Standalone Mode​

Leave SC_DB_HOST empty to automatically start a local PostgreSQLPostgreSQLAn open-source object-relational database system, developed by a global community and descended from the POSTGRES project at UC Berkeley in 1986. It uses and extends SQL, with transactions, many index types, extensions, and JSON data.PostgreSQL docsGlossary server inside the Docker container:

docker run --rm ghcr.io/sparecores/benchmark-pgbench-postgres:main

Note: Docker --memory, --cpus, and --cpuset-cpus limits do not change the tuning. With --memory, PostgreSQL is still sized for the host's memory and can be killed for running out of memory; with --cpus or --cpuset-cpus, set SC_DB_VCPUS to match.

The harnessharnessThe code that runs a benchmark tool. It prepares the inputs, starts the tool with fixed settings, often several times or in parallel, checks and collects the tool's output, and prints the results in one format. Its settings, not the tool's defaults, decide what the benchmark measures.WikipediaGlossary tunes the local server at startup, based on the identified vCPUvCPU: virtual CPUA CPU of a virtual machine, the unit in which cloud vendors sell compute. It is usually one hardware thread of a physical core with SMT, so two vCPUs may share one physical core.WikipediaGlossary count and system memory size, using the following algorithm, implemented in pgtune_leopard.py:

  • Take the host system memory (Docker --memory limits are ignored), M, from MemTotal in /proc/meminfo, rounded down to whole GiB (at least 1).

  • Take the vCPU count, C, from the optional SC_DB_VCPUS environment variable, which defaults to the host's logical CPUCPU: Central Processing UnitThe general-purpose processor of a computer, which runs the operating system and programs. A CPU has one or more physical cores, and with SMT each core runs two or more hardware threads.WikipediaGlossary count, even when Docker limits the container's CPUs.

  • Generate PostgreSQL settings as pgtune does with its form defaults: PostgreSQL 18, Linux, web application, SSDSSD: Solid-State DriveA storage device that keeps data in flash memory instead of on spinning disks, so it reads and writes much faster than an HDD, especially at random locations. A controller with its own firmware maps logical blocks to flash pages, and many SSDs write incoming data to a fast cache first, then move it to slower flash. Cloud servers use SSDs as local disks and behind network block storage.WikipediaGlossary storage, database size mid-RAM, and an automatic connection count.

  • Set shared_buffers to M/4 and effective_cache_size to 3M/4.

  • Set huge_pages to try when shared_buffers is at least 2 GB, otherwise off.

  • Set maintenance_work_mem to M/16, capped at 8 GB. Set autovacuum_work_mem to 2 GB when maintenance_work_mem reaches 2 GB.

  • Set work_mem to (M - shared_buffers) / (3 × (200 + W)), with a 4 MB minimum, where W is C when C ≥ 4 and 1 otherwise.

  • Set wal_buffers to 3% of shared_buffers, with a 32 kB minimum and a 16 MB cap; values between 14 and 16 MB are rounded up to 16 MB.

  • Use fixed values for the following settings:

    • min_wal_size: 1 GB
    • max_wal_size: 4 GB
    • checkpoint_completion_target: 0.9
    • default_statistics_target: 100
    • random_page_cost: 1.1
    • effective_io_concurrency: 200
    • jit: off
    • wal_compression: lz4lz4A lossless compression algorithm and format by Yann Collet from the LZ77 family, built for speed rather than compression ratio. It compresses and especially decompresses much faster than gzip, at a lower ratio; its higher levels use a slower high-compression mode that keeps decompression fast.LZ4Glossary
    • io_method: io_uring
  • When C ≥ 4, set max_worker_processes and max_parallel_workers to C, and max_parallel_workers_per_gather and max_parallel_maintenance_workers to C/2 rounded up, capped at 4.

  • Set autovacuum_max_workers to 4 when C ≥ 16 and to 5 when C ≥ 32; otherwise, keep the PostgreSQL default.

  • Override pgtunepgtuneA web tool that suggests PostgreSQL settings, such as shared_buffers and work_mem, from the memory, CPU count, storage type and workload of a server. Its suggestions are a starting point for tuning rather than an optimum.pgtuneGlossary's max_connections of 200 with 3,122 (the highest client count, 3,072, plus 50 reserved connections). The work_mem setting is still sized for 200 connections.

  • Set synchronous_commit to off when SC_DURABILITY=async, and on otherwise.

The output records the generated settings in postgres.requested_gucs and a pgtune form link in pgtune_share_url. The link shows pgtune's default of 200 connections, not the 3,122 override. The pgbench_ro workload applies further workload settings on top of these.

Remote Mode​

Set SC_DB_HOST to benchmark an existing PostgreSQL 18 server, such as a DBaaSDBaaS: Database as a ServiceA cloud service model in which the vendor runs a database for its users, handling provisioning, backups, patching and replication. Examples are Amazon RDS and Google Cloud SQL.WikipediaGlossary (Database as a Service) instance, from the same or a separate machine. Before running, check the following prerequisites:

  • The SC_DB_USER role can create databases (CREATEDB) and terminate other sessions on the benchmark database: it must be a superuser, a member of pg_signal_backend, or the role those sessions run as.
  • The benchmark database, SC_PGBENCH_DB (default pgbenchpgbenchThe benchmark tool shipped with PostgreSQL. It runs a SQL script repeatedly over many concurrent client sessions, and reports the throughput in transactions per second and the latency. Its built-in script is modeled on TPC-B, and custom scripts can define any other workload.PostgreSQL docsGlossary), holds no data that is needed. Restoring a cached dump drops and recreates it; building the dataset drops and recreates its benchmark tables.
  • The SC_DB_USER role owns the benchmark database or is a superuser, so it can set the pgbench_ro workload settings.
  • The client can reach SC_CDN_BASE_URL over HTTPSHTTPS: Hypertext Transfer Protocol SecureHTTP, the protocol of the web, encrypted with TLS. It protects requests and responses from eavesdropping and tampering, and its certificates let clients verify the identity of the server.WikipediaGlossary. The image restores a cached dataset dump from there and builds the dataset itself only when no dump exists for it; the run fails if the CDNCDN: Content Delivery NetworkA network of distributed servers that cache content, such as web pages, images or video, and serve it from locations close to its users. It lowers latency for the users and offloads traffic from the origin server.WikipediaGlossary (Content Delivery Network) is unreachable.
  • SC_DB_VCPUS is set to the database server's vCPU count. Without it, the image uses the logical CPU count of the machine it runs on, so on a separate client machine it picks concurrency pointsconcurrency pointsThe client counts at which a database benchmark measures throughput and latency, each with that number of connections open for a fixed time. Measuring several concurrency points, from one client up to a count derived from the server's size, shows how throughput and latency change with load.Glossary for the wrong server size.

Set the host, credentials, and server size:

docker run --rm \
-e SC_DB_HOST=<postgres-host> \
-e SC_DB_USER=<username> \
-e SC_DB_PASSWORD=<password> \
-e SC_DB_VCPUS=<database-vcpus> \
-e SC_DB_MEM_GIB=<database-memory-gib> \
-e SC_WORKLOAD=pgbench_ro \
ghcr.io/sparecores/benchmark-pgbench-postgres:main

SC_DB_MEM_GIB is only recorded in the output; for a remote server it is null unless it is set.

The image does not tune a remote server's configuration; the only settings it changes are the workload settings on the benchmark database.

Workload Settings​

In both modes, pgbench_ro sets the following defaults (benchmark.py) on its benchmark database with ALTER DATABASE ... SET, after building or restoring the dataset:

  • jit: off
  • work_mem: 64MB
  • max_parallel_workers_per_gather: 0

They apply to every pgbench session and override the server-level values, including the standalone pgtunepgtuneA web tool that suggests PostgreSQL settings, such as shared_buffers and work_mem, from the memory, CPU count, storage type and workload of a server. Its suggestions are a starting point for tuning rather than an optimum.pgtuneGlossary settings. The pgbench_tpcb workload changes no settings.

Run Duration​

A run prepares the dataset, then measures throughput at a series of concurrency pointsconcurrency pointsThe client counts at which a database benchmark measures throughput and latency, each with that number of connections open for a fixed time. Measuring several concurrency points, from one client up to a count derived from the server's size, shows how throughput and latency change with load.Glossary in the following steps:

  1. Restore the dataset from a cached dump on the CDN, or build it if no dump exists for it.
  2. Warm up once for SC_WARMUP_SECONDS (default: 120s) at the first concurrency point. Before each later point, run a settle period of SC_SETTLE_SECONDS (default: 60s) at that point's client count. Neither is measured. With SC_WARMUP_ONCE=false, every point gets the full warmup.
  3. Measure each concurrency point for SC_RUN_SECONDS (default: 300s), sampling 1% of transaction latencies.
  4. Report the highest TPMTPM: Transactions Per MinuteA throughput measure of transaction processing systems: the number of transactions they complete per minute. TPC-C reports a variant of it, tpmC, which counts only new-order transactions, while tools such as pgbench report transactions per second (TPS), which is 1/60 of TPM.WikipediaGlossary (Transactions Per Minute) of all concurrency points (and scale factors) as the score.

For pgbench_ro, benchmark.py sets the concurrency points to {1, V/2, V, 2·V}, where V is SC_DB_VCPUS and V/2 is rounded down. Duplicates collapse on small servers, so the default duration depends on V as follows, excluding dataset preparation:

VConcurrency pointsDuration
11, 213 minutes (120 + 60 + 2 × 300s)
21, 2, 419 minutes (120 + 2 × 60 + 3 × 300s)
31, 3, 619 minutes (120 + 2 × 60 + 3 × 300s)
4 or more1, V/2, V, 2·V25 minutes (120 + 3 × 60 + 4 × 300s)

For example, a production run on an AWSAWS: Amazon Web ServicesThe cloud computing platform of Amazon, launched in 2006 and the largest public cloud by market share. It offers hundreds of services, from virtual machines (EC2) and object storage (S3) to managed databases and machine learning.AWSGlossary (Amazon Web Services) m9g.24xlarge server with 96 vCPUs measured 1, 48, 96, and 192 clients, with 25 minutes of warmup and measurement (recorded output). Preparing the dataset and starting the server add to the total run time.

The following settings also change the number of concurrency points, and so the duration:

  • SC_PROFILE_VUS replaces the default concurrency points for pgbench_ro.
  • Points above the server's max_connections minus 50 are dropped, which can shorten runs against a remote server with a low connection limit.
  • pgbench_tpcb prepares and measures each scale factor in SC_SCALEFACTORS in turn, with a settle period before every point after the first warmup.

Each pgbench call times out after its duration plus 600 seconds. Each dataset build or restore command times out after 4 hours; the optional dump upload has no timeout.

Why 5 Minutes​

This is deliberately not a long-running benchmark. We tested 5-, 10-, 15-, and 30-minute measurement windows with five interleaved trials each, using BenchBaseBenchBaseA framework that benchmarks SQL databases, the successor to OLTPBench. It runs workloads such as YCSB and a Wikipedia workload against any relational database with a JDBC (Java Database Connectivity) driver, and collects per-transaction-type latency and throughput logs.GitHubGlossary's Wikipedia workload on three GCPGCP: Google Cloud PlatformThe cloud computing platform of Google. It offers virtual machines (Compute Engine), object storage, managed databases, Kubernetes and machine learning services, among others.Google CloudGlossary (Google Cloud Platform) server types and pgbench -S on a fourth.

Longer windows moved mean throughput by less than 2% and did not reduce run-to-run variation, which comes from load, OSOS: operating systemThe software that manages a computer's hardware and runs programs on it, such as Linux or Windows. A cloud server runs the OS of the image it was started from.Glossary, and noisy-neighbor effects rather than from too short an average. See Measurement duration for the results.

As a result, each score is a single 5-minute sample. Repeated runs on the same server type varied by a CVCV: Coefficient of VariationThe standard deviation of a set of measurements divided by their mean, often given as a percentage. As it is scale-free, it compares the variability of measurements of different magnitudes, such as how much repeated runs of different benchmarks vary.WikipediaGlossary (Coefficient of Variation) of about 0.5-4% in these tests, so treat smaller differences between server types as noise. These figures come from runs of BenchBase's Wikipedia workload and pgbench -S, not from pgbench_ro.

Production Setup​

The Inspector of Spare Cores Navigator runs this image with the following setup. A plain docker run differs from it as noted below.

Container Settings​

Production runs apply no sysctl or other host OSOS: operating systemThe software that manages a computer's hardware and runs programs on it, such as Linux or Windows. A cloud server runs the OS of the image it was started from.Glossary tweaks. The container runs privileged with the following settings:

  • host networking
  • seccomp=unconfined
  • high nofile and unlimited memlock ulimits (unlocking huge pages and io_uring)

In standalone mode, the harness itself starts postgres under nice -n -20niceA Unix command that runs a program with a changed scheduling priority, from -20 (highest) to 19 (lowest). Raising a process's priority above the default needs the CAP_SYS_NICE capability, which root has.man pageGlossary. The higher priority takes effect only when the container has CAP_SYS_NICE, as under --privileged; with a plain docker run, the server runs at normal priority.

Topology​

IaaSIaaS: Infrastructure as a ServiceA cloud service model that rents out compute, storage and network resources, such as virtual machines and disks, on demand. The vendor runs the hardware, and the user manages the operating system and everything above it.WikipediaGlossary (Infrastructure as a Service) runs place the client and database on the same node. DBaaS runs use a separate client VMVM: virtual machineA computer emulated in software on a physical host, with its own OS and a share of the host's CPUs, memory, and storage. Most cloud servers are VMs.WikipediaGlossary that always reaches the database over private VPCVPC: Virtual Private CloudAn isolated virtual network within a public cloud account, with its own IP address ranges, subnets, routing and firewall rules. Resources such as virtual machines and databases are placed in it to control how they reach each other and the internet.WikipediaGlossary (Virtual Private Cloud) addresses. On AWS, both are in the same availability zone; other vendors may place them in different zones of the same region.

Environment Variables​

VariableMeaningDefault
SC_WORKLOADWorkload: pgbench_ro or pgbench_tpcb.pgbench_ro
SC_DB_HOSTRemote database host; empty starts local PostgreSQL.Empty
SC_DB_PORTDatabase port. Remote mode only: leave it unset in standalone mode, which always uses 5432.5432
SC_DB_USERDatabase role. Remote mode only: leave it unset in standalone mode, which always uses postgres.postgres
SC_DB_PASSWORDDatabase password.postgres
SC_DB_NAMEAdmin database used for setup and settings queries.postgres
SC_PGBENCH_DBDatabase used by this benchmark; its contents are dropped and recreated.pgbench
SC_DB_SSLMODESSLSSL: Secure Sockets LayerA protocol that encrypts network connections and verifies the identity of the server, and optionally of the client, with certificates. TLS has replaced it, but SSL is still the common name for both, including in PostgreSQL's settings.PostgreSQL docsGlossary (Secure Sockets Layer) mode for the dataset restore, pg_dump, and the connection that drops and recreates the benchmark database. The pgbench tool and the setup queries do not use it.prefer
SC_CPU_SCALEpgbench_ro transaction work multiplier.1
SC_SCALEFACTORSComma-separated pgbench_tpcb scale factors.Unset
SC_SCALEFACTORpgbench_tpcb scale factor when SC_SCALEFACTORS is unset or empty.65
SC_PROFILE_VUSComma-separated pgbench_ro concurrency pointsconcurrency pointsThe client counts at which a database benchmark measures throughput and latency, each with that number of connections open for a fixed time. Measuring several concurrency points, from one client up to a count derived from the server's size, shows how throughput and latency change with load.Glossary. For pgbench_tpcb, it only sets the default SC_PROFILE_MAX_CLIENTS.Derived from database vCPUs
SC_PROFILE_SEARCHAllow adaptive concurrency search for pgbench_tpcb; forced off for pgbench_ro. At the default scale factor, it takes effect only when SC_PROFILE_MAX_CLIENTS is above the highest anchor.True for pgbench_tpcb; false for pgbench_ro
SC_PROFILE_IMPROVE_PCTThroughput improvement threshold for pgbench_tpcb search.5.0
SC_PROFILE_MAX_CLIENTSClient-count limit for the planned profile. The pgbench_tpcb search can extend past it while throughput improves, up to the scale factor. Raising it above the highest anchor lets pgbench_tpcb search add points only when a ladder rung lies between that anchor and the scale factor.Highest anchor
SC_PROFILE_HARD_MAX_CLIENTSpgbench_tpcb only: the highest client count search can reach, also capped by the scale factor and by max_connections minus 50. Has no effect on pgbench_ro.Highest anchor for pgbench_ro; 3072 for pgbench_tpcb
SC_RUN_SECONDSMeasurement duration per concurrency point.300
SC_WARMUP_SECONDSInitial warmup duration.120
SC_SETTLE_SECONDSSettle duration between concurrency points.60
SC_WARMUP_ONCEUse one full warmup, then settle between concurrency points.true
SC_DB_VCPUSDatabase vCPU count used for concurrency points and local tuning. Set it for remote databases: the default is the client host's logical CPU count.os.cpu_count() or 2
SC_CLIENT_VCPUSClient vCPU count recorded in output.os.cpu_count() or 2
SC_DB_MEM_GIBDatabase memory in GiB recorded in output. Set it for remote databases.Detected locally; unset for remote databases
SC_DURABILITYLocal server durability; async disables synchronous_commit.durable
SC_TOPOLOGYTopology recorded in output.single_vm locally; otherwise multi_vm
SC_CDN_BASE_URLCDN base URLURL: Uniform Resource LocatorThe address of a resource on the web or a network, such as https://sparecores.com/, made of a scheme, a host, and a path.Glossary for cached dataset dumps.https://cdn.sparecores.net/sc-inspector
SC_CDN_DATASET_POST_B64Optional base64-encoded presigned upload configuration for dataset dumps.Unset
SC_CDN_UPLOADPermit dataset upload when a valid upload configuration is supplied.1

See Results for the output fields.