MySQL Workbench Database Sizes

Question

I'm trying to find the total size on the hard disk that all of my MySQL Workbench databases are using.

Does anyone know of an easy way to figure this out?

If nothing else, the default location mysql/workbench uses for saving the raw data on a windows machine?

Thanks in advance! Quintis

Answer 1

I have some queries you can run against the INFORMATION_SCHEMA

Run this to get the Total MySQL Data and Index Usage 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;

Run this to get the Total MySQL Data and Index Usage 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);

Run this to get the Total MySQL Data and Index Usage By Database and 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 query, you will see (SELECT 3 pw). The pw stands for the Power Of 1024 to display the results.

• (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:

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

UPDATE

I just ran the first query as is on a client's DB

mysql> 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;
+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size | Index Size | Table Size |
+----------------+----------------------+----------------------+----------------------+
| MyISAM | 0.673 GB | 0.079 GB | 0.752 GB |
| InnoDB | 4.227 GB | 2.436 GB | 6.663 GB |
| Total | 4.900 GB | 2.515 GB | 7.415 GB |
+----------------+----------------------+----------------------+----------------------+
3 rows in set (0.79 sec)
mysql>

It works as is.

UPDATE 2015年01月16日 14:46 EST

Here are Queries to Report Sizes and Computes the Memory Units On-The-Fly

#Total MySQL Data and Index Usage By Storage Engine

SELECT IFNULL(ENGINE,'Total') "Storage Engine",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size" FROM
(SELECT ENGINE,DAT,NDX,TBL,IF(px>4,4,px) pw1,
IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT ENGINE,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM (SELECT engine,data_length,index_length
FROM information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql') AND ENGINE IS NOT NULL) AAA
GROUP BY ENGINE WITH ROLLUP) AAA ) AA) A,(SELECT ' BKBMBGBTB' units) B;

Total MySQL Data and Index Usage By Database

SELECT IFNULL(DB,'Total') "Database",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM (SELECT DB,DAT,NDX,TBL,IF(px>4,4,px) pw1,
IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT DB,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM
(SELECT table_schema DB,data_length,index_length FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')
AND ENGINE IS NOT NULL) AAA GROUP BY DB WITH ROLLUP) AAA) AA) A,
(SELECT ' BKBMBGBTB' units) B;

Total MySQL Data and Index Usage By Database/Storage Engine

SELECT IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size" FROM
(SELECT DB,ENGINE,DAT,NDX,TBL,
IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT DB,ENGINE,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM (SELECT table_schema DB,ENGINE,
data_length,index_length FROM information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql') AND ENGINE IS NOT NULL) AAA
GROUP BY DB,ENGINE WITH ROLLUP) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

UPDATE 2025年05月20日 17:46 EDT

MySQL 8.0 now has information_schema.innodb_tablespaces which has the column FILE_SIZE. This has been incorporated into these queries. You may be wondering why is there a LEFT JOIN in these queries. This is cover for tables that are not using the InnoDB Storage Engine (such as MyISAM).

MySQL 8.0 Database Size

SELECT IFNULL(DBN,'Total DB Size') DB
,LPAD(FORMAT(RC,0),14,' ') RowCount
,LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)),wd,' ') DataSize
,LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)),wd,' ') IndexSize
,LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)),wd,' ') TotalSize
,LPAD(CONCAT(FORMAT(DTF/POWER(1024,pw4),2),' ',SUBSTR(units,pw4*2+1,2)),wd,' ') DataFree
,LPAD(CONCAT(FORMAT(FSZ/POWER(1024,pw5),2),' ',SUBSTR(units,pw5*2+1,2)),wd,' ') FileSize
,LPAD(FORMAT(FRG,2),7,' ') FragPct
FROM (
 SELECT
 DB DBN,DAT,NDX,TBL,DTF,RC,FSZ,100-(TBL*100/FSZ) FRG
 ,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3,IF(pf>4,4,pf) pw4,IF(ps>4,4,ps) pw5
 FROM
 (
 SELECT *,
 FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
 FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
 FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz,
 FLOOR(LOG(IF(DTF=0,1,DTF))/LOG(1024)) pf,
 FLOOR(LOG(IF(FSZ=0,1,FSZ))/LOG(1024)) ps
 FROM (
 SELECT DB,TB,DAT,NDX,(DAT+NDX) TBL,DTF,RC,IFNULL(FILE_SIZE,DAT+NDX) FSZ FROM (
 SELECT table_schema DB,table_name TB
 ,SUM(data_length) DAT
 ,SUM(index_length) NDX
 ,SUM(data_free) DTF
 ,SUM(table_rows) RC
 FROM information_schema.tables
 WHERE table_schema NOT IN ('information_schema','performance_schema','mysql','sys')
 AND engine IS NOT NULL
 GROUP BY table_schema,table_name WITH ROLLUP
 ) AAAA LEFT JOIN information_schema.innodb_tablespaces BBBB
 ON CONCAT(DB,'/',TB) = BBBB.name WHERE TB IS NULL
 ) AAA
 ) AA
) A,(SELECT '_BKBMBGBTB' units) B,(SELECT 11 wd) C ORDER BY ISNULL(DBN),TBL DESC,DB;

MySQL 8.0 Table Sizes in Current DB

SELECT IFNULL(TB,'Total') table_name
,LPAD(FORMAT(RC,0),14,' ') RowCount
,LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)),wd,' ') DataSize
,LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)),wd,' ') IndexSize
,LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)),wd,' ') TotalSize
,LPAD(CONCAT(FORMAT(FSZ/POWER(1024,pw4),2),' ',SUBSTR(units,pw4*2+1,2)),wd,' ') FileSize
,LPAD(FORMAT(100-(TBL*100/FSZ),2),7,' ') FragPct
FROM (
 SELECT
 TB,DAT,NDX,TBL,DTF,RC,FSZ
 ,IF(px>4,4,px) pw1
 ,IF(py>4,4,py) pw2
 ,IF(pz>4,4,pz) pw3
 ,IF(ps>4,4,ps) pw4
 FROM
 (
 SELECT *,
 FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
 FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
 FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz,
 FLOOR(LOG(IF(FSZ=0,1,FSZ))/LOG(1024)) ps
 FROM (
 SELECT TB,DAT,NDX,(DAT+NDX) TBL,DTF,RC,IFNULL(FILE_SIZE,DAT+NDX) FSZ
 FROM (
 SELECT table_name TB
 ,SUM(data_length) DAT
 ,SUM(index_length) NDX
 ,SUM(data_free) DTF
 ,SUM(table_rows) RC
 FROM information_schema.tables
 WHERE table_schema = DATABASE() AND ENGINE IS NOT NULL
 GROUP BY table_name WITH ROLLUP
 ) AAAA LEFT JOIN information_schema.innodb_tablespaces BBBB
 ON CONCAT(DATABASE(),'/',TB) = BBBB.name
 ) AAA
 ) AA 
) A,(SELECT '_BKBMBGBTB' units) B,(SELECT 11 wd) C ORDER BY ISNULL(TB),TB;

• gbn: I've been using information_schema.partitions: is there any difference please?
• RolandoMySQLDBA: @gbn - The queries segregate by engine, which is not a column in information_schema.partitions. Other than that, information_schema.partitions catch all tables, even non-partitioned ones. Just remember to exclude information_schema and mysql from WHERE clauses checking table_schema.
• Quintis555: I had some errors with the code you provided, but I'm not experienced enough to know how to go about debugging them. i found some other code online SELECT table_schema, sum(data_length) / 1024 / 1024 "data", sum(index_length) / 1024 / 1024 "index", sum( data_length + index_length ) / 1024 / 1024 "total" FROM information_schema.TABLES GROUP BY table_schema; Does this look it will work? The whole information_schema/table_schema query is new to me. (If you can't tell, I'm quite new to all this :) Thanks!
• Reactgular: +1 just signed up so I can +1 this. Why doesn't this have more up votes? thanks!