Performance of the Test Monitor API on PostgreSQL

Learn about the query and ingestion performance of Test Monitor APIs on PostgreSQL for four common test data scenarios.

Scenario Description Performance Summary
High Mix-Low Volume Many products with low steps volume
  • Average number of steps ingested when ingesting 100 steps per request is 636 steps per second
  • Average response time for queries on the Product, Results, and Steps grids is less than 1 second
  • Average response time for queries on simple data spaces with filters on Part number and Program name is less than 1 second
  • Data spaces queries with filters on Part number contains take more than 30 seconds exceeding the timeout threshold
  • PostgreSQL storage used for 25 million step records is 65.1 GB with an average step size of 2.71 KB
Low Mix-High Volume Few products with high steps volume
  • Average number of steps ingested when ingesting 500 steps per request is 1082 steps per second
  • Average response time for queries on the Product, Results, and Steps grids is less than 1 second
  • Average response time for queries on the Steps grid is 2 seconds
  • Average response time for queries on simple data spaces with filters on Part number and Program name is less than 1 second
  • Data spaces queries with filters on Contains, Metadata, Input condition, and Path take more than 30 seconds exceeding the timeout threshold
  • PostgreSQL storage used for 25 million step records is 46.1 GB with an average step size of 1.93KB
Low Mix-Medium Volume Few products with high results volume
  • Average number of steps ingested when ingesting 100 steps per request is 594 steps per second
  • Average response time for queries on the Product and Steps grids is less than 1 second
  • Average response time for queries on the Results grid is 2 seconds
  • Average response time for queries on simple data spaces with filters on Program name and Metadata equals is 7 seconds
  • Data spaces queries with filters on Part number contains, Input condition, Product name, and Metadata contains take more than 30 seconds exceeding timeout threshold
  • PostgreSQL storage used for 25 million step records is 50.2 GB with an average step size of 2.1 KB
Low Mix-Very High Volume Single product with very high result and step volume
  • Average number of steps ingested when ingesting 500 steps per request is 1058 steps per second
  • Average response time for queries on the Product, Results, and simple Steps grids is 1 second
  • Average response time for queries on the Steps grid with filters on properties like Step name contains/equals/not null is 5 seconds
  • Average response time for queries on simple data spaces with filters on Part number and Program name is less than 1 second
  • Data spaces queries with filters on Contains, Input condition, and Metadata take more than 30 seconds exceeding the timeout threshold
  • PostgreSQL storage used for 25 million step records is 46 GB with an average step size of 1.93 KB

Control Conditions

NI evaluated the performance of the Test Monitor API for each scenario under the following infrastructure and measurement conditions.

  • A Test Monitor service deployed and managed by Kubernetes (AWS EKS) with the following specifications.
    Specification Description
    Node specification

    Type: r6a.4x large​

    vCPU: 16

    RAM: 128 GiB
    Pod specification

    CPU: 250 m

    Memory: 320 Mi (up to 512 Mi)
    Pod Replication

    Auto scale default: 2

    Auto scale maximum: 10
  • A Test Monitor database with the following specifications.
    Database Specification Description
    Source Single Instance of RDS PostgreSQL
    Specifications

    PostgreSQL version: 14.5

    Instance class: db.t4g.xlarge

    vCPU: 4

    RAM: 16 GB
  • Data uploaded from an EC2 instance into a PostgreSQL database in the same AWS cluster to remove any network latency. Data was uploaded as products, results and steps.
  • Queries for the following workflows from the Test Insights UI.
    • Product grid queries to view and filter products based on part number, product details, and metadata.
    • Results grid queries to view and filter results based on part number, result details, and metadata.
    • Steps grid queries to view and filter steps based on step name and measurement name.
    • Data spaces queries to filter and retrieve step details for visualization. Filters can include advanced LINQ filters, result metadata, step metadata, and condition filters.

Variable Conditions

NI evaluated the performance of the Test Monitor API under the following different dataset conditions.

Table 31. High Mix-Low Volume Scenario
Dataset size 5M 10M 15M 20M 25M
Number of Steps 5 Million 10 Million 15 Million 20 Million 25 Million
Number of Products 250 500 750 1000 1250
Number of Results per Product 200 200 200 200 200
Number of Steps per Result 100 100 100 100 100
Table 32. Low Mix-High Volume Scenario
Dataset size 5M 10M 15M 20M 25M
Number of Steps 5 Million 10 Million 15 Million 20 Million 25 Million
Number of Products 1 2 3 4 5
Number of Results per Product 313 313 313 313 313
Number of Steps per Result 16000 16000 16000 16000 16000
Table 33. Low Mix-Medium Volume Scenario
Dataset size 5M 10M 15M 20M 25M
Number of Steps 5 Million 10 Million 15 Million 20 Million 25 Million
Number of Products 50 50 50 50 50
Number of Results per Product 1000 2000 3000 4000 5000
Number of Steps per Result 100 100 100 100 100
Table 34. Low Mix-Very High Volume Scenario
Dataset size 5M 10M 15M 20M 25M
Number of Steps 5 Million 10 Million 15 Million 20 Million 25 Million
Number of Products 1 1 1 1 1
Number of Results per Product 1000 1000 1000 1000 1000
Number of Steps per Result 5000 10000 15000 20000 25000

High Mix-Low Volume Scenario: Performance Details

In this scenario, the number of steps per result and number of results per product remains constant. Data scale is increased by adding more products.

Table 35. Ingestion Performance

Steps were ingested in batches of 100 per request with no parallel ingestion or querying. The following table and chart show the ingestion rate for ingesting steps data in 5 million batch increments.
Ingestion Step Average number of Steps ingested per second Average number of requests per second
0 to 5 Million 744 7.4
5 Million to 10 Million 673 6.73
10 Million to 15 Million 607 6.07
15 Million to 20 Million 584 5.84
20 Million to 25 Million 574 5.74
Figure 5. Ingestion Performance: Histogram


Figure 6. Ingestion Performance: Cumulative Distribution Chart with 2 σ Trend Line


Table 36. Query Performance: Average Response Time

Query performance was captured at 10 queries per second for the following categories.
Filter applied Take1 Return count2 5M (seconds) 10M (seconds) 15M (seconds) 20M (seconds) 25M (seconds)
Products Grid Queries
Part number 1000 False 0.064 0.056 0.108 0.093 0.062
Family and Name 1000 False 0.071 0.059 0.058 0.061 0.06
Updated time 1000 False 0.069 0.062 0.103 0.076 0.069
Metadata 1000 False 0.081 0.086 0.08 0.078 0.065
No filter 1000 False 0.075 0.063 0.064 0.062 0.066
Results Grid Queries
Test program 1000 False 0.164 0.286 0.328 0.502 0.363
Part number 1000 False 0.152 0.073 0.095 0.091 0.075
Status 1000 False 0.169 0.129 0.136 0.145 0.135
Property 1000 False 0.147 0.195 0.754 0.864 0.813
Keyword 1000 False 0.239 0.379 0.38 0.252 0.296
Workspace 1000 False 0.112 0.149 0.145 0.131 0.326
Id 1000 False 0.234 0.115 0.222 0.153 0.125
No filter 1000 False 0.185 0.276 0.591 0.444 0.268
Count queries 0 True 0.102 0.113 0.263 0.335 0.435
Steps Grid Queries
Step name 1000 True 0.068 0.076 0.075 0.084 0.104
Measurement Name 1000 True 0.069 0.089 0.093 0.088 0.093
Measurement name and Step name 1000 True 0.086 0.079 0.078 0.083 0.093
Applying projections 1000 True 0.08 0.086 0.069 0.095 0.074
Step id 1000 True 0.068 0.084 0.065 0.069 0.077
Data Spaces Queries
Part number 1000 or 10000 False 3.584 1.171 1.343 1.967 2.383
Product name 10000 False 1.662 2.022 2.043 2.97 3.101
Part number and Test program 10000 False 0.974 1.099 1.462 1.591 1.42
Product name and Result status 10000 False 1.949 1.685 1.945 2.258 2.532
Part number and Result metadata 1000 False 1.659 0.518 0.723 0.841 0.995
Part number and Step status 10000 False 1.587 2.322 2.238 2.147 1.855
Part number, Test program and Step name 1000 or 10000 False 0.111 0.143 0.171 0.413 0.218
Part number and Paths 1000 False 0.712 0.48 0.719 1.131 1.011
Input conditions 1000 False 0.539 0.764 1.238 1.957 1.721
Part number (Path API) 1000 False 2.604 7.255 11.738 Timed out Timed out
Part number and Program name (Path API) 1000 False 0.105 0.147 0.219 0.141 0.13
Part number contains 10000 False 17.772 Timed out Timed out Timed out Timed out

1Represents the take parameter in the query request body, which limits the number of records fetched.

2Represents the returnCount parameter in the query request body, which fetches the total count of records matching the applied filter if true.

Note

6.7% of Data Spaces queries with the Part number contains filter returned 503 errors with an increase in data size. These errors indicate the resource limitations for Test Monitor service.
Figure 7. Data Spaces Query Histogram


Low Mix-High Volume Scenario: Performance Details

In this scenario, the number of steps per result and number of results per product remain constant. Data scale is increased by adding more products.

Table 37. Ingestion Performance

As each result has 16000 steps, steps were ingested in batches of 500 per request with no parallel ingestion or querying. The following table and chart show the ingestion rate for ingesting steps data in 5 million batch increments.
Ingestion Step Average number of steps ingested per second Average number of requests per second
0 to 5 Million 1182 2.36
5 Million to 10 Million 1138 2.28
10 Million to 15 Million 1085 2.17
15 Million to 20 Million 1017 2.03
20 Million to 25 Million 990 1.98
Figure 8. Ingestion Performance: Histogram


Figure 9. Ingestion Performance: Cumulative Distribution Chart with 2-σ Trend Line


Table 38. Query Performance: Average Response Time

Query performance was captured at 10 queries per second for the following categories.
Filter applied Take1 Return count2 5M (seconds) 10M (seconds) 15M (seconds) 20M (seconds) 25M (seconds)
Products Grid Queries
Part number 1000 False 0.088 0.084 0.066 0.088 0.056
Family and Name 1000 False 0.096 0.088 0.063 0.073 0.059
Updated time 1000 False 0.079 0.083 0.084 0.063 0.058
Metadata 1000 False 0.072 0.058 0.087 0.064 0.088
No filter 1000 False 0.082 0.059 0.063 0.099 0.064
Results Grid Queries
Test program 1000 False 0.086 0.08 0.094 0.093 0.092
Part number 1000 False 0.122 0.09 0.172 0.102 0.1
Status 1000 False 0.143 0.092 0.123 0.105 0.105
Property 1000 False 0.089 0.109 0.074 0.073 0.066
Keyword 1000 False 0.083 0.109 0.077 0.069 0.075
Workspace 1000 False 0.085 0.081 0.101 0.103 0.089
Id 1000 False 0.15 0.1 0.12 0.11 0.129
No filter 1000 False 0.08 0.113 0.15 0.126 0.127
Count query 0 True 0.082 0.082 0.077 0.075 0.065
Steps Grid Queries
Step name 1000 True 0.205 0.89 3.922 4.107 4.772
Measurement Name 1000 True 0.292 1.647 3.474 4.792 5.934
Measurement name and Step name 1000 True 0.17 0.749 3.42 2.742 4.565
Applying projections 1000 True 0.106 0.099 0.09 0.085 0.105
Step id 1000 True 0.107 0.079 0.104 0.092 0.096
Data Spaces Queries
Part number 1000 or 10000 False 1.15 1.381 1.073 1.172 1.086
Product name 10000 False 1.475 2.299 1.442 1.473 1.447
Part number and Test program 10000 False 1.698 2.059 1.48 1.448 1.749
Product name and Result status 10000 False 1.648 1.558 1.557 1.566 2.386
Part number and Result metadata 1000 False 0.594 0.843 2.065 1.008 2.492
Part number and Step status 10000 False 1.612 1.536 1.486 2.243 1.583
Part number, Test program and Step name 1000 or 10000 False 1.344 6.632 6.863 15.797 14.59
Part number and Paths 1000 False 0.273 2.648 1.217 0.32 1.095
Input conditions 1000 False 0.259 0.28 0.252 0.306 0.285
Part number (Path API) 1000 False 0.255 0.755 0.984 1.336 2.825
Part number and Program name (Path API) 1000 False 0.117 0.2 0.196 0.24 0.157
Input condition value - equals 1000 False 17.45 Timed out Timed out Timed out Timed out
Program name - Contains 1000 or 10000 False 2.281 Timed out Timed out Timed out Timed out
Result metadata - Contains 1000 False 2.355 Timed out Timed out Timed out Timed out

1Represents the take parameter in the query request body, which limits the number of records fetched.

2Represents the returnCount parameter in the query request body, which fetches the total count of records matching the applied filter if true.

Note

8.7% of Data Spaces queries with filters like Program name contains, Result metadata contains, and Input condition value equals returned 503 errors, which indicates the resource limitations for Test Monitor service.
Figure 10. Data Spaces Query Histogram


Low Mix-Medium Volume Scenario: Performance Details

In this scenario, the number of steps per result and number of products remain constant. Data scale is increased by adding new results under existing products.

Table 39. Ingestion Performance

As each result has 100 steps, steps were ingested in batches of 100 per request with no parallel ingestion or querying. The following table and chart show the ingestion rate for ingesting steps data in 5 million batch increments.
Ingestion Step Average number of steps ingested per second Average number of requests per second
0 to 5 Million 727 7.27
5 Million to 10 Million 590 5.9
10 Million to 15 Million 572 5.72
15 Million to 20 Million 541 5.41
20 Million to 25 Million 539 5.39
Figure 11. Ingestion Performance: Histogram


Figure 12. Ingestion Performance: Cumulative Distribution Chart with 2-σ Trend Line


Table 40. Query Performance: Average Response Time

Query performance was captured at 10 queries per second for the following categories.
Filter applied Take1 Return count2 5M (seconds) 10M (seconds) 15M (seconds) 20M (seconds) 25M (seconds)
Products Grid Queries
Part number 1000 False 0.081 0.064 0.077 0.072 0.104
Family and Name 1000 False 0.061 0.067 0.092 0.073 0.08
Updated time 1000 False 0.061 0.079 0.085 0.066 0.081
Metadata 1000 False 0.061 0.078 0.122 0.091 0.094
No filter 1000 False 0.065 0.067 0.058 0.095 0.055
Results Grid Queries
Test program 1000 False 0.2 0.541 1.811 1.482 1.878
Part number 1000 False 0.149 0.214 0.496 0.197 0.33
Status 1000 False 0.146 0.273 0.206 0.268 0.483
Property 1000 False 0.201 0.807 2.048 0.942 1.83
Keyword 1000 False 0.317 1.827 2.717 3.473 3.473
Workspace 1000 False 0.148 0.39 1.706 0.633 0.917
Id 1000 False 0.135 0.152 0.371 0.227 0.166
No filter 1000 False 0.259 0.662 1.741 1.323 1.269
Count query 0 True 0.131 0.315 2.001 0.869 2.574
Steps Grid Queries
Step name 1000 True 0.071 0.135 0.171 0.103 0.183
Measurement Name 1000 True 0.071 0.111 0.194 0.106 0.171
Measurement name and Step name 1000 True 0.069 0.113 0.289 0.122 0.217
Applying projections 1000 True 0.077 0.103 0.129 0.102 0.102
Step id 1000 True 0.081 0.085 0.149 0.102 0.133
Data Spaces queries
Part number and Test program 10000 False 0.427 1.124 2.571 1.035 3.897
Part number and Result metadata 1000 False 0.209 0.407 1.952 0.784 1.896
Part number, Test program and Step name 1000 or 10000 False 0.186 0.401 2.945 1.14 2.093
Part number and Paths 1000 False 0.784 4.956 7.993 5.836 8.107
Part number (Path API) 1000 False 10.452 20.032 Timed out 19.767 16.725
Part number and Program name (Path API) 1000 False 0.196 0.377 0.848 0.284 0.275
Part number (equals and contains) 1000 or 10000 False 9.1 Timed out Timed out Timed out Timed out
Input conditions 1000 False Timed out Timed out Timed out Timed out Timed out
Product name 10000 False 4.594 Timed out Timed out Timed out Timed out
Result metadata 1000 False 6.167 Timed out Timed out Timed out Timed out

1Represents the take parameter in the query request body, which limits the number of records fetched.

2Represents the returnCount parameter in the query request body, which fetches the total count of records matching the applied filter if true.

Note 39% of Data Spaces queries with filters like Contains, Product name equals, Result metadata, and Input condition returned 503 errors. These errors indicate the resource limitations for Test Monitor service.
Figure 13. Data Spaces Query Histogram


Low Mix-Very High Volume Scenario: Performance Details

In this scenario, the number of products and number of results per product remain constant. Data scale is increased by adding new steps under existing results.

Table 41. Ingestion Performance

As each result has more than 500 steps in each experiment, steps were ingested in batches of 500 per request with no parallel ingestion or querying. The following table and chart show the ingestion rate for ingesting steps data in 5 million batch increments.
Ingestion Step Average number of steps ingested per second Average number of requests per second
0 to 5 Million 1184 2.37
5 Million to 10 Million 1101 2.2
10 Million to 15 Million 1052 2.1
15 Million to 20 Million 999 2
20 Million to 25 Million 953 1.91
Figure 14. Ingestion Performance: Histogram


Figure 15. Ingestion Performance: Cumulative Distribution Chart with 2-σ Trend Line


Table 42. Query Performance: Average Response Time

Query performance was captured at 10 queries per second for the following categories.
Filter applied Take1 Return count2 5M (seconds) 10M (seconds) 15M (seconds) 20M (seconds) 25M (seconds)
Products Grid Queries
Part number 1000 False 0.076 0.079 0.063 0.087 0.052
Family and Name 1000 False 0.065 0.053 0.06 0.057 0.058
Updated time 1000 False 0.083 0.06 0.064 0.059 0.058
Metadata 1000 False 0.072 0.057 0.072 0.055 0.079
No filter 1000 False 0.071 0.052 0.058 0.059 0.056
Results Grid Queries
Test program 1000 False 0.133 0.082 0.085 0.091 0.097
Part number 1000 False 0.129 0.127 0.131 0.129 0.126
Status 1000 False 0.113 0.098 0.116 0.094 0.092
Property 1000 False 0.07 0.107 0.073 0.075 0.063
Keyword 1000 False 0.073 0.079 0.075 0.063 0.069
Workspace 1000 False 0.098 0.133 0.103 0.085 0.09
Id 1000 False 0.099 0.107 0.139 0.203 0.099
No filter 1000 False 0.131 0.137 0.107 0.126 0.144
Count query 0 True 0.075 0.070 0.071 0.057 0.056
Steps Grid Queries
Step name 1000 True 0.082 1.287 2.778 5.129 5.891
Measurement Name 1000 True 0.132 2.053 1.989 5.382 3.71
Measurement name and Step name 1000 True 0.142 1.277 3.927 4.681 7.07
Applying projections 1000 True 0.08 0.119 0.079 0.092 0.082
Step id 1000 True 0.08 0.092 0.093 0.09 0.108
Data Spaces Queries
Part number 1000 or 10000 False 1.039 1.038 1.01 1.084 1.132
Product name 10000 False 1.452 1.422 1.403 1.472 1.816
Part number and Test program 10000 False 1.465 1.478 1.48 1.528 1.44
Product name and Result status 10000 False 1.566 1.474 1.437 1.503 1.488
Part number and Result metadata 1000 False 0.623 0.606 1.074 1.609 0.343
Part number and Step status 10000 False 1.503 1.442 1.498 1.527 1.9
Part number, Test program, and Step name 1000 or 10000 False 1.412 9.546 2.031 3.761 5.345
Part number and Paths 1000 False 0.245 0.344 0.569 0.336 0.467
Input conditions 1000 False 0.241 0.226 0.234 0.232 0.223
Part number (Path API) 1000 False 0.235 0.422 0.482 0.748 0.431
Part number and Program name (Path API) 1000 False 0.099 0.227 0.167 0.145 0.217
Input condition value - equals 1000 False 14.024 Timed out Timed out Timed out Timed out
Program name - Contains 10000 False 2.498 Timed out Timed out Timed out Timed out
Result metadata - Contains 1000 False 1.575 Timed out Timed out Timed out Timed out

1Represents the take parameter in the query request body, which limits the number of records fetched.

2Represents the returnCount parameter in the query request body, which fetches the total count of records matching the applied filter if true.

Note 5.7% of Data Spaces queries with filters like Program name contains and Result metadata returned 503 errors. These errors indicate the resource limitations for Test Monitor service.
Figure 16. Data Spaces Query Histogram