☰

Warum sind partitionierte MySQL InnoDB-Tabellen doppelt so groß wie unpartitionierte?

tag-icon • tag-icon mysql innodb table-partitioning
Warum sind partitionierte MySQL InnoDB-Tabellen doppelt so groß wie unpartitionierte?

Ich habe zwei InnoDB-Tabellen, die in jeder Hinsicht identisch sind, außer dass eine partitioniert ist und die andere nicht:

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

Ich gebe in beide die gleichen Daten ein:

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

Das Ergebnis ist, dass partitionierte Daten doppelt so groß sind. Hier ist das Ergebnis von 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:

(Sehen Sie sich die Data_lengthWerte an: 16384 vs. 32768)

Dasselbe passiert mit etwas komplexeren Tabellen, die Millionen von Einträgen pro Datum enthalten: Nach der Partitionierung werden sie doppelt so groß. Dies ist sowohl im Tabellenstatus als auch in den tatsächlichen Dateigrößen zu sehen.

Ich kann keine Informationen zu solchen Problemen finden. Warum passiert das?

Aktualisierung: Folgendes erhalte ich, wenn ich die Anzahl der Partitionen auf 10 ändere und für jedes Datum 100 Einträge einfüge (insgesamt 10.000 für jede der beiden Tabellen):

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

(die Werte ändern sich nach den Einfügungen noch einige Minuten lang, stabilisieren sich aber schließlich) Und wenn ich die Dateien aufliste, erhalte ich Folgendes:

-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

Antwort1

Die Ursache des Problems wurde gefunden. Es wird durch die Art und Weise verursacht, wie MySQL Speicherplatz für wachsende Tabellendateien zuweist.

Mir ist jetzt klar, dass mein Beispiel hier für den Fall, den ich zu veranschaulichen versuchte, nicht so gut war, aber mein ursprüngliches Problem betraf große Datenbanken (mit Millionen winziger Zeilen).

ich habe das gefundenArtikeldas spricht über dieinnodb_spaceTool, mit dem ich festgestellt habe, dass meine Tabellen größtenteils aus leeren zugewiesenen Seiten bestehen, was mich zu diesem MySQL führtedoc-Seite. Es sagt:

Die Seiten werden in Bereiche von 1 MB für Seiten bis zu 16 KB Größe gruppiert

Und

Wenn ein Segment innerhalb des Tablespace wächst, weist InnoDB ihm die ersten 32 Seiten einzeln zu. Danach beginnt InnoDB, dem Segment ganze Extents zuzuweisen. InnoDB kannbis zu 4 Extentsauf einmal in ein großes Segment, um eine gute Datensequenziellität sicherzustellen.

Dies erklärt die leeren Seiten in meinen Tabellen. Infolgedessen .ibdkönnen die Dateien um ein Vielfaches größer sein als die eigentlichen Daten.

Die eigentliche Ursache hat nichts mit der Partitionierung zu tun, aber die Partitionierung verstärkt den Effekt, da Partitionsdateien viel kleiner sind und weniger Zeilen enthalten als Einzeldateitabellen (insbesondere, wenn viele fast leere Partitionen vorhanden sind, die immer noch ziemlich große Dateien aufweisen).

verwandte Informationen