1

I am calculating my innodb databases size with the following syntax. SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

As per Mysql documentation data_length column gives the clustered index length in pages. But I have not created index on that table.How to calculate exact total space,used space and free space for a table ?

  • Here is everything you need: dev.mysql.com/doc/refman/5.6/en/tables-table.html (also have a look in the comment section). Also, where do you have the info from that it's in pages? It's not. The data is pretty accurate for me. Given a page size of 16k this would be way off. – tombom Feb 14 '18 at 12:53
1

This won't be accurate for InnoDB tables. The size on disk is actually bigger than that reported via your query, take a look at :

select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES 

file_size (which is the logical file size on disk) and allocated_size (which is space allocated for this file and can be significantly smaller) more details here : https://www.percona.com/blog/2016/01/26/finding_mysql_table_size_on_disk/

  • 1
    Then how could we find the accurate db size of Innodb tables?? – sreekanth Feb 15 '18 at 6:14
0

Add Data_free into the formula:

SELECT  table_schema AS "Database",
        ROUND(SUM(data_length + index_length + data_free) / 1024 / 1024, 2)
               AS "Size (MB)"
    FROM  information_schema.TABLES
    GROUP BY  table_schema;

Every InnoDB table has a "clustered" PRIMARY KEY, even if it is the fall back of a 6-byte hidden number.

The three 'length' fields are in bytes, not blocks.

Another approach to computing the disk size for an InnoDB table is to estimate the size for the equivalent MyISAM table, then multiple by between 2 and 3. But DELETEs, etc, can mess with that number, since tables do not shrink without overt user action.

0

To find the exact size I have set innodb_file_per_table=OFF. Initially ibdata1 size was set to 500MB and disabled my autoextend. Then started inserting data into new table the "data_length" increased and "data_free" reduced. In some cases after deletion of records the "data_length" was increased and "data_free" reduced.

But the actual result should be reduction of "data_length" and growth of "data_free" on deletion of records.I am not sure why there is a deviation and not able to predict the exact used and free space of a table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.