How to analyze space used by tables, indexes

mysql

We have a database that has grown organically over the past year. How can we quickly analyze the space used by each table? We would like to consider the indexes as well.

When I am using innodb tables in my databases I like to use the innodb_file_per_table setting. This allows me to get a quick idea of what is going on with ls like chaos suggested.

This statement may give you a pretty good idea about how much space you are using.

use information_schema;
SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `TABLE_ROWS`, `DATA_LENGTH`, 
       `INDEX_LENGTH`, `DATA_FREE` FROM `TABLES`

Have I got some crazy queries for you to use. I wrote these two years ago and still use them today. Give them a Try !!!

Here is a query to sum up all data across all storage engines

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') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

Here is a query to sum up all data across all databases

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(FORMAT(SXSize/
POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
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')) AAA GROUP BY DB WITH ROLLUP) AA,
(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

Here is a query to sum up all data across all databases grouped by storage engine

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,IF(ISNULL(table_schema)=1,
'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
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') TableSize
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') AND
engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

CAVEAT: You will notice that at the tail end of all these queries is an inline SELECT that looks like this: (SELECT 3 pw)

The number 3 causes the report to come out in GigaBytes.
In fact here is a list of the numbers and the units of the report it uses:

(SELECT 0 pw) Reports in Bytes
(SELECT 1 pw) Reports in Kilobytes
(SELECT 2 pw) Reports in Megabytes
(SELECT 3 pw) Reports in Gigabytes
(SELECT 4 pw) Reports in Terabytes
(SELECT 5 pw) Reports in Petabytes (I have never used this setting but it's there in case you reach that number someday)

Enjoy !!!

Mysql has some built in functionality

use <datbase name>;
show table status;

A bit less crazy answer than from RolandoMySQLDBA, but using the same idea and having better formatting (only in MB)

use information_schema;

SELECT 
`TABLE_SCHEMA`, `TABLE_NAME`, `TABLE_ROWS`, 
CONCAT(LPAD(FORMAT(`DATA_LENGTH`/POWER(1024,2),3),17,' '),' ',SUBSTR(' KMGTP',2+1,1),'B') as "Data Size",
CONCAT(LPAD(FORMAT(`INDEX_LENGTH`/POWER(1024,2),3),17,' '),' ',SUBSTR(' KMGTP',2+1,1),'B') as "Index Size",   
CONCAT(LPAD(FORMAT((DATA_LENGTH +`INDEX_LENGTH`)/POWER(1024,2),3),17,' '),' ',SUBSTR(' KMGTP',2+1,1),'B') as "Total Size",   
`DATA_FREE` FROM `TABLES` ORDER BY (data_length+index_length) desc;

How to analyze space used by tables, indexes

Related

Recent Posts