☰

Почему секционированные таблицы MySQL InnoDB в два раза больше несекционированных?

tag-icon • tag-icon mysql innodb table-partitioning
Почему секционированные таблицы MySQL InnoDB в два раза больше несекционированных?

У меня есть две таблицы InnoDB, идентичные во всех отношениях, за исключением того, что одна из них секционирована, а другая — нет:

DROP TABLE IF EXISTS `simple_table`;
CREATE TABLE `simple_table` (
  `date` date NOT NULL,
  `item_id` bigint(8) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `partitioned_table`;
CREATE TABLE `partitioned_table` (
  `date` date NOT NULL,
  `item_id` bigint(8) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PARTITION BY RANGE ( TO_DAYS(`date`))
(PARTITION p20180207 VALUES LESS THAN (737098) ENGINE = InnoDB,
 PARTITION p20180208 VALUES LESS THAN (737099) ENGINE = InnoDB);

Я вставляю одни и те же данные в оба:

INSERT INTO `simple_table` (`date`, `item_id`) VALUES ('2018-02-07', 1), ('2018-02-07', 2), ('2018-02-07', 3);
INSERT INTO `simple_table` (`date`, `item_id`) VALUES ('2018-02-08', 1), ('2018-02-08', 2), ('2018-02-08', 3);
INSERT INTO `partitioned_table` (`date`, `item_id`) VALUES ('2018-02-07', 1), ('2018-02-07', 2), ('2018-02-07', 3);
INSERT INTO `partitioned_table` (`date`, `item_id`) VALUES ('2018-02-08', 1), ('2018-02-08', 2), ('2018-02-08', 3);

Результатом является то, что разделенные данные в два раза больше. Вот результат SHOW TABLE STATUS:

*************************** 1. row ***************************
        Name: partitioned_table
        Engine: InnoDB
        Version: 10
    Row_format: Compact
        Rows: 6
Avg_row_length: 5461
    Data_length: 32768
Max_data_length: 0
Index_length: 0
    Data_free: 0
Auto_increment: NULL
    Create_time: 2018-02-19 14:36:29
    Update_time: NULL
    Check_time: NULL
    Collation: utf8_unicode_ci
    Checksum: NULL
Create_options: partitioned
        Comment: 
*************************** 2. row ***************************
        Name: simple_table
        Engine: InnoDB
        Version: 10
    Row_format: Compact
        Rows: 6
Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
Index_length: 0
    Data_free: 0
Auto_increment: NULL
    Create_time: 2018-02-19 14:36:29
    Update_time: NULL
    Check_time: NULL
    Collation: utf8_unicode_ci
    Checksum: NULL
Create_options: 
        Comment:

(Посмотрите на Data_lengthзначения: 16384 против 32768)

То же самое происходит с таблицами немного более сложными и содержащими миллионы записей на дату: после применения разбиения они становятся вдвое больше. Это можно увидеть в статусе таблицы, а также в фактических размерах файлов.

Я не могу найти никакой информации по таким вопросам. Почему это происходит?

ОБНОВЛЕНИЕ: Вот что получится, если я изменю количество разделов на 10 и вставлю 100 записей для каждой даты (всего 10000 для каждой из двух таблиц):

           Name: partitioned_table
    Data_length: 655360
...
           Name: simple_table
    Data_length: 344064

(значения продолжают меняться в течение нескольких минут после вставки, но в конечном итоге стабилизируются) А если я перечислю файлы, то получу следующее:

-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180201.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180202.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180203.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180204.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180205.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180206.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180207.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180208.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180209.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180210.ibd
-rw-rw----  1 mysql mysql 409600 Feb 20 15:50 simple_table.ibd
-rw-rw----  1 mysql mysql    128 Feb 20 15:50 partitioned_table.par
-rw-rw----  1 mysql mysql   8596 Feb 20 15:50 partitioned_table.frm
-rw-rw----  1 mysql mysql   8596 Feb 20 15:50 simple_table.frm

решение1

Найдена причина проблемы — она вызвана тем, как MySQL выделяет дисковое пространство для растущих файлов таблиц.

Теперь я понимаю, что мой пример не очень хорош для случая, который я пытался проиллюстрировать, но изначально моя проблема была связана с большими базами данных (с миллионами маленьких строк).

я нашел этостатьякоторый говорит о том,innodb_spaceинструмент, с помощью которого я обнаружил, что мои таблицы в основном состоят из пустых выделенных страниц, что привело меня к этому MySQLстраница документа. Там написано:

Страницы сгруппированы в экстенты размером 1 МБ для страниц размером до 16 КБ.

и

Когда сегмент растет внутри табличного пространства, InnoDB выделяет ему первые 32 страницы по одной. После этого InnoDB начинает выделять сегменту целые экстенты. InnoDB может добавлятьдо 4 экстентовза один раз к большому сегменту, чтобы обеспечить хорошую последовательность данных.

Это объясняет пустые страницы в моих таблицах. В результате .ibdфайлы могут быть в несколько раз больше реальных данных.

Основная причина на самом деле не имеет ничего общего с секционированием, но секционирование усиливает эффект, поскольку файлы секций намного меньше и содержат меньше строк, чем однофайловые таблицы (особенно если есть много почти пустых секций, которые все равно имеют довольно большие размеры файлов).

Связанный контент