: How can I tell how big my Amazon RDS database (MySQL) is? I have a MySQL database for my website hosted on Amazon AWS using RDS. It has 5GB of storage available to it. How can I tell

@Heady270

I finally found the easy way to get this information directly from Amazon with a couple of clicks.


Log into the RDS management dashboard
Click on "DB Instances"
Click on the instance in which you are interested. That should expand it and show much more info about it.
View the "Monitoring" tab on the left (it should selected by default)
There is a "Storage" monitor that tells how much space it uses and shows a graph of how much is available:

Vote Up Vote Down

@Welton855

Here are more organized queries against the INFORMATION_SCHEMA

Sizes By Storage Engine

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;


Sizes By Database

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);


Sizes By Database/Storage Engine

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

In each of the three(3) queries, you will see (SELECT 3 pw). The pw stands for the Power Of 1024 to display the results in specific units:


(SELECT 0 pw) will Display the Report in Bytes
(SELECT 1 pw) will Display the Report in KiloBytes
(SELECT 2 pw) will Display the Report in MegaBytes
(SELECT 3 pw) will Display the Report in GigaBytes
(SELECT 4 pw) will Display the Report in TeraBytes
(SELECT 5 pw) will Display the Report in PetaBytes (please contact me if you run this one)


Here is a report query with a little less formatting in 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;


Give it a Try !!!

Vote Up Vote Down

@Jamie184

show table status from mydatabsename; where mydatabasename is your database name.

This shows you the metrics Data_length and Index_length per table and other metrics. You would have to total these columns and remember that they are in bytes so you would have to divide by 1024 to get kb and then by 1024 again to get megs and then by 1024 again to get gigs. This also shows the free space within your index/database allocation.

You can get more granular and sum() if you want to explore: dev.mysql.com/doc/refman/5.5/en/show-table-status.html
SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.PARTITIONS;


Shows remaining space in index/database allocation...

SELECT SUM(Data_length) FROM INFORMATION_SCHEMA.PARTITIONS;

SELECT SUM(Index_length) FROM INFORMATION_SCHEMA.PARTITIONS;


...shows data and index size used (you will have to add them for total allocation)

If you want to dissect things a bit more...

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";


Of course you can also use the MySQL Workbench like I do found here: dev.mysql.com/downloads/tools/workbench/ but that assumes you have port access to your database server. Still, you can do a lot offline too so worth the download. Please note that the workbench does not sum() allocations which does not make sense to me. But then again, I do not have the latest version either.

Vote Up Vote Down