Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
If you use innoDB, you will have log files which can become very large and add to the size of the database exponentially. This is something to keep in mind, and why the OS PoV is so important. –  mikemackintosh Jun 7 '12 at 17:39
add comment

2 Answers

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
share|improve this answer
    
+1 for mentioning both query and OS point-of-view because if there is a noticeable difference between them, it is great reminder to defrag the tables/tablespaces. –  RolandoMySQLDBA Jun 7 '12 at 15:02
add comment

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)
share|improve this answer
1  
haha, +1 for "(If you ever run with this, email me)" –  Derek Downey Jun 7 '12 at 15:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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