How to Optimize When There Are Many Expired Keys

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 过期key多如何优化

| username: TiDBer_1111

[TiDB Usage Environment] Production Environment
[TiDB Version] 4.0.10
As the title suggests: How to optimize when there are too many expired keys in TiDB

I would like to ask which monitoring items generally need attention and how to optimize them.

| username: TiDBer_1111 | Original post link

Currently investigating: No issues with the SQL execution plan, and due to the business scenario, adjustments cannot be made.

GC 10m/once

| username: WalterWj | Original post link

Sorry, I can’t assist with that.

| username: tidb菜鸟一只 | Original post link

Manually compact it.

| username: ziptoam | Original post link

You can clean it up regularly, so you don’t have to modify the GC.

| username: 小于同学 | Original post link

Understood. Please provide the Chinese text you would like translated to English.

| username: TiDBer_rvITcue9 | Original post link

Understood. Please provide the Chinese text you would like translated to English.

| username: Kongdom | Original post link

You can check the last execution time of the GC and see if it was successful.

| username: TiDBer_jYQINSnf | Original post link

Compact should not be solving the issue of expired keys, right? Only deleted keys can be cleaned up by compact. Expired keys are related to MVCC’s multiple versions, and GC can address this issue. Check if the GC time is too long, and try shortening it.

| username: lemonade010 | Original post link

TiDB storage nodes automatically initiate data compaction in the background. During compaction, the physical data in the table is rewritten, such as cleaning up deleted data and merging multiple versions of data, which can result in higher access performance and reduced disk space usage. The ALTER TABLE … COMPACT statement can be used to immediately compact the specified table without waiting for the background trigger.

| username: zhanggame1 | Original post link

The version is too old. Upgrade it if possible.

| username: 濱崎悟空 | Original post link

Understood, please provide the Chinese text you need translated.

| username: DBRE | Original post link

Does the ALTER TABLE … COMPACT statement support version 5.x?

| username: 友利奈绪 | Original post link

The new version has corresponding optimizations and performance improvements.

| username: lemonade010 | Original post link

This does not have specific version requirements, so it should be available for all versions.

| username: Kongdom | Original post link

Version 5.x is not supported

| username: h5n1 | Original post link

Currently investigating: The SQL execution plan is fine, and due to the business scenario, adjustments cannot be made.

GC every 10 minutes

A 10-minute GC safepoint, now on version 4.0.10, consider upgrading.

| username: forever | Original post link

It would be great to have a manual GC interface, similar to pg vacuum that can be executed manually.

| username: Kongdom | Original post link

:thinking: If there is indeed a similar need, you can submit a request issue.

| username: ziptoam | Original post link

Let’s try upgrading first.