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