MySQL Bugs: #36312: InnoDB DATA

Bug #36312	InnoDB DATA_FREE BUG?
Submitted:	24 Apr 2008 9:42	Modified:	20 Jan 22:15
Reporter:	talen xu
Status:	Closed
Category:	Server: Partition	Severity:	S3 (Non-critical)
Version:	5.1.24	OS:	Linux
Assigned to:	Mattias Jonsson	Target Version:	5.1+
Tags:	DATA_FREE InnoDB
Triage:	Triaged: D4 (Minor)

Description:
show table status show that InnoDB DATA_FREE is not accurate?

How to repeat:
Beginning with MySQL 5.1.24, the DATA_FREE column shows the free space in bytes for
InnoDB tables. (Bug#32440)

#*** InnoDB Specific options
default_table_type = InnoDB
transaction_isolation = REPEATABLE-READ
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 4G
innodb_data_file_path =
ibdata1:2G;ibdata2:2G;ibdata3:2G;ibdata4:2G;ibdata5:2G;ibdata6:2G;ibdata7:2G;ibdata8:2G;ibdata9:2G;ibdata10:2G;.....
#innodb_file_per_table

[root@NBCTC-DB7-23:7.23 /app/mysql ar]# ls -lhrt ibdata*
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata20
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata21
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata22
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata23
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata24
-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
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata28
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata29
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata30
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata31
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata32
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata33
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata34
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata35
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata36
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata37
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata38
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata39
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata40
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata41
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata42
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata43
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata44
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata45
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata46
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata47
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata48
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata49
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata50
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata51
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata52
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata53
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata54
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata55
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata56
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata57
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata58
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata59
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata60
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata61
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata62
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata63
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata64
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata65
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata66
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata67
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata68
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata69
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata70
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata71
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata72
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata73
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata74
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata75
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata76
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata77
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata78
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata79
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata80
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata81
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata82
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata83
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata84
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata85
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata86
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata87
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata88
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata89
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata90
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata91
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata92
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata93
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata94
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata95
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata96
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata97
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata98
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata99
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:27 ibdata100
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata9
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata5
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata13
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata11
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata6
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata4
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata14
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata18
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata16
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata15
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata12
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata7
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata3
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata2
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata10
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata8
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata19
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata17
-rw-rw----  1 mysql mysql 2.0G Apr 24 14:56 ibdata1

mysql> select * from TABLES where TABLE_NAME='t_sms_3f6' \G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: sms
     TABLE_NAME: t_sms_3f6
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 91780
 AVG_ROW_LENGTH: 271
    DATA_LENGTH: 24920064
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 11599872
      DATA_FREE: 2008104960
 Auto_increment: 134057
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: partitioned
        Comment: 
1 row in set (0.09 sec)

Beginning with MySQL 5.1.24, the DATA_FREE column shows the free space in bytes for
InnoDB tables. (Bug#32440)
mysql> select 2008104960/1024/1024/1024;
+---------------------------+
| 2008104960/1024/1024/1024 |
+---------------------------+
|            1.870193481445 | 
+---------------------------+
1 row in set (0.00 sec)

All tables use innodb and partition.The ls -lhrt showed that last used is ibdata19.so
used free_space=200-19*2=162G.but free_data is 1.87G from select * from TABLES where
TABLE_NAME='t_sms_3f6' \G

why?

Seems to be a duplicate of Bug#36278

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?

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%';

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)

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

Vasil,

you recently fixed something associated with data free. Please look at this.

--Heikki

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.

marked bug#38964 as a duplicate of this

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.

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.

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.

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).

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!).

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

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)

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.

Status should have been set to NDI.

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)

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)

Set status back to NDI pending merge to 6.0.

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)

Set back to NDI pending merge to 6.0.

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)

Fix also noted in 6.0.10 changelog; closed.