11

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 how big the data in it actually is (and therefore how much of the 5GB quota I have left)?

11

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 !!!

4

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

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

enter image description here

  • From the RDS docs, freeable memory is "how much RAM is available on the DB instance, in megabytes", not the storage usage of the instance: docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… – Evan Kroske Jun 5 '17 at 14:32
  • @EvanKroske It looks like "Free Storage Space" is actually the metric I want. Thanks for the doc link. – Stephen Ostermiller Jun 5 '17 at 15:06
  • It looks like AWS has revamped the entire interface since I wrote this answer. I've updated it based on what I see today. – Stephen Ostermiller Jun 5 '17 at 15:14
  • looks like they may have revamped it again, i dont see a monitoring tab. and the storage just lists the total size allocated. – Brian Thomas Jun 21 '18 at 19:47
2

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: http://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: http://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.

  • I have over 100 databases in this RDS instance. Is there a convenient way to query all of them and sum everything up? – Stephen Ostermiller May 12 '14 at 19:15
  • @Stephen Ostermiller 100 databases or 100 tables? A database consists of one or more schema(s). A schema is typically a subject area with related tables. – closetnoc May 12 '14 at 19:33
  • 100 databases each with about 20 tables. – Stephen Ostermiller May 12 '14 at 19:38
  • I am experimenting with the example on the page I referred to... will get back to you in just a sec. – closetnoc May 12 '14 at 19:41
  • @Stephen Ostermiller Updated the answer. Hope this helps. – closetnoc May 12 '14 at 20:02
1

2019 : For MySQL and MariaDB Please refer to this AWS provided link: https://aws.amazon.com/premiumsupport/knowledge-center/view-storage-rds-mysql-mariadb/

  • Useful to know what is using the storage, but my problem was just knowing how much storage I had left. – Stephen Ostermiller Sep 16 at 16:04
  • 1
    You can look at FreeStorageSpace metric in RDS monitoring for that and it has been mentioned in the document as well. – Vigneshwar Sep 16 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.