1

I am unable to free space in innodb tables.

I also set the innodb_files_per_table=1 so each table has separate ibd file.

When i query the information_Schema for checking the data size, index size and data_free, it always show some value like 7Mb in data_free column for some tables. Query :

SELECT table_schema "Data Base Name", table_name, (data_length + index_length) / 1024 / 1024 "Data Base Size in MB", ( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES where table_schema='DB_NAME' order by 4 desc limit 30;

I run the optimize table, also tried alter table table_name engine='innodb' but the data_free is still showing 7Mb.

I also enable the innodb_stats_on_metadata to refresh the stats and also do flush tables and reopen the table but still showing 7Mb in data_free.

Any idea how i get this free space issue resolved ?

Improve this question
4
  • That's normal on larger tables. InnoDB leaves approximately 1/16 free space by filling pages to around 15/16. This isn't a sign of a problem. Commented Jul 8, 2016 at 3:39
  • its not a large table, it only 145Mb table.. and i have seen that there are free spaces on small tables as well. Like 40Mb table and free space is 5Mb. Commented Jul 8, 2016 at 4:35
  • Sorry, I should have been more clear with my casual use of the word "larger." I was comparing to the other tables on your server -- the larger the table, the more likely it is that you will see significant free space. Commented Jul 8, 2016 at 10:38
  • I need to go back and research the answer a bit more, however; I am confident that the answer is "this is normal" but it's not because of the 15/16 rule because it is likely this number actually refers to the space on unused pages, not unused space on used pages. Commented Jul 8, 2016 at 10:47

1 Answer 1

Reset to default
1

Yes, 4M/5M/6M/7M "free" is normal for medium- to large-sized InnoDB tables. And it is usually 4M-7M, regardless of table size. You cannot get rid of that. (This is especially bad for partitioned tables, since it occurs on each partition.) What percentage of the space is that?

Small tables are allocated in a different way, and usually have less than 4MB of "free" space.

The 15/16 is not shown anywhere in information_schema, nor are most other types of 'wasted' space. The 15/16 is in each block, and refers to leaving some extra space to avoid a sudden block split when inserting rows.

The various categories of "free" space in InnoDB exist as an optimization to improve response time to queries, at the cost of 'wasted' disk space.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.