MySQL Bugs: #45840: read_buffer_size allocated for each partition when "insert into.. select * from"

Bug #45840	read_buffer_size allocated for each partition when "insert into.. select * from"
Submitted:	30 Jun 2009 6:18	Modified:	19 Dec 2009 11:05
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.30, 5.1.37	OS:	Any
Assigned to:	Anurag Shekhar	CPU Architecture:	Any
Tags:	read_buffer_size

Description:
When inserting into a partitioned table using 'insert into <target> select * from <src>',  read_buffer_size bytes of memory are allocated for each partition in the target table.

If you have a small read_buffer_size (say 1M), but huge number of partitions,
memory usage will be too high too.   Even worse if you have a large read_buffer_size.

The testcase will use (20 * read_buffer_size) memory up front.
The read_buffer_size must be specified during server startup,
not via "set session/set global"

--read_buffer_size=100M will cause this testcase to eat 2G memory
even though all tables are empty!

How to repeat:
drop table if exists `t1`;
create table `t1`(`a` int primary key)engine=myisam 
partition by key() partitions 20;
insert into `t1` select * from `t1`

Suggested fix:
either document this or consider not using so much memory for these queries.
Many folks have 300+ partitions per table, and using 300MB+ per insert...select is unreasonable.

Workaround is to use InnoDB tables instead of MyISAM.

Timothy: yes, this ate 1.4G of memory when server started with --read_buffer_size=100M.   Note for hundreds of partitions a normal read_buffer_size will be problematic too.

drop table if exists `t1`,`t2`;
create table `t1`(`a` int primary key)engine=myisam 
partition by key() partitions 20;
create table `t2`(`a` int primary key)engine=myisam;
insert into `t1` select * from `t2`

The effect of this bug is a spike in memory, and unrealistic performance:

mysql> insert into `t1` select * from `t2`;
Query OK, 0 rows affected (1 min 14.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

> 1 minute to insert zero rows !!!

mysql> insert into t1 select 1 from dual;
Query OK, 1 row affected (1 min 17.56 sec)
Records: 1  Duplicates: 0  Warnings: 0

> 1 minute to insert one row !!!

Seem like it would be fixed by backporting bug#35845 and some additional code to limit the maximum of used bulk_insert memory on insert on partitioned myisam tables.

What would be a reasonable total limit for the buffers?
num_of_parts * read_buffer_size?
N * read_buffer_size? (N = 10, 100 ???)
read_buffer_size? (which could result in very poor performance for heavily partitioned tables).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82888

3111 Anurag Shekhar	2009-09-10
      Bug #45840 read_buffer_size allocated for each partition when 
                   "insert into.. select * from"
        
      When inserting into a partitioned table using 'insert into <target> 
      select * from <src>', read_buffer_size bytes of memory are allocated 
      for each partition in the target table.
      
      This resulted in large memory consumption when the number of partitions 
      are high.
      
      This patch introduces a new method which tries to estimate the buffer 
      size required for each partition and limits the maximum buffer size 
      used to maximum of 10 * read_buffer_size.
      
      Additionally fix for bug#35845 is also back ported which checks for the 
      partitions actually expected to be effected by the insert resulting in 
      lower uses of resources.
     @ sql/ha_partition.cc
        Back ported patch for bug#35845.
        Introduced a method ha_partition::estimate_read_buffer_size
        to estimate buffer size required for each partition. 
        Method ha_partition::start_part_bulk_insert updated
        to update the read_buffer_size before calling bulk upload
        in storage engines.
     @ sql/ha_partition.h
        Back ported patch for bug#35845.
        Introduced a method ha_partition::estimate_read_buffer_size.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83147

3124 Anurag Shekhar	2009-09-14
      Bug #45840 read_buffer_size allocated for each partition when 
         "insert into.. select * from"
      
      When inserting into a partitioned table using 'insert into
      <target> select * from <src>', read_buffer_size bytes of memory are
      allocated for each partition in the target table.
      
      This resulted in large memory consumption when the number of
      partitions are high.
      
      This patch introduces a new method which tries to estimate the
      buffer size required for each partition and limits the maximum buffer
      size used to maximum of 10 * read_buffer_size.
     @ sql/ha_partition.cc
        Introduced a method ha_partition::estimate_read_buffer_size
        to estimate buffer size required for each partition. 
        Method ha_partition::start_part_bulk_insert updated
        to update the read_buffer_size before calling bulk upload
        in storage engines.
     @ sql/ha_partition.h
        Introduced a method ha_partition::estimate_read_buffer_size.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83565

3111 Anurag Shekhar	2009-09-17
      Bug #45840 read_buffer_size allocated for each partition when 
       "insert into.. select * from"
      
      When inserting into a partitioned table using 'insert into
      <target> select * from <src>', read_buffer_size bytes of memory
      are allocated for each partition in the target table.
      
      This resulted in large memory consumption when the number of
      partitions are high.
      
      This patch introduces a new method which tries to estimate the
      buffer size required for each partition and limits the maximum
      buffer size used to maximum of 10 * read_buffer_size, 
      11 * read_buffer_size in case of monotonic partition function.
     @ sql/ha_partition.cc
        Introduced a method ha_partition::estimate_read_buffer_size
        to estimate buffer size required for each partition. 
        Method ha_partition::start_part_bulk_insert updated
        to update the read_buffer_size before calling bulk upload
        in storage engines.
     @ sql/ha_partition.h
        Introduced a method ha_partition::estimate_read_buffer_size.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83593

3111 Anurag Shekhar	2009-09-17
      Bug #45840 read_buffer_size allocated for each partition when 
       "insert into.. select * from"
      
      When inserting into a partitioned table using 'insert into
      <target> select * from <src>', read_buffer_size bytes of memory
      are allocated for each partition in the target table.
      
      This resulted in large memory consumption when the number of
      partitions are high.
      
      This patch introduces a new method which tries to estimate the
      buffer size required for each partition and limits the maximum
      buffer size used to maximum of 10 * read_buffer_size, 
      11 * read_buffer_size in case of monotonic partition functions.
     @ sql/ha_partition.cc
        Introduced a method ha_partition::estimate_read_buffer_size
        to estimate buffer size required for each partition. 
        Method ha_partition::start_part_bulk_insert updated
        to update the read_buffer_size before calling bulk upload
        in storage engines.
        Added thd in ha_partition::start_part_bulk_insert method signature.
     @ sql/ha_partition.h
        Introduced a method ha_partition::estimate_read_buffer_size.
        Added thd in ha_partition::start_part_bulk_insert method signature.

Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:joro@sun.com-20090923082712-i6jc8tuy0uk1eakt) (merge vers: 5.1.40) (pib:11)

Documented bugfix in the 5.1.40 changelog as follows:

        When performing an INSERT ... SELECT into a partitioned table,
        read_buffer_size bytes of memory were allocated for every
        partition in the target table, resulting in consumption of large
        amounts of memory when the table had many partitions (more than 
        100).

        This fix changes the method used to estimate the buffer size
        required for each partition and limits the total buffer size
        to approximately 10 times read_buffer_size.

Set status to NDI, waiting on push to 5.4 trees.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)

Also documented in the 5.5.0 and 6.0.14 changelogs. Closed.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

No additional changelog entries needed. Closed.