Forgot the size of each database, want to calculate the size of all schemas

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

Original topic: 忘了各个库大小了,想统计所有schema的大小

| username: TiDBer_小阿飞

[TiDB Usage Environment] /Test/
[TiDB Version] V6.5.3
[Reproduction Path] As mentioned, I want to check the size of all databases, but it seems that the MySQL method doesn’t work.
[Encountered Problem: Problem Phenomenon and Impact]

1. Using the panel to query, the disk space usage is as follows:



2. Using SQL to query

[Resource Configuration]


This… does anyone have any solutions? I’ve tried switching schemas, but it didn’t help!

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

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;
Take a look at it this way.
| username: TiDBer_小阿飞 | Original post link

What is the unit of Approximate_Size? Is it KB?

| username: Kongdom | Original post link

  • APPROXIMATE_SIZE(MB): The estimated data size of the Region, in MB.
| username: tidb菜鸟一只 | Original post link

MB, is this your test cluster? It doesn’t seem to have much data…

| username: TiDBer_小阿飞 | Original post link

Well, test cluster.

| username: TiDB_C罗 | Original post link

The one I am running is inconsistent with the one on the PD dashboard.

| username: swino | Original post link

SELECT
    table_schema AS `database`,
    CONCAT(ROUND(SUM(data_length) / 1024 / 1024, 2), ' MB') AS `size`
FROM
    information_schema.tables
GROUP BY table_schema;
| username: 像风一样的男子 | Original post link

Query the length of each table in the database

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

| username: 普罗米修斯 | Original post link

SELECT
    table_schema AS 'Database',
    SUM(table_rows) AS 'Record Count',
    SUM(TRUNCATE(data_length / 1024 / 1024, 2)) AS 'Data Size (MB)',
    SUM(TRUNCATE(index_length / 1024 / 1024, 2)) AS 'Index Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length) DESC, SUM(index_length) DESC;
| username: andone | Original post link

select TABLE_SCHEMA, TABLE_NAME, round(data_length/1024/1024, 2), TABLE_ROWS 
from INFORMATION_SCHEMA.`TABLES` 
order by DATA_LENGTH desc
| username: zhanggame1 | Original post link

The MySQL method works too. In fact, with three replicas and compression, the estimated size and the actual disk usage usually differ quite a bit.

| username: Kongdom | Original post link

You can refer to the official solution provided

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.