Optimizing hotspot small tables

[tiancaiamao]

Focus this week	Current status
- Disable DDL for some cases - Finish performance test report	Target: Make this feature GA - KR1: Finish developing 95% - KR2: Finish testing 90% - KR3: Document, GA announce, etc 90%
Plan for the next 4 weeks	Status indicator
Finish all testing	The previous week is spend on core bank performance testing, and the result is expected

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

[tiancaiamao]

I'll just use the comment for the project progess here for the weekly report.
The date is recorded, so I don't need to write it.
The timeline is reversed, so the latest progress is on the bottom.

[tiancaiamao]

2021/10/25 This week:

• The parser PR is merged
• The ddl change part for 'alter table cache' is in review.

[tiancaiamao]

2021/11/01 This week:

• Two PRs for feature compatibility are merged: admin check table and stale read
• One PR to implement DML read operation is now ready for review
• And also a draft PR for the meta lock data management
• One more about the feature compatibility in reivew: forbit partition table and view

[tiancaiamao]

2021/11/08 This week:

• The first PR for read operation is merged
• The next PR to fix cache condition(issue introduced by the previous one) is merged
• Support write operation is pushed and under active review, @lcwangchao gave some insightful comments, several TODOs are found there:
  • loadDataFromOriginalTable should use a seperate transaction
  • The safety of "begin; sleep(1h)..." case is not well considered in the past
  • lock intend state should introduce a oldReadLease field to handle corner case
  • Both read and write operation need to renew lease for their locks

[JayLZhou]

@tiancaiamao please add this issue into compatibility check with other feature

[tiancaiamao]

2021/12/06

• No progress last week

Blocked by PR reviewing #30066, and it's been two weeks pending for review and 3 weeks since this work started. @bb7133

[tiancaiamao]

2021/12/11

• No progress on developing
• The blocking PR #30066 finally get merged (after 3 weeks)
  so renew write lock lease #30206 is now ready for review after it.

[tiancaiamao]

2021/12/20

• #30206 got merged, so the development is basically done
• Planning for testing now

[tiancaiamao]

2021/12/27

• The testing plan document was written last week
• The design document PR was updated and is now ready for review
• There are 2 new PRs are filed:
  • update the display of 'show create table' #30951
  • introduce a @@tidb_cache_table_lease sys variable #31018

[tiancaiamao]

2022/01/03

• This feature will not be GA in this sprint(5.4), because the testing is not finished yet
• Several PRs are filed this week for ecosystem tools compatibility #31105, #31106, #31191
• 1 bug is found #31077 during test and fixed
• The project will be paused for next weeks because @tiancaiamao will be oncall in the fire brigade team

[tiancaiamao]

2022/01/17

• In the last two weeks, the project is paused because of the oncall.
  This week the SQL infra team is in the bug jail again...

[tiancaiamao]

2022/02/14

There is no update for a long time during the Chinese new year (Spring festival)
Last week it was restarted.

During this period of time, several bugs are found during the performance test in the core banking scene.

• Repeated renew lease operation cause performance decrease issue/31474
• Support prepared plan cache for cached table issue/32003
• Aggregation push down to coprocesson conflict with cached table issue/32157

They're all fixed, and a basic QPS metric for the table cache visited is added pull/32171

[tiancaiamao]

2022/02/21

The performance test in the core bank scenario is done.
After caching some tables, the latency decrease by 23.8%, and throughput increase by 27.8% in RC isolation level.
And the latency decrease by 28.4%, and throughput increase by 35% in RR isolation level.

Three issues are found in the last week:

• Transaction conflict on internal SQL, "mysql.table_cache_meta" table #32386
• Filter condition is missing caused wrong result in cached table scanning #32422
• UnionScan is extremely inefficient on reading #32433

And I've filed PR for the first two #32387, they are under reviewing.
The last one is a performance issue, it's not cause by cached table itself, it's a long existing problem of the UnionScan executor.

[tiancaiamao]

2022/02/28

Last week there is not much progress.

• 3 PRs are blocked in reviewing #32387 #32590 #32643
• A new bug is found A corner case of renew lease operation on cached table #32642