Bug #38964 Data_free shown as true free amount * partition count

• Submitted: 22 Aug 2008 15:31
• Modified: 27 Aug 2008 8:15
• Reporter: Timothy Smith
• Status: Duplicate
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.1.28, 6.0.7
• OS: Any
• CPU Architecture: Any
• Tags: data_free, information_schema, innodb, partition

[22 Aug 2008 15:31] Timothy Smith

Description:
The INFORMATION_SCHEMA.TABLES.DATA_FREE column shows <true free amount> * <number of partitions> for partitioned InnoDB tables.

Shows 0 for MyISAM tables, so this may or may not affect MyISAM as well.  :-)

Thanks to Robin Cui for reporting this problem on Bug#32440 ([28 Jul 13:36] Robin Cui).

How to repeat:
--source include/have_innodb.inc

--disable_warnings
drop table if exists t1, t2;
--enable_warnings

let $engine_type = innodb;

eval create table t1 (id int) engine = $engine_type partition by hash(id) partitions 1;
eval create table t2 (id int) engine = $engine_type partition by hash(id) partitions 2;

select table_name, data_free, create_options
from information_schema.tables
where table_schema = 'test' and table_name in ('t1', 't2');

drop table t1, t2;

Results look like:

+drop table if exists t1, t2;
+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;
+select table_name, data_free, create_options
+from information_schema.tables
+where table_schema = 'test' and table_name in ('t1', 't2');
+table_name     data_free       create_options
+t1     4194304 partitioned
+t2     8388608 partitioned
+drop table t1, t2;

Note that data_free for t1 and t2 *should* be the same.

[27 Aug 2008 8:15] Mattias Jonsson

Duplicate of bug#36312

[22 Dec 2010 21:37] Mark Callaghan

Is this bug still open? I think it is. The value I get for Data_free is a function of the number of partitions in the table. My test does not use innodb_file_per_table so all tables use the system tablespace and should report the same value for Data_free.

my.cnf
------

[mysqld]
innodb_buffer_pool_size=16G
innodb_log_file_size=1900M
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=1
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
innodb_max_dirty_pages_pct=80

innodb_file_format=barracuda
skip_innodb_file_per_table

max_connections=2000
table_cache=2000

key_buffer_size=2000M

innodb_buffer_pool_size=2G
innodb_log_file_size=100M

test case
---------

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: