Location>code7788 >text

[postgres] Benchmarking with pgbench

Popularity：4 ℃/2024-08-31 17:53:24

preamble

pgbench is a simple program for benchmarking on postgres that usually comes with the installation. pgbench can run the same sequence of SQL statements over and over again in concurrent database paintings and calculate the average transaction rate.

Test Preparation

Since we're testing postgres, we definitely need a postgres first. the installation process is skipped.

Some environmental information:

  • postgres version: 15.3, default configuration after installation
  • os version：debian 12
  • Hardware configuration: vbox virtual machine, 4 cores, 4GB RAM, 40GB SSD storage

pgbench options

(of a computer) runpgbench --helpView Help Documentation

pgbench is a benchmarking tool for PostgreSQL.

Usage: pgbench [OPTION]...
  pgbench [OPTION]... [DBNAME]

Initialization options.
  -i, --initialize Call initialization mode.
  -I, --init-steps=[dtgGvpf]+ (default "dtgvp")
                           Run selected initialization steps
  -F, --fillfactor=NUM set fill factor
  -n, --no-vacuum do not run VACUUM during initialization
  -q, --quiet quiet logging (one message every 5 seconds)
  -s, ---scale=NUM Scale factor
  --foreign-keys Create foreign key constraints between tables
  --index-tablespace=TABLESPACE
                           Create indexes in the specified tablespace
  --partition-method=(range|hash)
                           Partition pgbench_accounts using this method (default: range)
  --partitions=NUM Partition pgbench_accounts into NUM parts (default: 0)
  ---tablespace=TABLESPACE creates tables in the specified tablespace
  --unlogged-tables Creates tables as unlogged tables

Options to select what to run.
  -b, --builtin=NAME[@W] Add built-in script NAME with weight W (default: 1)
                           (use "-b list" to list available scripts)
  -f, --file=FILENAME[@W] Add script FILENAME with weight W (default: 1)
  -N, --skip-some-updates Skip pgbench_tellers and pgbench_branches updates!
                           (same as "-b simple-update")
  -S, --select-only Execute SELECT type transactions only.
                           (same as "-b select-only")

Benchmarking options.
  -c, --client=NUM Number of concurrent database clients (default: 1)
  -C, --connect Create a new connection for each transaction.
  -D, --define=VARNAME=VALUE
                           Define variables for custom scripts
  -j, --jobs=NUM Number of threads (default: 1)
  -l, --log Write transaction time to log file
  -L, --latency-limit=NUM Counts transactions over NUM milliseconds as latency
  -M, --protocol=simple|extended|prepared
                           Protocol for submitting queries (default: simple)
  -n, --no-vacuum do not run VACUUM before testing
  -P, --progress=NUM Show thread progress report every NUM seconds
  -r, --report-per-command Report delays, failures and retries per command
  -R, --rate=NUM Target transaction rate per second
  -s, ---scale=NUM Report this scaling factor in the output
  -t, --transactions=NUM Number of transactions run per client (default: 10)
  -T, --time=NUM Benchmark duration in seconds
  -v, --vacuum-all Clean up all four standard tables before testing
  --aggregate-interval=NUM Aggregate data in NUM seconds
  --failures-detailed Report failures grouped by base type
  --log-prefix=PREFIX Prefix for transaction time log file
                           (default: "pgbench_log")
  ---max-tries=NUM Maximum number of attempts to run a transaction (default: 1)
  --progress-timestamp Use Unix epoch timestamp for progress
  --random-seed=SEED Set random seed ("time", "rand", integer)
  ---sampling-rate=NUM Proportion of transactions logged (e.g., 0.01 for 1%)
  --show-script=NAME Show the built-in script code and exit.
  --verbose-errors prints all error messages

Common options: -d, --debug
  -d, --debug print debug output
  -h, --host=HOSTNAME The database server host or socket directory.
  -p, --port=PORT The database server port number.
  -U, --username=USERNAME Connect as the specified database user.
  -V, --version Outputs version information and exits.
  -? , --help Displays this help information and exits.

Report bugs to <pgsql-bugs@>.
PostgreSQL home page: </>.

Test Example

  1. Initialize the test data. The following command automatically creates four test tables in one of thepgbench_accountsThe pgbench creates 200000 rows of data in the test table. If you initialize again, pgbench will delete the old table first and then build the test table.
pgbench -i -s 2
  1. Perform a simple benchmark test using the default configuration
$ pgbench

pgbench (15.3)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)> # Types of tests used in this test
scaling factor: 2 # Used to record the data volume scale factor set at initialization
query mode: simple # The test is the specified query type
number of clients: 1 # Number of clients
number of threads: 1 # Number of threads per client
maximum number of tries: 1
number of transactions per client: 10 # Number of transactions per client
number of transactions actually processed: 10/10 # Number of transactions actually completed and planned to be completed
number of failed transactions: 0 (0.000%)
latency average = 7.501 ms # Average response time
initial connection time = 1.650 ms
tps = 133.317335 (without initial connection time)

Delete test data

DROP TABLE IF EXISTS pgbench_accounts;
DROP TABLE IF EXISTS pgbench_branches;
DROP TABLE IF EXISTS pgbench_history;
DROP TABLE IF EXISTS pgbench_tellers;

Test Data

500w data volume, postgres is the default configuration

serial number test command Average response time TPS Total services
1 pgbench -T60 6.857 ms 145.833890 8750
2 pgbench -j2 -c4 -T60 11.505 ms 347.677286 20858
3 pgbench -j4 -c8 -T60 13.721 ms 583.052560 34987
<Previous Next>