How do I find the size of a MySQL database?
Solution 1:
The two fields you want are.
-
Data_length
This is the size of the data within the table in bytes.
-
Index_length
This is the size of the table's index in bytes.
You can limit the output to one table with a database with.
SHOW TABLE STATUS LIKE 'tablename';
You can't remove the other columns from the output. You might find the output easier to read from a console by printing the output rows vertically. Just replace the ";
" for "\G
".
Solution 2:
I have a rather wild set of queries that would address this nicely. I wrote these about 2 years ago and they work just great. I still use them for reporting to clients.
Query to Give You Database Size Grouped By Storage Engine in MB
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 2 pw) A
ORDER BY TSize;
Query to Give You Database Size Grouped By Database in MB
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 2 pw) BB
ORDER BY (SDSize+SXSize);
Query to Give You Database Size Grouped By Database and Storage Engine in MB
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 2 pw) A) AA
ORDER BY schemaname, schema_score,engine_score;
All three queries have one thing in common: A simple SELECT query (SELECT 2 pw).
The pw stands for power, the exponent used against the number 1024. You can adjust the query to give you Database Sizes with different units:
(SELECT 0 pw) --reports the Database Size in Bytes
(SELECT 1 pw) --reports the Database Size in Kilobytes
(SELECT 2 pw) --reports the Database Size in Megabytes
(SELECT 3 pw) --reports the Database Size in Gigabytes
(SELECT 4 pw) --reports the Database Size in Terabytes
(SELECT 5 pw) --reports the Database Size in Petabytes (email me if you reach this size)
Give Them a Try !!!