What is Infrastructure Calibration?
Performance (response time and throughput) depends on the infrastructure, database, and application. Tuning all three layers simultaneously is a daunting task; however, this is often the first step for many who start the PoC process. The opposite way is to start from a known workload and establish the infrastructure capability. This is the first in the series of posts as we move up the layer.
Start at the Infrastructure
So the Infrastructure Calibration runs a known workload and effectively measures infrastructure capability. Why start at the infrastructure? A bottleneck can limit performance. Databases and applications run on top of the infrastructure. Any infrastructure bottlenecks will hinder database and application performance. So it is logical to start from the infrastructure and move up the stack.
Use Simple Workload to Start
There are many well-known workloads to choose from to start the Infrastructure Calibration. Use the simplest workload initially. Identifying bottlenecks and making adjustments to remove them is generally called tuning. Removing one bottleneck often reveals a bottleneck in another component. As such, tuning is an iterative process that requires expertise in all layers. A simple workload makes the task easier. The workload must be easy to understand, easy to run, and most importantly easy to reproduce.
Keep it Simple
CPU, RAM, Storage, Network, and operating system are the major components in the infrastructure. Many books have been written on tuning these components. For a database, read operation is a function of CPU/RAM performance and write (or update) is a function of storage performance. A very fast CPU/RAM and very fast storage will provide fast database response time. In the spirit of the layered approach and simplicity, start with response time and limit the concurrency to one (thereby not testing the throughput) in the beginning.
Multiple nodes, tables, indexes, and constraints, joins are supported in CockroachDB. Multiple nodes increase complexity by introducing external dependencies. Often, network and operating system tuning are necessary. Again, in the spirit of the layered approach and simplicity, configure the simplest database by using one node, one table, no index, primary key constraints, and no joins.
Applications can read, insert, update, and delete data from the database. Most commercial OLTP applications using microservices read mostly with some updates. CockroachDB can support complex SQL. Again, In the spirit of the layered approach and simplicity, simple primary key read and updates will be used.
YCSB (Yahoo Cloud Services Benchmark) was introduced in 2010. “The goal of the Yahoo Cloud Serving Benchmark (YCSB) project is to develop a framework and common set of workloads for evaluating the performance of different “key-value” and “cloud” serving stores.” YCSB fits the requirements of “Keep It Simple” for the Infrastructure Calibration. YCSB concurrency can be controlled, has a single table and uses simple SQL with the primary key. YCSB has workloads A, B, C, D, E, and F. YCSB Workload B does 95% read and 5% update.
1ms read and 3ms update
For YCSB type workloads, CockroachDB is expected to deliver 1 to 2 millisecond read response time and about 2 to 4 millisecond update response time. How this expectation was derived will be in another post. Once the hardware delivers the expected performance, then concurrency testing to measure the throughput can begin. Otherwise, the configuration of the hardware and CockroachDB should be examined to establish an expected baseline before continuing.
An example of a baseline comparison
Measurements on three systems are provided as an example: A MacBook Pro (13-inch, 2018), a couple of years old server with spinning disk, and a cloud server with average SSD. Lower response time is better. The left graph shows read response generally benefits from faster CPUs with response times under 1ms. The right graph shows older spinning disk performing poorly (compared to the other two) with 160ms response.
Steps Using YCSB for Infrastructure Calibration
Below describes the recommended way to select the workload, run a quick 1-minute test, and verify the baseline infrastructure performance.
Select a workload
CockroachDB is shipped with a number of sample workloads. The simplest of the well-known workloads is called YCSB. YCSB can be used to show insert, read, and update response times — the performance of the three most basic SQL operations.
- YCSB has 10 fields with a single field primary key.
CREATE TABLE usertable ( ycsb_key VARCHAR(255) NOT NULL, field0 STRING NULL, field1 STRING NULL, field2 STRING NULL, field3 STRING NULL, field4 STRING NULL, field5 STRING NULL, field6 STRING NULL, field7 STRING NULL, field8 STRING NULL, field9 STRING NULL, CONSTRAINT "primary" 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) );
- CockroachDB will populate initial data with the multi-value statement below. The benefits of multi-value statements are described here.
INSERT INTO usertable VALUES ($1, $2, __more9__), (__more600__)
- YCSB can update any one of the 10 fields using one of the DML below.
UPDATE usertable SET field0 = $2 WHERE ycsb_key = $1 UPDATE usertable SET field1 = $2 WHERE ycsb_key = $1 UPDATE usertable SET field2 = $2 WHERE ycsb_key = $1 UPDATE usertable SET field3 = $2 WHERE ycsb_key = $1 UPDATE usertable SET field4 = $2 WHERE ycsb_key = $1 UPDATE usertable SET field5 = $2 WHERE ycsb_key = $1 UPDATE usertable SET field6 = $2 WHERE ycsb_key = $1 UPDATE usertable SET field7 = $2 WHERE ycsb_key = $1 UPDATE usertable SET field8 = $2 WHERE ycsb_key = $1 UPDATE usertable SET field9 = $2 WHERE ycsb_key = $1
- YCSB read on the primary key.
SELECT * FROM usertable WHERE ycsb_key = $1
Start a single node CockroachDB
A single node CockroachDB is documented. A simple version is below:
cockroach start-single-node --insecure --background
Measure the baseline performance
- Start by inserting default 10,000 rows of data.
cockroach workload init ycsb I191210 02:21:30.263658 1 workload/workloadsql/dataload.go:135 imported usertable (0s, 10000 rows)
- Start a one read and update workloads for 60 seconds. Observe the average read and update response time. Read performance is usually gated by CPU and RAM. Update performance is usually gated by IO.
cockroach workload run ycsb --concurrency 1 --duration 60s _elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms) ... (remove for brevity) 60.0s 0 3308.2 3217.5 0.3 0.3 0.5 4.5 read 60.0s 0 178.5 168.3 0.8 0.9 1.0 2.6 update _elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total 60.0s 0 193063 3217.5 0.3 0.3 0.4 0.5 17.8 read _elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total 60.0s 0 10097 168.3 0.8 0.8 1.0 1.4 12.1 update
Compare with other Infrastructure
- Tabulate using your favorite graphing tool or copy this Google Sheet, cut/paste the “avg(ms)” values into the “data” tab, then check out how your system stack on the “charts” tab.