| Bug #36312 | InnoDB DATA_FREE BUG? | ||
|---|---|---|---|
| Submitted: | 24 Apr 2008 7:42 | Modified: | 22 Dec 2010 22:09 |
| Reporter: | talen xu | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.1.24,5.1.51 | OS: | Linux |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | DATA_FREE InnoDB | ||
[24 Apr 2008 7:42]
talen xu
[24 Apr 2008 9:30]
Domas Mituzas
Seems to be a duplicate of Bug#36278
[24 Apr 2008 9:55]
talen xu
I do not think that is bug 36278.I am also aware that the free_data there is in kilobytes.Even so,it is not correct mysql> select 2008104960/1024/1024; +----------------------+ | 2008104960/1024/1024 | +----------------------+ | 1915.07812500 | +----------------------+ 1 row in set (0.00 sec) Because I allocate the total size is 200G why?
[27 Apr 2008 11:40]
Sveta Smirnova
Thank you for the report. Verified as described. Seems only partitioned tables are affected. Test case: --source include/have_innodb.inc create table t1(id int) engine=innodb; insert into t1 values(1),(2),(3),(4); create table t2(id int) engine=innodb PARTITION BY HASH(id*2) partitions 4; create table t3(id int) engine=innodb PARTITION BY HASH(id*2) partitions 2; select TABLE_NAME, data_free from information_schema.TABLES where TABLE_NAME like 't%'; show table status like 't%';
[27 Apr 2008 13:19]
talen xu
mysql> select TABLE_NAME, data_free/1024/1024 from information_schema.TABLES where TABLE_NAME like 't_'; +------------+---------------------+ | TABLE_NAME | data_free/1024/1024 | +------------+---------------------+ | t1 | 150.71191406 | | t2 | 602.84765625 | | t3 | 301.42382813 | +------------+---------------------+ 3 rows in set (0.00 sec)
[27 Apr 2008 14:02]
talen xu
ls -lrth ibdata* -rw-rw---- 1 mysql mysql 2.0G Apr 24 14:27 ibdata25 -rw-rw---- 1 mysql mysql 2.0G Apr 24 14:27 ibdata26 -rw-rw---- 1 mysql mysql 2.0G Apr 24 14:27 ibdata27 Table t1 shows that it is correct
[23 Jun 2008 17:49]
Heikki Tuuri
Vasil, you recently fixed something associated with data free. Please look at this. --Heikki
[24 Jun 2008 7:03]
Vasil Dimov
Data_free is multiplied by the number of partitions.
Here is a test case and a gdb output:
create table t1(id int) engine=innodb;
create table t2(id int) engine=innodb PARTITION BY HASH(id*2) partitions 2;
select TABLE_NAME, data_free from information_schema.TABLES where TABLE_NAME like 't%';
gdb during the above select:
Breakpoint 4, ha_innobase::info (this=0x2529c10, flag=86) at handler/ha_innodb.cc:6656
6656 ib_table->space) * 1024;
(gdb) ins ib_table->name
$56 = 0x1814220 "test/t1"
(gdb) c
Continuing.
Breakpoint 4, ha_innobase::info (this=0x252b010, flag=16) at handler/ha_innodb.cc:6656
6656 ib_table->space) * 1024;
(gdb) ins ib_table->name
$57 = 0x1816620 "test/t2#P#p0"
(gdb) c
Continuing.
Breakpoint 3, ha_partition::info (this=0x252ac10, flag=86) at ha_partition.cc:4495
4495 stats.delete_length+= file->stats.delete_length;
(gdb) ins file->table_share->table_name
$58 = {str = 0x252a315 "t2", length = 2}
(gdb) c
Continuing.
Breakpoint 4, ha_innobase::info (this=0x252b1f8, flag=16) at handler/ha_innodb.cc:6656
6656 ib_table->space) * 1024;
(gdb) ins ib_table->name
$59 = 0x1817a20 "test/t2#P#p1"
(gdb) c
Continuing.
Breakpoint 3, ha_partition::info (this=0x252ac10, flag=86) at ha_partition.cc:4495
4495 stats.delete_length+= file->stats.delete_length;
(gdb) ins file->table_share->table_name
$60 = {str = 0x252a315 "t2", length = 2}
(gdb) c
Continuing.
This loop inside sql/ha_partition.cc is adding the free space multiple times (line 4495):
4485 do
4486 {
4487 if (bitmap_is_set(&(m_part_info->used_partitions), (file_array - m_file)))
4488 {
4489 file= *file_array;
4490 file->info(HA_STATUS_VARIABLE);
4491 stats.records+= file->stats.records;
4492 stats.deleted+= file->stats.deleted;
4493 stats.data_file_length+= file->stats.data_file_length;
4494 stats.index_file_length+= file->stats.index_file_length;
4495 stats.delete_length+= file->stats.delete_length;
4496 if (file->stats.check_time > stats.check_time)
4497 stats.check_time= file->stats.check_time;
4498 }
4499 } while (*(++file_array));
We have the following dilemma: there is a pool of 10MB free space (for example). There are 2 tables in it - t2-partition0 and t2-partition1. For InnoDB these are two independent tables, and when asked how much is the free space in t2-partition0 the answer is 10MB, same for t2-partition1. Then the partitioning code assumes that t2 has free space of 20MB because each of its two pars has 10MB.
I think this should be handled at partitioning level. It may not be as simple as counting the free space only once (i.e. dividing the result to the number of partitions) because if innodb_file_per_table is ON, then the free space is indeed 20MB, in the above example, because 2 files are created - t2#P#p0.ibd and t2#P#p1.ibd, each one being 10MB.
Setting this to "Server: Partitioning" and de-assigning from myself. Feel free to set back to InnoDB bug if changes to InnoDB code are needed.
[27 Aug 2008 8:16]
Mattias Jonsson
marked bug#38964 as a duplicate of this
[2 Dec 2008 9:14]
Mattias Jonsson
Assigning to myself, if all partitions have the same stat variables, then I assume it uses the same tablespace (or innodb-file). This might be wrong result on an empty table, or if the tablespace/innodb-file is used concurrently by another thread.
[16 Dec 2008 15:22]
Mattias Jonsson
For tables using TABLESPACES, one can query I_S.PARTITIONS for this like: select table_schema,table_name, sum(table_rows), sum(data_length)/sum(table_rows) as 'avg_row_length', sum(data_length), sum(max_data_length), sum(index_length), sum(data_free) from (select table_schema, table_name, tablespace_name, sum(table_rows) as table_rows, data_length, max_data_length, index_length, data_free from information_schema.partitions where table_schema = 'test' and table_name = 't2' group by table_schema, table_name, tablespace_name) a group by table_schema, table_name; This is also possible to do on InnoDB tables, although they do not use TABLESPACE, but one self must check if the InnoDB table was created with innodb_file_per_table or not, since that cannot be checked within the storage engine api. I will propose a patch which will default to innodb_file_per_table, but if it is obvious not using innodb_file_per_table, it will not sum the values for DATA_FREE etc. I assume it is NOT created with innodb_file_per_table if all these statements are true: Table is using ENGINE = InnoDB. All partitions have the same data_file_length, index_file_length, delete_length, check_time. NOT all partitions have the same number of records, deleted records, mean_rec_length. (Might be innodb_file_per_table and evently distributed records, defaulting to guess on innodb_file_per_table, to not break current default). I will not make any other changes on how ha_partition::info() reports the DATA_FREE etc. on table level (i.e. do grouping on tablespaces) since it is possible to get that information on a partition level through I_S.PARTITIONS.
[19 Dec 2008 8:23]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/62055 2739 Mattias Jonsson 2008-12-19 Bug#36312: InnoDB DATA_FREE BUG? Problem is that DATA_FREE in SHOW TABLE STATUS is not correct when not using innodb_file_per_table. The solution is to use I_S.PARTITIONS instead. This is only a small fix for correcting mean record length and always return 0 if the table is empty.
[19 Dec 2008 8:30]
Mattias Jonsson
Here is a print out from a ha_partition::info(HA_STATUS_VARIABLE) showing the results from each partitions ::info call. (r - records, d - deleted records, f - data file length, i - index file length, dl - deleted length, c - create time, m - mean record length) part: r 1 d 0 f 16384 i 0 dl 0 c 0 m 16384 part: r 1 d 0 f 16384 i 0 dl 0 c 0 m 16384 part: r 2 d 0 f 16384 i 0 dl 0 c 0 m 8192 part: r 1 d 0 f 16384 i 0 dl 0 c 0 m 16384 part: r 1 d 0 f 16384 i 0 dl 0 c 0 m 16384 The above result is the same for both tables created with and without innodb_file_per_table. It would be too many restrictions and special treatment for this to be user friendly and/or correct for all variants. So my proposal would be the following: Always return the sum of all partitions for the statistics (accept for mean record length, which we recalculate). And document this behavior. (The correct value can be accumulated from I_S.PARTITIONS, if one have the knowledge of how the storage engine stores the data, i.e. if innodb_file_per_table was set when created all partitions. This is also true for engines using tablespaces).
[8 Jan 2009 10:36]
Mattias Jonsson
Patch queued to mysql-5.1-bugteam and mysql-6.0-bugteam (the patch was only a small fix for correcting mean record length and always return 0 if the table is empty. I.e. not changing the behavior for DATA_FREE!).
[14 Jan 2009 9:32]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/63197 2748 He Zhenxing 2009-01-14 [merge] Auto merge
[15 Jan 2009 6:38]
Bugs System
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:davi.arnaut@sun.com-20090113150631-y84w12o2zmh7j3qd) (merge vers: 5.1.31) (pib:6)
[15 Jan 2009 17:01]
Jon Stephens
Documented bugfix in the 5.1.31 changelog as follows:
SHOW TABLE STATUS could show a nonzero value for the Mean record
length of a partitioned InnoDB table, even if the table
contained no rows.
Also updated info in SHOW TABLE STATUS section of 5.1/6.0 Manual per developer comments.
Set back to PQ status pending merge to 6.0 tree.
[15 Jan 2009 18:29]
Jon Stephens
Status should have been set to NDI.
[19 Jan 2009 11:28]
Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:06]
Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 13:58]
Jon Stephens
Set status back to NDI pending merge to 6.0.
[19 Jan 2009 16:12]
Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[19 Jan 2009 17:04]
Jon Stephens
Set back to NDI pending merge to 6.0.
[20 Jan 2009 18:58]
Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:mattias.jonsson@sun.com-20090108102010-3caae52fb95b5v4x) (merge vers: 6.0.10-alpha) (pib:6)
[20 Jan 2009 21:15]
Jon Stephens
Fix also noted in 6.0.10 changelog; closed.
[22 Dec 2010 22:09]
Gary Pendergast
Reopening, as this bug appears to have been re-introduced at some point. I've been able to reproduce in 5.1.51 using MarkC's test case on Bug #38964: create table t0 (id int) engine = innodb; create table t1 (id int) engine = innodb partition by hash(id) partitions 1; create table t2 (id int) engine = innodb partition by hash(id) partitions 2; create table t4 (id int) engine = innodb partition by hash(id) partitions 4; insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8); insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t2 select * from t1; insert into t4 select * from t1; insert into t0 select * from t1; show table status\G drop table t0, t1, t2, t4; ---------------- note that Data_free = real_value * number_of_partitions output ------ *************************** 1. row *************************** Name: t0 Engine: InnoDB Version: 10 Row_format: Compact Rows: 2097549 Avg_row_length: 32 Data_length: 67731456 Max_data_length: 0 Index_length: 0 Data_free: 4194304 Auto_increment: NULL Create_time: 2010-12-22 13:34:43 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: *************************** 2. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 2097505 Avg_row_length: 30 Data_length: 64585728 Max_data_length: 0 Index_length: 0 Data_free: 4194304 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: partitioned Comment: *************************** 3. row *************************** Name: t2 Engine: InnoDB Version: 10 Row_format: Compact Rows: 2098567 Avg_row_length: 31 Data_length: 66191360 Max_data_length: 0 Index_length: 0 Data_free: 8388608 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: partitioned Comment: *************************** 4. row *************************** Name: t4 Engine: InnoDB Version: 10 Row_format: Compact Rows: 2099135 Avg_row_length: 31 Data_length: 66158592 Max_data_length: 0 Index_length: 0 Data_free: 16777216 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: partitioned Comment:
[25 Dec 2010 23:09]
Mattias Jonsson
This bug is not re-introduced, since the behavior of DATA_FREE/delete_length was never changed, see the comments and commit.
