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 ?

Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
  • That's normal on larger tables. InnoDB leaves approximately 1/16 free space by [filling pages to around 15/16](http://dev.mysql.com/doc/refman/5.7/en/innodb-physical-structure.html). This isn't a sign of a problem. – Michael - sqlbot Jul 08 '16 at 03: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. – Aman Aggarwal Jul 08 '16 at 04: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. – Michael - sqlbot Jul 08 '16 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. – Michael - sqlbot Jul 08 '16 at 10:47

1 Answers1

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222