Benchmarking #

The amelie bench [login] [client options] [bench options] command can be used for server benchmarking using predefined tests. The command is compatible with the Console Client options.

The following benchmarking options are supported:

TPC-B Style Benchmark #

This is the default benchmarking type, made to match the PostgreSQL pgbench command to get an exact performance comparison with PostgreSQL.

It continuously executes the following multi-statement transaction, which involves four tables.

begin

update accounts
set abalance = abalance + :delta
where aid = :aid;

select abalance into :abalance
from accounts
where aid = :aid;

update tellers
set tbalance = tbalance + :delta
where tid = :tid;

update branches
set bbalance = bbalance + :delta
where bid = :bid;

insert into history(tid, bid, aid, delta, time)
values (:tid, :bid, :aid, :delta, current_timestamp);

commit

Insert Benchmark #

Sequentially Insert rows using the INSERT statement. The number of rows per one operation can be configured using the batch option. The test table’s primary index is tree.

Watch out for available RAM.

Upsert Benchmark #

Randomly Insert rows using the INSERT ON CONFLICT DO UPDATE statement. The number of rows per one operation can be configured using the batch option. The test table’s primary index is hash.

The number of rows in the table is set to 100000 random keys.

Resolved Benchmark #

Randomly Insert rows where each column is generated as stored or resolved together. The number of rows per one operation can be configured using the batch option. The test table’s primary index is hash.

create table __bench.test (
  ts   timestamp as ( current_timestamp::date_bin('3 sec'::interval) ) stored,
  id   int random (10000),
  hits int default 0 as ( hits + 1 ) resolved,
  primary key(ts, id)
) with (type = 'hash');

Import Benchmark #

Sequentially stream rows using the HTTP API. The number of rows per one operation can be configured using the batch option. The test table’s primary index is tree.

Watch out for available RAM.

Recommendations #

Please note that the number of frontends, backends in the system, as well as using unlogged tables directly impact the performance. It is a good idea to start with defaults and then alter the numbers to see how is affects the performance.

More is not always better, especially if you are running benchmarks on the same machine. There is a possible situation when workers will start competing for CPU cores and locking each other.

Learn more about the IO and Compute.

Third-Party Benchmarking tools #

Using any HTTP benchmarking tool configured to generate custom requests is possible.

Please ensure the benchmarking tool can adequately adjust the pressure level to match the processing capabilities.

One such tool is wrk.

# do tpcb style benchmark for 1 minute
amelie bench --uri="http://localhost:3485" --time=60

# do batch insert for 10 secs
amelie bench --uri="http://localhost:3485" --type="insert" --batch=600

# benchmark using previously saved server credentials
amelie login home --uri="http://localhost:3485"
amelie bench home