Why are partitioned MySQL InnoDB tables twice as big as unpartitioned ones?

Question

I have two InnoDB tables identical in every respect except for one being partitioned, and the other - not:

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

I insert the same data into both:

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

The result is that partitioned data is twice as big. Here is the result of 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:

(Look at the Data_length values: 16384 vs. 32768)

The same thing happens with tables a bit more complex and containing millions of entries per date: after applying partitioning they become twice as big. This can be seen in table status as well as the actual file sizes.

I cannot find any information on issues like this. Why is it happening?

UPD: Here is what I get if I change the number of partitions to 10 and insert 100 entries for each date (10000 total for each of the two tables):

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

(the values keep changing for a couple of minutes after the inserts, but eventually they stabilize) And if I list the files I get this:

-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

I doubt benchmarking this with two rows is going to give you any sort of useful data. Does this persist if you insert a million test rows? — ceejayoz, Feb 19 '18 at 14:48
@ceejayoz yes, as I already said in the description. And with several hundred date partitions. — Grisha S, Feb 19 '18 at 14:58
I'd take a close look at this bit: `PARTITION p20180207 VALUES LESS THAN (737098) ENGINE = InnoDB, PARTITION p20180208 VALUES LESS THAN (737099) ENGINE = InnoDB`. I suspect you're duplicating any rows less than 737099, because you've got both partitions storing **less than** about the same number. — ceejayoz, Feb 19 '18 at 15:02
@ceejayoz, no, that's just the way range partitioning has to be defined in MySQL — Grisha S, Feb 19 '18 at 15:06

score 0 · Answer 1 · answered Feb 22 '18 at 13:35

Found the cause of the problem - it is caused by how MySQL allocates disk space for growing table files.

I realize now that my example here was not that good for the case I was trying to illustrate, but my original problem was with large databases (with millions of tiny rows)

I found this article that talks about the innodb_space tool using which I found that my tables mostly consist of empty allocated pages, which led me to this MySQL doc page. It says:

The pages are grouped into extents of size 1MB for pages up to 16KB in size

and

When a segment grows inside the tablespace, InnoDB allocates the first 32 pages to it one at a time. After that, InnoDB starts to allocate whole extents to the segment. InnoDB can add up to 4 extents at a time to a large segment to ensure good sequentiality of data.

This explains the empty pages in my tables. As a result, the .ibd files can be up to several times bigger that the actual data.

The root cause has nothing to do with partitioning really, but partitioning amplifies the effect because partition files are much smaller and contain fewer rows than single-file tables (especially if there is a lot of almost empty partitions, which still have pretty large file sizes)

Why are partitioned MySQL InnoDB tables twice as big as unpartitioned ones?

1 Answers1