Benchmark Setup with ltbench

In this section, we provide step by step instructions to benchmark Canopy’ write throughput. For these benchmark steps, we use a standard benchmarking tool called ltbench.

Create an Instance to Run ltbench

ltbench is a standard benchmarking tool provided by LightDB. ltbench repeatedly runs given SQL commands and measures the number of completed transactions per second.

Since ltbench itself consumes CPU power, we recommend running it on a separate machine than those running your Canopy cluster. ltbench’s own documentation also points out that putting it on the same machine as the tested database can skew test results.

In these tests, we’re therefore going to create a separate EC2 instance to run ltbench, and place the instance in the same AWS region as our Canopy cluster. We will also use a large EC2 (m4.16xlarge) instance to ensure ltbench itself doesn’t become the performance bottleneck.

Benchmark INSERT Throughput

Initialize and Distribute Tables

Before we start, we need to tell ltbench to initialize the benchmarking environment by creating test tables. Then, we need to connect to the Canopy coordinator node and distribute the table that we’re going to run INSERT benchmarks on.

To initialize the test environment and distribute the related table, you need to get a connection string to the cluster. Then, you need to run the following two commands

ltbench -i connection_string_to_coordinator

ltsql connection_string_to_coordinator -c "SELECT create_distributed_table('ltbench_history', 'aid');"

Create SQL File for ltbench

ltbench runs the given SQL commands repeatedly and reports results. For this benchmark run, we will use the INSERT command that comes with ltbench.

To create the related SQL commands, create a file named insert.sql and paste the following lines into it

\set nbranches :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\set aid random(1, :naccounts)
\set bid random(1, :nbranches)
\set tid random(1, :ntellers)
\set delta random(-5000, 5000)
INSERT INTO ltbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

Benchmark INSERT commands

By default, ltbench opens a single connection to the database and sends INSERT commands through this connection. To benchmark write throughput, we’re going to open parallel connections to the database and issue concurrent commands. In particular, we’re going to use ltbench’s -j parameter to specify the number of concurrent threads and -c parameter to specify the number of concurrent connections. We will also set the duration for our tests to 30 seconds using the -T parameter.

To run ltbench with these parameters, simply type

ltbench connection_string_to_coordinator -j 64 -c 256 -f insert.sql -T 30

Please note that these parameters open 256 concurrent connections to Canopy. If you’re running Canopy on your own instances, you will need to increase the default max_connections setting.

Benchmark UPDATE Throughput

Initialize and Distribute Tables

Before we start, we need to tell ltbench to initialize the benchmarking environment by creating test tables. Then, we need to connect to the Canopy coordinator node and distribute the table that we’re going to run UPDATE benchmarks on.

To initialize the test environment and distribute the related table, you need to get a connection string to the cluster. Then, you need to run the following two commands

ltbench -i connection_string_to_coordinator

# INSERT and UPDATE tests run on different distributed tables
ltsql connection_string_to_coordinator -c "SELECT create_distributed_table('ltbench_accounts', 'aid');"

Create SQL File for ltbench

ltbench runs the given SQL commands repeatedly and reports results. For this benchmark run, we will use one of the UPDATE commands that comes with ltbench.

To create the related SQL commands, create a file named update.sql and paste the following lines into it

\set naccounts 100000 * :scale
\set aid random(1, :naccounts)
\set delta random(-5000, 5000)
UPDATE ltbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

Benchmark UPDATE commands

By default, ltbench opens a single connection to the database and sends UPDATE commands through this connection. To benchmark write throughput, we’re going to open parallel connections to the database and issue concurrent commands. In particular, we’re going to use ltbench’s -j parameter to specify the number of concurrent threads and -c parameter to specify the number of concurrent connections. We will also set the duration for our tests to 30 seconds using the -T parameter.

To run ltbench with these parameters, simply type

ltbench connection_string_to_coordinator -j 64 -c 256 -f update.sql -T 30

Please note that these parameters open 256 concurrent connections to Canopy. If you’re running Canopy on your own instances, you will need to increase the default max_connections setting.