cockroachdb / loadgen Goto Github PK
View Code? Open in Web Editor NEWCockroachDB load generators
License: Apache License 2.0
CockroachDB load generators
License: Apache License 2.0
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:
RETURNING NOTHING
syntax.IMPORT
data from CSV.COUNT(*)
).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:
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:
$ 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
$ 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)
$ 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):
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:
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.
Andrews-MBP:loadgen andrewwoods$ ./tpcc/tpcc -load postgresql://localhost:5432/tpcc?sslmode=disable
couldn't create database: pq: syntax error at or near "NOT"
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.