We help IT Professionals succeed at work.

MYSQL,Data_Free column in show table status

motioneye
motioneye asked
on
Medium Priority
3,391 Views
Last Modified: 2012-05-06
When executed show table status can someone tell me whether Data_free counted as byte,kb or MB?

+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+
| Name    | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+
| address | InnoDB |      10 | Compact    | 1215860 |             76 |    92913664 |               0 |            0 |   4194304 |        1250001 |
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+
Comment
Watch Question

UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
That's in Bytes ..The number of allocated but unused bytes
UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
Pls take a look at here for for more..

http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html

Author

Commented:
Hi,'
Is Data_free refer to hard disk free space or database free space?
UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
It refers to Database free space..

Author

Commented:
Hi,
If it refer to database free space, is there a way which I can shrink this free space ???
UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
Only way we can do this is by means of OPTIMIZE at table level....

http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

Not sure how it would do this.. but lets give a try.

Author

Commented:
As you can see from below table status, what al tables reported on same data_free



mysql> show table status;
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+
| Name    | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+
| address | InnoDB |      10 | Compact    | 1193448 |             77 |    92913664 |               0 |            0 |   4194304 |        1250001 |
| house   | InnoDB |      10 | Compact    |  100454 |             47 |     4734976 |               0 |            0 |   4194304 |         100001 |
| import  | InnoDB |      10 | Compact    |       0 |              0 |       16384 |               0 |            0 |   4194304 |              1 |
| shoe    | InnoDB |      10 | Compact    |       1 |          16384 |       16384 |               0 |            0 |   4194304 |              2 |
| shop    | InnoDB |      10 | Compact    |       7 |           2340 |       16384 |               0 |            0 |   4194304 |           NULL |
| trip    | InnoDB |      10 | Compact    |  150288 |             66 |     9977856 |               0 |            0 |   4194304 |         150001 |
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+
6 rows in set (0.17 sec)
UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
This is seems to be InnoDB issue.... no worries about that and my bad pls forget about optimize it doesn't matter as all InnoDB tables have a Data_free as 4194304

You may want to have a look at the Bug and clarification from InnoDB creator on this...

http://bugs.mysql.com/bug.php?id=36312
UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
I hope the bug details must have shed light on Data_free issue. Have you gone thru it?

Author

Commented:
Found the link from http://lists.mysql.com/commits/62055, but how do I install the patch ?
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:
I don't suggest you to do any patching/upgrading for this kind of small thing. If you are on Windows then don't at all try.. if you are on unix/linux  then you need to download the source from MySQL download area and then compile it ... but it hardly makes any sense to do this exercise as this Data_free hardly matters any where... but if you really wants to check Data_free for innodb tables then go for per table table by setting innodb_file_per_table in config file..

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
ic ok, then I should follow yours
UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
That's fine..

Thanks,
Umesh
Senior Principal Technical Support Engineer
Top Expert 2009
Commented:
Its working... Checked at my end.. Here is the test case
## Checked the value of parameter innodb_file_per_table
 
show variables like 'innodb_file_per_table';
 
innodb_file_per_table OFF
 
######## Creted innoDB table
 
create table umesh001(id int not null AUTO_INCREMENT PRIMARY key)engine=InnoDB;
create table umesh002(id int not null AUTO_INCREMENT PRIMARY key)engine=InnoDB;
create table umesh003(id int not null AUTO_INCREMENT PRIMARY key)engine=InnoDB;
create table umesh004(id int not null AUTO_INCREMENT PRIMARY key)engine=InnoDB;
 
## Checking table status for Data_free column
 
show table status like 'umesh%';
 
umesh001	InnoDB	10	Compact	0	0	16384	0	0	4194304	1	2009-02-24 10:07:38	\N	\N	latin1_swedish_ci	\N		
umesh002	InnoDB	10	Compact	0	0	16384	0	0	4194304	1	2009-02-24 10:07:39	\N	\N	latin1_swedish_ci	\N		
umesh003	InnoDB	10	Compact	0	0	16384	0	0	4194304	1	2009-02-24 10:07:39	\N	\N	latin1_swedish_ci	\N		
umesh004	InnoDB	10	Compact	0	0	16384	0	0	4194304	1	2009-02-24 10:07:39	\N	\N	latin1_swedish_ci	\N		
 
You may notice that Data_free for all the InnoDB table is "4194304"
 
Now time has come to set innodb_file_per_table = ON
 
SET GLOBAL innodb_file_per_table = ON;
 
As expected got Error Code : 1238
Variable 'innodb_file_per_table' is a read only variable
(0 ms taken)
 
So would bring down MySQL and add this entry in my.ini
Added below line to my.in & restarted MySQL server
 
innodb_file_per_table
 
show variables like 'innodb_file_per_table';
 
innodb_file_per_table ON
 
############
 
Now droping earlier created tables and recreating them..
 
drop table umesh001,umesh002,umesh003,umesh004;
 
create table umesh001(id int not null AUTO_INCREMENT PRIMARY key)engine=InnoDB;
create table umesh002(id int not null AUTO_INCREMENT PRIMARY key)engine=InnoDB;
create table umesh003(id int not null AUTO_INCREMENT PRIMARY key)engine=InnoDB;
create table umesh004(id int not null AUTO_INCREMENT PRIMARY key)engine=InnoDB;
 
show table status like 'umesh%';
 
Now you can see Data_free as 0...
 
umesh001	InnoDB	10	Compact	0	0	16384	0	0	0	1	2009-02-24 10:07:38	\N	\N	latin1_swedish_ci	\N		
umesh002	InnoDB	10	Compact	0	0	16384	0	0	0	1	2009-02-24 10:07:39	\N	\N	latin1_swedish_ci	\N		
umesh003	InnoDB	10	Compact	0	0	16384	0	0	0	1	2009-02-24 10:07:39	\N	\N	latin1_swedish_ci	\N		
umesh004	InnoDB	10	Compact	0	0	16384	0	0	0	1	2009-02-24 10:07:39	\N	\N	latin1_swedish_ci	\N		
 
Now would add some records and see if Data_free has changed...
 
Hope this clears...

Open in new window

Author

Commented:
If we create table without a data its always 0, but when u start to work on it, do delete, but still we dont get free size in table level. the Data_free reported either in per table or in single file its the same size, but this size  4194304 its too small for disk having 40GB free space. Owner is new table with per table each file option enable



    -> ;
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+-
| Name    | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free |
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+-
| address | InnoDB |      10 | Compact    | 1197650 |             77 |    92913664 |               0 |            0 |   4194304 |
| house   | InnoDB |      10 | Compact    |  100454 |             47 |     4734976 |               0 |            0 |   4194304 |
| import  | InnoDB |      10 | Compact    |       0 |              0 |       16384 |               0 |            0 |   4194304 |
| owner   | InnoDB |      10 | Compact    |   20301 |             78 |     1589248 |               0 |            0 |   4194304 |
| shoe    | InnoDB |      10 | Compact    |       1 |          16384 |       16384 |               0 |            0 |   4194304 |
| shop    | InnoDB |      10 | Compact    |       7 |           2340 |       16384 |               0 |            0 |   4194304 |
| trip    | InnoDB |      10 | Compact    |  150288 |             66 |     9977856 |               0 |            0 |   4194304 |
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+-
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:
Hmm.. better I would suggest to log a bug to MySQL regarding this.. because I tried this on couple of versions and ending with same results...  something is definitely not correct. I guess this value would hardly make any sense and because of that no one has paid attention to it..

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.