Bug #69325 MySQL uses significantly more memory for ALTER TABLE than expected
Submitted: 27 May 2013 9:22 Modified: 19 Dec 2013 15:25
Reporter: Justin Swanhart Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.11 OS:Any
Assigned to: CPU Architecture:Any
Tags: ALTER TABLE, innodb_sort_buffer_size, Memory

[27 May 2013 9:22] Justin Swanhart
Description:
I have InnoDB configured to use a 20GB InnoDB buffer pool.
I have innodb_sort_buffer_size=64M

The manual says that 3 * innodb_sort_buffer_size will be allocated for an ALTER TABLE.

The VIRT size of MySQL is 60GB during the ALTER operation:
top - 02:15:04 up 16 min,  2 users,  load average: 2.01, 1.80, 1.04
Tasks: 308 total,   2 running, 306 sleeping,   0 stopped,   0 zombie
Cpu(s):  4.1%us,  0.5%sy,  0.0%ni, 93.8%id,  1.5%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  24598260k total, 24135092k used,   463168k free,   120436k buffers
Swap: 16777208k total,     3324k used, 16773884k free, 14259392k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                                                            
 3907 mysql     20   0 60.6g 8.6g 7148 S 76.4 36.8   7:51.25 mysqld                                                                                                                                                                             
  537 root      20   0     0    0    0 S  2.0  0.0   0:01.86 md0_raid1    

There are 84 partitions on the table I'm altering.

This is the alter:
alter table lineorder add key(lo_orderdatekey),  add key(lo_suppkey), add key(lo_partkey), add key(lo_custkey) ;

It appears to be using:
innodb_sort_buffer_size * 3 * number_of_partitions * number_of_indexes_being_added
     

How to repeat:
[mysqld]
datadir=/data/datadirs/mysql_56
basedir=/usr/local/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

max_allowed_packet=1G

innodb_buffer_pool_size=20G
innodb_log_file_size=4G
innodb_file_per_table
innodb_file_format=barracuda
innodb_buffer_pool_instances=6
innodb_write_io_threads=12
innodb_read_io_threads=12
innodb_flush_method=O_DIRECT
innodb_io_capacity=10000

innodb_sort_buffer_size=64M

innodb_stats_on_metadata=0

innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=32M

read_buffer_size=2M
key_buffer_size=32M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

CREATE TABLE `lineorder` (
  `LO_OrderKey` int(10) unsigned NOT NULL,
  `LO_LineNumber` tinyint(4) NOT NULL,
  `LO_CustKey` mediumint(9) NOT NULL,
  `LO_PartKey` mediumint(9) NOT NULL,
  `LO_SuppKey` mediumint(9) NOT NULL,
  `LO_OrderDateKey` int(11) NOT NULL,
  `LO_OrderPriority` varchar(15) DEFAULT NULL,
  `LO_ShipPriority` char(1) DEFAULT NULL,
  `LO_Quantity` tinyint(4) DEFAULT NULL,
  `LO_ExtendedPrice` decimal(10,0) DEFAULT NULL,
  `LO_OrdTotalPrice` decimal(10,0) DEFAULT NULL,
  `LO_Discount` decimal(10,0) DEFAULT NULL,
  `LO_Revenue` decimal(10,0) DEFAULT NULL,
  `LO_SupplyCost` decimal(10,0) DEFAULT NULL,
  `LO_Tax` tinyint(4) DEFAULT NULL,
  `LO_CommitDateKey` int(11) NOT NULL,
  `LO_ShipMode` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (LO_OrderDateKey)
(PARTITION p1992_01 VALUES LESS THAN (19920131) ENGINE = InnoDB,
 PARTITION p1992_02 VALUES LESS THAN (19920231) ENGINE = InnoDB,
 PARTITION p1992_03 VALUES LESS THAN (19920331) ENGINE = InnoDB,
 PARTITION p1992_04 VALUES LESS THAN (19920431) ENGINE = InnoDB,
 PARTITION p1992_05 VALUES LESS THAN (19920531) ENGINE = InnoDB,
 PARTITION p1992_06 VALUES LESS THAN (19920631) ENGINE = InnoDB,
 PARTITION p1992_07 VALUES LESS THAN (19920731) ENGINE = InnoDB,
 PARTITION p1992_08 VALUES LESS THAN (19920831) ENGINE = InnoDB,
 PARTITION p1992_09 VALUES LESS THAN (19920931) ENGINE = InnoDB,
 PARTITION p1992_10 VALUES LESS THAN (19921031) ENGINE = InnoDB,
 PARTITION p1992_11 VALUES LESS THAN (19921131) ENGINE = InnoDB,
 PARTITION p1992_12 VALUES LESS THAN (19921231) ENGINE = InnoDB,
 PARTITION p1993_01 VALUES LESS THAN (19930131) ENGINE = InnoDB,
 PARTITION p1993_02 VALUES LESS THAN (19930231) ENGINE = InnoDB,
 PARTITION p1993_03 VALUES LESS THAN (19930331) ENGINE = InnoDB,
 PARTITION p1993_04 VALUES LESS THAN (19930431) ENGINE = InnoDB,
 PARTITION p1993_05 VALUES LESS THAN (19930531) ENGINE = InnoDB,
 PARTITION p1993_06 VALUES LESS THAN (19930631) ENGINE = InnoDB,
 PARTITION p1993_07 VALUES LESS THAN (19930731) ENGINE = InnoDB,
 PARTITION p1993_08 VALUES LESS THAN (19930831) ENGINE = InnoDB,
 PARTITION p1993_09 VALUES LESS THAN (19930931) ENGINE = InnoDB,
 PARTITION p1993_10 VALUES LESS THAN (19931031) ENGINE = InnoDB,
 PARTITION p1993_11 VALUES LESS THAN (19931131) ENGINE = InnoDB,
 PARTITION p1993_12 VALUES LESS THAN (19931231) ENGINE = InnoDB,
 PARTITION p1994_01 VALUES LESS THAN (19940131) ENGINE = InnoDB,
 PARTITION p1994_02 VALUES LESS THAN (19940231) ENGINE = InnoDB,
 PARTITION p1994_03 VALUES LESS THAN (19940331) ENGINE = InnoDB,
 PARTITION p1994_04 VALUES LESS THAN (19940431) ENGINE = InnoDB,
 PARTITION p1994_05 VALUES LESS THAN (19940531) ENGINE = InnoDB,
 PARTITION p1994_06 VALUES LESS THAN (19940631) ENGINE = InnoDB,
 PARTITION p1994_07 VALUES LESS THAN (19940731) ENGINE = InnoDB,
 PARTITION p1994_08 VALUES LESS THAN (19940831) ENGINE = InnoDB,
 PARTITION p1994_09 VALUES LESS THAN (19940931) ENGINE = InnoDB,
 PARTITION p1994_10 VALUES LESS THAN (19941031) ENGINE = InnoDB,
 PARTITION p1994_11 VALUES LESS THAN (19941131) ENGINE = InnoDB,
 PARTITION p1994_12 VALUES LESS THAN (19941231) ENGINE = InnoDB,
 PARTITION p1995_01 VALUES LESS THAN (19950131) ENGINE = InnoDB,
 PARTITION p1995_02 VALUES LESS THAN (19950231) ENGINE = InnoDB,
 PARTITION p1995_03 VALUES LESS THAN (19950331) ENGINE = InnoDB,
 PARTITION p1995_04 VALUES LESS THAN (19950431) ENGINE = InnoDB,
 PARTITION p1995_05 VALUES LESS THAN (19950531) ENGINE = InnoDB,
 PARTITION p1995_06 VALUES LESS THAN (19950631) ENGINE = InnoDB,
 PARTITION p1995_07 VALUES LESS THAN (19950731) ENGINE = InnoDB,
 PARTITION p1995_08 VALUES LESS THAN (19950831) ENGINE = InnoDB,
 PARTITION p1995_09 VALUES LESS THAN (19950931) ENGINE = InnoDB,
 PARTITION p1995_10 VALUES LESS THAN (19951031) ENGINE = InnoDB,
 PARTITION p1995_11 VALUES LESS THAN (19951131) ENGINE = InnoDB,
 PARTITION p1995_12 VALUES LESS THAN (19951231) ENGINE = InnoDB,
 PARTITION p1996_01 VALUES LESS THAN (19960131) ENGINE = InnoDB,
 PARTITION p1996_02 VALUES LESS THAN (19960231) ENGINE = InnoDB,
 PARTITION p1996_03 VALUES LESS THAN (19960331) ENGINE = InnoDB,
 PARTITION p1996_04 VALUES LESS THAN (19960431) ENGINE = InnoDB,
 PARTITION p1996_05 VALUES LESS THAN (19960531) ENGINE = InnoDB,
 PARTITION p1996_06 VALUES LESS THAN (19960631) ENGINE = InnoDB,
 PARTITION p1996_07 VALUES LESS THAN (19960731) ENGINE = InnoDB,
 PARTITION p1996_08 VALUES LESS THAN (19960831) ENGINE = InnoDB,
 PARTITION p1996_09 VALUES LESS THAN (19960931) ENGINE = InnoDB,
 PARTITION p1996_10 VALUES LESS THAN (19961031) ENGINE = InnoDB,
 PARTITION p1996_11 VALUES LESS THAN (19961131) ENGINE = InnoDB,
 PARTITION p1996_12 VALUES LESS THAN (19961231) ENGINE = InnoDB,
 PARTITION p1997_01 VALUES LESS THAN (19970131) ENGINE = InnoDB,
 PARTITION p1997_02 VALUES LESS THAN (19970231) ENGINE = InnoDB,
 PARTITION p1997_03 VALUES LESS THAN (19970331) ENGINE = InnoDB,
 PARTITION p1997_04 VALUES LESS THAN (19970431) ENGINE = InnoDB,
 PARTITION p1997_05 VALUES LESS THAN (19970531) ENGINE = InnoDB,
 PARTITION p1997_06 VALUES LESS THAN (19970631) ENGINE = InnoDB,
 PARTITION p1997_07 VALUES LESS THAN (19970731) ENGINE = InnoDB,
 PARTITION p1997_08 VALUES LESS THAN (19970831) ENGINE = InnoDB,
 PARTITION p1997_09 VALUES LESS THAN (19970931) ENGINE = InnoDB,
 PARTITION p1997_10 VALUES LESS THAN (19971031) ENGINE = InnoDB,
 PARTITION p1997_11 VALUES LESS THAN (19971131) ENGINE = InnoDB,
 PARTITION p1997_12 VALUES LESS THAN (19971231) ENGINE = InnoDB,
 PARTITION p1998_01 VALUES LESS THAN (19980131) ENGINE = InnoDB,
 PARTITION p1998_02 VALUES LESS THAN (19980231) ENGINE = InnoDB,
 PARTITION p1998_03 VALUES LESS THAN (19980331) ENGINE = InnoDB,
 PARTITION p1998_04 VALUES LESS THAN (19980431) ENGINE = InnoDB,
 PARTITION p1998_05 VALUES LESS THAN (19980531) ENGINE = InnoDB,
 PARTITION p1998_06 VALUES LESS THAN (19980631) ENGINE = InnoDB,
 PARTITION p1998_07 VALUES LESS THAN (19980731) ENGINE = InnoDB,
 PARTITION p1998_08 VALUES LESS THAN (19980831) ENGINE = InnoDB,
 PARTITION p1998_09 VALUES LESS THAN (19980931) ENGINE = InnoDB,
 PARTITION p1998_10 VALUES LESS THAN (19981031) ENGINE = InnoDB,
 PARTITION p1998_11 VALUES LESS THAN (19981131) ENGINE = InnoDB,
 PARTITION p1998_12 VALUES LESS THAN (19981231) ENGINE = InnoDB) */

Suggested fix:
Don't use 3x the memory in the system.
[27 May 2013 10:32] Shane Bester
So we now have a situation where this is possible to consume inordinate amounts of memory with default settings (assuming sufficient file descriptors).

-----
drop table if exists t1;
create table t1(a int,b int,key(a))engine=innodb 
partition by hash(a) partitions 8192;
alter table t1 add key(b);
-----
[27 May 2013 14:52] Shane Bester
Documentation here: http://dev.mysql.com/doc/refman/5.6/en/innodb-
parameters.html#sysvar_innodb_sort_buffer_size

We need to document the memory requirement for DDL statements, keeping in mind partitions, and multiple DDL operations in a single SQL.

Then, we could attempt to reduce the overall memory footprint. Alloc/dealloc after each partition is processed?

--------------------------------------------------------------------------------
  n        time(i)         total(B)   useful-heap(B) extra-heap(B)    stacks(B)
--------------------------------------------------------------------------------
317 31,220,578,990   21,219,106,816   21,219,106,816             0            0
100.00% (21,219,106,816B) (page allocation syscalls) mmap/mremap/brk, --alloc-fns, etc.
->99.75% (21,166,043,136B) 0x36798EDFB9: mmap (syscall-template.S:82)
| ->95.04% (20,166,115,328B) 0x934380: os_mem_alloc_large(unsigned long*) (os0proc.cc:158)
| | ->94.38% (20,025,606,144B) 0x974DCA: row_log_allocate(dict_index_t*, dict_table_t*, bool, dtuple_t const*, unsigned long const*) (row0log.cc:2618)
| | | ->94.38% (20,025,606,144B) 0x6170F9: prepare_inplace_alter_table_dict(Alter_inplace_info*, TABLE const*, TABLE const*, char const*, unsigned long, unsigned long, unsigned long, bool, bool) (handler0alter.cc:2952)
| | |   ->94.38% (20,025,606,144B) 0x905112: ha_innobase::prepare_inplace_alter_table(TABLE*, Alter_inplace_info*) (handler0alter.cc:4053)
| | |     ->94.38% (20,025,606,144B) 0xAB5700: ha_partition::prepare_inplace_alter_table(TABLE*, Alter_inplace_info*) (ha_partition.cc:7987)
| | |       ->94.38% (20,025,606,144B) 0x60EFB0: mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) [clone .isra.91] (sql_table.cc:6515)
| | |         ->94.38% (20,025,606,144B) 0x7BEE66: mysql_alter_table(THD*, char*, char*, st_ha_create_information*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool) (sql_table.cc:8271)
| | |           ->94.38% (20,025,606,144B) 0x857BF3: Sql_cmd_alter_table::execute(THD*) (sql_alter.cc:316)
| | |             ->94.38% (20,025,606,144B) 0x7693C7: mysql_execute_command(THD*) (sql_parse.cc:4642)
| | |               ->94.38% (20,025,606,144B) 0x76B056: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5213)
| | |                 ->94.38% (20,025,606,144B) 0x76BC7F: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1324)
| | |                   ->94.38% (20,025,606,144B) 0x737DCD: do_handle_one_connection(THD*) (sql_connect.cc:983)
| | |                     ->94.38% (20,025,606,144B) 0x737E4E: handle_one_connection (sql_connect.cc:899)
| | |                       ->94.38% (20,025,606,144B) 0xA3FD34: pfs_spawn_thread (pfs.cc:1924)
| | |                         ->94.38% (20,025,606,144B) 0x3679C07D12: start_thread (pthread_create.c:309)
| | |                           ->94.38% (20,025,606,144B) 0x36798F168B: clone (clone.S:115)
[28 May 2013 16:39] Mark Callaghan
Can the docs get updated to explain this case? Is this only an issue for fast-index-create?
[19 Dec 2013 15:25] Daniel Price
Noted in 5.6.16, 5.7.4 changelog:

An online "ALTER TABLE" operation would consume more memory than
expected. During an online "ALTER TABLE" operation, an online log buffer
containing a head and tail buffer is created for each index that is
created or rebuilt. The tail buffer is the writer context and is only
required for concurrent write operations on an index while the "ALTER
TABLE" operation is in progress. The head buffer is the reader context and
is only required during the log apply phase. To reduce memory consumption,
the tail buffer is now allocated when the first DML statement is run on
the index, and the head buffer is only allocated in the log apply phase
and freed afterwards.
[3 Feb 2014 11:48] Laurynas Biveinis
5.6$ bzr log -r 5702
------------------------------------------------------------
revno: 5702
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: r-5.6
timestamp: Thu 2013-12-19 11:44:13 +0530
message:
  Bug #16868967	MYSQL USES SIGNIFICANTLY MORE MEMORY
  		FOR ALTER TABLE THAN EXPECTED
  
  Problem:
    During online alter table, an online log buffer would be created for
  index that is being rebuilt or created. This online log contains two
  buffers - head and tail. Alter table takes lot of memory than
  expected even there is no concurrent write for index. But Both head
  and tail buffer will be useful when there is a concurrent 
  write on the index. If there are many partitions involved, it is
  unlikely that all the partitions would be modified concurrently. 
   
  Solution:
    Allocate the tail buffer only when first DML statement happens on the
  index. Allocate the head buffer only in apply phase and freed at the
  end of the apply phase.
  
  	Approved by Marko rb#3850
[4 Jan 2016 13:01] Daniel Price
Posted by developer:
 
The formula for estimating memory consumption has been corrected in the documentation.
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_sort_buffer_si...