Friday, May 18, 2012

Calculate MySQL Database Size

To calculate the size of database and checking the fragmentation in MySQL.  Execute below command , it will provide you size of MySQL database and Fragmentation detail.

>SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema;

To know the size of  MySQL  database  .. Execute command

>SELECT table_schema "Data Base Name", sum( data_length) / 1024 / 1024 "Data Base Size in MB"  FROM information_schema.TABLES GROUP BY table_schema;

To know the size the index

>SELECT table_schema "Data Base Name", sum(  index_length ) / 1024 / 1024 "Index Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

If you are using only MyISAM tables, you can also calculate size by command(This command will be executed on shell prompt)

$du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`

1 comment:

ANIL YADAV said...

Hi All

A note.. The actual disk size may be different from calculated.