I have several hundred databases running on the same MySQL server. How can I get a list sorted by the size of the databases?
-
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. – Mike Mackintosh Jun 7 '12 at 17:39
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)
-
1
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
-
+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
-
1The ordering didn't work for me: I had to tweak it to
ORDER BY sum( data_length + index_length ) ASC
. Otherwise, it sorted alpabetically. – TRiG Dec 22 '14 at 12:00