ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / InnoDB System Tablespace, ibdata1 file size
3 сообщений из 3, страница 1 из 1
InnoDB System Tablespace, ibdata1 file size
    #38546345
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Dear experts,

I have another question about InnoDB Data Structure.
As i read documentation it states that System Tablespace is the default place where InnoDB store all his tables, indexes and datas if innodb_file_per_table = 0 (or disabled).

It also states that ibdata1 file is the only "data file" created by default after MySQL fresh installation.
So data file is a file that InnoDB/MySQL and any other RDBMS uses for storing all it's data.

And as i stated above if innodb_file_per_table = 0 then all InnoDB data will be stored in ibdata1 and it equally means that ibdata1 is System Tablespace.

Another thing that Documentation says is in System Tablespace InnoDB stores it's : data dictionary, undo log, change buffer and doublewrite buffer. System Tablespace

So in ibdata1 file there are segments other than Table Datas --- data dictionary, undo log, change buffer and doublewrite buffer.

OK. That is quite simple to catch. i agree :)

But After fresh installation of MySQL 5.6.15 on Centos 6.5:


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)


mysql> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)


mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)




Fresh default installation states that initial size of ibdata1 will be 12M and with autoextend feature.

For testing i just check size of ibdata1:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
[root@linuxsrv1 log]# cd /var/lib/mysql/
[root@linuxsrv1 mysql]# ls -l
total 176144
-rw-rw----. 1 mysql mysql       56 Jan  4 02:53 auto.cnf
-rw-rw----. 1 mysql mysql 79691776 Feb  2 14:02 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Feb  2 14:02 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Jan  4 02:53 ib_logfile1
drwx------. 2 mysql mysql     4096 Jan  4 02:53 mysql
srwxrwxrwx. 1 mysql mysql        0 Feb  2 13:11 mysql.sock
drwx------. 2 mysql mysql     4096 Jan  4 02:53 performance_schema




Size of ibdata1 file is 79691776 bytes:

Код: sql
1.
2.
3.
4.
5.
6.
7.
mysql> select 79691776/1024/1024;
+--------------------+
| 79691776/1024/1024 |
+--------------------+
|        76.00000000 |
+--------------------+
1 row in set (0.00 sec)



So it is 76 M in size.

It is a fresh installation so there are nothing but system tables in MySQL:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT count(*) tables
       FROM information_schema.TABLES
       WHERE  table_schema not in ('mysql','information_schema','performance_schema');

+--------+
| tables |
+--------+
|      0 |
+--------+
1 row in set (0.01 sec)



Size information of these system tables are:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
mysql> SELECT count(*) tables,
    ->        concat(round(sum(table_rows)/1000000,2),'M') rows,
    ->        concat(round(sum(data_length)/(1024),2),'K') data_K,
    ->        concat(round(sum(index_length)/(1024),2),'K') idx_K,
    ->        concat(round(sum(data_length+index_length)/(1024),2),'K') total_size_K,
    ->        concat(round(sum(data_length)/(1024*1024),2),'M') data_M,
    ->        concat(round(sum(index_length)/(1024*1024),2),'M') idx_M,
    ->        concat(round(sum(data_length+index_length)/(1024*1024),2),'M') total_size_M,
    ->        concat(round(sum(data_length)/(1024*1024*1024),2),'G') data_G,
    ->        concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx_G,
    ->        concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size_G,
    ->        round(sum(index_length)/sum(data_length),2) idxfrac
    ->        FROM information_schema.TABLES;
+--------+-------+---------+---------+--------------+--------+-------+--------------+--------+-------+--------------+---------+
| tables | rows  | data_K  | idx_K   | total_size_K | data_M | idx_M | total_size_M | data_G | idx_G | total_size_G | idxfrac |
+--------+-------+---------+---------+--------------+--------+-------+--------------+--------+-------+--------------+---------+
|    139 | 0.08M | 697.56K | 109.00K | 806.56K      | 0.68M  | 0.11M | 0.79M        | 0.00G  | 0.00G | 0.00G        |    0.16 |
+--------+-------+---------+---------+--------------+--------+-------+--------------+--------+-------+--------------+---------+
1 row in set (0.02 sec)




It is just 0.79 M in size.

So The question is how i can figure out why my single ibdata1 file in 79 M in size? in fact i have not got such size of tables.

Actually 79 - 0.79 = 78.21 M size of data file is used some other things or it is just allocated for feature use. How and why?

Maybe it is allocated and captured by Undo Log? if it is? how i can measure the size of Undo Log?
Thanks in advance.
...
Рейтинг: 0 / 0
02.02.2014, 14:37:02
| Ответить | Цитировать | Написать  
InnoDB System Tablespace, ibdata1 file size
    #38546506
Aleksandr Kuzminsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Aleksandr Kuzminsky
Гость
Shahriyar.R,


Размер пустого ibdata1 10 мегабайт. Если размер 72М, то скорее всего tablespace имеет какую-то историю.
Я бы попробовал исследовать ibdata1 с помощью утилиты innodb_ruby Jeremy Cole.

Посмотрите примеры тут:
http://blog.jcole.us/2013/01/03/a-quick-introduction-to-innodb-ruby/
...
Рейтинг: 0 / 0
02.02.2014, 19:49:24
| Ответить | Цитировать | Написать  
InnoDB System Tablespace, ibdata1 file size
    #38546612
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Aleksandr Kuzminsky,

Thank you for a great advice.

Really useful link.
...
Рейтинг: 0 / 0
02.02.2014, 22:22:30
| Ответить | Цитировать | Написать  
3 сообщений из 3, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / InnoDB System Tablespace, ibdata1 file size
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=38546506&tid=1835294]:
 0ms
get settings:
 9ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 44ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 30ms
get tp. blocked users:
 1ms
others: 210ms
total:  317ms
созд. / загр.: 317ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]