我怎么知道我的Amazon RDS数据库（MySQL）有多大？

#1 楼

这是针对INFORMATION_SCHEMA的更有条理的查询

存储引擎的大小

SELECT
    IFNULL(B.engine, 'Total') "Storage Engine",
    CONCAT(LPAD(REPLACE(FORMAT(B.DSize / POWER(1024, pw), 3), ',', ''), 17, ' '), ' ', SUBSTR(' KMGTP', pw + 1, 1), 'B') "Data Size",
    CONCAT(LPAD(REPLACE( FORMAT(B.ISize / POWER(1024, pw), 3), ',', ''), 17, ' '), ' ', SUBSTR(' KMGTP', pw + 1, 1), 'B') "Index Size",
    CONCAT(LPAD(REPLACE( FORMAT(B.TSize / POWER(1024, pw), 3), ',', ''), 17, ' '), ' ', SUBSTR(' KMGTP', pw + 1, 1), 'B') "Table Size" 
FROM
    (SELECT
            engine,
            SUM(data_length) DSize,
            SUM(index_length) ISize,
            SUM(data_length + index_length) TSize 
        FROM
            information_schema.tables 
        WHERE
            table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
            AND engine IS NOT NULL 
        GROUP BY engine WITH ROLLUP
    ) B,
    (SELECT 3 pw) A 
ORDER BY TSize;

数据库的大小

SELECT
    dbname,
    Concat(Lpad(Format(sdsize / Power(1024, pw), 3), 17, ' '), ' ', Substr(' KMGTP', pw + 1, 1), 'B') "Data Size",
    Concat(Lpad(Format(sxsize / Power(1024, pw), 3), 17, ' '), ' ', Substr(' KMGTP', pw + 1, 1), 'B') "Index Size",
    Concat(Lpad(Format(stsize / Power(1024, pw), 3), 17, ' '), ' ', Substr(' KMGTP', pw + 1, 1), 'B') "Total Size" 
FROM
    (SELECT
            Ifnull(db, 'All Databases') DBName,
            Sum(dsize) SDSize,
            Sum(xsize) SXSize,
            Sum(tsize) STSize 
        FROM (SELECT
                    table_schema DB,
                    data_length DSize,
                    index_length XSize,
                    data_length + index_length TSize 
                FROM information_schema.tables 
                WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
            ) AAA 
        GROUP BY db WITH rollup
    ) AA,
    (SELECT 3 pw) BB 
ORDER BY ( sdsize + sxsize ); 

按数据库/存储引擎的大小

SELECT
    Statistic,
    DataSize "Data Size",
    IndexSize "Index Size",
    TableSize "Table Size" 
FROM
    (SELECT
            IF(ISNULL(table_schema) = 1, 10, 0) schema_score,
            IF(ISNULL(engine) = 1, 10, 0) engine_score,
            IF(ISNULL(table_schema) = 1, 'ZZZZZZZZZZZZZZZZ', table_schema) schemaname,
            IF(ISNULL(B.table_schema) + ISNULL(B.engine) = 2, "Storage for All Databases", IF(ISNULL(B.table_schema) + ISNULL(B.engine) = 1, CONCAT("Storage for ", B.table_schema), CONCAT(B.engine, " Tables for ", B.table_schema))) Statistic,
            CONCAT(LPAD(REPLACE(FORMAT(B.DSize / POWER(1024, pw), 3), ',', ''), 17, ' '), ' ', SUBSTR(' KMGTP', pw + 1, 1), 'B') DataSize,
            CONCAT(LPAD(REPLACE( FORMAT(B.ISize / POWER(1024, pw), 3), ',', ''), 17, ' '), ' ', SUBSTR(' KMGTP', pw + 1, 1), 'B') IndexSize,
            CONCAT(LPAD(REPLACE(FORMAT(B.TSize / POWER(1024, pw), 3), ',', ''), 17, ' '), ' ', SUBSTR(' KMGTP', pw + 1, 1), 'B') TableSize 
        FROM
            (SELECT
                    table_schema,
                    engine,
                    SUM(data_length) DSize,
                    SUM(index_length) ISize,
                    SUM(data_length + index_length) TSize 
                FROM
                    information_schema.tables 
                WHERE
                    table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
                    AND engine IS NOT NULL 
                GROUP BY
                    table_schema, engine WITH ROLLUP
            ) B,
            (SELECT 3 pw) A
    ) AA 
ORDER BY schemaname, schema_score, engine_score;

CAVEAT

在三个（3）查询中，每个查询将会看到(SELECT 3 pw)。 pw代表1024的幂以特定单位显示结果：



(SELECT 0 pw)将以字节为单位显示报告

(SELECT 1 pw)将显示以千字节为单位的报告

(SELECT 2 pw)将以兆字节为单位显示报告

(SELECT 3 pw)将以千兆字节为单位显示报告

(SELECT 4 pw)将以兆字节为单位显示报告

(SELECT 5 pw)将以PetaBytes显示报告（如果运行此报告，请与我联系）

这里是一个报告查询，其格式略微低于KB：

SELECT
    IFNULL(db, 'Total') "Database",
    datsum / power(1024, pw) "Data Size",
    ndxsum / power(1024, pw) "Index Size",
    totsum / power(1024, pw) "Total" 
FROM
    (
        SELECT
            db,
            SUM(dat) datsum,
            SUM(ndx) ndxsum,
            SUM(dat + ndx) totsum 
        FROM
            (
                SELECT table_schema db, data_length dat, index_length ndx 
                FROM information_schema.tables 
                WHERE engine IS NOT NULL AND table_schema NOT IN ('information_schema', 'mysql')
            ) AA 
        GROUP BY db WITH ROLLUP
    ) A,
    (SELECT 1 pw) B;

试试看!!!

#2 楼

我终于找到了简单的方法，只需单击几下即可直接从Amazon获取此信息。


登录到RDS管理仪表板

单击“数据库实例” “
单击您感兴趣的实例。这将展开它并显示更多有关它的信息。
查看“监视”选项卡。

有一个“可用存储空间”图：

#3 楼

show table status from mydatabsename;，其中mydatabasename是您的数据库名称。

这将向您显示每个表的指标Data_length和Index_length以及其他指标。您必须对这些列进行总计，并记住它们以字节为单位，因此必须除以1024以获取kb，然后再除以1024以获取兆，然后再除以1024以获取gig。这也显示了索引/数据库分配中的可用空间。 5.5 / zh-CN / show-table-status.html

SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.PARTITIONS;

显示索引/数据库分配中的剩余空间...

SELECT SUM(Data_length) FROM INFORMATION_SCHEMA.PARTITIONS;

SELECT SUM(Index_length) FROM INFORMATION_SCHEMA.PARTITIONS;

...显示使用的数据和索引大小（您必须添加它们才能进行总分配）

如果您想进一步剖析一下内容...

select sum(Data_length) from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA = "myschema";

select sum(Index_length) from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA = "myschema";

select sum(DATA_FREE) from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA = "myschema";

select sum(Data_length) from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA = "myschema" and TABLE_NAME = "aspecifictable";

select sum(Index_length) from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA = "myschema" and TABLE_NAME = "aspecifictable";

当然，您也可以使用MySQL Workbench，就像我在这里找到的那​​样：http://dev.mysql.com/downloads/tools/workbench/，但前提是您拥有端口访问您的数据库服务器。不过，您也可以离线进行很多操作，因此值得下载。请注意，工作台不会对我没有意义的sum（）分配。但是话又说回来，我也没有最新版本。

#4 楼

2019年：对于MySQL和MariaDB，请参阅此AWS提供的链接：
https://aws.amazon.com/premiumsupport/knowledge-center/view-storage-rds-mysql-mariadb/

#5 楼

这个答案是关于PostgreSQL的，但是我想它也应该与MySQL相同。
步骤1-查看您的数据库存储大小（在此处了解有关将GiB转换为MB的更多信息）步骤2-查看您的数据库可用存储空间

单击监视多维数据集将为您提供更准确的数字：

步骤3-从存储中减去可用存储空间空间
应该很容易。