Is it possible to allocate data free for certain table?

Posted on

Question :

After deleted a large dataset (~50GB) and optimized tables, some tables have data free (~50GB) and parts of tables have only data free (~KB). As my understanding, the data free are reused and not keeping the ibdata growing.

The situation is: My commonly used tables are growing really fast, but unfortunately the data free are allocated to not commonly used tables. The disk space of ibdata is keep growing but the data free is not reused.

The question is: Can the data free allocated to commonly used tables, so it can reuse the (~50GB) space, since the storage is costly on cloud?

Answer :

You have not said whether you are using MyISAM or InnoDB. I will assume InnoDB.

First of all, “Data_free” is a rotten metric. It shows only one aspect of the free space. There are about 3 other major contributors to “free” space, for which there are no metrics.

Second, if you are using innodb_file_per_table = OFF, then the only real metric is the size of the ibdata1 table. “Data_free” has a radically different meaning for file-per-table versus not. It sounds like you might have set that setting one way for some tables, the other way for others.

Third, tiny table allocate more space 16KB at a time; large tables grow by 4MB (or maybe 8MB?) at a time. So the space will ‘lurch’, helping to make things unpredictable.

Give us a sample of the ‘evidence’; we can probably explain what it means and sympathize with over how cryptic/deceptive it is.

Leave a Reply

Your email address will not be published. Required fields are marked *