Description

When a table is too small, it is located in just one region, that region would become a hotspot, and such hot spots would cause a performance bottleneck. By directly caching the small table data in the TiDB layer, such hot spot issues can be solved.

For a small, often used and rarely changed table, caching the whole table in memory in the TiDB server can improve performance.

Document Collection

• Proposal doc: docs: add small table caching proposal #23673
• Weekly report: See the comments of this issue.

Talent Challenge Program information

• Mentor of this issue: @tiancaiamao
• Recommended skills: Golang, DDL online schema change(Chinese), lease(Chinese)
• Estimated Workloads: 2 Month

Milestones and action items

Milestone 1: Support schema change for caching table, Expected finish date: TBD

• add grammar support in parser #28926
• handle DDL schema changes when a table is set to cachable #29085
  • support alter table xxx nocache operation #29645
• compatibility check with other feature
  • admin check / checksum table #29124
  • forbid stale read for cache table and support history read #29218
  • Forbid cache table operation on partitioned table or view #29318
  • disable prepare plan cache for cached table #29641
  • For cache table should not be allowed to create indexes #29659
  • forbit 'alter table cache' on INFORMATION_SCHEMA,PERF_SCHEMA etc #29837
  • statement summary should know the slow write is blocked on read lock lease #29927
  • Disable some DDL operation when the table is cached #32826
• compatibility check with ecosystem tools
  • do not write binlog for 'alter table cache/nocache' DDL statement #31105
  • ignore the TableCacheStatus table info in br, so it's handled as a normal table #31190

Milestone 2: Support reading and writing on cached table, Expected finish date: TBD

• handle meta data for the cache table #29151
• Support writing to a cached table #29445
  • renew write lock lease operation for cached table #30205
• Support read from a cached table #29446
  • Load the table data to membuffer and cache it #29546
  • Add Renew read lease operation for cache table #29849
  • Make the lease time configurable #29485

Milestone 3: Test with abnormal injection cases

• Test by inject failpoint during some DDL change phase
• Chaos testing for the read/write operation

Misc:

• metrics for small cached table #29958
• Add telemetry for cache table #29959
• 'show create table' and INFORMATION_SCHEMA.TABLE #30950
• limit the size of the table that can be cached #30962

Limitation & Known bugs

• small cached table doesn't support index merge #29682
• clean up the mysql.table_cache_meta table data
• maintain the local state to avoid unnecessary load cache request
• better display for 'explain analyze ...' on a cached table #31184