Stock load

• Types of load
• Load test initialization
• Available parameters
• Load initialization examples
• Running a load test
• Global parameters for all types of load
• getCustomerHistory load
• Parameters for getCustomerHistory
• getRandomCustomerHistory load
• Parameters for getRandomCustomerHistory
• insertRandomOrder load
• Parameters for insertRandomOrder
• submitRandomOrder load
• Parameters for submitRandomOrder
• submitSameOrder load
• Parameters for submitSameOrder
• Examples of running the loads
• Interpretation of results

Simulates a warehouse of an online store: creates multi-product orders, gets a list of orders per customer.

Types of load

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.
• getRandomCustomerHistory 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).
• submitRandomOrder 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:

1. ydb workload stock init [init options...]

• init options: Initialization options.

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

1. ydb workload init --help

Available parameters

3 tables are created using the following DDL statements:

1. CREATE TABLE `stock`(product Utf8, quantity Int64, PRIMARY KEY(product)) WITH (AUTO_PARTITIONING_BY_LOAD = ENABLED, AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = <min-partitions>);
2. 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);
3. 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:

1. 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:

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

Running a load test

To run the load, execute the command:

1. 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 types of workload.
• 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:

1. ydb workload run --help

Global parameters for all types of load

getCustomerHistory load

This type of load reads the specified number of orders for the customer with id = 10000.

YQL query:

1. DECLARE $cust AS Utf8;
2. DECLARE $limit AS UInt32;

4. SELECT id, customer, created FROM orders view ix_cust
5. WHERE customer = 'Name10000'
6. ORDER BY customer DESC, created DESC
7. LIMIT $limit;

To run this type of load, execute the command:

1. 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 of a specific type of load

Parameters for getCustomerHistory

getRandomCustomerHistory load

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

YQL query:

1. DECLARE $cust AS Utf8;
2. DECLARE $limit AS UInt32;

4. SELECT id, customer, created FROM orders view ix_cust
5. WHERE customer = $cust
6. ORDER BY customer DESC, created DESC
7. LIMIT $limit;

To run this type of load, execute the command:

1. 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: Parameters of a specific type of load

Parameters for getRandomCustomerHistory

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:

1. DECLARE $ido AS UInt64;
2. DECLARE $cust AS Utf8;
3. DECLARE $lines AS List<Struct<product:Utf8,quantity:Int64>>;
4. DECLARE $time AS DateTime;

6. INSERT INTO `orders`(id, customer, created) VALUES
7. ($ido, $cust, $time);
8. UPSERT INTO `orderLines`(id_order, product, quantity)
9. SELECT $ido, product, quantity FROM AS_TABLE( $lines );

To run this type of load, execute the command:

1. ydb workload stock run insertRandomOrder [global workload options...] [specific workload options...]

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

Parameters for insertRandomOrder

submitRandomOrder load

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:

1. DECLARE $ido AS UInt64;
2. DECLARE $cust AS Utf8;
3. DECLARE $lines AS List<Struct<product:Utf8,quantity:Int64>>;
4. DECLARE $time AS DateTime;

6. INSERT INTO `orders`(id, customer, created) VALUES
7. ($ido, $cust, $time);

9. UPSERT INTO `orderLines`(id_order, product, quantity)
10. SELECT $ido, product, quantity FROM AS_TABLE( $lines );

12. $prods = SELECT * FROM orderLines AS p WHERE p.id_order = $ido;

14. $cnt = SELECT COUNT(*) FROM $prods;

16. $newq =
17. SELECT
18. p.product AS product,
19. COALESCE(s.quantity, 0) - p.quantity AS quantity
20. FROM $prods AS p
21. LEFT JOIN stock AS s
22. ON s.product = p.product;

24. $check = SELECT COUNT(*) AS cntd FROM $newq as q WHERE q.quantity >= 0;

26. UPSERT INTO stock
27. SELECT product, quantity FROM $newq WHERE $check=$cnt;

29. $upo = SELECT id, $time AS tm FROM orders WHERE id = $ido AND $check = $cnt;

31. UPSERT INTO orders SELECT id, tm AS processed FROM $upo;

33. SELECT * FROM $newq AS q WHERE q.quantity < 0

To run this type of load, execute the command:

1. ydb workload stock run submitRandomOrder [global workload options...] [specific workload options...]

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

Parameters for submitRandomOrder

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:

1. DECLARE $ido AS UInt64;
2. DECLARE $cust AS Utf8;
3. DECLARE $lines AS List<Struct<product:Utf8,quantity:Int64>>;
4. DECLARE $time AS DateTime;

6. INSERT INTO `orders`(id, customer, created) VALUES
7. ($ido, $cust, $time);

9. UPSERT INTO `orderLines`(id_order, product, quantity)
10. SELECT $ido, product, quantity FROM AS_TABLE( $lines );

12. $prods = SELECT * FROM orderLines AS p WHERE p.id_order = $ido;

14. $cnt = SELECT COUNT(*) FROM $prods;

16. $newq =
17. SELECT
18. p.product AS product,
19. COALESCE(s.quantity, 0) - p.quantity AS quantity
20. FROM $prods AS p
21. LEFT JOIN stock AS s
22. ON s.product = p.product;

24. $check = SELECT COUNT(*) as cntd FROM $newq AS q WHERE q.quantity >= 0;

26. UPSERT INTO stock
27. SELECT product, quantity FROM $newq WHERE $check=$cnt;

29. $upo = SELECT id, $time AS tm FROM orders WHERE id = $ido AND $check = $cnt;

31. UPSERT INTO orders SELECT id, tm AS processed FROM $upo;

33. SELECT * FROM $newq AS q WHERE q.quantity < 0

To run this type of load, execute the command:

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

• global workload options: The global options for all types of load.
• 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.

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

Possible result:

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

8. Txs     Txs/Sec Retries Errors  p50(ms) p95(ms) p99(ms) pMax(ms)
9. 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.

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

Possible result:

1. Txs     Txs/Sec Retries Errors  p50(ms) p95(ms) p99(ms) pMax(ms)
2. 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.

1. ydb workload stock run getRandomCustomerHistory -s 5 -t 10 --print-timestamp

Possible result:

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

8. Txs     Txs/Sec Retries Errors  p50(ms) p95(ms) p99(ms) pMax(ms)
9. 5200       1040 0       0       8       17      23      50

Interpretation of results

• 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.
• pMax(ms): 100th percentile of request latency, in ms.
• Timestamp: Timestamp of the end of the time window.