cockroachdb / loadgen Goto Github PK

Hello,

Thanks for the loadgen module for cockroachdb. The README is not fully compiled I believe is docker an prerequisite for this loadgen module ?. If not can you list the basic steps on how to run this against an cockroachdb cluster. Thank you.

Regards,
Ilan

It is too slow now !

It takes over 10 minutes to run check 3.3.2.4 on 10k warehouses. Some other checks failed to run because I closed the connection of the machine running the checks by accident. It would be great to be able to run just some of the checks instead of all of them.

it is useful specify certain columns to have specific value when generating random data. For example, https://www.cockroachlabs.com/docs/stable/alter-table.html split at command could be used to pre-split ranges for follow the sun, parallelism test. partitioning feature in the future could be tested as well.

-set.value database.table.column=value1[:ratio],value2:ratio

if a single value is given and no ratio is given, then assumed to be 100%
if the total ratio is less than 100%, then remainder will be filled with random data
if the total ratio is over 100%, then error out
when multiple default values are given and some ratios are not specified, then unknown ratios will be equally divided out of remaining from 100%

-set.list database.table.column=file

each row in the file will be used as a value based on random distribution

Consider the following scenario:
You have a 6 node cluster, on which you run YCSB as a load generator (with some high concurrency). You rebalance the cluster to 20 nodes, while maintaining continuous load. You hot-reload the proxy to send load to all 20 nodes. You get some modest throughput increase, but not as much as you expected:

This is because the long running connections do not get rebalanced to the new nodes. If the connections were to drop, then haproxy would spread the connections around (if configured to leastconn, at least), but without that, the load continues going to the original 6 nodes.

HAProxy is not the ideal way to manage long-running workers, but it's also unrealistic to model workers as throwing thousands of QPS of load for hours and hours over a single long-running connection. Other YCSB empirical evaluations I've seen do not run the load for very long, so it would not be illegitimate to occasionally drop and reconnect our workers, just to let the load-balancer adjust the connections per cockroach node.

We need a "diagnostic" load generator. Occasionally customers come to us and say "I ran query X" and even though we can analyze their schema if they give it to us, we also want to get a quick understanding of their machine's performance characteristics (since they don't always share their data) before going forward.

This is currently easy if we are primarily interested in their machine's raw read or write QPS (as we can point them to ycsb or kv), but for some other questions, there isn't a straightforward solution. For instance, if we want to understand their scan performance, asking them to go through the burden of running dbgen and tpc-h load is a lot of work.

We ideally want a load generator that does the following tasks, but also kills two birds with one stone by providing a blueprint for simple tasks that we can point users to that does:

1. Bulk inserts optimized with RETURNING NOTHING syntax.
2. IMPORT data from CSV.
3. Simple large scans (COUNT(*)).
4. Repeated single key 100% reads (i.e. the simplest degenerate case of kv)

Any other simple tasks that we would want to know to diagnose very basic performance characteristics @petermattis?

The history table doesn't have a primary key which means Cockroach autogenerates a primary key with a unique_rowid() function. This creates a hotspot as every insertion into the history table will be touch the last range of that table (unique_rowid() produces IDs that are usually sorted). We should investigate adding a UUID primary key for this table.

checkConsistency() is currently prohibitively slow for large numbers of warehouses and provides no progress indication. The most egregious check is for W_YTD = sum(H_AMOUNT) for each warehouse:

	var sumHAmount float64
	for i := 0; i < *warehouses; i++ {
		if err := db.QueryRow("SELECT w_ytd FROM warehouse WHERE w_id=$1", i).Scan(&wYTD); err != nil {
			return err
		}
		if err := db.QueryRow("SELECT SUM(h_amount) FROM history WHERE h_w_id=$1", i).Scan(&sumHAmount); err != nil {
			return err
		}
		if wYTD != sumHAmount {
			fmt.Printf("check failed: w_ytd=%f != sum(h_amount)=%f for warehouse %d\n", wYTD, sumHAmount, i)
		}
	}

The main problem here is that the SELECT SUM(h_amount) FROM history WHERE h_w_id=$1 has to do a lookup join when scanning the history_h_w_id_h_d_id_idx in order to retrieve h_amount. We could add a STORING clause to that index. Alternately, we could perform a single group by query: SELECT h_w_id, SUM(h_amount) FROM history GROUP BY h_w_id. Not sure which would be faster. Adding the STORING clause would certainly be easier.

allow primary key to match when generating data for multiple tables so that two or more tables can be joined later for select

Percona has a TPC-C load generator which we should take a close look at to see if its implementation is doing anything that we might want to also do, with respect to schemas, indexes, and query rewrites. The SIGMOD 2017 Amazon Aurora paper uses this load generator for benchmarking Aurora's TPC-C performance.

cc @jordanlewis

For example, if I run:

./ycsb -duration 20m -tolerate-errors -concurrency 3 -splits 50 -rate-limit 100

I'd expect 300 qps. Instead, I get:

Loading complete: 7.0s
     0s       1370.7          11 / 0 / 0        1361 / 0           0 / 0
     1s        258.2         247 / 0 / 0          11 / 0           0 / 0
     2s        254.8         241 / 0 / 0          14 / 0           0 / 0
     3s        256.8         240 / 0 / 0          17 / 0           0 / 0
     4s        252.4         240 / 0 / 0          12 / 0           0 / 0
     5s        254.9         249 / 0 / 0           6 / 0           0 / 0
     6s        258.0         244 / 0 / 0          14 / 0           0 / 0
     7s        251.6         236 / 0 / 0          16 / 0           0 / 0
     8s        255.0         242 / 0 / 0          13 / 0           0 / 0
     9s        261.6         251 / 0 / 0          10 / 0           0 / 0
    10s        259.9         246 / 0 / 0          14 / 0           0 / 0
    11s        261.1         247 / 0 / 0          14 / 0           0 / 0
    12s        269.8         258 / 0 / 0          12 / 0           0 / 0
    13s        262.7         253 / 0 / 0          10 / 0           0 / 0
elapsed______ops/sec__reads/empty/errors___writes/errors____scans/errors
    14s        267.5         255 / 0 / 0          12 / 0           0 / 0
    15s        269.7         258 / 0 / 0          12 / 0           0 / 0
    16s        263.7         253 / 0 / 0          11 / 0           0 / 0
    17s        273.4         262 / 0 / 0          11 / 0           0 / 0
    18s        261.1         255 / 0 / 0           6 / 0           0 / 0
    19s        260.9         249 / 0 / 0          12 / 0           0 / 0
    20s        263.7         251 / 0 / 0          13 / 0           0 / 0

Similarly, if I run:

./ycsb -duration 20m -tolerate-errors -concurrency 3 -splits 50

I'd expect 150 qps, but I get:

Loading complete: 7.0s
     0s       1305.9           6 / 0 / 0        1301 / 0           0 / 0
     1s        128.7         124 / 0 / 0           5 / 0           0 / 0
     2s        128.8         125 / 0 / 0           4 / 0           0 / 0
     3s        131.9         124 / 0 / 0           8 / 0           0 / 0
     4s        132.3         124 / 0 / 0           8 / 0           0 / 0
     5s        132.4         126 / 0 / 0           6 / 0           0 / 0
     6s        128.7         122 / 0 / 0           7 / 0           0 / 0
     7s        132.0         124 / 0 / 0           8 / 0           0 / 0
     8s        128.8         124 / 0 / 0           5 / 0           0 / 0
     9s        129.1         120 / 0 / 0           9 / 0           0 / 0
    10s        132.4         125 / 0 / 0           7 / 0           0 / 0
    11s        128.6         120 / 0 / 0           9 / 0           0 / 0
    12s        129.0         124 / 0 / 0           5 / 0           0 / 0
    13s        129.0         124 / 0 / 0           5 / 0           0 / 0

We should just cargo-cult the use of a rate.Limiter from kv, which works much better. I can fix this in a few days, but it's not a top priority at the moment.

Now that the tables are partitioned, we should assign workers to DB connection pools based on the partitions. At the moment, workers are assigned to connection pools by doing:

	for i := range workers {
		w := i % *warehouses
		workers[i] = newWorker(i, w, dbs[w%len(dbs)], &wg)
	}

So warehouse w is assigned to DB connection pool w%numPools. This is at odds with the range-based partitioning of the tables by warehouse ID. Contiguous warehouse IDs will be on the same partition. The above initialization should be fixed so that we determine the partition for a warehouse and from that partition we round-robin amongst the connection pools that are connected to the partition.

2.4.2.3 says:

For transactions that rollback as a result of an unused item number, the complete transaction profile must be executed with the exception that the following steps need not be done:

We currently give up too early and don't process the rest of the items.

We use the TPC-H queries in many different Distributed SQL tests. It would be helpful to have a single documentation page listing the various queries, a short description in prose of what the query does, and DistSQL query plan URLs (with the associated cockroach hash that generated that query plan), and link all known issues that are encountered when running those queries (e.g. such as known memory usages due to large Hash Joins).

This will also be helpful once we start manually rewriting the currently unsupported correlated-subquery queries and we want to keep track of what we're doing to those queries.

Feature requested by @nstewart.

Hi guys,

I try to run the TPC-C loadgen against a three node CockroachDB cluster.

Yet, I am not sure what is the appropriate way to specify the connection URL for a multi-node cluster:

While for the Postgres JDBC driver supports multiple endpoints in the connection URL, it seems that driver used within the loadgen does not supported multiple endpoints as follows:
"postgresql://IP1:26257,IP2:26257,IP2:26257?sslmode=disable"

Is it sufficient to provide just one endpoint and the driver will resolve all nodes in the cluster and distribute the load across all nodes?

If yes, in case of a node failure, will the client automatically use another available node in the cluster?

Or is an external load balancer (i.e. HAProxy) required to achieve a (fair) load balancing?

Thanks in advance for any kind of assistance.

my docker file

FROM golang
RUN mkdir -p /u/certs
RUN chmod 701 /u/certs
RUN echo -e "-----BEGIN RSA PRIVATE KEY-----..." > /u/certs/client.root.key
RUN chmod 600 /u/certs/client.root.key
RUN echo -e "-----BEGIN CERTIFICATE-----..." > /u/certs/client.root.crt
RUN chmod 600 /u/certs/client.root.crt
RUN echo -e "-----BEGIN CERTIFICATE-----..." > /u/certs/ca.crt
RUN chmod 600 /u/certs/ca.crt
RUN chown root /u/certs/*
RUN chgrp root /u/certs/*
RUN ls -l /u/certs/
RUN go get github.com/cockroachdb/loadgen/tpcc

ENTRYPOINT ["tpcc"]

execution

sudo docker run -it --rm abcdefghij -tolerate-errors   "postgresql://[email protected]:12345/tpcc?sslcert=/u/certs/ca.crt&sslkey=/u/certs/client.root.key&sslmode=verify-ca&sslrootcert=/u/certs/client.root.crt"
_time______opName__ops/s(inst)__ops/s(cum)__p50(ms)__p90(ms)__p95(ms)__p99(ms)_pMax(ms)
   1s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0      0.0
   2s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0      0.0
   3s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0      0.0
   4s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0      0.0
   5s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0      0.0
2018/05/15 04:22:35 error in delivery: tls: failed to find any PEM data in certificate input
   6s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0      0.0
   7s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0      0.0
   8s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0      0.0
2018/05/15 04:22:38 error in payment: tls: failed to find any PEM data in certificate input

Seems like I can only use an insecure connection.

YCSB, being a "NoSQL" workload, is a good candidate for stress testing CockroachDB's JSON support and making sure the performance is roughly the same for similar KV-style workloads to the SQL interface.

Since there is a single table and two queries (one read, one write), refactoring the table to use a JSON schema and refactoring the queries should not involve too much refactoring.

We should add a -json flag to ycsb (rather than just replacing the current version) so that this can be done optionally, and so that we can cross compare with the non-JSON variant.

I'm running tpcc using prebuilt loadgen against prebuilt cockroach binary.

The link to the prebuilt loadgen:
wget https://edge-binaries.cockroachdb.com/cockroach/workload.LATEST

The link to the prebuilt cockroash binary:
https://binaries.cockroachdb.com/cockroach-v2.0.4.linux-amd64.tgz

The command I'm using for the testing:
./workload.LATEST run tpcc --drop --ramp=30s --warehouses=1 --duration=300s --split --scatter "postgresql://root@HOST:26257?sslmode=disable"

The error I got:
Error: failed insert into warehouse: pq: there is no unique or exclusion constraint matching the ON CONFLICT specification

Please take a look.

I just noticed that the implementation of the payment transaction is incomplete. It's supposed to update the c_data field if the customer had bad credit. Instead, it doesn't. I'll fix this.

I am using centos 7.5. Any idea how to fix this?

$ go version 
go version go1.9.4 linux/amd64

$ make build
GOBIN=/home/admincentos/src/go/loadgen/bin go install -v \
	./vendor/github.com/golang/lint/golint \
	./vendor/github.com/kisielk/errcheck \
	./vendor/golang.org/x/tools/cmd/goimports
import cycle not allowed
package ./vendor/github.com/golang/lint/golint
	imports flag
	imports errors
	imports runtime
	imports runtime/internal/atomic
	imports unsafe
	imports runtime
vendor/github.com/kisielk/errcheck/main.go:13:2: use of internal package not allowed
make: *** [deps] Error 1

prepared statement has limit of 64K parameters. Add an option for using dynamic SQL instead of prepared statement.

Now that TPC-C forces serializability, leveling the playing field between CockroachDB and Postgres, Postgres gives up on some transactions with a "deadlock detected".

We need to add retry logic when these deadlocks occur, so that the load generator does not fail.

Two possible mechanisms which come to mind are:

• A manual pause time between successive operations
• Run at full tilt for 30s to measure maximum throughput, and then add pauses to bring throughput to x% of the maximum.

Hi guys,

I am trying to run the TPC-C workload against a 3-node cluster. Hereby, I basically follow this article. Yet, I am facing the following error:
Error: failed insert into district: pq: relation "district" does not exist

I have checked the dashboard and all tables are created but no data is inserted:

Here are some more technical details:

CockroachDB version: 2.0.3
Loadgen: latest version from https://edge-binaries.cockroachdb.com/cockroach/workload.LATEST

Command:
./loadgen.sh run tpcc --drop --init --ramp=30s --duration=300s --warehouses=10 --scatter --split "postgres://192.168.100.5:26257?sslmode=disable postgres://192.168.100.12:26257?sslmode=disable postgres://192.168.100.38:26257?sslmode=disable"

Yet, when running the workload only with one node in the connection URL it works without issues but the load is mainly issued against the provided node and not balanced across the cluster.

Command:
./loadgen.sh run tpcc --drop --init --ramp=30s --duration=300s --warehouses=10 --scatter --split "postgres://192.168.100.5:26257?sslmode=disable"

Thanks in advance for any help

After restarting a cluster, leases for the tpcc tables can be concentrated on 1 or 2 nodes. It would be useful for faster iteration to add a -scatter option to scatter the replicas/leases across the nodes. See kv for related code which performs scatter operations after splitting, though I think the work here is even simpler as we can just scatter all of the ranges for the tpcc tables.

Extracted from this post-merge comment

Hi guys,
is there any config option, which handles the output format of the TPC-C load generator, e.g. to get the output in a more structured way such as JSON, CSV,...

Any recommendations are much appreciated

Here are step that I am executing:

1. Create Database "loadgentpc"

$ docker run --rm -it --network cockroachdb-network cockroachdb/cockroach sql --insecure --url postgresql://root@roach1:26257?sslmode=disable --execute "CREATE DATABASE loadgentpc;"
# Server version: CockroachDB CCL v1.1.6 (linux amd64, built 2018/03/12 17:58:05, go1.8.3) (same version as client)
# Cluster ID: 32c0b615-7d26-4169-a63c-ceb6ef56d6ff
CREATE DATABASE

2. Check if DB was created

$ docker run --rm -it --network cockroachdb-network cockroachdb/cockroach sql --insecure --url postgresql://root@roach1:26257?sslmode=disable --execute "SHOW DATABASES;"
# Server version: CockroachDB CCL v1.1.6 (linux amd64, built 2018/03/12 17:58:05, go1.8.3) (same version as client)
# Cluster ID: 32c0b615-7d26-4169-a63c-ceb6ef56d6ff
+--------------------+
|      Database      |
+--------------------+
| crdb_internal      |
| information_schema |
| loadgentpc         |
| pg_catalog         |
| system             |
+--------------------+
(5 rows)

3. Load DB with loadgen-tpcc

$ docker run --rm -it --network cockroachdb-network cockroachdb/loadgen-tpcc postgresql://root@roach1:26257/loadgentpc?sslmode=disable -drop -load -v -tolerate-errors
_time______opName__ops/s(inst)__ops/s(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
   1s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0
   2s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0
   3s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0
2018/03/24 16:06:58 error in payment: dial tcp 127.0.0.1:26257: getsockopt: connection refused

This step throws error, am not sure why is it trying to connect to localhost?

We do not currently have a way to run TPCC benchmark comparisons with Oracle

Hi,

I don't think the make build command is working out-of-box:

$ make build
GOBIN=/home/zeyuanhu/loadgen/bin go install -v \
        ./vendor/github.com/golang/lint/golint \
        ./vendor/github.com/kisielk/errcheck \
        ./vendor/golang.org/x/tools/cmd/goimports
vendor/github.com/golang/lint/golint/golint.go:19:2: cannot find package "github.com/golang/lint" in any of:
        /usr/lib/go-1.10/src/github.com/golang/lint (from $GOROOT)
        /home/zeyuanhu/go/src/github.com/golang/lint (from $GOPATH)
vendor/github.com/kisielk/errcheck/main.go:13:2: cannot find package "github.com/kisielk/errcheck/internal/errcheck" in any of:
        /usr/lib/go-1.10/src/github.com/kisielk/errcheck/internal/errcheck (from $GOROOT)
        /home/zeyuanhu/go/src/github.com/kisielk/errcheck/internal/errcheck (from $GOPATH)
vendor/github.com/kisielk/errcheck/main.go:14:2: cannot find package "github.com/kisielk/gotool" in any of:
        /usr/lib/go-1.10/src/github.com/kisielk/gotool (from $GOROOT)
        /home/zeyuanhu/go/src/github.com/kisielk/gotool (from $GOPATH)
vendor/golang.org/x/tools/cmd/goimports/goimports.go:24:2: cannot find package "golang.org/x/tools/imports" in any of:
        /usr/lib/go-1.10/src/golang.org/x/tools/imports (from $GOROOT)
        /home/zeyuanhu/go/src/golang.org/x/tools/imports (from $GOPATH)
Makefile:40: recipe for target 'deps' failed
make: *** [deps] Error 1

Is there any further step I need to do? I'm new to Go.

Thanks for the help!

The TPC-H DDLs currently do not specify primary key columns for the table, but the TPC-H specification (see page 14) has an explicit requirement for what the primary key must be for each table.

The TPC-C spec has sanity checks on the randomized txns (9.2.2.5):

1. At least 0.9% and at most 1.1% of the New-Order transactions must roll back as a result of an unused item number.
2. The average number of order-lines per order must be in the range of 9.5 to 10.5 and the number of order- lines per order must be uniformly distributed from 5 to 15 for the New-Order transactions that are submitted to the SUT during the measurement interval.
3. The number of remote order-lines must be at least 0.95% and at most 1.05% of the number of order-lines that are filled in by the New-Order transactions that are submitted to the SUT during the measurement interval.
4. The number of remote Payment transactions must be at least 14% and at most 16% of the number of Payment transactions that are submitted to the SUT during the measurement interval.
5. The number of customer selections by customer last name in the Payment transaction must be at least 57% and at most 63% of the number of Payment transactions that are submitted to the SUT during the measurement interval.
6. The number of customer selections by customer last name in the Order-Status transaction must be at least 57% and at most 63% of the number of Order-Status transactions that are submitted to the SUT during the measurement interval.

We should add metrics to track the above and output these metrics when the test ends along with an indication of whether we met the requirements.

Once cockroachdb/cockroach#16611 is merged, cockroach will have a cockroach load command that can ingest CSV files rapidly. This will obviate the need to use backup/restore to load TPC-H data into cockroach. Document this in the README when that command is available, as it will be useful for higher scalefactor ingestion.

The current schema introspection is below

SELECT column_name, data_type, column_default, is_nullable FROM information_schema.columns WHERE (table_name = $1) AND (table_schema = $2)

This no longer works with 2.0 which adds table_catalog, table_schema, table_name. Suggest:

• move old table_schema to new table_catalog
• add default public to table_schema if not overridden from the command line

The spec requires all entities (districts, customers etc) be 1-indexed, but has no such requirement for warehouses, which are currently 0-indexed. For consistency, we should make warehouses 1-indexed as well.

Currently, the delivery transaction is executed synchronously by the worker that picks it. This runs counter to the spec, which states that

The Delivery transaction must be executed in deferred mode. This mode of execution is primarily characterized by queuing the transaction for deferred execution, returning control to the originating terminal independently from the completion of the transaction, and recording execution information into a result file.

However, other load generators like the Percona TPCC implementation don't implement this either. Do we need to?

It should be straightforward to copy the latency histograms from the KV code.

We do not currently have a way to run TPCC benchmark comparisons with MySql

I couldn't find example how to set this <db URL>. I'm guessing that this should be JDBC connection string? Also how can I specify user and password?

$ docker run --rm cockroachdb/loadgen-tpcc -h
Usage of tpcc:
  tpcc <db URL>

  -check
        Run consistency checks.
  ...

I tried like that, but it doesn't work.

$ docker run --rm \
    --network roach_roach-net \
    cockroachdb/loadgen-tpcc \
    jdbc:postgresql://roach1:26257 \
    -drop \
    -load \
    -v \
    -ops-stats \
    -tolerate-errors
_time______opName__ops/s(inst)__ops/s(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
   1s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0
   2s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0
   3s    newOrder          0.0         0.0      0.0      0.0      0.0      0.0
2018/03/20 21:04:41 error in payment: dial tcp 127.0.0.1:26257: getsockopt: connection refused

Thanks for any info :)

See cockroachdb/cockroach-go#16

crdb.Tx retries retryable errors, but the sniffing of this is broken by wrapping them. As a result, we're not retrying when we should:

2017/11/09 21:16:26 error in payment: select by last name fail: pq: restart transaction: HandledRetryableTxnError: TransactionAbortedError: txn aborted "sql txn" id=7f5561b8 key=/Table/51/1/98/0 rw=true pri=0.03538397 iso=SERIALIZABLE stat=ABORTED epo=1 ts=1510280185.403832112,107 orig=1510280185.403832112,107 max=1510280183.481292605,72 wto=false rop=false seq=6

In the short term, we should probably remove errors.Wrapf inside any crdb.Tx closures in this repo. In the long term, perhaps there's a way to add them back.