ttljob: introduce sql.ttl.default_select_rate_limit cluster setting, ttl_select_rate_limit storage param #110742
Assignees
Labels
A-row-level-ttl
branch-release-23.2
Used to mark GA and release blockers, technical advisories, and bugs for 23.2
branch-release-23.2.0-rc
(deleted)
C-enhancement
Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
GA-blocker
T-sql-foundations
SQL Foundations Team (formerly SQL Schema + SQL Sessions)
Comments
sumeerbhola
added
C-enhancement
ecwall
changed the title
ecwall
changed the title
craig bot
pushed a commit
that referenced
this issue
Oct 17, 2023
112119: ttljob: introduce sql.ttl.default_select_rate_limit cluster setting, ttl_select_rate_limit storage param r=ecwall a=ecwall Fixes #110742 Add sql.ttl.default_select_rate_limit cluster setting and ttl_select_rate_limit table storage param to set TTL select rate limit. This sets the number of records per table per second per node that can be selected by the TTL job. Co-authored-by: Evan Wall <wall@cockroachlabs.com>
rafiss
pushed a commit
to rafiss/cockroach
that referenced
this issue
Dec 7, 2023
…ttl_select_rate_limit storage param Fixes cockroachdb#110742 Release note (sql change): Add sql.ttl.default_select_rate_limit cluster setting and ttl_select_rate_limit table storage param to set TTL select rate limit. This sets the number of records per table per second per node that can be selected by the TTL job.
rafiss
pushed a commit
to rafiss/cockroach
that referenced
this issue
Dec 7, 2023
…ttl_select_rate_limit storage param Fixes cockroachdb#110742 Release note (sql change): Add sql.ttl.default_select_rate_limit cluster setting and ttl_select_rate_limit table storage param to set TTL select rate limit. This sets the number of records per table per second per node that can be selected by the TTL job.
rafiss
added
GA-blocker
branch-release-23.2
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Ideally, admission control (AC) should handle all shaping of traffic that affects a bottleneck resource. And AC is well suited to shaping TTL traffic when the bottleneck is CPU or write bandwidth. But we have seen production cases where the TTL job is run frequently and scans a large table quickly and saturates the provisioned read bandwidth and read IOPS. These two resource dimensions are not currently considered by admission control and the work involved to change that is substantial (tracked in #107623).
For the TTL case, we need a quick workaround for the above cases. The TTL job has an optional
sql.ttl.default_delete_rate_limitthat can be set to shape the writes, but we need one for the reads too, in cases where there few writes (e.g. the TTL job running every 24h, but usual expiry happening after 365 days). We should introduce an optionalsql.ttl.default_select_rate_limitfor these scenarios.@ecwall
Additionally, add a ttl_select_rate_limit storage param similar to ttl_delete_rate_limit.
Jira issue: CRDB-31587
Epic CRDB-18322
The text was updated successfully, but these errors were encountered: