Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8702

Unexpected Memory Consumption for Bulk Index Creation in InnoDB.

    XMLWordPrintable

    Details

      Description

      Hi,

      I am creating a table without indexes and then adding indexes to the table. The table definition is the following:

      CREATE TABLE `test_wo_keys` (
        `f01` int AUTO_INCREMENT,
        `f02` bigint, `f03` bigint, `f04` enum('a','b'),
        `f05` date, `f06` int, `f07` int, `f08` double, `f09` int,
        `f10` bigint, `f11` double, `f12` enum('a','b','c','d','e'),
        `f13` int, `f14` int, `f15` varchar(255), `f16` int, `f17` int, `f18` int,
        `f19` double, `f20` double, `f21` double, `f22` double, `f23` double, `f24` tinyint,
        `f25` double, `f26` double, `f27` double, `f28` double, `f29` int unsigned,
        `f30` int unsigned, `f31` bigint, `f32` int unsigned, `f33` bigint,
        `f34` int unsigned, `f35` int unsigned,
        PRIMARY KEY `f01` (`f01`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

      and the indexes are the following:

      ALTER TABLE test_wo_keys
        ADD KEY f02 (f02), ADD KEY f03 (f03), ADD KEY f04 (f04), ADD KEY f05 (f05),
        ADD KEY f06 (f06), ADD KEY f07 (f07), ADD KEY f08 (f08), ADD KEY f09 (f09), 
        ADD KEY f10 (f10), ADD KEY f11 (f11), ADD KEY f12 (f12), ADD KEY f13 (f13),
        ADD KEY f14 (f14), ADD KEY f16 (f16), ADD KEY f17 (f17), ADD KEY f18 (f18),
        ADD KEY f19 (f19), ADD KEY f20 (f20), ADD KEY f21 (f21), ADD KEY f22 (f22),
        ADD KEY f23 (f23), ADD KEY f24 (f24), ADD KEY f25 (f25), ADD KEY f26 (f26),
        ADD KEY f27 (f27), ADD KEY f28 (f28), ADD KEY f29 (f29), ADD KEY f30 (f30),
        ADD KEY f31 (f31), ADD KEY f32 (f32), ADD KEY f33 (f33), ADD KEY f34 (f34);

      When I add the indexes on the table containing 5.000.000 rows with "innodb_sort_buffer_size = 64M", memory consumption raises by 10 GB. According to the documentation, it should only raise by 3x 64 MB plus row pointers. My observations are far from what is described in the documentation.

      Thanks for looking into that.

      Best regards,

      JFG

        Attachments

          Activity

            People

            Assignee:
            jplindst Jan Lindström
            Reporter:
            jgagne Jean-François Gagné
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated: