Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 如何准备评估某张表的大小
|
username: TiDBer_rdb3y58G
As mentioned, I tried the following three queries, and the results were vastly different. How can I correctly evaluate the table size?
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 = 'test'
AND table_name IN ('example_table2')
GROUP BY db_name , table_name , region_id) tabinfo
GROUP BY db_name , table_name;
+---------+----------------+------------------+-----------+
| db_name | table_name | Approximate_Size | Disk_Size |
+---------+----------------+------------------+-----------+
| test | example_table2 | 647.00 | 599.07 |
+---------+----------------+------------------+-----------+
SELECT
table_schema,
table_name,
table_rows,
TRUNCATE(data_length / 1024 / 1024, 2) AS 'data(MB)',
TRUNCATE(index_length / 1024 / 1024, 2) AS 'index(MB)'
FROM
information_schema.tables
WHERE
table_schema = 'test'
AND table_name = 'example_table2'
ORDER BY
data_length DESC, index_length DESC;
+--------------+----------------+------------+----------+-----------+
| table_schema | table_name | table_rows | data(MB) | index(MB) |
+--------------+----------------+------------+----------+-----------+
| test | example_table2 | 8000000 | 256.34 | 122.07 |
+--------------+----------------+------------+----------+-----------+
SELECT A.*
FROM INFORMATION_SCHEMA.TABLE_STORAGE_STATS A, information_schema.tables B
WHERE A.table_id = B.TIDB_TABLE_ID
AND A.table_schema = 'test'
AND A.table_name = 'example_table2';
+--------------+----------------+----------+------------+--------------+--------------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | PEER_COUNT | REGION_COUNT | EMPTY_REGION_COUNT | TABLE_SIZE | TABLE_KEYS |
+--------------+----------------+----------+------------+--------------+--------------------+------------+------------+
| test | example_table2 | 178 | 3 | 1 | 0 | 36 | 532480 |
+--------------+----------------+----------+------------+--------------+--------------------+------------+------------+
1 row in set (0.03 sec)
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: