Stock load

• Types of load
• • Available parameters
• Running a load test
• getCustomerHistory load
• getRandomCustomerHistory load
• insertRandomOrder load
• submitRandomOrder load
• submitSameOrder load
• Examples of running the loads

This load test runs 5 types of load:

• getCustomerHistory reads the specified number of orders for the customer with id = 10000. This creates a workload to read the same rows from different threads.
• reads the specified number of orders made by a randomly selected customer. A load that reads data from different threads is created.
• insertRandomOrder creates a random order. For example, a customer has created an order of 2 products, but hasn’t yet paid for it, hence the quantities in stock aren’t decreased for the products. The database writes the data about the order and products. The read/write load is created (the INSERT checks for an existing entry before inserting the data).
• creates and processes a randomly generated order. For example, a customer has created and paid an order of 2 products. The data about the order and products is written to the database, product availability is checked and quantities in stock are decreased. A mixed data load is created.
• submitSameOrder: Creates orders with the same set of products. For example, all customers buy the same set of products (a newly released phone and a charger). This creates a workload of competing updates of the same rows in the table.

Load test initialization

To get started, create tables and populate them with data:

• : Initialization options.

See the description of the command to run the data load:

ydb workload init --help

3 tables are created using the following DDL statements:

CREATE TABLE `stock`(product Utf8, quantity Int64, PRIMARY KEY(product)) WITH (AUTO_PARTITIONING_BY_LOAD = ENABLED, AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = <min-partitions>);
CREATE TABLE `orders`(id Uint64, customer Utf8, created Datetime, processed Datetime, PRIMARY KEY(id), INDEX ix_cust GLOBAL ON (customer, created)) WITH (READ_REPLICAS_SETTINGS = "per_az:1", AUTO_PARTITIONING_BY_LOAD = ENABLED, AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = <min-partitions>, UNIFORM_PARTITIONS = <min-partitions>, AUTO_PARTITIONING_MAX_PARTITIONS_COUNT = 1000);
CREATE TABLE `orderLines`(id_order Uint64, product Utf8, quantity Int64, PRIMARY KEY(id_order, product)) WITH (AUTO_PARTITIONING_BY_LOAD = ENABLED, AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = <min-partitions>, UNIFORM_PARTITIONS = <min-partitions>, AUTO_PARTITIONING_MAX_PARTITIONS_COUNT = 1000);

Load initialization examples

Creating a database with 1000 products, 10000 items of each product, and no orders:

ydb workload stock init -p 1000 -q 10000 -o 0

Creating a database with 10 products, 100 items of each product, 10 orders, and a minimum number of shards equal 100:

ydb workload stock init -p 10 -q 100 -o 10 ----min-partitions 100

Running a load test

To run the load, execute the command:

ydb workload stock run [workload type...] [global workload options...] [specific workload options...]

During this test, workload statistics for each time window are displayed on the screen.

• workload type: The .
• global workload options: The global options for all types of load.
• specific workload options: Options of a specific load type.

See the description of the command to run the data load:

ydb workload run --help

YQL query:

To run this type of load, execute the command:

ydb workload stock run getCustomerHistory [global workload options...] [specific workload options...]

• global workload options: The global options for all types of load.
• specific workload options:

Parameters for getCustomerHistory

getRandomCustomerHistory load

This type of load reads the specified number of orders from randomly selected customers.

YQL query:

DECLARE $cust AS Utf8;
DECLARE $limit AS UInt32;
SELECT id, customer, created FROM orders view ix_cust
    ORDER BY customer DESC, created DESC
    LIMIT $limit;

To run this type of load, execute the command:

ydb workload stock run getRandomCustomerHistory [global workload options...] [specific workload options...]

• global workload options: The global options for all types of load.
• specific workload options:

insertRandomOrder load

This type of load creates a randomly generated order. The order includes several different products, 1 item per product. The number of products in the order is generated randomly based on an exponential distribution.

YQL query:

DECLARE $ido AS UInt64;
DECLARE $cust AS Utf8;
DECLARE $lines AS List<Struct<product:Utf8,quantity:Int64>>;
DECLARE $time AS DateTime;
INSERT INTO `orders`(id, customer, created) VALUES
    ($ido, $cust, $time);
UPSERT INTO `orderLines`(id_order, product, quantity)
    SELECT $ido, product, quantity FROM AS_TABLE( $lines );

To run this type of load, execute the command:

• global workload options: The .
• specific workload options: Parameters of a specific type of load

Parameters for insertRandomOrder

This type of load creates a randomly generated order and processes it. The order includes several different products, 1 item per product. The number of products in the order is generated randomly based on an exponential distribution. Order processing consists in decreasing the number of ordered products in stock.

YQL query:

DECLARE $ido AS UInt64;
DECLARE $cust AS Utf8;
DECLARE $lines AS List<Struct<product:Utf8,quantity:Int64>>;
DECLARE $time AS DateTime;
INSERT INTO `orders`(id, customer, created) VALUES
    ($ido, $cust, $time);
UPSERT INTO `orderLines`(id_order, product, quantity)
    SELECT $ido, product, quantity FROM AS_TABLE( $lines );
$prods = SELECT * FROM orderLines AS p WHERE p.id_order = $ido;
$cnt = SELECT COUNT(*) FROM $prods;
$newq =
    SELECT
        p.product AS product,
        COALESCE(s.quantity, 0) - p.quantity AS quantity
    FROM $prods AS p
    LEFT JOIN stock AS s
    ON s.product = p.product;
$check = SELECT COUNT(*) AS cntd FROM $newq as q WHERE q.quantity >= 0;
UPSERT INTO stock
    SELECT product, quantity FROM $newq WHERE $check=$cnt;
$upo = SELECT id, $time AS tm FROM orders WHERE id = $ido AND $check = $cnt;
UPSERT INTO orders SELECT id, tm AS processed FROM $upo;
SELECT * FROM $newq AS q WHERE q.quantity < 0

To run this type of load, execute the command:

• global workload options: The global options for all types of load.
• specific workload options:

submitSameOrder load

This type of load creates an order with the same set of products and processes it. Order processing consists in decreasing the number of ordered products in stock.

YQL query:

DECLARE $ido AS UInt64;
DECLARE $cust AS Utf8;
DECLARE $lines AS List<Struct<product:Utf8,quantity:Int64>>;
DECLARE $time AS DateTime;
INSERT INTO `orders`(id, customer, created) VALUES
    ($ido, $cust, $time);
    SELECT $ido, product, quantity FROM AS_TABLE( $lines );
$prods = SELECT * FROM orderLines AS p WHERE p.id_order = $ido;
$cnt = SELECT COUNT(*) FROM $prods;
$newq =
    SELECT
        p.product AS product,
        COALESCE(s.quantity, 0) - p.quantity AS quantity
    FROM $prods AS p
    LEFT JOIN stock AS s
    ON s.product = p.product;
$check = SELECT COUNT(*) as cntd FROM $newq AS q WHERE q.quantity >= 0;
UPSERT INTO stock
    SELECT product, quantity FROM $newq WHERE $check=$cnt;
$upo = SELECT id, $time AS tm FROM orders WHERE id = $ido AND $check = $cnt;
UPSERT INTO orders SELECT id, tm AS processed FROM $upo;
SELECT * FROM $newq AS q WHERE q.quantity < 0

To run this type of load, execute the command:

ydb workload stock run submitSameOrder [global workload options...] [specific workload options...]

• global workload options: The .
• specific workload options: Parameters of a specific type of load

Parameters for submitSameOrder

Examples of running the loads

• Run the load insertRandomOrder for 5 seconds across 10 threads with 1000 products.

ydb workload stock run insertRandomOrder -s 5 -t 10 -p 1000

Possible result:

Elapsed Txs/Sec Retries Errors  p50(ms) p95(ms) p99(ms) pMax(ms)
1           132 0       0       69      108     132     157
2           157 0       0       63      88      97      104
3           156 0       0       62      84      104     120
4           160 0       0       62      77      90      94
5           174 0       0       61      77      97      100
Txs     Txs/Sec Retries Errors  p50(ms) p95(ms) p99(ms) pMax(ms)
779       155.8 0       0       62      89      108     157

• Run the submitSameOrder load for 5 seconds across 5 threads with 2 products per order, printing out only final results.

ydb workload stock run submitSameOrder -s 5 -t 5 -p 1000 --quiet

Possible result:

Txs     Txs/Sec Retries Errors  p50(ms) p95(ms) p99(ms) pMax(ms)
16          3.2 67      3       855     1407    1799    1799

• Run the getRandomCustomerHistory load for 5 seconds across 100 threads, printing out time for each time window.

Possible result:

Elapsed Txs/Sec Retries Errors  p50(ms) p95(ms) p99(ms) pMax(ms)        Timestamp
1          1046 0       0       7       16      25      50      2022-02-08T17:47:26Z
2          1070 0       0       7       17      22      28      2022-02-08T17:47:27Z
3          1041 0       0       7       17      22      28      2022-02-08T17:47:28Z
4          1045 0       0       7       17      23      31      2022-02-08T17:47:29Z
5           998 0       0       8       18      23      42      2022-02-08T17:47:30Z
Txs     Txs/Sec Retries Errors  p50(ms) p95(ms) p99(ms) pMax(ms)
5200       1040 0       0       8       17      23      50

• Elapsed: Time window ID. By default, a time window is 1 second.
• Txs/sec: Number of successful load transactions in the time window.
• Retries: The number of repeat attempts to execute the transaction by the client in the time window.
• Errors: The number of errors that occurred in the time window.
• p50(ms): 50th percentile of request latency, in ms.
• p95(ms): 95th percentile of request latency, in ms.
• p99(ms): 99th percentile of request latency, in ms.
• : 100th percentile of request latency, in ms.