Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

When I see the size of my db_1 its listed as only 4k. But when I show table status for db_1, I see that the total size is approximately 3.7 GB based on data length. Why is there this discrepancy? Also why is ibdata1 so large?

drwx------ 2 mysql mysql 4.0K Jun  3 08:52 db_1
-rw-r-xr-x 1 mysql mysql  22G Jun  3 08:51 ibdata1
drwx------ 2 mysql mysql 4.0K Apr 30 22:30 my_DB
drwxr-xr-x 2 mysql dba   8.0K May  1 12:44 mysql
-rw-r-xr-x 1 mysql root  4.4K Jun  1 14:33 
-rw-rw---- 1 mysql mysql    5 May 17 18:19 
-rw-r-xr-x 1 mysql mysql  51K Jun  3 08:41 
drwxr-xr-x 2 mysql dba   4.0K Apr 30 22:29 performance_schema







----------------+---------+
| Name                                           | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free   | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+------------------------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| xxxxxxxxxxxxxxxxxxxxxx                         | InnoDB |      10 | Compact    |      117 |            140 |       16384 |               0 |            0 | 17825792000 |           NULL | 2012-05-01 13:24:34 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx| InnoDB |      10 | Compact    |   466203 |            248 |   116015104 |               0 |            0 | 17825792000 |           NULL | 2012-05-01 13:31:28 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx      | InnoDB |      10 | Compact    |  1028265 |            111 |   114147328 |               0 |            0 | 17825792000 |           NULL | 2012-05-01 13:32:36 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| xxxxxxxxxxxxxxxxxxxxxxxx                       | InnoDB |      10 | Compact    |  1353685 |             98 |   133922816 |               0 |            0 | 17825792000 |           NULL | 2012-05-01 13:32:49 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| xxxxxxxxxxxxxxxxxx                             | InnoDB |      10 | Compact    |    50546 |             52 |     2637824 |               0 |            0 | 17825792000 |           NULL | 2012-05-01 13:04:18 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| xxxxxxxxxxxxxxxxxxxxxx                         | InnoDB |      10 | Compact    |    44281 |             83 |     3686400 |               0 |            0 | 17825792000 |           NULL | 2012-05-01 13:19:25 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| xxxxxxxxxxxxxxxxxxxxxxxxx                      | InnoDB |      10 | Compact    |  5841698 |            124 |   729808896 |               0 |    163397632 | 17825792000 |           NULL | 2012-05-01 13:17:29 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| xxxxxxxxxxxxxxxxxxxxxxxx                       | InnoDB |      10 | Compact    | 52327792 |             60 |  3171942400 |               0 |            0 | 17825792000 |           NULL | 2012-05-03 17:09:41 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+------------------------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
share|improve this question
1  
Heh! db_1 is a directory, so 4k is about right. See below for the actual database storage file ibdata1. –  Fiasco Labs Jun 3 '12 at 18:10

1 Answer 1

This is a very common question which has been asked many times.
This is because you are storing all the tables in a single InnoDB file (ibdata1). This file does not shrink after data is deleted, ever. So at one point you had 22gb of data stored in all your InnoDB tables, even though you may not have that much now.

To solve this issue you need to drop all your InnoDB tables, shut down MySQL, delete that file, start MySQL back up, and then re-import those tables.

Also you can use the MySQL setting innodb_file_per_table to split each InnoDB table into its own file. The files will still not automatically shrink, but you can issue a cleanup with alter table ... type=innodb.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.