| Bug #33372 | InnoDB free: 0 on a newly created ( and non 0 size) table | ||
|---|---|---|---|
| Submitted: | 19 Dec 2007 16:45 | Modified: | 8 Jan 2008 20:24 |
| Reporter: | Matthew Lord | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.0.52 | OS: | Any |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
| Tags: | free space, innodb, tablespace size | ||
[20 Dec 2007 12:38]
Heikki Tuuri
Matt, Paul, InnoDB free means the number of 1 MB totally free extents minus a safety margin. It is kind of 'guaranteed' free space. For a very small table, the .ibd file is < 1 MB, which means InnoDB free will show 0. Even if InnoDB free shows 0, you can insert rows, as long as you do not need to allocate new extents. Paul, can you add this explanation to the manual, to the appropriate place? Vasil a few weeks ago in 5.1 moved the 'InnoDB free' from the table comment to an Information Schema table (deleted_length or something like that in MySQL talk). Regards, Heikki
[20 Dec 2007 16:14]
Paul DuBois
Changing to Server: Documentation/Verified because this is a documentation issue. Assigning to myself.
[8 Jan 2008 20:24]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. Clarified the SHOW TABLE STATUS page using Heikki's explanation.
Description: When using innodb_file_per_table and the autoextend option the InnoDB free: info is sometimes wrong. An example is when the table is new. mysql> create table ibtest (c1 char(100) ) ENGINE=INNODB; Query OK, 0 rows affected (0.27 sec) mysql> show table status like "ibtest"\G *************************** 1. row *************************** Name: ibtest Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2007-12-19 11:38:48 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: InnoDB free: 0 kB 1 row in set (0.09 sec) mysql> insert into ibtest values ("boo"), ("bar"); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> show table status like "ibtest"\G *************************** 1. row *************************** Name: ibtest Engine: InnoDB Version: 10 Row_format: Compact Rows: 2 Avg_row_length: 8192 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2007-12-19 11:38:48 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: InnoDB free: 0 kB 1 row in set (0.00 sec) -rw-rw---- 1 mysql mysql 96K Dec 19 11:43 ibtest.ibd How to repeat: mysql test create table ibtest (c1 char(100) ) ENGINE=INNODB; show table status like "ibtest"\G insert into ibtest values ("boo"), ("bar"); show table status like "ibtest"\G Suggested fix: We should either ensure that this value is correct and has real meaning or clearly document that it can generally be ignored when using innodb_file_per_table and or the autoextend option.