Find the largest databases on my server

Question

I have several hundred databases running on the same MySQL server. How can I get a list sorted by the size of the databases?

Answer 1

If you want to query in the INFORMATION_SCHEMA database I have the following:

Total Storage By Database in MB

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "DataSize",
CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "IndexSize",
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);

Total Storage 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 2 pw) A ORDER BY TSize;

Total Storage Database and Storage Engine

SELECT 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') "Table Size" 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 ORDER BY TSize;

Please note in all the queries you see a subquery that looks like this: (SELECT 2 pw)

• (SELECT 0 pw) present the Database Sizes in Bytes
• (SELECT 1 pw) present the Database Sizes in KiloBytes
• (SELECT 2 pw) present the Database Sizes in MegaBytes
• (SELECT 3 pw) present the Database Sizes in GigaBytes
• (SELECT 4 pw) present the Database Sizes in TeraBytes
• (SELECT 5 pw) present the Database Sizes in PetaBytes (If you ever run with this, email me)

Answer 2

This should get you the size of the data and the indexes of your databases (only looks at MyISAM or InnoDB):

SELECT table_schema 'database', 
 concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size 
FROM information_schema.TABLES
WHERE ENGINE=('MyISAM' || 'InnoDB' ) 
GROUP BY table_schema 
ORDER BY size ASC;

Note that this is not necessarily the size on disk, especially when using InnoDB. Your ibdata files (and individual *.idb files if using innodb_file_per_table option) grow continuously but do not shrink automatically.

To get file size on disk, if using linux:

du -s /path/to/mysql/data/dir | sort -n