sqlstats: speed up flush operation #123476
Assignees
Labels
branch-release-24.1
Used to mark GA and release blockers and technical advisories for 24.1
C-bug
Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.
T-observability
Comments
xinhaoz
added
C-bug
craig bot
pushed a commit
that referenced
this issue
May 3, 2024
123254: sqlstats: rewrite SELECT FOR UDPATE in flush as INSERT UPDATE ON CONFLICT r=xinhaoz a=xinhaoz Please note only the latest commit is for review in this comment. ------------------- Previously, the UPDATE logic for flushing sql stats was split into a 3 part transaction due to using SELECT FOR UPDATE - 1. Attempt to INSERT 2. SELECT row FOR UPDATE 3. UPDATE We can make this faster by using INSERT .. ON CONFLICT UPDATE. ``` Previous: BenchmarkSqlStatsMaxFlushTime/single-application/writes=update/10000-fingerprints-10 1 32487051083 ns/op New: BenchmarkSqlStatsMaxFlushTime/single-application/writes=update/10000-fingerprints-10 1 13356979917 ns/op ``` Epic: none Fixes: #123476 Release note: None Co-authored-by: Xin Hao Zhang <xzhang@cockroachlabs.com>
blathers-crl bot
pushed a commit
that referenced
this issue
May 3, 2024
…LICT Previously, the UPDATE logic for flushing sql stats was split into a 3 part transaction due to using SELECT FOR UPDATE - 1. Attempt to INSERT 2. SELECT row FOR UPDATE 3. UPDATE We can make this faster by using INSERT .. ON CONFLICT UPDATE. ``` Previous: BenchmarkSqlStatsMaxFlushTime/single-application/writes=update/10000-fingerprints-10 1 32487051083 ns/op New: BenchmarkSqlStatsMaxFlushTime/single-application/writes=update/10000-fingerprints-10 1 13356979917 ns/op ``` Epic: none Fixes: #123476 Release note: None
xinhaoz
added a commit
that referenced
this issue
May 4, 2024
…LICT Previously, the UPDATE logic for flushing sql stats was split into a 3 part transaction due to using SELECT FOR UPDATE - 1. Attempt to INSERT 2. SELECT row FOR UPDATE 3. UPDATE We can make this faster by using INSERT .. ON CONFLICT UPDATE. ``` Previous: BenchmarkSqlStatsMaxFlushTime/single-application/writes=update/10000-fingerprints-10 1 32487051083 ns/op New: BenchmarkSqlStatsMaxFlushTime/single-application/writes=update/10000-fingerprints-10 1 13356979917 ns/op ``` Epic: none Fixes: #123476 Release note: None
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
The sql stats flush currently uses a 3 part txn to update a row in the sql stats tables on flush:
INSERT -> SELECT FOR UDPATE -> UPDATE.
This makes the update row operation much slower, increasing the overfall flush time.
This txn be shortened to a single statement,
INSERT .. ON CONFLICT UPDATE ....Jira issue: CRDB-38367
Epic CRDB-37544
The text was updated successfully, but these errors were encountered: