CODEWITHSUNDEEP
mysqlinnodbmyisam
sergio
sergio

Reputation: 5260

Inno DB table file size

I created database "test_data_base" and created Inno db table "test_inno_db_table"(MySQL 5.6.6). Go to "var/lib/mysql/test_data_base/" folder(on Ubuntu 13.04) and saw my table file with some default size, and i have the default file for innoDb "ibdata". Then I set some data to this table and saw the file size, but nothing was changed.

http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html - Here we can see that innodb_file_per_table is on by default. But why did the size of my files does not change. For example when I create MyIsam table, it create 3 files for this table and when I set a data, of course, the file size will increase too.

Why does the files value does not increase when I set a new data?

Upvotes: 0

Views: 636

Answers (1)

Jason Heo
Jason Heo

Reputation: 10246

Are you sure your innodb_file_per_table is enabled? check:

mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

I think this value is 'OFF', so global table space ib_data1 is used. then, huge amount of disk space is pre-allocated. you can find initial size like this:

mysql> show variables like '%innodb%data%';
+--------------------------+------------------------+
| Variable_name            | Value                  |
+--------------------------+------------------------+
| innodb_data_file_path    | ibdata1:10M:autoextend |

UPDATED

Here is what i tried. I keep watching how file size glows. it grow up by 32kb at once. also I'm using barracuda format. I suggest that insert more more many many records.

mysql> create table db_size_test( a int, b int, c int);
Query OK, 0 rows affected (0.01 sec)


[XXXX]$ ls -lh db_size_test.ibd
-rw-rw---- 1 xxxx xxxx  96K 11월 21 21:26 db_size_test.ibd

mysql> select count(*) from db_size_test;
+----------+
| count(*) |
+----------+
|     1001 |
+----------+
1 row in set (0.00 sec)

[XXX]$ ls -lh db_size_test.ibd
-rw-rw---- 1 xxxx xxxxx 128K 11월 21 21:28 db_size_test.ibd

Upvotes: 1

Related Questions