☰

¿Por qué las tablas MySQL InnoDB particionadas son dos veces más grandes que las no particionadas?

tag-icon • tag-icon mysql innodb table-partitioning
¿Por qué las tablas MySQL InnoDB particionadas son dos veces más grandes que las no particionadas?

Tengo dos tablas InnoDB idénticas en todos los aspectos, excepto que una está particionada y la otra no:

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

Inserto los mismos datos en 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);

El resultado es que los datos particionados son el doble de grandes. Aquí está el 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:

(Mire los Data_lengthvalores: 16384 vs. 32768)

Lo mismo ocurre con las tablas un poco más complejas y que contienen millones de entradas por fecha: después de aplicarles particiones se vuelven dos veces más grandes. Esto se puede ver en el estado de la tabla, así como en los tamaños reales de los archivos.

No puedo encontrar ninguna información sobre temas como este. ¿Por qué está sucediendo?

ACTUALIZACIÓN: Esto es lo que obtengo si cambio el número de particiones a 10 e inserto 100 entradas para cada fecha (10000 en total para cada una de las dos tablas):

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

(los valores siguen cambiando durante un par de minutos después de las inserciones, pero eventualmente se estabilizan) Y si enumero los archivos, obtengo esto:

-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

Respuesta1

Encontré la causa del problema: se debe a la forma en que MySQL asigna espacio en disco para los archivos de tablas en crecimiento.

Ahora me doy cuenta de que mi ejemplo aquí no era tan bueno para el caso que estaba tratando de ilustrar, pero mi problema original era con bases de datos grandes (con millones de filas pequeñas).

encontré estoartículoque habla de laespacio_innodbherramienta mediante la cual descubrí que mis tablas consisten en su mayoría en páginas vacías asignadas, lo que me llevó a este MySQLpágina de documento. Dice:

Las páginas se agrupan en extensiones de tamaño 1 MB para páginas de hasta 16 KB de tamaño.

y

Cuando un segmento crece dentro del espacio de tabla, InnoDB le asigna las primeras 32 páginas una a la vez. Después de eso, InnoDB comienza a asignar extensiones completas al segmento. InnoDB puede agregarhasta 4 extensionesa la vez a un segmento grande para garantizar una buena secuencialidad de los datos.

Esto explica las páginas vacías en mis tablas. Como resultado, los .ibdarchivos pueden ser hasta varias veces más grandes que los datos reales.

La causa raíz no tiene nada que ver con la partición en realidad, pero la partición amplifica el efecto porque los archivos de partición son mucho más pequeños y contienen menos filas que las tablas de un solo archivo (especialmente si hay muchas particiones casi vacías, que todavía tienen tamaños de archivo bastante grandes). )

información relacionada