search

k3s-io / kine

Run Kubernetes on MySQL, Postgres, sqlite, dqlite, not etcd.
Apache License 2.0
1.65k stars 237 forks source link

Switch to single-transaction native SQL compaction #51

Closed brandond closed 4 years ago

brandond commented 4 years ago

Adds additional indexes and a transaction-locked optimized native SQL compaction query that replaces the existing unlocked row-by-row compaction logic. Highly inspired by https://github.com/kubernetes/kubernetes/blob/master/staging/src/k8s.io/apiserver/pkg/storage/etcd3/compact.go

This results in a roughly 400x reduction in compaction time for postgres, 150x for mysql, and a 40x for sqlite. Most of this is due to reduced round-trips and throughput to the database - the current code retrieves, evaluates, and (potentially) deletes each row individually during compaction.

Also updates SQL drivers to fix transaction handling bugs:

This is related to https://github.com/rancher/k3s/issues/1311

Test cluster: k3s 1.19.3 - 1 server 1 agent - rancher 2.5 - artifactory-jcr app

  1. start kine standalone binary
  2. start k3s server with external kine as datastore endpoint
  3. start k3s agent
  4. install cert-manager helm chart from cli
  5. install rancher helm chart from cli
  6. log in to rancher, configure local cluster, add artifactory-jcr app with ClusterIP
  7. wait until 3rd compact is run

Code from this PR:

postgres: 
time="2020-10-22T16:09:44-07:00" level=info msg="Configuring DB connection pooling: maxIdleConns=2, maxOpenConns=0, connMaxLifetime=0s"
time="2020-10-22T16:09:44-07:00" level=info msg="Kine listening on tcp://0.0.0.0:2379"
time="2020-10-22T16:14:44-07:00" level=info msg="COMPACT deleted 0 rows from 2 revisions in 1.087702ms - compacted to 2/4983"
time="2020-10-22T16:19:45-07:00" level=info msg="COMPACT deleted 3084 rows from 4981 revisions in 26.46531ms - compacted to 4983/7537"
time="2020-10-22T16:24:44-07:00" level=info msg="COMPACT deleted 2427 rows from 2554 revisions in 16.755748ms - compacted to 7537/10119"

mysql:
time="2020-10-22T16:36:33-07:00" level=info msg="Configuring DB connection pooling: maxIdleConns=2, maxOpenConns=0, connMaxLifetime=0s"
time="2020-10-22T16:36:34-07:00" level=info msg="Kine listening on tcp://0.0.0.0:2379"
time="2020-10-22T16:41:34-07:00" level=info msg="COMPACT deleted 0 rows from 2 revisions in 994.448µs - compacted to 2/4832"
time="2020-10-22T16:46:34-07:00" level=info msg="COMPACT deleted 2973 rows from 4830 revisions in 77.697576ms - compacted to 4832/7550"
time="2020-10-22T16:51:34-07:00" level=info msg="COMPACT deleted 2545 rows from 2718 revisions in 42.397822ms - compacted to 7550/9896"

sqlite:
time="2020-10-22T16:53:49-07:00" level=info msg="Configuring DB connection pooling: maxIdleConns=2, maxOpenConns=0, connMaxLifetime=0s"
time="2020-10-22T16:53:49-07:00" level=info msg="Kine listening on tcp://0.0.0.0:2379"
time="2020-10-22T16:58:49-07:00" level=info msg="COMPACT deleted 0 rows from 2 revisions in 133.642µs - compacted to 2/4682"
time="2020-10-22T17:03:49-07:00" level=info msg="COMPACT deleted 2871 rows from 4680 revisions in 23.521617ms - compacted to 4682/7546"
time="2020-10-22T17:08:49-07:00" level=info msg="COMPACT deleted 2642 rows from 2864 revisions in 19.711813ms - compacted to 7546/9892"

Current code (with stats logging patch https://github.com/brandond/kine/commit/45327c4f0051297309ae1f8ea848e132a172eab7)

postgres:
time="2020-10-23T01:40:38-07:00" level=info msg="Configuring DB connection pooling: maxIdleConns=2, maxOpenConns=0, connMaxLifetime=0s"
time="2020-10-23T01:40:38-07:00" level=info msg="Kine listening on tcp://0.0.0.0:2379"
time="2020-10-23T01:45:38-07:00" level=info msg="COMPACT deleted 0 rows from 0 revisions in 321ns - compacted to 0/5071"
time="2020-10-23T01:50:48-07:00" level=info msg="COMPACT deleted 2345 rows from 4072 revisions in 9.309559208s - compacted to 4072/7584"
time="2020-10-23T01:55:46-07:00" level=info msg="COMPACT deleted 2299 rows from 2513 revisions in 7.371245154s - compacted to 6585/10132"

mysql:
time="2020-10-23T01:57:53-07:00" level=info msg="Configuring DB connection pooling: maxIdleConns=2, maxOpenConns=0, connMaxLifetime=0s"
time="2020-10-23T01:57:53-07:00" level=info msg="Kine listening on tcp://0.0.0.0:2379"
time="2020-10-23T02:02:53-07:00" level=info msg="COMPACT deleted 0 rows from 0 revisions in 491ns - compacted to 0/5269"
time="2020-10-23T02:08:03-07:00" level=info msg="COMPACT deleted 2488 rows from 4270 revisions in 10.639137845s - compacted to 4270/7635"
time="2020-10-23T02:13:00-07:00" level=info msg="COMPACT deleted 2114 rows from 2366 revisions in 7.556593544s - compacted to 6636/9972"

sqlite:
time="2020-10-23T01:20:01-07:00" level=info msg="Configuring DB connection pooling: maxIdleConns=2, maxOpenConns=0, connMaxLifetime=0s"
time="2020-10-23T01:20:01-07:00" level=info msg="Kine listening on tcp://0.0.0.0:2379"
time="2020-10-23T01:25:01-07:00" level=info msg="COMPACT deleted 0 rows from 0 revisions in 280ns - compacted to 0/5177"
time="2020-10-23T01:30:02-07:00" level=info msg="COMPACT deleted 2409 rows from 4178 revisions in 1.047498734s - compacted to 4178/7431"
time="2020-10-23T01:35:02-07:00" level=info msg="COMPACT deleted 1994 rows from 2254 revisions in 697.222053ms - compacted to 6432/9780"
davidnuzik commented 4 years ago

@brandond When this is ready, can we also backport into 1.17, 1.18, and 1.19 ? Hosted Rancher will be needing this fix.

brandond commented 4 years ago

I've just added some before/after perf figures.

brandond commented 4 years ago

Just for the record, this was run against a RDS MySQL database that had grown out of control (~450GB) and while the compaction did not run as fast as it would on a clean database, it did not affect etcd client (k3s server) functionality. 

Configuring mysql database connection pooling: maxIdleConns=2, maxOpenConns=10, connMaxLifetime=0s
Kine listening on tcp://0.0.0.0:2380
COMPACT deleted 1000 rows from 1000 revisions in 260.646311ms - compacted to 54507838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 209.451541ms - compacted to 54508838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 210.975307ms - compacted to 54509838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 209.147251ms - compacted to 54510838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 249.981449ms - compacted to 54511838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 408.709057ms - compacted to 54512838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 482.409274ms - compacted to 54513838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 216.395163ms - compacted to 54514838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 244.119247ms - compacted to 54515838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 421.660987ms - compacted to 54516838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 297.367336ms - compacted to 54517838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 237.869493ms - compacted to 54518838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 242.567905ms - compacted to 54519838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 371.445965ms - compacted to 54520838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 443.500974ms - compacted to 54521838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 221.708513ms - compacted to 54522838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 430.106759ms - compacted to 54523838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 514.208017ms - compacted to 54524838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 1.4346351s - compacted to 54525838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 1.24711466s - compacted to 54526838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 1.241122638s - compacted to 54527838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 1.352619799s - compacted to 54528838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 1.239903625s - compacted to 54529838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 1.25365536s - compacted to 54530838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 1.346442674s - compacted to 54531838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 1.195258922s - compacted to 54532838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 1.375034317s - compacted to 54533838/66353379
COMPACT deleted 1000 rows from 1000 revisions in 1.338638356s - compacted to 54534838/66353379