Назад | Перейти на главную страницу

Почему секционированные таблицы 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)

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

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

UPD: Вот что я получу, если изменю количество разделов на 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

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

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

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

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

и

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

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

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