MySQL Bugs: #78270: Unexpected Memory Consumption for Bulk Index Creation in InnoDB.

Bug #78270	Unexpected Memory Consumption for Bulk Index Creation in InnoDB.
Submitted:	29 Aug 2015 22:50	Modified:	13 Jan 2016 20:44
Reporter:	Jean-François Gagné	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	MySQL 5.6.26.	OS:	Any (CentOS release 6.6)
Assigned to:		CPU Architecture:	Any

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 auxiliary pointers.  My observations are far from what is described in the documentation.

Thanks for looking into that.

Best regards,

JFG

How to repeat:
See description.

Suggested fix:
From storage/innobase/row/row0merge.cc, it looks like the scanning of the table allocates RAM to store the index data prior to sorting, not only auxiliary pointers.  Once enough data is gathered, it is sorted and then written to disk via a buffer of innodb_sort_buffer_size.

From what I understand of the code, I would expect to consume an additional innodb_sort_buffer_size per created index (an additional 2 GB for 32 indexes), but I observe much more (10 GB).  The delta might be in InnoDB memory management structures (heap), but the full details escape me.

Some more details in the following:
http://jfg-mysql.blogspot.nl/2015/08/unexpected-memory-consumption-for-bulk-index-creation...

Hello Jean-François Gagné,

Thank you for the report.
I observed similar behavior during my tests.

Thanks,
Umesh

// Start up

scripts/mysql_install_db --basedir=/export/umesh/server/binaries/mysql-advanced-5.6.26 --datadir=/export/umesh/server/binaries/mysql-advanced-5.6.26/78270
bin/mysqld --innodb_sort_buffer_size=64M --basedir=/export/umesh/server/binaries/mysql-advanced-5.6.26 --datadir=/export/umesh/server/binaries/mysql-advanced-5.6.26/78270 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.6.26/78270/log.err 2>&1 &

-- Provision data

#!/bin/perl

my @f04 = ('a','b');
my @f11 = ('a'..'e');
my @f111 = (1..127);
my $txt = 'x' x 250;

for(my $i=1; $i <= 5000000; $i++) {

	my $f  = $f04[rand @f04];
	my $f1 = $f11[rand @f11];
	my $tiny = $f111[rand @f111];    
	print "$i,$i,$i,$f,now(),$i,$i,$i,$i,$i,$tiny,$f1,$i,$i,$txt,$i,$i,$i,$i,$i,$i,$i,$i,$i,$i,$i,$i,$i,$i,$i,$i,$i,$i,$i,$i\n";
	exit();
}

create database if not exists test;
use test;

drop table if exists `test_wo_keys`;
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;

load data local infile '/export/umesh/server/binaries/mysql-advanced-5.6.26/5m.dump' into table test_wo_keys fields terminated by ',';

## Prepare data

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.26: perl l.pl > 5m.dump

## Create schema and load data

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.26:  bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.26-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database if not exists test;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> use test;
Database changed
mysql>
mysql> load data local infile '/export/umesh/server/binaries/mysql-advanced-5.6.26/5m.dump' into table test_wo_keys fields terminated by ',';
Query OK, 5000000 rows affected, 65535 warnings (1 min 27.85 sec)
Records: 5000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> 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;
Query OK, 0 rows affected (0.01 sec)

mysql> 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);
Query OK, 0 rows affected (5 min 28.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

## Session 2 ( incrased from 1536176K -> 15182116K during index creation but reduced to 1732784 afterwards)

[umshastr@hod03]~/bugs/jemalloc/lib: ps aux|grep mysqld|grep -v grep
umshastr 25339 18.2  0.2 1536176 580784 pts/0  Sl   13:45   4:14 bin/mysqld --innodb_sort_buffer_size=64M --basedir=/export/umesh/server/binaries/mysql-advanced-5.6.26 --datadir=/export/umesh/server/binaries/mysql-advanced-5.6.26/78270 --core-file --socket=/tmp/mysql_ushastry.sock --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.6.26/78270/log.err
[umshastr@hod03]~/bugs/jemalloc/lib:
[umshastr@hod03]~/bugs/jemalloc/lib: pmap 25339
 total          1536176K

.
 total          7252260K

.
 total         13936932K
.
.
 total         15182116K
.
 total         15182116K

-- After index creation
 total          1732784K

The formula for estimating memory consumption has been corrected in the documentation (MySQL Bug #69325):
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_sort_buffer_si...

Thank you for the bug report.

Hi,

I am not sure updating the documentation is a way to solve this problem.

To me, this looks more than a documentation bug: allocating 10 GB for an ALTER table creating 32 indexes is also a resource allocation problem.

I could like this bug to be re-opened and I would like RAM consumption to be optimized here.

Thanks,

JFG