Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MYSQL,Data_Free column in show table status

Posted on 2009-02-18
16
Medium Priority
?
3,131 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 |
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+
0
Comment
Question by:motioneye
  • 10
  • 6
16 Comments
 
LVL 26

Expert Comment

by:Umesh
ID: 23672249
That's in Bytes ..The number of allocated but unused bytes
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23672264
Pls take a look at here for for more..

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

Author Comment

by:motioneye
ID: 23672297
Hi,'
Is Data_free refer to hard disk free space or database free space?
0
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

 
LVL 26

Expert Comment

by:Umesh
ID: 23672365
It refers to Database free space..
0
 

Author Comment

by:motioneye
ID: 23688895
Hi,
If it refer to database free space, is there a way which I can shrink this free space ???
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23688903
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.
0
 

Author Comment

by:motioneye
ID: 23688906
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)
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23688940
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
0
 
LVL 26

Expert Comment

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

0
 

Author Comment

by:motioneye
ID: 23708989
Found the link from http://lists.mysql.com/commits/62055, but how do I install the patch ?
0
 
LVL 26

Assisted Solution

by:Umesh
Umesh earned 1500 total points
ID: 23709010
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..

0
 

Author Comment

by:motioneye
ID: 23711747
ic ok, then I should follow yours
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23711785
That's fine..

Thanks,
Umesh
0
 
LVL 26

Accepted Solution

by:
Umesh earned 1500 total points
ID: 23718389
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

0
 

Author Comment

by:motioneye
ID: 23729664
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 |
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+-
0
 
LVL 26

Assisted Solution

by:Umesh
Umesh earned 1500 total points
ID: 23730328
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..

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month11 days, 12 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question