How to free up disk space when TiDB has a small amount of data but a large number of SST files occupying the disk?

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

Original topic: tidb数据量不大,但存在大量sst文件占用磁盘,怎么才能释放磁盘空间?

| username: TiDBer_ypbaDT9d

[TiDB Usage Environment]
[TiDB Version] v6.5.0
[Issue Encountered: After the database disk usage reached 90%, large table data was backed up to another server, then dropped and rebuilt. The database data is now only a few dozen GB, but the disk usage still occupies more than 400 GB, with a large number of SST files present. Checked GC, and GC is executing normally, but the disk space remains occupied.]
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]



| username: zhanggame1 | Original post link

You can manually clean it up. Refer to:

| username: TiDBer_ypbaDT9d | Original post link

Executing tikv-ctl --host ip:port compact -d kv indicates success, but the disk size has not decreased.

| username: Kongdom | Original post link

Check the PD monitoring in Grafana to see if there are a large number of empty regions?

| username: TiDB_C罗 | Original post link

Wait a little longer, it will be recycled soon.

| username: TiDBer_ypbaDT9d | Original post link

According to the monitoring, there are very few empty regions.

| username: TiDBer_ypbaDT9d | Original post link

I used to think the same way. I cleaned up hundreds of gigabytes of data. Initially, the disk usage dropped by a few dozen gigabytes, but after waiting for a few days, it didn’t decrease any further. Instead, the disk usage kept increasing.

| username: redgame | Original post link

tikv-ctl --data-dir /path/to/tikv compact --db kv

| username: TiDBer_ypbaDT9d | Original post link

Prompt successful, disk space did not decrease.

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

Take a look at the size.

| username: TiDBer_ypbaDT9d | Original post link

The image you provided is not accessible. Please provide the text content that needs to be translated.

| username: h5n1 | Original post link

  1. Any method of checking table size in the database is inaccurate and should not be relied upon.
  2. Compression method:
    tikv-ctl --host tikv_ip:port compact -c write -d kv --bottommost force
    tikv-ctl --host tikv_ip:port compact -c default -d kv --bottommost force
  3. Move the GC forward. If the size hasn’t changed after completing step 2, then that’s the actual size. Consider scaling out.
| username: tidb菜鸟一只 | Original post link

That’s basically it. The SQL statistics you mentioned are for MySQL, which are not accurate in TiDB. Use the one I provided; it should be a bit more accurate. Of course, the most accurate way is to check the SST size on the host.

| username: TiDBer_ypbaDT9d | Original post link

After cleaning up several months of data, theoretically, the disk usage shouldn’t be the same as before the cleanup after just a few days. The daily data increase has basically remained unchanged.

| username: TiDBer_ypbaDT9d | Original post link

It seems like the disk usage is still the same size, even though we only cleaned up a few months’ worth of data (at that time, one KV node was using over 400GB). After a few days, the overall database size is much smaller than before the cleanup, but the disk usage remains the same. It feels like some space hasn’t been released.

| username: h5n1 | Original post link

To compact in my way, you need to add --bottommost force.

| username: TiDBer_ypbaDT9d | Original post link

After execution, the disk usage did not decrease. Monitoring shows that write operations occupy a lot, which feels like historical versions have not been reclaimed.

| username: h5n1 | Original post link

Monitor TiKV Detail → GC. Then find a table with more modifications and run explain analyze select count(*) to check.

| username: TiDBer_ypbaDT9d | Original post link

Found the reason. There is a small table that is constantly being updated, and the historical versions have taken up a lot of space. Deleting that table solved the problem.

| username: h5n1 | Original post link

The issue of historical versions not being cleaned up is also a problem. Your GC is only 10 minutes, so there shouldn’t normally be so many historical versions. I suggest upgrading.