Start Here
See and learn how Finch works by creating and running simple benchmarks. Follow this page from top to bottom ↓
Compile the finch binary:
cd bin/finch/
go build finch
Stay in this directory.
Create a MySQL user, database, and table that Finch can use. Running the following as a MySQL root/super user.
To copy, mouse over a code block and click the button in the upper-right.
CREATE USER finch@'%' IDENTIFIED BY 'amazing';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES
ON finch.* TO finch@'%';
CREATE DATABASE IF NOT EXISTS finch;
USE finch;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id INT UNSIGNED NOT NULL PRIMARY KEY,
n INT NOT NULL,
c VARCHAR(100) NOT NULL,
INDEX (n)
);
Copy-paste each code block into the file name given above each one.
insert-rows.sql
-- rows: 100,000
INSERT INTO finch.t1 (id, n, c) VALUES (@id, @n, @c)
setup.yaml
stage:
trx:
- file: insert-rows.sql
data:
id:
generator: "auto-inc"
n:
generator: "int"
c:
generator: "str-fill-az"
Run the INSERT benchmark:
./finch setup.yaml
Finch should complete after few seconds with output similar to below (click to expand).
The benchmark statistics are printed near the end of the output like (scroll right →):
interval| duration| runtime| clients| QPS| min| P999| max| r_QPS| r_min| r_P999| r_max| w_QPS| w_min| w_P999| w_max| TPS| c_min| c_P999| c_max| errors|compute
1| 60.8| 60.8| 1| 1,645| 365| 3,019| 192,360| 0| 0| 0| 0| 1,645| 365| 3,019| 192,360| 0| 0| 0| 0| 0|local
Your numbers will vary (possible by a lot). Since this is a quick tutorial, let’s just examine these four columns that are stats for all queries:
QPS| min| P999| max|
1,645| 365| 3,019| 192,360|
Everyone knows QPS: 1,645 on this run. The next three columns are query response times, and Finch reports all query times in microseconds (µs).
- The minimum query response time was 365 µs. The compute has a locally-attached SSD, so this minimum is believable.
- The P999 (99.9th percentile) query response time was about 3.0 milliseconds (converting from µs).
- The maximum query response time was about 192 milliseconds (converting from µs), which is high for a locally-attached SSD.
Copy-paste each code block into the file name given above each one.
read-only.sql
SELECT n, c FROM finch.t1 WHERE id = @id
read-only.yaml
stage:
runtime: 10s
workload:
- clients: 4
trx:
- file: read-only.sql
data:
id:
generator: "int"
Run the read-only benchmark:
./finch read-only.yaml
What kind of QPS and response time stats did you get on your machine? On this machine:
interval| duration| runtime| clients| QPS| min| P999| max| r_QPS| r_min| r_P999| r_max| w_QPS| w_min| w_P999| w_max| TPS| c_min| c_P999| c_max| errors|compute
1| 10| 10| 4| 17,651| 47| 794| 2,406| 17,651| 47| 794| 2,406| 0| 0| 0| 0| 0| 0| 0| 0| 0|local
17,651 QPS: not bad. A 47 microsecond read: very fast. A 2.4 millisecond read: pretty slow.
The previous two benchmarks were too easy. Let’s write a transaction (trx) that locks and update rows, then run that trx on several clients. To make it extra challenging, let’s limit it to the first 1,000 rows. This should create row lock contention and slow down performance noticeably.
Copy-paste each code block into the file name given above each one.
rw-trx.sql
BEGIN
SELECT c FROM finch.t1 WHERE id = @id FOR UPDATE
UPDATE finch.t1 SET n = n + 1 WHERE id = @id
COMMIT
row-lock.yaml
stage:
runtime: 20s
workload:
- clients: 4
trx:
- file: rw-trx.sql
data:
id:
generator: "int"
scope: trx
params:
max: 1,000
Before running the benchmark, execute this query on MySQL to get the current number of row lock waits:
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits';
Then run the slow row-lock benchmark:
./finch row-lock.yaml
Once the benchmark completes (in 20s), execute SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits'; on MySQL again.
The number of row locks waits should have increased, proving that the trx caused them.
Query response times should be higher (slower), too:
interval| duration| runtime| clients| QPS| min| P999| max| r_QPS| r_min| r_P999| r_max| w_QPS| w_min| w_P999| w_max| TPS| c_min| c_P999| c_max| errors|compute
1| 20| 20| 4| 9,461| 80| 1,659| 79,518| 2,365| 148| 1,096| 37,598| 2,365| 184| 1,202| 40,770| 2,365| 366| 2,398| 79,518| 0|local
Scroll right (→) and notice TPS 2,365: Finch measures transactions per second (TPS) when the Finch trx (rw-trx.sql) has an explicit SQL transaction: BEGIN and COMMIT.
c_max 79,518 means the maximum COMMIT time was 79,518 µs (80 milliseconds), which is pretty slow for a locally-attached SSD, but this is why Finch measures COMMIT latency: when a transaction commits, MySQL makes the data changes durable on disk, which is one of the slowest (but most important) operations of an ACID-compliant database.
Learn the Concepts underlying these benchmarks and how Finch works.