忘了各个库大小了，想统计所有schema的大小

[TiDBer_小阿飞]

【 TiDB 使用环境】/测试/
【 TiDB 版本】V6.5.3
【复现路径】如题，想查下所有库的大小，用MySQL的方法貌似不行啊
【遇到的问题：问题现象及影响】

1.使用面板查询，占用磁盘空间如下：

2.使用SQL查询

【资源配置】

这。。。各位大神有啥办法？试过切换schema了，没啥用！

[tidb菜鸟一只 (小菜一颗)]

SELECT
db_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
GROUP BY db_name , table_name , region_id) tabinfo
GROUP BY db_name ;
这样看下

[TiDBer_小阿飞]

Approximate_Size的单位是啥？KB么？

[Kongdom]

• APPROXIMATE_SIZE(MB)：估算的 Region 的数据量大小，单位是 MB。

[tidb菜鸟一只 (小菜一颗)]

MB，你这是测试集群吗，看着没啥数据啊。。。

[TiDBer_小阿飞]

嗯，测试集群

[TiDB_C罗]

我运行的跟PD dashboard上的不一致

[swino]

SELECT
table_schema AS database,
CONCAT(ROUND(SUM(data_length) / 1024 / 1024, 2), ’ MB’) AS size
FROM
information_schema.tables
GROUP BY table_schema;

[像风一样的男子]

查询数据库各个表长度

select TABLE_SCHEMA,TABLE_NAME, round(data_length/1024/1024,2),TABLE_ROWS from INFORMATION_SCHEMA.TABLES order by DATA_LENGTH desc

[普罗米修斯]

select

table_schema as ‘数据库’,

sum(table_rows) as ‘记录数’,

sum(truncate(data_length/1024/1024, 2)) as ‘数据容量(MB)’,

sum(truncate(index_length/1024/1024, 2)) as ‘索引容量(MB)’

from information_schema.tables

group by table_schema

order by sum(data_length) desc, sum(index_length) desc;

[andone (Ti D Ber Lq Q Mt Rp V)]

select TABLE_SCHEMA,TABLE_NAME, round(data_length/1024/1024,2),TABLE_ROWS from INFORMATION_SCHEMA.TABLES order by DATA_LENGTH desc

[zhanggame1 (Ti D Ber G I13ecx U)]

MySQL的方法也行，实际上三副本加压缩，估算出来的大小和实际硬盘占用一般差距挺大的

[Kongdom]

可以参考官方给出方案
https://docs.pingcap.com/zh/tidb/v6.5/manage-cluster-faq#如何预估-tidb-中一张表的大小