How to analyze space used by tables, indexes

Question

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.

Answer 1

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`

Answer 2

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 !!!

Answer 3

Mysql has some built in functionality

use <datbase name>;
show table status;

Answer 4

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;

Answer 5

Try using a tool like this to view your MySQL instance in an easy-to-read format: http://www.aquafold.com/index-mysql.html

Answer 6

ls -la /var/lib/mysql/*

Answer 7

Have phpmyadmin installed on your server.

phpMyAdmin is a free software tool written in PHP intended to handle the administration of MySQL over the World Wide Web. phpMyAdmin supports a wide range of operations with MySQL. The most frequently used operations are supported by the user interface (managing databases, tables, fields, relations, indexes, users, permissions, etc), while you still have the ability to directly execute any SQL statement