[MDEV-8702] Unexpected Memory Consumption for Bulk Index Creation in InnoDB. Created: 2015-08-30  Updated: 2023-04-12  Resolved: 2023-04-11

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.0.21
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Jean-François Gagné Assignee: Jan Lindström (Inactive)
Resolution: Won't Fix Votes: 1
Labels: upstream-wontfix
Environment:

CentOS release 6.6


 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

 Comments   
Comment by Jean-François Gagné [ 2015-08-30 ]

Corresponding MySQL bug: http://bugs.mysql.com/bug.php?id=78270

From the 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.

Comment by Jean-François Gagné [ 2015-08-30 ]

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

Comment by Elena Stepanova [ 2015-08-31 ]

Strangely, even though in a comment to the blogpost Marko Mäkelä said that the problem was partially fixed in 5.7 (and a part of that part was fixed in an early version of 5.7), I still observe it on 5.7.8. Of course, maybe it consumes somewhat less memory or does it slower, but it's not enough to preserve my instance from OOM.

I don't know if it's any helpful, but here is some info from 5.7 (it has some new memory-related capabilities). The same ALTER was performed on the same table, only with ~2.5M rows instead of 5M (to avoid OOM).

Before ALTER TABLE

 
MySQL [performance_schema]> select event_name, count_alloc, sum_number_of_bytes_alloc, current_number_of_bytes_used, high_number_of_bytes_used from performance_schema.memory_summary_global_by_event_name where count_alloc > 0 and event_name not like '%performance_schema%' order by SUM_NUMBER_OF_BYTES_ALLOC ;
 
+---------------------------------------+-------------+---------------------------+------------------------------+---------------------------+
 
| event_name                            | count_alloc | sum_number_of_bytes_alloc | current_number_of_bytes_used | high_number_of_bytes_used |
 
+---------------------------------------+-------------+---------------------------+------------------------------+---------------------------+
 
| memory/mysys/lf_slist                 |           1 |                        32 |                           32 |                        32 |
 
| memory/innodb/fil0fil                 |           1 |                        32 |                            0 |                        32 |
 
| memory/sql/MYSQL_LOCK                 |           2 |                        96 |                           48 |                        48 |
 
| memory/sql/TABLE::sort_io_cache       |           1 |                       280 |                          280 |                       280 |
 
| memory/mysys/lf_node                  |           5 |                       784 |                          784 |                       784 |
 
| memory/sql/TABLE_SHARE::mem_root      |           5 |                      6080 |                         6080 |                      6080 |
 
| memory/sql/TABLE                      |           6 |                      6388 |                         6388 |                      6388 |
 
| memory/sql/thd::main_mem_root         |           2 |                     16320 |                        16320 |                     16320 |
 
| memory/mysys/IO_CACHE                 |           3 |                     65544 |                        65544 |                     65544 |
 
| memory/sql/Filesort_buffer::sort_keys |           1 |                    262040 |                       262040 |                    262040 |
 
+---------------------------------------+-------------+---------------------------+------------------------------+---------------------------+

After ALTER

 
MySQL [performance_schema]> select event_name, count_alloc, sum_number_of_bytes_alloc, current_number_of_bytes_used, high_number_of_bytes_used from performance_schema.memory_summary_global_by_event_name where count_alloc > 0 and event_name not like '%performance_schema%' order by SUM_NUMBER_OF_BYTES_ALLOC ;
 
+------------------------------------------+-------------+---------------------------+------------------------------+---------------------------+
 
| event_name                               | count_alloc | sum_number_of_bytes_alloc | current_number_of_bytes_used | high_number_of_bytes_used |
 
+------------------------------------------+-------------+---------------------------+------------------------------+---------------------------+
 
| memory/sql/frm::form_pos                 |           3 |                        21 |                            0 |                         7 |
 
| memory/mysys/lf_slist                    |           2 |                        64 |                           64 |                        64 |
 
| memory/innodb/buf0flu                    |           2 |                        96 |                            0 |                        96 |
 
| memory/mysys/my_file_info                |           5 |                       102 |                            0 |                        31 |
 
| memory/innodb/handler0alter              |           1 |                       104 |                            0 |                       104 |
 
| memory/sql/frm::extra_segment_buff       |           3 |                       186 |                            0 |                        62 |
 
| memory/sql/MYSQL_LOCK                    |           5 |                       208 |                           48 |                       112 |
 
| memory/innodb/read0read                  |           1 |                       280 |                          280 |                       280 |
 
| memory/innodb/ha_innodb                  |           1 |                       288 |                          288 |                       288 |
 
| memory/sql/TABLE::sort_io_cache          |           2 |                       560 |                          280 |                       280 |
 
| memory/mysys/lf_node                     |           7 |                       864 |                          864 |                       864 |
 
| memory/innodb/sync0types                 |          32 |                      1152 |                            0 |                      1152 |
 
| memory/innodb/dict_stats_n_diff_on_level |          32 |                      1280 |                            0 |                        40 |
 
| memory/sql/frm::string                   |           5 |                      3640 |                            0 |                      1097 |
 
| memory/innodb/eval0eval                  |         171 |                      4618 |                            0 |                       380 |
 
| memory/innodb/rem0rec                    |         129 |                      5285 |                            0 |                        69 |
 
| memory/innodb/dict0stats                 |          64 |                      6656 |                            0 |                       208 |
 
| memory/innodb/dict0dict                  |          32 |                     10496 |                            0 |                       328 |
 
| memory/innodb/os0event                   |          96 |                     13056 |                         8704 |                     13056 |
 
| memory/sql/frm                           |           4 |                     13637 |                            0 |                     13593 |
 
| memory/innodb/trx0undo                   |          47 |                     16544 |                        16544 |                     16544 |
 
| memory/sql/TABLE                         |          24 |                     40593 |                        23041 |                     23940 |
 
| memory/sql/TABLE_SHARE::mem_root         |          20 |                     40824 |                        24472 |                     24472 |
 
| memory/sql/thd::main_mem_root            |           8 |                     65280 |                        16320 |                     48960 |
 
| memory/innodb/dict_stats_index_map_t     |        1056 |                     76032 |                            0 |                      2376 |
 
| memory/mysys/IO_CACHE                    |           6 |                    131088 |                        65544 |                     65544 |
 
| memory/sql/Filesort_buffer::sort_keys    |           2 |                    524080 |                       262040 |                    262040 |
 
| memory/innodb/std                        |         976 |                   1084720 |                            0 |                     25784 |
 
| memory/innodb/row0log                    |          64 |                   4204800 |                            0 |                   4202816 |
 
| memory/innodb/btr0bulk                   |       43520 |                   7307776 |                            0 |                       896 |
 
| memory/innodb/btr0sea                    |        3797 |                  16869464 |                            0 |                      5568 |
 
| memory/innodb/row_merge_sort             |           1 |                 201326592 |                            0 |                 201326592 |
 
| memory/innodb/fil0fil                    |        2666 |                 210949224 |                            0 |                   1065256 |
 
| memory/innodb/row0merge                  |          35 |                3839907064 |                            0 |                3839907064 |
 
| memory/innodb/mem0mem                    |     1086433 |                8088205272 |                       326864 |                3832199240 |
 
+------------------------------------------+-------------+---------------------------+------------------------------+---------------------------+

(Truncate does not work on this, so I provide 'before' and 'after' to see what relates to the ALTER).

Comment by Jean-François Gagné [ 2015-08-31 ]

Hi Elena,

I think Marko's comment was about the following bug: https://bugs.mysql.com/bug.php?id=78262
This corresponds to this MariaDB bug: https://mariadb.atlassian.net/browse/MDEV-8696

JFG

Comment by Jan Lindström [ 2023-04-11 ]

10.1 is EOL.

Generated at Thu Feb 08 07:29:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.