The space occupied by TiDB table statistics differs significantly from the actual data size

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

Original topic: TIDB 表统计空间占用量与实际数据量相差很多

| username: dovefi

[TiDB Usage Environment] Production Environment
[TiDB Version] v5.1.4
[Reproduction Path]
[Encountered Problem: Phenomenon and Impact]
In the production environment, there is a table with a total of more than 5000 rows of data, but according to the statistics from INFORMATION_SCHEMA.TIKV_REGION_STATUS, the space occupied is more than 1300GB. The business scenario is that this table frequently updates a certain field value and uses transactions.

mysql> select DB_NAME,TABLE_NAME,sum(APPROXIMATE_SIZE)/1024 as table_size_G from INFORMATION_SCHEMA.TIKV_REGION_STATUS where DB_NAME not in ('INFORMATION_SCHEMA','METRICS_SCHEMA','PERFORMANCE_SCHEMA') and TABLE_NAME="bucket" group by DB_NAME,TABLE_NAME order by table_size_G desc limit 100;
+----------+------------+--------------+
| DB_NAME  | TABLE_NAME | table_size_G |
+----------+------------+--------------+
| xxxxxx | bucket     |    1293.7627 |
+----------+------------+--------------+

Data volume

mysql> select count(1) from bucket;
+----------+
| count(1) |
+----------+
|     5029 |
+----------+

The analyze table command was used, but it seems that the statistical results are still problematic.

| username: cassblanca | Original post link

APPROXIMATE_SIZE is measured in bytes. Brother, did you perhaps miss a 1024? Moreover, this value is an estimate and cannot accurately reflect the table size.

| username: 大飞哥online | Original post link

Take a look at the IS_INDEX column: 0 means it is not an index, 1 means it is an index.

| username: 大飞哥online | Original post link

APPROXIMATE_SIZE: The approximate data size of the Region (MB)

The unit is MB, not bytes. Brother, you got it wrong.

| username: 大飞哥online | Original post link

Using GROUP BY on IS_INDEX can reveal whether the index or the data is larger.

| username: zhanggame1 | Original post link

The unit of sum(APPROXIMATE_SIZE)/1024 is indeed GB.

| username: zhanggame1 | Original post link

Check the GC progress, it shouldn’t be that there hasn’t been a GC for a long time, right?

| username: 像风一样的男子 | Original post link

I checked the table data size from INFORMATION_SCHEMA.tables using the query:
select TABLE_SCHEMA, TABLE_NAME, round(data_length/1024/1024,2), TABLE_ROWS from tables order by DATA_LENGTH desc
See if the statistics for the two tables are consistent.

| username: 像风一样的男子 | Original post link

I checked the official query for the data size of a table.

How to Estimate the Size of a Table in TiDB?

To estimate the size of a table in TiDB, you can refer to the following query:

SELECT
    db_name,
    table_name,
    ROUND(SUM(total_size / cnt), 2) Approximate_Size,
    ROUND(SUM(total_size / cnt / (SELECT
                    ROUND(AVG(value), 2)
                FROM
                    METRICS_SCHEMA.store_size_amplification
                WHERE
                    value > 0)),
            2) Disk_Size
FROM
    (SELECT
        db_name,
            table_name,
            region_id,
            SUM(Approximate_Size) total_size,
            COUNT(*) cnt
    FROM
        information_schema.TIKV_REGION_STATUS
    WHERE
        db_name = @dbname
            AND table_name IN (@table_name)
    GROUP BY db_name , table_name , region_id) tabinfo
GROUP BY db_name , table_name;

When using the above query, you need to fill in and replace the following fields according to your actual situation:

  • @dbname: The name of the database.
  • @table_name: The name of the target table.

Additionally, in the above query:

  • store_size_amplification represents the average compression ratio of the cluster. Besides using the SELECT * FROM METRICS_SCHEMA.store_size_amplification; query to get this information, you can also check the Size amplification metric under the PD - statistics balance panel in Grafana monitoring for each node. The average compression ratio of the cluster is the average of the Size amplification values for all nodes.
  • Approximate_Size represents the size of a single replica of the table before compression. This value is an estimate and not an exact value.
  • Disk_Size represents the size of the table after compression, which can be estimated based on Approximate_Size and store_size_amplification.
| username: Fly-bird | Original post link

The man who is like the wind is very professional :smiley:

| username: zhanggame1 | Original post link

The official documentation, in fact, isn’t very useful for this kind of query.

| username: Kongdom | Original post link

:+1: :+1: :+1: This is the closest to the actual value, officially certified.

| username: zhanggame1 | Original post link

It doesn’t solve the actual problem, such as why 5000 rows of data occupy 1300G of space.

| username: 大飞哥online | Original post link

The index data is also in this table.

| username: 大飞哥online | Original post link

So we need to check the IS_INDEX column to see whether it is occupied by data or by the index.

| username: Kongdom | Original post link

It could be due to frequent additions, modifications, and deletions of data, resulting in unmerged regions or historical versions that have not been garbage collected.

| username: ajin0514 | Original post link

APPROXIMATE_SIZE: Approximate data size of the Region (MB) take a look

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

Just compact this table, add -c write -d kv

mysql -uroot -pXXX -hxxx -PXXX information_schema -e "select region_id from tikv_region_status WHERE DB_NAME NOT IN ('INFORMATION_SCHEMA', 'METRICS_SCHEMA', 'PERFORMANCE_SCHEMA') AND TABLE_NAME = 'bucket'" > region_list
cat region_list | while read line
do
  tiup ctl:v5.1.4 tikv --host xxxx:20160 compact -r $line -d kv -c write --threads 1 --bottommost force
  tiup ctl:v5.1.4 tikv --host xxx:20160 compact -r $line -d kv -c default --threads 1 --bottommost force
done
| username: dovefi | Original post link

I checked and it is real data.

| username: dovefi | Original post link

The DBA found that the GC has not been executed for a long time. Please have the DBA follow up and take a look.