Reducing Network Latency and Improving Read Performance With CockroachDB and PolyScale.ai
This tutorial explores how to benefit from lowered latency, increased performance, and reduced infrastructure costs, as well as faster reads in more locations.
CORE
·
Join the DZone community and get the full member experience.
Join For FreeMotivation
CockroachDB makes multi-region simple. When CockroachDB spans geographically, we often need to add a minimum of two more regions for a multi-region cluster. This unique capability has many strengths but it comes at a cost. Oftentimes, our customers demand CockroachDB be available in regions where we see low demand, and bringing those regions online is not cost-effective to the organization. As of this writing, we support the most popular regions in GCP and AWS; some regions are not exposed in the cloud console but are available via support ticket.
PolyScale operates a global network of PoPs (Points of Presence). Think of PoPs as regional database connections. This versatility provides a cost-effective solution to reduce global network latency by bringing the database closer to the end user. The network of PoPs spans multiple cloud providers, thereby bridging the gap between cloud providers. PolyScale complements CockroachDB in the way that CockroachDB can be more accessible in many more geographic locations and many other cloud providers than provided out of the box by Cockroach Cloud.
According to their website:
"PolyScale is a Platform as a Service that makes data-driven apps faster by simplifying global data distribution and caching. PolyScale caches database data locally to users reducing latency, accelerating read performance, and scaling throughput."
PolyScale provides several benefits:
- Increased query performance
- Lowered global latency
- Reduced database infrastructure costs
- Increase engineering productivity
- High availability and fault tolerance
I will be evaluating this product for a customer use case. This is attractive because if it works, the customer can benefit from lowered latency, increased performance, and reduced infrastructure costs. They already have a multi-region cluster with industry best uptime SLA and resiliency story and now they just need faster reads in many more locations. They will also benefit from increased productivity, as PolyScale is very easy to get started with.
I will be looking at each benefit individually and share my opinion. That said, I want to thank the PolyScale team as they've been invaluable in my research. They've shown partnership, patience, and an open mind in my effort to make this work.
High-Level Steps
- Provision a CockroachDB cluster
- Create a PolyScale account and cache
- Verify setup
- Verify network latency claims
- Verify inserts
- YCSB Workload B
- YCSB Workload C
- PolyScale Self-Hosted
- PgBench Workload
- Conclusion
Step-By-Step Instructions
Start a Cluster
I am using a CockroachDB Serverless cluster you can provision with a generous free tier. I intentionally created a cluster in GCP's europe-west1 region to test out the PolyScale promise of reduced network latency. I am accessing the cluster from North Jersey.
Create a PolyScale Account and Cache
Once you have a working CockroachDB cluster, you can navigate to the PolyScale website and sign up for their service. Once the account is set up, create a cache with your database information.
Once you create, you should see a prompt with the new connection information.
PolyScale relies on the application_name to identify the cache ID. You can see the application_name session variable in the connection string.
Verify Setup
The first thing it does is run a network test across all of the available PoP endpoints. The network test attempts to establish a TCP connection to each endpoint.
Alternatively, you can navigate to the Connect page and run the test manually.
Verify Network Latency Claims
I would like to run a network latency test using a SELECT 1 query. The goal is to verify the Lowered global latency claim.
If I run the query against the cluster directly using a Cockroach client:
artem@artem-read-replicas-7698.8nj.cockroachlabs.cloud:26257/defaultdb> select 1;
?column?
------------
1
Time: 78ms total (execution 1ms / network 78ms)
After several attempts, I get a consistent 78-80ms result. If I query the cluster via PolyScale, I have to execute the query several times before I start seeing speed up. Let's leave the execution latency aside for now.
Time: 99ms total (execution 452ms / network 99ms)
artem@psedge.global:5432/defaultdb> select 1;
?column?
------------
1
Time: 8ms total (execution 452ms / network 8ms)
artem@psedge.global:5432/defaultdb> select 1;
?column?
------------
1
Time: 10ms total (execution 452ms / network 10ms)
We reduced the network round trip from 80ms to about 10ms. The PoP endpoint is in Cliffton New Jersey, which is about 20 miles away from me. I want to test this with the psql client as well.
Using the psql client, we have to make sure to turn the timing on, i.e. \timing:
psql "host=artem-read-replicas-7698.8nj.cockroachlabs.cloud sslmode=require port=26257 user='artem' dbname='defaultdb'"
defaultdb=> \timing
Timing is on.
defaultdb=> select 1;
?column?
----------
1
(1 row)
Time: 89.312 ms
defaultdb=> select 1;
?column?
----------
1
(1 row)
Time: 82.807 ms
defaultdb=> select 1;
And using PolyScale:
Time: 93.463 ms
defaultdb=> select 1;
?column?
----------
1
(1 row)
Time: 51.222 ms
defaultdb=> select 1;
?column?
----------
1
(1 row)
Time: 25.901 ms
defaultdb=> select 1;
?column?
----------
1
(1 row)
Time: 34.170 ms
defaultdb=> select 1;
?column?
----------
1
(1 row)
Time: 36.517 ms
The results are certainly in favor of PolyScale. I feel optimistic that PolyScale can deliver reduced latencies at lower costs. This however is not a conclusive test and we need to look further.
Verify Inserts
Given PolyScale's strengths are in scaling reads, I was curious how writes work using the product. I was happy to see that inserts still work even though we're reading from a cache. That is advantageous compared to CockroachDB follower reads, as we don't have to have read-only transactions to access reads. According to PolyScale, they need to see the writes for cache eviction.
Inserting five records directly yields the following result:
artem@artem-read-replicas-7698.8nj.cockroachlabs.cloud:26257/defaultdb> insert into test (val) values (1), (2), (3), (4), (5) returning id;
id
----------------------------------------
c32b396e-4c6a-4fa4-ba6a-88ff7f8ade78
e7f87e27-a64d-4c52-b8ba-a31ac2cb9f0c
b63dc86b-caef-4cf8-a0bd-d93963077538
bbba98cf-e2a1-45eb-9f3b-c8b8ebda7082
812c002f-5bac-462c-892a-9ad4d4674e04
(5 rows)
Time: 103ms total (execution 5ms / network 98ms)
Using PolyScale:
fca50ae8-e264-4dba-bc8c-256b067e0c64@psedge.global:5432/ycsb> insert into test (val) values (1), (2), (3), (4), (5) returning id;
id
----------------------------------------
b9b78909-8c14-4a8b-bbaf-7729e565d99d
d7e761f6-48b1-4587-9b72-ee21cb896786
47d3a388-72ef-4527-b3d1-b56d55eb2d28
680da903-b811-4949-a7a9-cf4600a775cb
50d81939-2a80-45d2-88d0-1f6f86f2de60
(5 rows)
Time: 102ms total (execution 6ms / network 95ms)
I was pleasantly surprised that insert speeds are not impacted, considering there is another hop to a PoP location before it hits the gateway node.
YCSB Workload B
The next thing I want to validate is scaling reads in a mixed workload environment. To be completely transparent, it took me a few days to grasp the full potential of the platform. Originally, I was planning to test PolyScale using the pgbench workload generator. Still, unfortunately, PolyScale does not work well with mixed workload scenarios where a large portion of the workload is write and read queries to generate unique fingerprints, meaning our cache hit ratio is very low. PolyScale is a caching tool and thereby we need to focus on read-heavy workloads where a large number of queries have their values in the cache.
That said, I switched my focus to the ycsb workload.
There are several workloads to choose from, and the ones we are going to look at are workloads B and C. Workload B has a 95/5 percent ratio of reads to writes. We don't always have a choice of workloads when we talk with customers and typically those workloads are mixed. In my case, a 95/5 ratio fits my customer use case. This test can also validate the applicability of PolyScale in the face of writes. What use is it to us if we cannot write to the database?
The first thing we're going to do is save the connection URL in an environment variable.
export DATABASE_URL="postgresql://artem:password@artem-read-replicas-7698.8nj.cockroachlabs.cloud:26257/ycsb?sslmode=verify-full"
We then initialize the workload:
cockroach workload init ycsb \ --data-loader IMPORT \ --drop \ $DATABASE_URL
We can now capture the baseline using our CockroachDB Serverless cluster directly.
cockroach workload run ycsb \ --duration=5m \ --display-every=5s \ --display-format=simple \ --concurrency=100 \ --tolerate-errors \ --workload B \ $DATABASE_URL
I230510 15:23:42.835123 1 workload/cli/run.go:460 [-] 3 creating load generator... done (took 4m50.264092916s)
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
5.0s 0 14.1 833.2 113.2 125.8 385.9 604.0 read
5.0s 0 0.8 47.0 113.2 130.0 134.2 436.2 update
10.0s 0 851.5 842.3 109.1 125.8 243.3 570.4 read
10.0s 0 44.0 45.5 113.2 125.8 134.2 419.4 update
15.0s 0 857.2 847.2 109.1 121.6 419.4 436.2 read
15.0s 0 53.4 48.1 109.1 130.0 419.4 419.4 update
20.0s 0 811.2 838.2 117.4 130.0 419.4 838.9 read
20.0s 0 42.0 46.6 117.4 142.6 419.4 436.2 update
25.0s 0 866.2 843.8 109.1 125.8 218.1 520.1 read
25.0s 0 42.8 45.8 109.1 130.0 142.6 453.0 update
30.0s 0 871.3 848.4 109.1 121.6 130.0 503.3 read
30.0s 0 42.0 45.2 113.2 125.8 234.9 419.4 update
35.0s 0 860.9 850.2 109.1 125.8 251.7 536.9 read
35.0s 0 50.4 45.9 109.1 125.8 134.2 436.2 update
40.0s 0 816.7 846.0 109.1 121.6 419.4 637.5 read
40.0s 0 43.8 45.7 113.2 125.8 419.4 436.2 update
45.0s 0 876.1 849.3 109.1 121.6 201.3 570.4 read
45.0s 0 44.0 45.5 113.2 125.8 134.2 419.4 update
50.0s 0 910.8 855.5 100.7 121.6 125.8 436.2 read
50.0s 0 50.2 46.0 104.9 121.6 130.0 419.4 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
55.0s 0 843.7 854.4 113.2 125.8 142.6 637.5 read
55.0s 0 40.2 45.4 113.2 130.0 134.2 142.6 update
60.0s 0 825.4 852.0 113.2 125.8 419.4 453.0 read
60.0s 0 43.6 45.3 117.4 130.0 419.4 436.2 update
65.0s 0 824.8 849.9 113.2 125.8 419.4 536.9 read
65.0s 0 47.4 45.4 117.4 125.8 419.4 503.3 update
70.0s 0 889.9 852.8 104.9 121.6 125.8 536.9 read
70.0s 0 45.0 45.4 109.1 125.8 130.0 503.3 update
75.0s 0 811.4 850.0 113.2 121.6 419.4 771.8 read
75.0s 0 49.4 45.7 117.4 130.0 402.7 419.4 update
80.0s 0 835.2 849.1 109.1 121.6 419.4 838.9 read
80.0s 0 46.6 45.7 113.2 125.8 130.0 503.3 update
85.0s 0 847.1 849.0 109.1 125.8 419.4 671.1 read
85.0s 0 41.4 45.5 113.2 130.0 251.7 436.2 update
90.0s 0 844.8 848.7 109.1 117.4 402.7 453.0 read
90.0s 0 49.8 45.7 113.2 121.6 130.0 130.0 update
95.0s 0 847.7 848.7 109.1 125.8 419.4 671.1 read
95.0s 0 39.0 45.4 113.2 130.0 436.2 536.9 update
100.0s 0 850.8 848.8 113.2 121.6 209.7 536.9 read
100.0s 0 46.0 45.4 113.2 125.8 142.6 503.3 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
105.0s 0 825.1 847.7 113.2 125.8 419.4 671.1 read
105.0s 0 44.2 45.3 117.4 130.0 142.6 419.4 update
110.0s 0 875.5 848.9 109.1 125.8 201.3 771.8 read
110.0s 0 43.4 45.3 113.2 130.0 134.2 142.6 update
115.0s 0 830.5 848.1 113.2 134.2 419.4 520.1 read
115.0s 0 41.2 45.1 113.2 134.2 142.6 419.4 update
120.0s 0 840.9 847.8 113.2 125.8 130.0 637.5 read
120.0s 0 47.2 45.2 113.2 130.0 134.2 134.2 update
125.0s 0 852.4 848.0 109.1 125.8 130.0 453.0 read
125.0s 0 42.2 45.0 109.1 125.8 134.2 436.2 update
130.0s 0 834.4 847.5 113.2 125.8 352.3 570.4 read
130.0s 0 47.2 45.1 113.2 130.0 159.4 436.2 update
135.0s 0 830.0 846.8 113.2 125.8 419.4 453.0 read
135.0s 0 45.4 45.1 117.4 130.0 151.0 436.2 update
140.0s 0 838.7 846.5 109.1 125.8 419.4 503.3 read
140.0s 0 46.6 45.2 113.2 134.2 142.6 453.0 update
145.0s 0 840.0 846.3 113.2 121.6 130.0 536.9 read
145.0s 0 42.6 45.1 113.2 125.8 134.2 436.2 update
150.0s 0 876.3 847.3 109.1 121.6 402.7 453.0 read
150.0s 0 43.2 45.0 113.2 125.8 419.4 436.2 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
155.0s 0 861.4 847.8 109.1 125.8 243.3 503.3 read
155.0s 0 40.8 44.9 113.2 134.2 419.4 453.0 update
160.0s 0 824.2 847.0 113.2 125.8 419.4 453.0 read
160.0s 0 43.4 44.9 113.2 130.0 419.4 436.2 update
165.0s 0 829.1 846.5 109.1 125.8 419.4 536.9 read
165.0s 0 47.4 44.9 113.2 130.0 436.2 436.2 update
170.0s 0 855.5 846.8 113.2 125.8 142.6 520.1 read
170.0s 0 52.0 45.1 113.2 130.0 142.6 436.2 update
175.0s 0 839.6 846.5 113.2 125.8 419.4 469.8 read
175.0s 0 39.0 45.0 113.2 130.0 159.4 436.2 update
180.0s 0 855.5 846.8 113.2 125.8 134.2 436.2 read
180.0s 0 46.2 45.0 113.2 130.0 436.2 436.2 update
185.0s 0 851.7 846.9 109.1 125.8 251.7 536.9 read
185.0s 0 47.0 45.1 109.1 125.8 134.2 419.4 update
190.0s 0 832.2 846.5 113.2 121.6 419.4 570.4 read
190.0s 0 43.4 45.0 113.2 125.8 419.4 453.0 update
195.0s 0 818.6 845.8 113.2 125.8 419.4 536.9 read
195.0s 0 42.4 44.9 117.4 130.0 419.4 536.9 update
200.0s 0 827.4 845.4 113.2 125.8 419.4 637.5 read
200.0s 0 43.6 44.9 113.2 130.0 419.4 436.2 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
205.0s 0 835.4 845.1 113.2 125.8 419.4 453.0 read
205.0s 0 43.0 44.9 117.4 134.2 419.4 436.2 update
210.0s 0 854.0 845.3 109.1 121.6 125.8 436.2 read
210.0s 0 42.4 44.8 113.2 125.8 142.6 419.4 update
215.0s 0 824.3 844.8 113.2 125.8 419.4 671.1 read
215.0s 0 42.6 44.8 117.4 130.0 436.2 520.1 update
220.0s 0 852.2 845.0 113.2 125.8 142.6 453.0 read
220.0s 0 43.4 44.7 117.4 130.0 402.7 419.4 update
225.0s 0 860.7 845.4 109.1 121.6 402.7 520.1 read
225.0s 0 42.4 44.7 113.2 121.6 134.2 436.2 update
230.0s 0 832.2 845.1 113.2 125.8 419.4 453.0 read
230.0s 0 40.6 44.6 117.4 130.0 419.4 436.2 update
235.0s 0 807.4 844.3 113.2 125.8 419.4 503.3 read
235.0s 0 43.4 44.6 117.4 130.0 419.4 453.0 update
240.0s 0 886.2 845.1 109.1 121.6 125.8 704.6 read
240.0s 0 46.0 44.6 109.1 125.8 130.0 436.2 update
245.0s 0 881.4 845.9 109.1 121.6 125.8 671.1 read
245.0s 0 46.6 44.6 109.1 125.8 134.2 419.4 update
250.0s 0 850.0 846.0 109.1 125.8 159.4 453.0 read
250.0s 0 46.6 44.7 113.2 130.0 151.0 159.4 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
255.0s 0 864.9 846.3 109.1 125.8 134.2 671.1 read
255.0s 0 46.6 44.7 113.2 125.8 402.7 436.2 update
260.0s 0 839.1 846.2 113.2 125.8 419.4 503.3 read
260.0s 0 47.0 44.7 113.2 134.2 436.2 436.2 update
265.0s 0 865.6 846.6 100.7 125.8 402.7 536.9 read
265.0s 0 47.8 44.8 109.1 130.0 167.8 419.4 update
270.0s 0 841.0 846.5 113.2 130.0 419.4 453.0 read
270.0s 0 46.4 44.8 117.4 130.0 134.2 453.0 update
275.0s 0 890.1 847.3 109.1 121.6 151.0 671.1 read
275.0s 0 43.8 44.8 113.2 125.8 419.4 503.3 update
280.0s 0 936.7 848.9 100.7 117.4 121.6 637.5 read
280.0s 0 48.8 44.9 104.9 117.4 125.8 130.0 update
285.0s 0 920.8 850.1 100.7 117.4 130.0 503.3 read
285.0s 0 49.4 45.0 104.9 121.6 130.0 402.7 update
290.0s 0 927.4 851.5 100.7 117.4 125.8 503.3 read
290.0s 0 47.6 45.0 104.9 117.4 125.8 130.0 update
295.0s 0 925.4 852.7 104.9 117.4 121.6 503.3 read
295.0s 0 47.0 45.0 109.1 121.6 125.8 226.5 update
300.0s 0 939.3 854.1 100.7 117.4 121.6 604.0 read
300.0s 0 54.0 45.2 104.9 117.4 243.3 402.7 update
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
300.0s 0 256245 854.1 111.1 109.1 125.8 402.7 838.9 read
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
300.0s 0 13559 45.2 113.7 113.2 130.0 402.7 536.9 update
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result
300.0s 0 269804 899.3 111.2 109.1 125.8 402.7 838.9
The majority of the read queries are lookups on the key. The table schema looks like so:
table_name | create_statement
-------------+------------------------------------------------------------
usertable | CREATE TABLE public.usertable (
| ycsb_key VARCHAR(255) NOT NULL,
| field0 STRING NOT NULL,
| field1 STRING NOT NULL,
| field2 STRING NOT NULL,
| field3 STRING NOT NULL,
| field4 STRING NOT NULL,
| field5 STRING NOT NULL,
| field6 STRING NOT NULL,
| field7 STRING NOT NULL,
| field8 STRING NOT NULL,
| field9 STRING NOT NULL,
| CONSTRAINT usertable_pkey PRIMARY KEY (ycsb_key ASC),
| FAMILY fam_0_ycsb_key (ycsb_key),
| FAMILY fam_1_field0 (field0),
| FAMILY fam_2_field1 (field1),
| FAMILY fam_3_field2 (field2),
| FAMILY fam_4_field3 (field3),
| FAMILY fam_5_field4 (field4),
| FAMILY fam_6_field5 (field5),
| FAMILY fam_7_field6 (field6),
| FAMILY fam_8_field7 (field7),
| FAMILY fam_9_field8 (field8),
| FAMILY fam_10_field9 (field9)
| )
This is beneficial to using PolyScale as we can cache these lookups.
We can now run the same workload against the PolyScale cache:
Let's store the connection URL in an environment variable just like before.
export DATABASE_URL="postgres://artem:password@psedge.global:5432/ycsb?application_name=<application_name_hash>"
Run the workload:
I230510 18:44:55.372022 1 workload/cli/run.go:622 [-] 1 random seed: 13993795009590803103 I230510 18:44:55.372060 1 workload/cli/run.go:429 [-] 2 creating load generator... W230510 18:44:55.553757 1 workload/cli/run.go:438 [-] 3 retrying after error while creating load: failed to initialize the load generator: pq: User unknown to PolyScale W230510 18:44:55.707697 1 workload/cli/run.go:438 [-] 4 retrying after error while creating load: failed to initialize the load generator: pq: User unknown to PolyScale W230510 18:44:55.948552 1 workload/cli/run.go:438 [-] 5 retrying after error while creating load: failed to initialize the load generator: pq: User unknown to PolyScale
As indicated above, PolyScale identifies a user with the application_name parameter set to the cacheId for the cache. If you are using a client where you cannot change the application_name parameter, you need to instead create a database user that has a username of the cacheId. Otherwise, you will see an error that says User unknown to PolyScale. In our case, the ycsb workload has application_name hard coded to ycsb. The common workaround in any situation is to either hardcode the cache ID or change the SQL username to the cache ID.
With the new user created, then we can change the environment variable.
export DATABASE_URL="postgres://your_cache_id:password@psedge.global:5432/ycsb"
To make things a bit more interesting, let's look at the PolyScale cache summary page to understand the effectiveness of the cache.
I've been testing the product for the past couple of days, and the summary page was cluttered with old information. Today the product does not allow me to clear the stats and I created a new cache specifically for this test. Once we run queries against it, it will update with the necessary information.
Finally, we can run the YCSB Workload B:
cockroach workload run ycsb \ --duration=5m \ --display-every=5s \ --display-format=simple \ --concurrency=100 \ --tolerate-errors \ --workload B \ $DATABASE_URL
I230510 15:35:40.198227 1 workload/cli/run.go:460 [-] 3 creating load generator... done (took 5m4.394515542s)
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
5.0s 0 15.4 954.8 104.9 151.0 192.9 352.3 read
5.0s 0 0.8 52.0 109.1 151.0 192.9 243.3 update
10.0s 0 1040.9 997.7 100.7 159.4 335.5 738.2 read
10.0s 0 51.8 51.9 104.9 151.0 234.9 369.1 update
15.0s 0 962.6 986.0 109.1 184.5 318.8 939.5 read
15.0s 0 47.0 50.3 125.8 192.9 335.5 369.1 update
20.0s 0 1406.7 1091.1 92.3 121.6 243.3 536.9 read
20.0s 0 70.2 55.2 100.7 134.2 268.4 369.1 update
25.0s 0 1581.2 1189.2 31.5 117.4 134.2 251.7 read
25.0s 0 84.0 61.0 104.9 125.8 142.6 159.4 update
30.0s 0 1607.8 1258.9 28.3 113.2 159.4 268.4 read
30.0s 0 83.6 64.8 100.7 134.2 192.9 201.3 update
35.0s 0 1297.6 1264.5 46.1 142.6 318.8 1073.7 read
35.0s 0 70.2 65.5 117.4 151.0 318.8 335.5 update
40.0s 0 1554.3 1300.7 37.7 134.2 226.5 520.1 read
40.0s 0 78.4 67.1 109.1 159.4 318.8 503.3 update
45.0s 0 1966.0 1374.6 23.1 109.1 125.8 318.8 read
45.0s 0 103.8 71.2 100.7 121.6 134.2 318.8 update
50.0s 0 1579.2 1395.1 29.4 113.2 192.9 251.7 read
50.0s 0 82.8 72.4 100.7 130.0 218.1 251.7 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
55.0s 0 939.3 1353.7 100.7 117.4 130.0 226.5 read
55.0s 0 49.8 70.3 104.9 125.8 134.2 134.2 update
60.0s 0 917.0 1317.3 100.7 121.6 184.5 260.0 read
60.0s 0 48.4 68.5 104.9 142.6 192.9 243.3 update
65.0s 0 751.1 1273.7 113.2 234.9 318.8 704.6 read
65.0s 0 38.4 66.2 121.6 285.2 302.0 771.8 update
70.0s 0 839.4 1242.7 109.1 151.0 285.2 369.1 read
70.0s 0 40.6 64.4 113.2 142.6 151.0 285.2 update
75.0s 0 933.3 1222.1 100.7 117.4 142.6 243.3 read
75.0s 0 46.4 63.2 104.9 121.6 151.0 167.8 update
80.0s 0 933.7 1204.0 100.7 121.6 142.6 318.8 read
80.0s 0 45.2 62.0 104.9 121.6 142.6 184.5 update
85.0s 0 892.1 1185.7 104.9 121.6 134.2 352.3 read
85.0s 0 51.4 61.4 109.1 125.8 142.6 142.6 update
90.0s 0 644.4 1155.6 142.6 285.2 318.8 738.2 read
90.0s 0 32.2 59.8 142.6 243.3 302.0 335.5 update
95.0s 0 748.8 1134.2 125.8 184.5 209.7 352.3 read
95.0s 0 36.4 58.6 125.8 176.2 209.7 268.4 update
100.0s 0 872.0 1121.1 109.1 134.2 159.4 251.7 read
100.0s 0 44.6 57.9 113.2 130.0 142.6 176.2 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
105.0s 0 904.4 1110.8 104.9 125.8 142.6 335.5 read
105.0s 0 50.0 57.5 109.1 130.0 151.0 167.8 update
110.0s 0 844.1 1098.7 109.1 142.6 159.4 335.5 read
110.0s 0 47.6 57.0 113.2 134.2 159.4 268.4 update
115.0s 0 678.4 1080.4 130.0 285.2 318.8 671.1 read
115.0s 0 35.8 56.1 134.2 201.3 318.8 335.5 update
120.0s 0 830.8 1070.0 113.2 142.6 201.3 352.3 read
120.0s 0 40.0 55.4 113.2 134.2 159.4 176.2 update
125.0s 0 866.8 1061.9 109.1 151.0 285.2 352.3 read
125.0s 0 42.4 54.9 109.1 159.4 302.0 318.8 update
130.0s 0 874.6 1054.7 109.1 134.2 167.8 260.0 read
130.0s 0 43.2 54.5 113.2 142.6 159.4 167.8 update
135.0s 0 806.2 1045.5 109.1 167.8 318.8 671.1 read
135.0s 0 44.8 54.1 109.1 159.4 285.2 352.3 update
140.0s 0 737.9 1034.5 121.6 218.1 318.8 637.5 read
140.0s 0 39.6 53.6 125.8 184.5 302.0 335.5 update
145.0s 0 853.1 1028.2 109.1 142.6 176.2 352.3 read
145.0s 0 45.2 53.3 113.2 151.0 176.2 268.4 update
150.0s 0 889.4 1023.6 104.9 134.2 167.8 335.5 read
150.0s 0 45.2 53.0 109.1 134.2 176.2 176.2 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
155.0s 0 888.0 1019.2 104.9 134.2 176.2 335.5 read
155.0s 0 48.4 52.9 109.1 142.6 167.8 192.9 update
160.0s 0 773.5 1011.5 113.2 201.3 318.8 939.5 read
160.0s 0 42.2 52.5 121.6 159.4 318.8 637.5 update
165.0s 0 1002.0 1011.3 113.2 159.4 192.9 369.1 read
165.0s 0 54.0 52.6 125.8 167.8 209.7 318.8 update
170.0s 0 1294.3 1019.6 54.5 151.0 243.3 402.7 read
170.0s 0 66.4 53.0 113.2 201.3 243.3 318.8 update
175.0s 0 1718.8 1039.6 28.3 117.4 142.6 285.2 read
175.0s 0 93.8 54.2 104.9 125.8 159.4 318.8 update
180.0s 0 1838.6 1061.7 26.2 117.4 167.8 671.1 read
180.0s 0 99.2 55.4 104.9 142.6 260.0 318.8 update
185.0s 0 1359.8 1069.8 46.1 151.0 285.2 1006.6 read
185.0s 0 70.8 55.8 130.0 201.3 335.5 453.0 update
190.0s 0 2097.0 1096.8 25.2 117.4 167.8 335.5 read
190.0s 0 106.6 57.2 109.1 134.2 226.5 285.2 update
195.0s 0 2127.7 1123.3 23.1 113.2 151.0 335.5 read
195.0s 0 111.6 58.6 104.9 151.0 251.7 318.8 update
200.0s 0 2206.6 1150.4 22.0 109.1 134.2 352.3 read
200.0s 0 111.2 59.9 104.9 130.0 209.7 302.0 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
205.0s 0 1436.5 1157.3 41.9 159.4 243.3 1409.3 read
205.0s 0 77.0 60.3 121.6 209.7 318.8 419.4 update
210.0s 0 1578.2 1167.3 37.7 151.0 251.7 520.1 read
210.0s 0 84.2 60.9 117.4 209.7 318.8 402.7 update
215.0s 0 2116.6 1189.4 24.1 113.2 167.8 285.2 read
215.0s 0 106.8 61.9 104.9 130.0 243.3 268.4 update
220.0s 0 1326.0 1192.5 96.5 117.4 192.9 335.5 read
220.0s 0 60.4 61.9 104.9 151.0 243.3 285.2 update
225.0s 0 902.1 1186.1 100.7 130.0 167.8 352.3 read
225.0s 0 53.2 61.7 104.9 134.2 184.5 243.3 update
230.0s 0 696.1 1175.4 130.0 234.9 318.8 704.6 read
230.0s 0 36.0 61.1 134.2 268.4 318.8 671.1 update
235.0s 0 828.0 1168.0 113.2 151.0 176.2 318.8 read
235.0s 0 45.6 60.8 113.2 142.6 167.8 218.1 update
240.0s 0 884.1 1162.1 104.9 125.8 192.9 503.3 read
240.0s 0 48.2 60.6 104.9 130.0 176.2 234.9 update
245.0s 0 900.0 1156.8 104.9 134.2 184.5 251.7 read
245.0s 0 47.2 60.3 104.9 159.4 184.5 218.1 update
250.0s 0 783.3 1149.3 113.2 167.8 302.0 671.1 read
250.0s 0 46.6 60.0 117.4 167.8 302.0 318.8 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
255.0s 0 754.3 1141.6 121.6 176.2 268.4 436.2 read
255.0s 0 40.6 59.6 121.6 167.8 251.7 302.0 update
260.0s 0 887.2 1136.7 104.9 130.0 159.4 335.5 read
260.0s 0 48.0 59.4 109.1 142.6 167.8 184.5 update
265.0s 0 852.3 1131.3 109.1 142.6 167.8 335.5 read
265.0s 0 53.0 59.3 109.1 142.6 167.8 192.9 update
270.0s 0 913.8 1127.3 104.9 125.8 142.6 335.5 read
270.0s 0 50.4 59.1 104.9 134.2 151.0 176.2 update
275.0s 0 647.6 1118.5 130.0 285.2 352.3 838.9 read
275.0s 0 36.4 58.7 130.0 285.2 352.3 704.6 update
280.0s 0 740.7 1111.8 121.6 209.7 251.7 352.3 read
280.0s 0 37.8 58.3 125.8 209.7 243.3 251.7 update
285.0s 0 805.1 1106.4 109.1 184.5 218.1 352.3 read
285.0s 0 44.4 58.1 113.2 184.5 218.1 352.3 update
290.0s 0 881.8 1102.5 109.1 134.2 176.2 260.0 read
290.0s 0 45.2 57.9 109.1 134.2 176.2 268.4 update
295.0s 0 904.2 1099.2 104.9 125.8 142.6 335.5 read
295.0s 0 44.6 57.6 109.1 130.0 151.0 167.8 update
300.0s 0 680.0 1092.2 130.0 226.5 302.0 1006.6 read
300.0s 0 31.2 57.2 134.2 226.5 318.8 704.6 update
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
300.0s 0 327660 1092.2 85.5 100.7 151.0 234.9 1409.3 read
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
300.0s 0 17160 57.2 115.5 109.1 159.4 268.4 771.8 update
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result
300.0s 0 344820 1149.4 87.0 100.7 151.0 243.3 1409.3
It may be hard to see, but if we look at the very bottom of each run, we can quickly see the difference:
CockroachDB directly:
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total 300.0s 0 256245 854.1 111.1 109.1 125.8 402.7 838.9 read _elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total 300.0s 0 13559 45.2 113.7 113.2 130.0 402.7 536.9 update _elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result 300.0s 0 269804 899.3 111.2 109.1 125.8 402.7 838.9
PolyScale:
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total 300.0s 0 327660 1092.2 85.5 100.7 151.0 234.9 1409.3 read _elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total 300.0s 0 17160 57.2 115.5 109.1 159.4 268.4 771.8 update _elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result 300.0s 0 344820 1149.4 87.0 100.7 151.0 243.3 1409.3
We're seeing a speedup across total ops, ops/sec, and reduced p50, p99, and pMax. Not sure why p95 is higher for PolyScale, but overall this is good.
Let's switch to the PolyScale summary page:
Our workload is about 48% effective in cache hits. Let's now run a read-only workload to understand the behavior when there are 100% reads.
YCSB Workload C
CockroachDB:
cockroach workload run ycsb \ --duration=5m \ --display-every=5s \ --display-format=simple \ --concurrency=100 \ --tolerate-errors \ --workload C \ $DATABASE_URL
I230510 14:33:48.366710 1 workload/cli/run.go:460 [-] 3 creating load generator... done (took 4m37.746131792s)
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
5.0s 0 13.0 732.8 125.8 167.8 436.2 520.1 read
10.0s 0 754.6 743.7 121.6 159.4 436.2 637.5 read
15.0s 0 777.2 754.9 121.6 142.6 436.2 838.9 read
20.0s 0 725.1 747.4 130.0 159.4 453.0 872.4 read
25.0s 0 889.5 775.8 109.1 134.2 436.2 805.3 read
30.0s 0 932.6 802.0 109.1 117.4 125.8 503.3 read
35.0s 0 961.6 824.8 104.9 117.4 125.8 436.2 read
40.0s 0 981.7 844.4 100.7 113.2 125.8 503.3 read
45.0s 0 886.1 849.0 109.1 130.0 402.7 436.2 read
50.0s 0 774.8 841.6 121.6 142.6 436.2 570.4 read
55.0s 0 796.9 837.5 121.6 134.2 436.2 469.8 read
60.0s 0 755.5 830.7 121.6 151.0 436.2 671.1 read
65.0s 0 743.5 824.0 121.6 151.0 453.0 805.3 read
70.0s 0 766.0 819.9 125.8 176.2 453.0 604.0 read
75.0s 0 686.3 810.9 121.6 352.3 536.9 604.0 read
80.0s 0 785.4 809.4 121.6 142.6 436.2 503.3 read
85.0s 0 769.1 807.0 121.6 142.6 436.2 738.2 read
90.0s 0 783.8 805.7 121.6 142.6 436.2 570.4 read
95.0s 0 813.2 806.1 117.4 134.2 419.4 805.3 read
100.0s 0 768.2 804.2 121.6 142.6 436.2 838.9 read
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
105.0s 0 789.8 803.5 121.6 134.2 436.2 771.8 read
110.0s 0 754.4 801.3 125.8 151.0 436.2 671.1 read
115.0s 0 751.0 799.1 125.8 159.4 436.2 604.0 read
120.0s 0 780.2 798.3 125.8 151.0 436.2 671.1 read
125.0s 0 805.6 798.6 121.6 151.0 436.2 520.1 read
130.0s 0 896.7 802.4 109.1 130.0 318.8 536.9 read
135.0s 0 958.9 808.2 104.9 121.6 134.2 453.0 read
140.0s 0 921.7 812.2 109.1 121.6 125.8 436.2 read
145.0s 0 905.5 815.4 109.1 121.6 130.0 671.1 read
150.0s 0 880.7 817.6 113.2 125.8 402.7 570.4 read
155.0s 0 886.6 819.8 113.2 125.8 402.7 486.5 read
160.0s 0 892.7 822.1 109.1 125.8 402.7 436.2 read
165.0s 0 901.9 824.5 109.1 121.6 134.2 469.8 read
170.0s 0 876.0 826.1 113.2 130.0 142.6 453.0 read
175.0s 0 933.3 829.1 104.9 121.6 151.0 453.0 read
180.0s 0 922.6 831.7 104.9 125.8 151.0 469.8 read
185.0s 0 886.2 833.2 109.1 125.8 419.4 469.8 read
190.0s 0 906.8 835.1 109.1 130.0 142.6 570.4 read
195.0s 0 904.4 836.9 109.1 125.8 209.7 671.1 read
200.0s 0 896.3 838.4 113.2 125.8 134.2 436.2 read
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
205.0s 0 922.1 840.4 104.9 121.6 402.7 436.2 read
210.0s 0 899.6 841.8 109.1 125.8 385.9 838.9 read
215.0s 0 893.2 843.0 109.1 125.8 134.2 453.0 read
220.0s 0 909.5 844.5 109.1 121.6 402.7 536.9 read
225.0s 0 917.6 846.2 109.1 130.0 134.2 520.1 read
230.0s 0 887.1 847.1 109.1 130.0 142.6 503.3 read
235.0s 0 890.6 848.0 113.2 125.8 419.4 520.1 read
240.0s 0 905.5 849.2 109.1 125.8 130.0 520.1 read
245.0s 0 949.6 851.2 104.9 117.4 125.8 738.2 read
250.0s 0 869.6 851.6 113.2 130.0 419.4 520.1 read
255.0s 0 920.7 853.0 104.9 121.6 130.0 536.9 read
260.0s 0 914.2 854.1 109.1 125.8 130.0 536.9 read
265.0s 0 870.5 854.4 113.2 125.8 419.4 771.8 read
270.0s 0 877.6 854.9 109.1 130.0 419.4 503.3 read
275.0s 0 934.5 856.3 109.1 121.6 130.0 436.2 read
280.0s 0 909.4 857.3 109.1 125.8 134.2 503.3 read
285.0s 0 889.1 857.8 113.2 130.0 159.4 536.9 read
290.0s 0 829.1 857.3 121.6 142.6 419.4 453.0 read
295.0s 0 840.0 857.0 121.6 142.6 419.4 469.8 read
300.0s 0 820.2 856.4 117.4 134.2 419.4 738.2 read
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
300.0s 0 256926 856.4 116.7 113.2 134.2 419.4 872.4 read
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result
300.0s 0 256926 856.4 116.7 113.2 134.2 419.4 872.4
PolyScale:
230510 17:42:50.929786 1 workload/cli/run.go:460 [-] 3 creating load generator... done (took 5m3.332333041s)
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
5.0s 0 17.8 1098.0 96.5 125.8 176.2 335.5 read
10.0s 0 1384.6 1241.1 92.3 113.2 142.6 385.9 read
15.0s 0 1320.9 1267.8 92.3 142.6 234.9 637.5 read
20.0s 0 1176.9 1245.0 100.7 159.4 251.7 738.2 read
25.0s 0 1619.8 1320.0 88.1 121.6 167.8 318.8 read
30.0s 0 1737.6 1389.6 32.5 109.1 125.8 251.7 read
35.0s 0 1735.7 1439.0 30.4 113.2 142.6 335.5 read
40.0s 0 1100.3 1396.7 75.5 218.1 352.3 1073.7 read
45.0s 0 1504.0 1408.6 46.1 142.6 176.2 369.1 read
50.0s 0 1769.9 1444.7 30.4 117.4 142.6 335.5 read
55.0s 0 1711.0 1468.9 31.5 121.6 176.2 251.7 read
60.0s 0 1655.3 1484.5 35.7 121.6 159.4 570.4 read
65.0s 0 1169.2 1460.2 62.9 192.9 335.5 1543.5 read
70.0s 0 1744.9 1480.6 32.5 121.6 142.6 352.3 read
75.0s 0 1741.3 1497.9 30.4 125.8 226.5 469.8 read
80.0s 0 1920.5 1524.4 25.2 109.1 134.2 335.5 read
85.0s 0 1415.1 1517.9 48.2 151.0 302.0 973.1 read
90.0s 0 1585.5 1521.7 44.0 134.2 159.4 453.0 read
95.0s 0 1846.7 1538.8 27.3 113.2 151.0 335.5 read
100.0s 0 1893.2 1556.5 26.2 109.1 134.2 260.0 read
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
105.0s 0 1896.9 1572.7 26.2 113.2 142.6 302.0 read
110.0s 0 1136.0 1552.9 60.8 192.9 318.8 1073.7 read
115.0s 0 1780.1 1562.7 32.5 121.6 142.6 369.1 read
120.0s 0 1943.9 1578.6 26.2 109.1 125.8 453.0 read
125.0s 0 2003.5 1595.6 24.1 109.1 121.6 335.5 read
130.0s 0 1674.4 1598.6 37.7 125.8 226.5 704.6 read
135.0s 0 1230.6 1585.0 58.7 192.9 302.0 1006.6 read
140.0s 0 1833.8 1593.9 28.3 121.6 151.0 402.7 read
145.0s 0 1798.1 1600.9 29.4 121.6 167.8 352.3 read
150.0s 0 2037.5 1615.5 24.1 109.1 117.4 335.5 read
155.0s 0 1334.2 1606.4 52.4 167.8 318.8 738.2 read
160.0s 0 1634.6 1607.3 39.8 142.6 192.9 318.8 read
165.0s 0 970.6 1588.0 104.9 125.8 134.2 335.5 read
170.0s 0 1193.2 1576.4 100.7 121.6 167.8 285.2 read
175.0s 0 1413.8 1571.7 96.5 134.2 226.5 704.6 read
180.0s 0 1348.0 1565.5 56.6 142.6 285.2 453.0 read
185.0s 0 1836.5 1572.9 27.3 109.1 125.8 176.2 read
190.0s 0 2030.2 1584.9 23.1 104.9 117.4 335.5 read
195.0s 0 2008.1 1595.7 24.1 113.2 142.6 260.0 read
200.0s 0 1669.5 1597.6 37.7 134.2 243.3 1073.7 read
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
205.0s 0 1506.2 1595.4 46.1 142.6 226.5 637.5 read
210.0s 0 1680.9 1597.4 35.7 113.2 125.8 352.3 read
215.0s 0 1105.0 1585.9 100.7 134.2 184.5 318.8 read
220.0s 0 1573.3 1585.7 92.3 109.1 121.6 268.4 read
225.0s 0 1169.8 1576.4 65.0 184.5 318.8 906.0 read
230.0s 0 1700.8 1579.1 32.5 130.0 192.9 503.3 read
235.0s 0 1958.2 1587.2 25.2 113.2 134.2 318.8 read
240.0s 0 1996.5 1595.7 25.2 109.1 151.0 318.8 read
245.0s 0 1011.0 1583.8 104.9 134.2 201.3 369.1 read
250.0s 0 858.2 1569.3 113.2 159.4 302.0 671.1 read
255.0s 0 1172.7 1561.5 100.7 125.8 151.0 260.0 read
260.0s 0 1514.6 1560.6 92.3 121.6 167.8 260.0 read
265.0s 0 1877.9 1566.6 26.2 109.1 130.0 209.7 read
270.0s 0 1500.3 1565.3 46.1 159.4 302.0 637.5 read
275.0s 0 1304.1 1560.6 54.5 176.2 243.3 604.0 read
280.0s 0 888.0 1548.6 109.1 234.9 385.9 486.5 read
285.0s 0 954.8 1538.2 104.9 121.6 142.6 260.0 read
290.0s 0 1069.4 1530.1 100.7 121.6 130.0 352.3 read
295.0s 0 1105.8 1522.9 104.9 159.4 302.0 906.0 read
300.0s 0 1830.9 1528.0 26.2 104.9 117.4 335.5 read
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
300.0s 0 458408 1528.0 65.4 54.5 130.0 209.7 1543.5 read
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result
300.0s 0 458408 1528.0 65.4 54.5 130.0 209.7 1543.5
Let's again compare side by side.
CockroachDB:
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total 300.0s 0 256926 856.4 116.7 113.2 134.2 419.4 872.4 read _elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result 300.0s 0 256926 856.4 116.7 113.2 134.2 419.4 872.4
PolyScale:
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total 300.0s 0 458408 1528.0 65.4 54.5 130.0 209.7 1543.5 read _elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result 300.0s 0 458408 1528.0 65.4 54.5 130.0 209.7 1543.5
Here is where we are seeing almost 2x performance improvement over CockroachDB. Let's again look at the summary page.
We're now getting 53% cache hits cumulatively with Workload B. The number of total queries has grown significantly and we can see the cache contributes to the performance heavily in this case.
PolyScale Self-Hosted
As the last thing in our experiment, I would like to evaluate the PolyScale Self-Hosted option, which allows customers to deploy their own PoP in the location of their choosing. This should potentially improve the performance and latency of the customer application.
Let's run all of the tests to date using the local PoP. You have to follow the PolyScale docs to stand up an instance of the PolyScale Docker environment running a local cache. You will need an API key, which at the time of this writing requires filling out a form.
Then start the container using the following command:
docker run -e PCE_API_KEY=<your_api_key> \ -p 26257:9010 \ ghcr.io/polyscale/pce:stable
Port 9010 is the internal port where PolyScale exposes the PostgreSQL protocol. We now have the flexibility to map the external port to the native CockroachDB port 26257 and stop using the PostgreSQL port 5432 like the hosted PolyScale cache. Then, all we have to do is swap the psedge.global for 127.0.0.1 and the port of your choice. You can reuse the same cache as before.
The added benefit of this method is the breadth of observability you get. My Docker logs show the following:
[2023-05-11 18:38:09.381][43][info][filter] [src/filters/postgres/parsers_backend.cpp:124] [314][22] closing state for describe after no data found [2023-05-11 18:38:09.485][43][info][filter] [src/filters/postgres/parsers_backend.cpp:124] [314][23] closing state for describe after no data found [2023-05-11 18:38:09.593][43][info][filter] [src/filters/postgres/parsers_backend.cpp:124] [314][24] closing state for describe after no data found
Finally, connect to the PoP using the following command and the cockroach client:
cockroach sql --url "postgresql://your_cache_id:password@127.0.0.1:26257/ycsb?sslmode=require"
artem@127.0.0.1:26257/ycsb> select 1;
?column?
------------
1
Time: 89ms total (execution 1ms / network 88ms)
artem@127.0.0.1:26257/ycsb> select 1;
?column?
------------
1
Time: 5ms total (execution 0ms / network 5ms)
Would you look at that? The network latency is now 5ms - a big improvement over our original attempt going through Clifton PoP. Recall also that our execution latency was consistently 452 ms, i.e., Time: 99ms total (execution 452ms / network 99ms), and I originally said let's leave that aside. Well, I'm relieved to see that that execution latency is gone now that we're deploying a PoP locally to the client.
For posterity, here's the test using psql client.
psql "host=127.0.0.1 \ sslmode=require \ port=26257 \ user='username' \ dbname='defaultdb' \ application_name='<your_cache_id>'"
defaultdb=> select 1;
?column?
----------
1
(1 row)
Time: 93.111 ms
defaultdb=> select 1;
?column?
----------
1
(1 row)
Time: 6.727 ms
Let's insert a few records and measure:
artem@127.0.0.1:26257/ycsb> insert into test (val) values (1), (2), (3), (4), (5) returning id;
id
----------------------------------------
806dcb98-0be8-43b2-aa73-8b7805111e99
f2a17d1b-2fca-40be-82a3-21403a63a1e9
44803c21-3e38-492f-abe9-2e81e46e063a
58b622b2-cacc-4d15-be47-d5ff280df848
c0b417d9-5306-4e65-be34-7156265c86f2
Time: 94ms total (execution 5ms / network 90ms)
Not a huge improvement. Let's see the YCSB workload B next.
Export DATABASE_URL="postgres://your_cache_id:password@127.0.0.1:26257/ycsb".
cockroach workload run ycsb \ --duration=5m \ --display-every=5s \ --display-format=simple \ --concurrency=100 \ --tolerate-errors \ --workload B \ $DATABASE_URL
I230511 18:22:07.713241 1 workload/cli/run.go:460 [-] 3 creating load generator... done (took 4m49.643270167s)
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
5.0s 0 27.8 1641.0 83.9 88.1 121.6 159.4 read
5.0s 0 1.3 77.8 88.1 92.3 125.8 159.4 update
10.0s 0 2692.9 2166.6 1.2 88.1 88.1 184.5 read
10.0s 0 146.8 112.3 88.1 92.3 96.5 109.1 update
15.0s 0 3062.3 2465.2 1.1 88.1 92.3 104.9 read
15.0s 0 165.8 130.1 88.1 92.3 96.5 104.9 update
20.0s 0 2253.1 2412.2 1.9 88.1 92.3 117.4 read
20.0s 0 111.2 125.4 88.1 92.3 96.5 100.7 update
25.0s 0 2160.8 2361.9 3.3 88.1 92.3 192.9 read
25.0s 0 117.6 123.8 88.1 92.3 96.5 100.7 update
30.0s 0 2451.5 2376.8 1.2 88.1 88.1 96.5 read
30.0s 0 120.6 123.3 88.1 92.3 92.3 96.5 update
35.0s 0 2497.5 2394.0 1.4 88.1 92.3 192.9 read
35.0s 0 132.4 124.6 88.1 92.3 96.5 469.8 update
40.0s 0 2485.6 2405.5 1.3 88.1 92.3 453.0 read
40.0s 0 126.2 124.8 88.1 92.3 96.5 121.6 update
45.0s 0 2441.6 2409.5 1.3 88.1 96.5 243.3 read
45.0s 0 132.8 125.7 88.1 96.5 142.6 234.9 update
50.0s 0 2496.1 2418.1 1.3 88.1 92.3 109.1 read
50.0s 0 132.8 126.4 88.1 92.3 96.5 109.1 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
55.0s 0 2535.4 2428.8 1.4 88.1 92.3 104.9 read
55.0s 0 142.2 127.8 88.1 92.3 96.5 104.9 update
60.0s 0 2675.6 2449.4 1.2 88.1 88.1 100.7 read
60.0s 0 138.2 128.7 88.1 92.3 96.5 100.7 update
65.0s 0 2664.7 2465.9 1.1 88.1 88.1 209.7 read
65.0s 0 139.6 129.5 88.1 92.3 92.3 104.9 update
70.0s 0 2668.7 2480.4 1.2 88.1 92.3 104.9 read
70.0s 0 143.2 130.5 88.1 92.3 100.7 109.1 update
75.0s 0 2679.4 2493.7 1.3 88.1 92.3 419.4 read
75.0s 0 136.4 130.9 88.1 96.5 109.1 419.4 update
80.0s 0 2679.6 2505.3 2.1 92.3 104.9 159.4 read
80.0s 0 137.6 131.3 88.1 104.9 117.4 134.2 update
85.0s 0 2893.9 2528.1 1.1 88.1 88.1 453.0 read
85.0s 0 144.6 132.1 88.1 92.3 96.5 104.9 update
90.0s 0 2806.4 2543.6 1.2 88.1 92.3 453.0 read
90.0s 0 148.2 133.0 88.1 92.3 109.1 117.4 update
95.0s 0 2698.1 2551.7 1.2 88.1 92.3 192.9 read
95.0s 0 149.4 133.9 88.1 92.3 104.9 104.9 update
100.0s 0 2791.1 2563.7 1.3 88.1 92.3 130.0 read
100.0s 0 139.0 134.1 88.1 92.3 104.9 125.8 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
105.0s 0 2765.1 2573.3 1.2 88.1 88.1 184.5 read
105.0s 0 141.4 134.5 88.1 92.3 96.5 104.9 update
110.0s 0 2762.5 2581.9 1.3 88.1 96.5 117.4 read
110.0s 0 145.4 135.0 88.1 96.5 104.9 117.4 update
115.0s 0 2856.4 2593.8 1.2 88.1 92.3 117.4 read
115.0s 0 143.6 135.3 88.1 92.3 96.5 104.9 update
120.0s 0 2829.7 2603.7 1.2 88.1 92.3 125.8 read
120.0s 0 145.0 135.7 88.1 92.3 100.7 109.1 update
125.0s 0 2713.6 2608.1 1.2 88.1 96.5 419.4 read
125.0s 0 142.0 136.0 88.1 92.3 104.9 453.0 update
130.0s 0 2807.1 2615.7 1.1 88.1 92.3 109.1 read
130.0s 0 159.4 136.9 88.1 92.3 100.7 121.6 update
135.0s 0 2715.3 2619.4 1.2 88.1 92.3 104.9 read
135.0s 0 143.4 137.1 88.1 92.3 100.7 113.2 update
140.0s 0 2732.0 2623.4 1.4 88.1 92.3 109.1 read
140.0s 0 139.4 137.2 88.1 92.3 100.7 113.2 update
145.0s 0 2827.4 2630.5 1.4 88.1 92.3 117.4 read
145.0s 0 141.6 137.4 88.1 92.3 104.9 121.6 update
150.0s 0 2758.3 2634.7 1.4 88.1 96.5 113.2 read
150.0s 0 141.8 137.5 88.1 92.3 104.9 121.6 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
155.0s 0 2746.2 2638.3 1.2 88.1 92.3 109.1 read
155.0s 0 140.2 137.6 88.1 92.3 96.5 109.1 update
160.0s 0 2788.5 2643.0 1.1 88.1 92.3 104.9 read
160.0s 0 149.4 138.0 88.1 92.3 100.7 268.4 update
165.0s 0 2826.6 2648.6 1.1 88.1 92.3 104.9 read
165.0s 0 142.2 138.1 88.1 92.3 100.7 104.9 update
170.0s 0 2864.7 2654.9 1.1 88.1 92.3 109.1 read
170.0s 0 154.6 138.6 88.1 92.3 100.7 113.2 update
175.0s 0 2817.0 2659.6 1.3 88.1 92.3 419.4 read
175.0s 0 142.6 138.7 88.1 96.5 104.9 218.1 update
180.0s 0 2732.5 2661.6 1.2 88.1 92.3 100.7 read
180.0s 0 146.4 138.9 88.1 92.3 96.5 100.7 update
185.0s 0 2644.3 2661.1 1.2 88.1 96.5 142.6 read
185.0s 0 146.0 139.1 88.1 92.3 104.9 113.2 update
190.0s 0 2737.3 2663.1 1.2 88.1 88.1 104.9 read
190.0s 0 146.2 139.3 88.1 92.3 96.5 104.9 update
195.0s 0 2625.2 2662.2 1.2 88.1 92.3 134.2 read
195.0s 0 144.8 139.4 88.1 92.3 100.7 142.6 update
200.0s 0 2603.2 2660.7 1.2 88.1 92.3 167.8 read
200.0s 0 127.2 139.1 88.1 92.3 109.1 117.4 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
205.0s 0 2622.0 2659.7 1.4 88.1 96.5 117.4 read
205.0s 0 126.8 138.8 88.1 92.3 104.9 113.2 update
210.0s 0 2606.6 2658.5 1.2 88.1 88.1 113.2 read
210.0s 0 138.6 138.8 88.1 92.3 100.7 130.0 update
215.0s 0 2546.6 2655.9 1.2 88.1 92.3 104.9 read
215.0s 0 137.8 138.8 88.1 92.3 96.5 113.2 update
220.0s 0 2551.0 2653.5 1.2 88.1 88.1 96.5 read
220.0s 0 134.4 138.7 88.1 92.3 92.3 96.5 update
225.0s 0 2615.4 2652.6 1.2 88.1 92.3 125.8 read
225.0s 0 142.8 138.8 88.1 92.3 100.7 104.9 update
230.0s 0 2585.3 2651.2 1.3 88.1 92.3 113.2 read
230.0s 0 131.2 138.6 88.1 92.3 100.7 109.1 update
235.0s 0 2565.9 2649.4 1.4 88.1 92.3 113.2 read
235.0s 0 139.4 138.6 88.1 92.3 104.9 113.2 update
240.0s 0 2640.1 2649.2 1.2 88.1 92.3 192.9 read
240.0s 0 139.0 138.6 88.1 92.3 96.5 100.7 update
245.0s 0 2618.4 2648.5 1.3 88.1 92.3 104.9 read
245.0s 0 141.4 138.7 88.1 92.3 96.5 104.9 update
250.0s 0 2645.2 2648.5 1.2 88.1 92.3 209.7 read
250.0s 0 140.2 138.7 88.1 92.3 100.7 117.4 update
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
255.0s 0 2634.6 2648.2 1.2 88.1 92.3 109.1 read
255.0s 0 131.2 138.6 88.1 92.3 100.7 109.1 update
260.0s 0 2590.1 2647.1 1.2 88.1 88.1 419.4 read
260.0s 0 135.0 138.5 88.1 92.3 92.3 100.7 update
265.0s 0 2573.2 2645.7 1.5 88.1 96.5 218.1 read
265.0s 0 134.2 138.4 88.1 96.5 109.1 130.0 update
270.0s 0 2603.3 2644.9 1.2 88.1 92.3 419.4 read
270.0s 0 134.2 138.4 88.1 92.3 96.5 100.7 update
275.0s 0 2613.3 2644.3 1.2 88.1 92.3 109.1 read
275.0s 0 141.6 138.4 88.1 92.3 100.7 117.4 update
280.0s 0 2589.5 2643.4 1.4 88.1 96.5 453.0 read
280.0s 0 133.0 138.3 88.1 96.5 100.7 113.2 update
285.0s 0 2675.9 2643.9 1.4 88.1 96.5 142.6 read
285.0s 0 130.2 138.2 88.1 96.5 125.8 192.9 update
290.0s 0 2726.6 2645.4 1.2 88.1 92.3 419.4 read
290.0s 0 145.4 138.3 88.1 92.3 100.7 109.1 update
295.0s 0 2636.5 2645.2 1.3 88.1 92.3 302.0 read
295.0s 0 144.2 138.4 88.1 92.3 100.7 104.9 update
300.0s 0 2592.5 2644.3 1.2 88.1 92.3 453.0 read
300.0s 0 136.2 138.4 88.1 92.3 100.7 113.2 update
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
300.0s 0 793298 2644.3 33.2 1.2 88.1 92.3 453.0 read
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
300.0s 0 41509 138.4 86.5 88.1 92.3 100.7 469.8 update
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result
300.0s 0 834807 2782.7 35.8 1.4 88.1 92.3 469.8
This is very promising. Recall our previous PolyScale Workload B results:
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total 300.0s 0 327660 1092.2 85.5 100.7 151.0 234.9 1409.3 read _elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total 300.0s 0 17160 57.2 115.5 109.1 159.4 268.4 771.8 update _elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result 300.0s 0 344820 1149.4 87.0 100.7 151.0 243.3 1409.3
We now process almost 3x better than our previous result (i.e., 834807 ops vs 344820).
Let's look at the metrics:
I'm excited to run the Workload C!!!
cockroach workload run ycsb \ --duration=5m \ --display-every=5s \ --display-format=simple \ --concurrency=100 \ --tolerate-errors \ --workload C \ $DATABASE_URL
I230511 18:38:09.595527 1 workload/cli/run.go:460 [-] 3 creating load generator... done (took 5m50.836536042s)
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
5.0s 0 11.6 827.8 100.7 318.8 352.3 352.3 read
10.0s 0 1394.3 1110.8 88.1 218.1 251.7 604.0 read
15.0s 0 2928.5 1716.6 1.6 88.1 96.5 151.0 read
20.0s 0 2204.6 1838.7 2.8 302.0 369.1 503.3 read
25.0s 0 2937.2 2058.3 2.5 184.5 302.0 369.1 read
30.0s 0 5017.2 2551.4 1.9 88.1 100.7 130.0 read
35.0s 0 5213.6 2931.7 1.7 88.1 104.9 151.0 read
40.0s 0 5379.0 3237.6 1.7 88.1 100.7 134.2 read
45.0s 0 5424.7 3480.8 1.8 88.1 104.9 335.5 read
50.0s 0 5598.3 3692.4 2.0 88.1 100.7 130.0 read
55.0s 0 5666.8 3871.9 2.1 92.3 109.1 159.4 read
60.0s 0 5970.0 4046.7 2.0 88.1 100.7 134.2 read
65.0s 0 6257.6 4216.8 2.2 88.1 100.7 167.8 read
70.0s 0 6221.1 4360.0 2.0 88.1 100.7 385.9 read
75.0s 0 6420.8 4497.4 2.1 88.1 104.9 142.6 read
80.0s 0 6594.0 4628.4 2.2 88.1 100.7 130.0 read
85.0s 0 6723.7 4751.7 2.5 88.1 100.7 260.0 read
90.0s 0 6831.4 4867.2 2.4 88.1 100.7 184.5 read
95.0s 0 7246.8 4992.4 2.4 88.1 100.7 260.0 read
100.0s 0 7117.2 5098.7 3.0 92.3 100.7 369.1 read
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
105.0s 0 6969.3 5187.8 2.5 92.3 109.1 176.2 read
110.0s 0 7500.4 5292.9 2.5 88.1 96.5 419.4 read
115.0s 0 7281.7 5379.4 2.8 92.3 100.7 453.0 read
120.0s 0 7497.8 5467.6 3.1 92.3 100.7 260.0 read
125.0s 0 7466.5 5547.6 3.0 88.1 100.7 176.2 read
130.0s 0 7503.0 5622.8 3.0 92.3 96.5 469.8 read
135.0s 0 7578.9 5695.2 3.4 92.3 100.7 159.4 read
140.0s 0 7629.1 5764.3 3.3 92.3 100.7 251.7 read
145.0s 0 7656.2 5829.5 3.1 92.3 100.7 260.0 read
150.0s 0 7961.1 5900.6 2.5 88.1 96.5 419.4 read
155.0s 0 6791.4 5929.3 2.6 92.3 100.7 469.8 read
160.0s 0 7750.3 5986.2 3.0 92.3 100.7 436.2 read
165.0s 0 6863.4 6012.8 3.5 92.3 104.9 335.5 read
170.0s 0 7863.8 6067.3 2.9 88.1 100.7 151.0 read
175.0s 0 7391.4 6105.1 2.8 88.1 100.7 176.2 read
180.0s 0 8107.5 6160.7 3.8 92.3 100.7 159.4 read
185.0s 0 7546.5 6198.2 3.3 92.3 100.7 151.0 read
190.0s 0 7062.1 6220.9 3.0 92.3 104.9 192.9 read
195.0s 0 7126.5 6244.1 2.4 88.1 96.5 142.6 read
200.0s 0 6998.4 6263.0 2.2 88.1 100.7 134.2 read
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
205.0s 0 7850.9 6301.7 3.3 92.3 100.7 453.0 read
210.0s 0 8385.3 6351.3 3.3 88.1 100.7 469.8 read
215.0s 0 8135.3 6392.8 2.8 88.1 96.5 369.1 read
220.0s 0 7162.7 6410.3 2.6 92.3 100.7 151.0 read
225.0s 0 7122.3 6426.1 2.4 88.1 96.5 335.5 read
230.0s 0 7149.6 6441.9 2.5 88.1 100.7 142.6 read
235.0s 0 6739.3 6448.2 2.4 88.1 100.7 335.5 read
240.0s 0 7754.8 6475.4 2.8 88.1 100.7 167.8 read
245.0s 0 7024.8 6486.6 2.6 88.1 100.7 142.6 read
250.0s 0 8111.3 6519.1 3.1 88.1 100.7 159.4 read
255.0s 0 7607.0 6540.4 3.0 92.3 100.7 159.4 read
260.0s 0 6869.9 6546.8 2.5 88.1 100.7 419.4 read
265.0s 0 6091.9 6538.2 2.0 88.1 104.9 151.0 read
270.0s 0 7900.0 6563.4 3.0 88.1 100.7 260.0 read
275.0s 0 8252.5 6594.1 3.5 92.3 100.7 159.4 read
280.0s 0 7711.3 6614.1 3.0 88.1 100.7 436.2 read
285.0s 0 7802.7 6634.9 2.4 88.1 96.5 335.5 read
290.0s 0 7958.9 6657.8 2.9 88.1 100.7 268.4 read
295.0s 0 7351.4 6669.5 2.5 88.1 100.7 130.0 read
300.0s 0 6433.0 6665.6 2.4 92.3 109.1 419.4 read
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
300.0s 0 1999672 6665.6 15.0 2.6 92.3 104.9 604.0 read
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result
300.0s 0 1999672 6665.6 15.0 2.6 92.3 104.9 604.0
Here are the results from our previous PolyScale run:
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total 300.0s 0 458408 1528.0 65.4 54.5 130.0 209.7 1543.5 read _elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result 300.0s 0 458408 1528.0 65.4 54.5 130.0 209.7 1543.5
Compare that to the new results:
_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total 300.0s 0 1999672 6665.6 15.0 2.6 92.3 104.9 604.0 read _elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result 300.0s 0 1999672 6665.6 15.0 2.6 92.3 104.9 604.0
We are processing 4x more queries and our ops/sec are also 4x.
PgBench Workload
As the very last experiment, I would like to focus on the PgBench workload. In my line of work, I don't necessarily have a choice of workloads when customers reach out with a database pain. We see read-heavy, write heavy and mixed workloads. The ycsb Workloads B and C demonstrate the impact of caching well. As a counter-example, I'd like to demonstrate where PolyScale will not make a significant impact, with or without a local cache. PgBench is a standard benchmark tool that ships with PostgreSQL. It is ubiquitous and something we see most frequently used when customers evaluate CockroachDB. The example I'm going to demonstrate is optimized for CockroachDB, meaning the TPC-B explicit transaction shipped with PgBench is rewritten as a common table expression. Please see my articles on optimizing PgBench for CockroachDB for more information:
- Using pgbench With CockroachDB Serverless
- Optimizing Pgbench for CockroachDB Part 1
- Optimizing Pgbench for CockroachDB Part 2
Using PgBench with CockroachDB Serverless directly and a separate connection per transaction:
pgbench \
--host=${PGHOST} \
--no-vacuum \
--file=tpcb-cockroach.sql@1 \
--client=8 \
--jobs=8 \
--username=${PGUSER} \
--port=${PGPORT} \
-T 60 \
-P 5 \
-C \
--failures-detailed \
${PGDATABASE}
progress: 5.5 s, 10.2 tps, lat 110.543 ms stddev 27.768, 0 failed progress: 10.3 s, 11.6 tps, lat 103.826 ms stddev 12.722, 0 failed progress: 15.1 s, 11.7 tps, lat 102.692 ms stddev 8.984, 0 failed progress: 20.0 s, 11.4 tps, lat 101.976 ms stddev 9.157, 0 failed progress: 25.6 s, 11.5 tps, lat 101.600 ms stddev 8.100, 0 failed progress: 30.4 s, 11.6 tps, lat 100.336 ms stddev 8.167, 0 failed progress: 35.4 s, 11.3 tps, lat 103.329 ms stddev 9.042, 0 failed progress: 40.3 s, 11.4 tps, lat 101.837 ms stddev 6.412, 0 failed progress: 45.2 s, 11.5 tps, lat 100.112 ms stddev 6.307, 0 failed progress: 50.1 s, 11.4 tps, lat 101.227 ms stddev 6.855, 0 failed progress: 55.0 s, 11.4 tps, lat 99.705 ms stddev 4.721, 0 failed progress: 60.4 s, 11.9 tps, lat 100.051 ms stddev 6.512, 0 failed transaction type: tpcb-cockroach.sql scaling factor: 1 query mode: simple number of clients: 8 number of threads: 8 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 690 number of failed transactions: 0 (0.000%) number of serialization failures: 0 (0.000%) number of deadlock failures: 0 (0.000%) latency average = 102.223 ms latency stddev = 11.448 ms average connection time = 596.884 ms tps = 11.391485 (including reconnection times)
The next step is to run the same test against PolyScale Serverless and a separate connection per transaction.
pgbench "host=psedge.global port=5432 user='artem' dbname='defaultdb' application_name='your_cache_id' sslmode=require" \ --no-vacuum \ --file=tpcb-cockroach.sql@1 \ --client=8 \ --jobs=8 \ -T 60 \ -P 5 \ -C \ --failures-detailed
progress: 5.3 s, 7.1 tps, lat 151.924 ms stddev 43.906, 0 failed progress: 10.3 s, 9.5 tps, lat 112.534 ms stddev 9.015, 0 failed progress: 15.2 s, 10.1 tps, lat 110.195 ms stddev 6.313, 0 failed progress: 20.0 s, 9.5 tps, lat 114.546 ms stddev 10.560, 0 failed progress: 25.6 s, 7.7 tps, lat 121.107 ms stddev 15.663, 0 failed progress: 30.5 s, 8.8 tps, lat 130.285 ms stddev 38.751, 0 failed progress: 35.4 s, 9.8 tps, lat 111.316 ms stddev 7.099, 0 failed progress: 40.3 s, 9.8 tps, lat 109.574 ms stddev 10.634, 0 failed progress: 45.1 s, 9.9 tps, lat 106.072 ms stddev 4.950, 0 failed progress: 50.6 s, 8.0 tps, lat 137.115 ms stddev 52.324, 0 failed progress: 55.0 s, 9.0 tps, lat 114.435 ms stddev 10.955, 0 failed progress: 60.6 s, 10.0 tps, lat 105.880 ms stddev 6.483, 0 failed transaction type: tpcb-cockroach.sql scaling factor: 1 query mode: simple number of clients: 8 number of threads: 8 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 552 number of failed transactions: 0 (0.000%) number of serialization failures: 0 (0.000%) number of deadlock failures: 0 (0.000%) latency average = 117.730 ms latency stddev = 26.544 ms average connection time = 756.460 ms tps = 9.090481 (including reconnection times)
The performance is a bit worse. Let's run the same test against a local cache:
pgbench "host=127.0.0.1 port=26257 user='artem' dbname='defaultdb' application_name='your_cache_id' sslmode=require" \ --no-vacuum \ --file=tpcb-cockroach.sql@1 \ --client=8 \ --jobs=8 \ -T 60 \ -P 5 \ -C \ --failures-detailed
progress: 5.0 s, 8.6 tps, lat 131.663 ms stddev 38.022, 0 failed progress: 10.7 s, 9.3 tps, lat 123.430 ms stddev 19.788, 0 failed progress: 15.6 s, 10.1 tps, lat 113.609 ms stddev 11.022, 0 failed progress: 20.5 s, 9.5 tps, lat 113.968 ms stddev 13.071, 0 failed progress: 25.5 s, 10.0 tps, lat 119.194 ms stddev 13.611, 0 failed progress: 30.4 s, 10.1 tps, lat 117.146 ms stddev 11.048, 0 failed progress: 35.3 s, 9.0 tps, lat 118.848 ms stddev 17.437, 0 failed progress: 40.1 s, 9.9 tps, lat 120.464 ms stddev 18.411, 0 failed progress: 45.1 s, 9.7 tps, lat 123.710 ms stddev 25.389, 0 failed progress: 50.0 s, 8.5 tps, lat 116.464 ms stddev 15.821, 0 failed progress: 55.2 s, 7.4 tps, lat 117.977 ms stddev 13.877, 0 failed pgbench: error: connection to server at "127.0.0.1", port 26257 failed: FATAL: Upstream connection error (PolyScale) DETAIL: Failed to connect to the upstream database pgbench: error: client 4 aborted while establishing connection pgbench: error: connection to server at "127.0.0.1", port 26257 failed: FATAL: Upstream connection error (PolyScale) DETAIL: Failed to connect to the upstream database pgbench: error: client 1 aborted while establishing connection progress: 60.1 s, 7.0 tps, lat 112.718 ms stddev 10.650, 0 failed transaction type: tpcb-cockroach.sql scaling factor: 1 query mode: simple number of clients: 8 number of threads: 8 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 552 number of failed transactions: 0 (0.000%) number of serialization failures: 0 (0.000%) number of deadlock failures: 0 (0.000%) latency average = 119.183 ms latency stddev = 19.422 ms average connection time = 739.329 ms tps = 9.165370 (including reconnection times) pgbench: error: Run was aborted; the above results are incomplete.
We are not seeing any improvement whether we use PolyScale Serverless or local. In fact, we get errors with a couple of clients.
Let's run the same test using a single connection per session.
Using PgBench with CockroachDB Serverless directly and a single connection per transaction:
pgbench \
--host=${PGHOST} \
--no-vacuum \
--file=tpcb-cockroach.sql@1 \
--client=8 \
--jobs=8 \
--username=${PGUSER} \
--port=${PGPORT} \
-T 60 \
-P 5 \
--failures-detailed \
${PGDATABASE}
progress: 5.0 s, 42.8 tps, lat 159.810 ms stddev 159.885, 0 failed progress: 10.0 s, 47.6 tps, lat 160.127 ms stddev 209.041, 0 failed progress: 15.0 s, 46.0 tps, lat 178.640 ms stddev 257.350, 0 failed progress: 20.0 s, 44.0 tps, lat 183.237 ms stddev 194.204, 0 failed progress: 25.0 s, 43.6 tps, lat 185.614 ms stddev 211.550, 0 failed progress: 30.0 s, 42.2 tps, lat 181.886 ms stddev 154.730, 0 failed progress: 35.0 s, 41.8 tps, lat 193.950 ms stddev 348.516, 0 failed progress: 40.0 s, 40.8 tps, lat 195.000 ms stddev 252.481, 0 failed progress: 45.0 s, 44.2 tps, lat 184.320 ms stddev 279.705, 0 failed progress: 50.0 s, 46.8 tps, lat 167.793 ms stddev 205.700, 0 failed progress: 55.0 s, 45.4 tps, lat 167.558 ms stddev 206.144, 0 failed progress: 60.0 s, 44.0 tps, lat 191.623 ms stddev 295.580, 0 failed transaction type: tpcb-cockroach.sql scaling factor: 1 query mode: simple number of clients: 8 number of threads: 8 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 2654 number of failed transactions: 0 (0.000%) number of serialization failures: 0 (0.000%) number of deadlock failures: 0 (0.000%) latency average = 179.301 ms latency stddev = 237.941 ms initial connection time = 611.111 ms tps = 44.546191 (without initial connection time)
The following test is using PgBbench with PolyScale Serverless and a single connection for the session:
pgbench "host=psedge.global port=5432 user='artem' dbname='defaultdb' application_name='10f2f763-1cc3-482e-a3ca-abab21e67e0e' sslmode=require" \ --no-vacuum \ --file=tpcb-cockroach.sql@1 \ --client=8 \ --jobs=8 \ -T 60 \ -P 5 \ --failures-detailed
progress: 5.0 s, 41.4 tps, lat 161.884 ms stddev 104.366, 0 failed progress: 10.0 s, 48.2 tps, lat 164.292 ms stddev 156.686, 0 failed progress: 15.0 s, 47.2 tps, lat 170.998 ms stddev 133.104, 0 failed progress: 20.0 s, 44.6 tps, lat 177.537 ms stddev 112.391, 0 failed progress: 25.0 s, 44.0 tps, lat 176.075 ms stddev 160.491, 0 failed progress: 30.0 s, 42.0 tps, lat 194.870 ms stddev 246.448, 0 failed progress: 35.0 s, 42.0 tps, lat 190.376 ms stddev 241.178, 0 failed progress: 40.0 s, 40.0 tps, lat 199.472 ms stddev 155.507, 0 failed progress: 45.0 s, 47.4 tps, lat 171.893 ms stddev 135.157, 0 failed progress: 50.0 s, 46.6 tps, lat 169.876 ms stddev 168.642, 0 failed progress: 55.0 s, 45.2 tps, lat 170.815 ms stddev 187.009, 0 failed progress: 60.0 s, 43.4 tps, lat 193.513 ms stddev 217.260, 0 failed transaction type: tpcb-cockroach.sql scaling factor: 1 query mode: simple number of clients: 8 number of threads: 8 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 2668 number of failed transactions: 0 (0.000%) number of serialization failures: 0 (0.000%) number of deadlock failures: 0 (0.000%) latency average = 178.017 ms latency stddev = 173.356 ms initial connection time = 718.873 ms tps = 44.880455 (without initial connection time)
Performance is on par with direct connection. The final test is to run PgBench against the local cache and a single connection.
pgbench "host=127.0.0.1 port=26257 user='artem' dbname='defaultdb' application_name='10f2f763-1cc3-482e-a3ca-abab21e67e0e' sslmode=require" \ --no-vacuum \ --file=tpcb-cockroach.sql@1 \ --client=8 \ --jobs=8 \ -T 60 \ -P 5 \ --failures-detailed
progress: 5.0 s, 40.0 tps, lat 164.834 ms stddev 122.563, 0 failed progress: 10.0 s, 48.8 tps, lat 160.760 ms stddev 170.777, 0 failed progress: 15.0 s, 47.8 tps, lat 170.314 ms stddev 140.472, 0 failed progress: 20.0 s, 45.6 tps, lat 167.467 ms stddev 230.886, 0 failed progress: 25.0 s, 44.4 tps, lat 182.303 ms stddev 216.929, 0 failed progress: 30.0 s, 42.8 tps, lat 185.109 ms stddev 192.897, 0 failed progress: 35.0 s, 42.4 tps, lat 182.918 ms stddev 234.083, 0 failed progress: 40.0 s, 41.2 tps, lat 198.508 ms stddev 331.531, 0 failed progress: 45.0 s, 40.4 tps, lat 194.331 ms stddev 279.567, 0 failed progress: 50.0 s, 40.2 tps, lat 202.052 ms stddev 228.768, 0 failed progress: 55.0 s, 38.4 tps, lat 214.685 ms stddev 320.612, 0 failed progress: 60.0 s, 37.2 tps, lat 209.055 ms stddev 257.384, 0 failed transaction type: tpcb-cockroach.sql scaling factor: 1 query mode: simple number of clients: 8 number of threads: 8 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 2554 number of failed transactions: 0 (0.000%) number of serialization failures: 0 (0.000%) number of deadlock failures: 0 (0.000%) latency average = 185.856 ms latency stddev = 235.054 ms initial connection time = 788.788 ms tps = 42.963951 (without initial connection time)
I am not seeing any difference in whichever method we choose. I am not convinced PolyScale is applicable in every case. You see the most bang for the buck when you pair PolyScale with read-heavy workloads where most queries are reads and the resultsets can be cached. For a read/write workload, your mileage may vary.
Conclusion
Overall, I think the product lives up to its promises, albeit in certain situations where we have a choice to choose a workload. Considering the wonderful experience with the engineering and a well-designed product that's very easy to use, it pairs well with CockroachDB. In those situations, I can see a value-add in having PolyScale in the picture.
Published at DZone with permission of Artem Ervits. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments