| 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: | |
| 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 10:32]
MySQL Verification Team
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]
MySQL Verification Team
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...
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.