Tuesday, November 1, 2016

Useful queries to find data sizes in MySQL

Get the table size for all MyIASM tables and all INNODB tables, separated by data usage and index usage:

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 3 pw) A ORDER BY TSize;

Get the data size and index size and table size for a specific table in a specific database (need to change the schema and table name):

SET @DB_NAME = 'my_db';
SET @TABLE_NAME = 'my_table';

SELECT 
table_schema AS "Database",  
table_name AS "Table",   
ROUND(((data_length) / 1024 / 1024), 2) "Data in MB",  
ROUND(((index_length) / 1024 / 1024), 2) "Index in MB",   
ROUND(((data_length + index_length) / 1024 / 1024), 2) "Total in MB" 
FROM information_schema.TABLES WHERE table_schema = @DB_NAME AND table_name = @TABLE_NAME;

Get the data size for each schema on a MySQL server (may want to change from GB to MB by removing one of the 1024 when dividing the sum depending on the sizes of the schemas):

SELECT   table_schema AS "Database", 
         ROUND((SUM(index_length)/1024/1024/1024),2) AS "Index in GB",
         ROUND((SUM(data_length)/1024/1024/1024),2) AS "Data in GB",
         ROUND((SUM(data_length+index_length)/1024/1024/1024),2) AS "Total in GB"
FROM     information_schema.tables
WHERE table_schema NOT IN ('performance_schema','sys','information_schema', 'mysql')
GROUP BY table_schema 
ORDER BY 2 DESC;

Size of all tables that are InnoDB (running this can be very slow on systems with lots of tables)

SELECT CONCAT(db,'.',tb) as 'db_name.table_name' ,CONCAT(ROUND( tbsz/POWER(1024,IF(pw<0,0,IF(pw>4,4,pw))),3),' ', SUBSTR(' KMGT',IF(pw<0,0,IF(pw>4,4,pw))+1,1)) table_size FROM (SELECT data_length+index_length tbsz,table_schema db,table_name tb FROM information_schema.tables WHERE engine='InnoDB' AND table_schema NOT IN ('performance_schema','sys','information_schema', 'mysql')) A, (SELECT 3 pw) B;

Get the size of all tables in a specific schema with largest table first (need to change the schema name):

SET @DB_NAME = 'my_db';

SELECT table_schema as "Database", table_name AS "Table", 
ROUND(((index_length) / 1024 / 1024 / 1024), 2) "Index in GB",
ROUND(((data_length) / 1024 / 1024 / 1024), 2) "Data in GB",
ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 2) "Total in GB" 
FROM information_schema.TABLES WHERE table_schema = @DB_NAME ORDER BY (data_length + index_length) DESC;



No comments:

Post a Comment