SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 29.11.2014 18:52:53

gif-t
Завсегдатай
Зарегистрирован: 08.08.2011
Сообщений: 74

Размер таблицы увеличивается при переводе с MyISAM на InnoDB в 6,5 раз

Есть таблица на 1158 строки:

CREATE TABLE `EXXXXX` (
  `id1` tinyint(3) unsigned NOT NULL,
  `id2` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `id3` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `id4` tinyint(3) unsigned NOT NULL,
  `p0` blob NOT NULL,
  `p1` blob NOT NULL,
  `p2` blob NOT NULL,
  `p3` blob NOT NULL,
  `p4` blob NOT NULL,
  `p5` blob NOT NULL,
  `p6` blob NOT NULL,
  `p7` blob NOT NULL,
  `p8` blob NOT NULL,
  `p9` blob NOT NULL,
  PRIMARY KEY (`id2`,`id3`,`id4`,`id1`) KEY_BLOCK_SIZE=16
) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=2;

Занимает 186,55 MB.

Перевожу её на движок MyISAM:

CREATE TABLE `EXXXXX_copy` (
  `id1` tinyint(3) unsigned NOT NULL,
  `id2` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `id3` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `id4` tinyint(3) unsigned NOT NULL,
  `p0` blob NOT NULL,
  `p1` blob NOT NULL,
  `p2` blob NOT NULL,
  `p3` blob NOT NULL,
  `p4` blob NOT NULL,
  `p5` blob NOT NULL,
  `p6` blob NOT NULL,
  `p7` blob NOT NULL,
  `p8` blob NOT NULL,
  `p9` blob NOT NULL,
  PRIMARY KEY (`id2`,`id3`,`id4`,`id1`) KEY_BLOCK_SIZE=1024
) ENGINE=MyISAM DEFAULT CHARSET=ascii ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=2;

Размер становится 28,70 MB, т.е. уменьшается в 6,5 раз. Так и должно быть?
Таблицы могу слить на файлообменник

Отредактированно gif-t (29.11.2014 19:00:29)

Неактивен

 

#2 29.11.2014 23:45:53

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Размер таблицы увеличивается при переводе с MyISAM на InnoDB в 6,5 раз

В описанном примере размер таблицы уменьшается при переходе на MyISAM что совсем не эквивалентно увеличению при переходе на InnoDB
http://sqlinfo.ru/forum/viewtopic.php?pid=34391#p34391


И к вопросу о размере InnoDB таблиц
http://sqlinfo.ru/forum/viewtopic.php?id=3481

Неактивен

 

#3 30.11.2014 12:00:30

gif-t
Завсегдатай
Зарегистрирован: 08.08.2011
Сообщений: 74

Re: Размер таблицы увеличивается при переводе с MyISAM на InnoDB в 6,5 раз

vasya написал:

В описанном примере размер таблицы уменьшается при переходе на MyISAM что совсем не эквивалентно увеличению при переходе на InnoDB

Я пробовал переводить InnoDB  -> MyISAM -> InnoDB. Делал optimize table. Всё равно файл на жестком диске у InnoDB в 7 раз больше.

На странице http://sqlinfo.ru/forum/viewtopic.php?id=3481 я нашел:

paulus написал:

Место, занимаемое таблицей, равно месту, которое занимают данные (Data_length),
индексы (Index_length), и месту, которое резервирует хранилище под расширение
таблицы (оно пишется в комментарии — InnoDB free: xxx kB).

Я правильно понимаю что надо смотреть на параметр Data_free? У меня он 0. Но если даже это неверное значение и движок на самом деле резервирует места в 7 раз больше, в таком случае логичным будет если размер файла таблицы не увеличится при вставке еще одной строки. Ведь в файле таблицы в таком случае зарезервировано места еще на 6500 тысяч строк. А размер при вставке увеличивается.

EXXXXX_InnoDB                 
Engine: InnoDB
Version: 10
Row_format: Dynamic   
Rows: 498 (на самом деле 1000)
Avg_row_length: 167426
Data_length: 83378176 (79,52 MB)
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL         
Create_time: 2014-11-30 12:33:06
Update_time: NULL               
Check_time: NULL               
Collation: ascii_general_ci
Checksum: NULL   
Create_options: row_format=DYNAMIC KEY_BLOCK_SIZE=2     

EXXXXX_MyISAM                 
Engine: MyISAM
Version: 10
Row_format: Dynamic   
Rows: 1000
Avg_row_length: 12608
Data_length: 12608756 (12,02 MB)
Max_data_length: 281474976710655
Index_length: 16384
Data_free: 0
Auto_increment: NULL         
Create_time: 2014-11-29 20:08:47
Update_time: 2014-11-29 20:08:47
Check_time: NULL               
Collation: ascii_general_ci
Checksum: NULL   
Create_options: row_format=DYNAMIC KEY_BLOCK_SIZE=2

Для проверки я увеличил количество строк в 2 раза, до 2000. Размер таблицы InnoDB удвоился и стал 157,55 MB (165 199 872). Т.е. на движке innodb размер таблицы всегда в 6.5 раз больше, чем на MyISAM и я не понимаю почему? Согласитесь, разница в цене на ssd между 256 Гб и 2 Тб довольно велика..., полнейший абсурд

EXXXXX_InnoDB_2000
Engine: InnoDB
Version: 10
Row_format: Dynamic   
Rows: 983 (на самом деле 2000)
Avg_row_length: 168056
Data_length: 165199872 (157,55 MB)
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL           
Create_time: 2014-11-30 12:51:14
Update_time: NULL               
Check_time: NULL               
Collation: ascii_general_ci 
Checksum: NULL     
Create_options row_format=DYNAMIC KEY_BLOCK_SIZE=2

InnoDB: http://rghost.ru/59332840
MyISAM: http://rghost.ru/59332879

Отредактированно gif-t (30.11.2014 12:07:06)

Неактивен

 

#4 30.11.2014 12:11:56

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Размер таблицы увеличивается при переводе с MyISAM на InnoDB в 6,5 раз

А какая суммарная длина всех хранимых блобов SUM(LENGTH(p0))+SUM(LENGTH(p1))+...? Сколько строк в таблице?

Неактивен

 

#5 30.11.2014 12:51:34

gif-t
Завсегдатай
Зарегистрирован: 08.08.2011
Сообщений: 74

Re: Размер таблицы увеличивается при переводе с MyISAM на InnoDB в 6,5 раз

А как будет выглядеть запрос для структуры:

CREATE TABLE `EXXXXX_MyISAM` (
  `id1` tinyint(3) unsigned NOT NULL,
  `id2` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `id3` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `id4` tinyint(3) unsigned NOT NULL,
  `p0` blob NOT NULL,
  `p1` blob NOT NULL,
  `p2` blob NOT NULL,
  `p3` blob NOT NULL,
  `p4` blob NOT NULL,
  `p5` blob NOT NULL,
  `p6` blob NOT NULL,
  `p7` blob NOT NULL,
  `p8` blob NOT NULL,
  `p9` blob NOT NULL,
  PRIMARY KEY (`id2`,`id3`,`id4`,`id1`) KEY_BLOCK_SIZE=1024
) ENGINE=MyISAM DEFAULT CHARSET=ascii ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=2;

Строк: 1000 (см. прошлое сообщение)

Вот дампы табличек:
InnoDB: http://rghost.ru/59332840
MyISAM: http://rghost.ru/59332879

Отредактированно gif-t (30.11.2014 12:54:39)

Неактивен

 

#6 30.11.2014 13:10:07

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Размер таблицы увеличивается при переводе с MyISAM на InnoDB в 6,5 раз

Каждый BLOB, не помещающийся в запись, будет храниться в отдельной странице хранилища, которая занимает 16k. Получается 10 блобов x 1000 строк x 16k = 160M. В перконе можно поменять размер страницы, но это тяжелая операция, которая делает его бинарно не совместимым.

http://www.percona.com/blog/2010/02/09/ … in-innodb/

Another thing to beware with Innodb BLOB storage is the fact external blob pages are not shared among the blobs. Each blob, even if it has 1 byte which does not fit on the page will have its own 16K allocated. This can be pretty inefficient so I’d recommend avoiding multiple large blobs per row when possible. Much better decision in many cases could be combine data in the single large Blob (and potentially compress it)

Неактивен

 

#7 30.11.2014 13:18:24

gif-t
Завсегдатай
Зарегистрирован: 08.08.2011
Сообщений: 74

Re: Размер таблицы увеличивается при переводе с MyISAM на InnoDB в 6,5 раз

Спасибо, это всё объясняет

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson