☰

Por que as tabelas MySQL InnoDB particionadas são duas vezes maiores que as não particionadas?

tag-icon • tag-icon mysql innodb table-partitioning
Por que as tabelas MySQL InnoDB particionadas são duas vezes maiores que as não particionadas?

Eu tenho duas tabelas InnoDB idênticas em todos os aspectos, exceto uma sendo particionada e a outra - não:

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);

Eu insiro os mesmos dados em ambos:

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);

O resultado é que os dados particionados são duas vezes maiores. Aqui está o resultado de 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:

(Veja os Data_lengthvalores: 16384 vs. 32768)

O mesmo acontece com tabelas um pouco mais complexas e contendo milhões de entradas por data: após aplicar o particionamento elas ficam duas vezes maiores. Isso pode ser visto no status da tabela, bem como nos tamanhos reais dos arquivos.

Não consigo encontrar nenhuma informação sobre questões como esta. Por que isso está acontecendo?

Atualização: Aqui está o que obtenho se alterar o número de partições para 10 e inserir 100 entradas para cada data (10.000 no total para cada uma das duas tabelas):

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

(os valores continuam mudando por alguns minutos após as inserções, mas eventualmente eles se estabilizam) E se eu listar os arquivos, recebo o seguinte:

-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

Responder1

Encontrou a causa do problema - é causado pela forma como o MySQL aloca espaço em disco para aumentar os arquivos de tabela.

Percebo agora que meu exemplo aqui não foi tão bom para o caso que estava tentando ilustrar, mas meu problema original era com bancos de dados grandes (com milhões de linhas minúsculas).

Eu achei istoartigoque fala sobre oinnodb_spaceferramenta com a qual descobri que minhas tabelas consistem principalmente em páginas alocadas vazias, o que me levou a este MySQLpágina do documento. Diz:

As páginas são agrupadas em extensões de tamanho 1 MB para páginas de até 16 KB

e

Quando um segmento cresce dentro do tablespace, o InnoDB aloca as primeiras 32 páginas, uma de cada vez. Depois disso, o InnoDB começa a alocar extensões inteiras para o segmento. InnoDB pode adicionaraté 4 extensõesde cada vez para um grande segmento para garantir uma boa sequencialidade de dados.

Isso explica as páginas vazias nas minhas tabelas. Como resultado, os .ibdarquivos podem ser várias vezes maiores que os dados reais.

A causa raiz não tem nada a ver com o particionamento, mas o particionamento amplifica o efeito porque os arquivos de partição são muito menores e contêm menos linhas do que as tabelas de arquivo único (especialmente se houver muitas partições quase vazias, que ainda possuem tamanhos de arquivo bastante grandes )

informação relacionada