Difference between revisions of "mysql database size analysis"

Latest revision as of 03:01, 9 May 2016

report on database size

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')) AAA GROUP BY DB WITH ROLLUP) AA,
(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

sample output

+------------------------+----------------------+----------------------+----------------------+ | DBName | Data Size | Index Size | Total Size | +------------------------+----------------------+----------------------+----------------------+ | onlinebookmarks | 0.000 GB | 0.000 GB | 0.000 GB | | mw_bjjwiki | 0.094 GB | 0.047 GB | 0.140 GB | | All Databases | 0.348 GB | 0.101 GB | 0.449 GB | +------------------------+----------------------+----------------------+----------------------+