Bug #35845 unneccesary call to ha_start_bulk_insert for not used partitions
Submitted: 4 Apr 2008 20:08 Modified: 19 Dec 2009 8:46
Reporter: Mattias Jonsson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S5 (Performance)
Version:5.1.23 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any

[4 Apr 2008 20:08] Mattias Jonsson
Description:
This is related to bug#35843, slow slave when row based replication to a partitioned myisam table.

Partitioning could be using less resources and be faster if the partitioning handler could postpone the call to every partitions ha_start_bulk_insert until it actually will be used. (and only call ha_end_bulk_insert for those partitions that was started).

How to repeat:
See bug#35843, or do a insert select from an innodb into a heavily partitioned myisam table where the data only goes to a small fraction of the partitions, and compare between 5.1.22 and 5.1.23

Suggested fix:
Partitioning should try to postpone the ha_start_bulk_insert for the partitions to before it will actually insert any rows into the partition (Do not know if it is possible, since the start_bulk_insert may have locking restrictions, and we cannot ensure that the write_row will come in partitioning order.) And of course only do ha_end_bulk_insert for those partitions we called ha_start_bulk_insert for.
[14 Apr 2008 16:47] Susanne Ebrecht
Verified as described.
[5 Sep 2008 11:21] Bugs System
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/53321

2697 Mattias Jonsson	2008-09-05
      Bug#35845: unneccesary call to ha_start_bulk_insert for not used partitions
      
      Problem is that when insert (ha_start_bulk_insert) in i partitioned table,
      it will call ha_start_bulk_insert for every partition, used or not.
      
      Solution is to delay the call to the partitions ha_start_bulk_insert until
      the first row is to be inserted into that partition
[5 Sep 2008 11:21] Mattias Jonsson
The parts.rpl_partition test can be used to verify that this speeds up row-based replication to a partitioned table.
[16 Oct 2008 8:49] Mikael Ronström
Rewrite the guess_bulk_insert_rows in the following manner:
1) Create a new variable on part_info monotonic_function.
This variable is true if the partitioning function is monotonic
increasing or decreasing
2) For inserts into tables with monotonic function use 50% for
the first partition
3) All other cases should use
(total_rows_predicted - inserted_rows_so_far) / tot_parts_in_table
4) total < inserted then set to 0
[17 Oct 2008 18:30] Bugs System
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/56491

2697 Mattias Jonsson	2008-10-17
      Bug#35845: unneccesary call to ha_start_bulk_insert for not used partitions
      
      Problem is that when insert (ha_start_bulk_insert) in i partitioned table,
      it will call ha_start_bulk_insert for every partition, used or not.
      
      Solution is to delay the call to the partitions ha_start_bulk_insert until
      the first row is to be inserted into that partition
[8 Dec 2008 15:20] Mattias Jonsson
pushed into mysql-6.0-bugteam
[20 Jan 2009 18:54] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)
[20 Jan 2009 21:28] Jon Stephens
Documented in the 6.0.10 changelog as follows:

        Unnecessary calls were made in the server code for performing
        bulk inserts on partitions for which no inserts needed to be
        made.

I've set this to NDI because it appears there should be a push to 5.1 trees as well. Please advise if this is not the case.
[29 Jan 2009 8:22] Jon Stephens
Tagged changelog entry "See also Bug #35843". Closed per comment from Joro.
[4 Sep 2009 12:53] Mattias Jonsson
Creating a backported patch to 5.1.

Will increase performance on all inserts and lower the resource usage for cases where inserts only affects a limited set of all partitions.

(Better performance/resource usage because it will not call ha_start_bulk_insert for partitions that does not receive any rows, and will not initialize its bulk_insert buffers etc.)
[4 Sep 2009 13:02] Bugs System
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/82459
[11 Sep 2009 23:08] Bugs System
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/83090

3601 Mattias Jonsson	2009-09-12 [merge]
      Null merge of bug#35845 (backport to 5.1)
[14 Sep 2009 13:50] Mattias Jonsson
Pushed to mysql-5.1-bugteam and null merged into mysql-pe (since already there).
[14 Sep 2009 16:03] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[15 Sep 2009 9:48] Jon Stephens
Fix also documented in the 5.4.4 changelog.

Set status to NDI pending merge to 5.1 tree.
[6 Oct 2009 8:59] Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20090916070128-6053el2ucp5z7pyn) (merge vers: 5.1.39) (pib:11)
[6 Oct 2009 9:19] Jon Stephens
Fix also documented in the 5.1.40 changelog.

Closed.
[7 Oct 2009 1:42] Paul DuBois
The 5.4 fix has been pushed into 5.4.3.
[23 Oct 2009 18:33] David Nguyen
I just tested 5.1.40 and still see MySQL accessing all partitions during an "INSERT ON DUPLICATE KEY" operation when I'm just inserting into 1 partition.  As a result, updating 9771 rows takes 34 seconds.

mysql> source 1_insert.sql
Query OK, 9771 rows affected, 1088 warnings (0.36 sec)
Records: 9771  Duplicates: 0  Warnings: 0

mysql> source 1_insert.sql
Query OK, 19542 rows affected, 1088 warnings (34.21 sec)
Records: 9771  Duplicates: 9771  Warnings: 0

mysql>  select count(*) from table1 where part_num between 20090910 and 20090911;
+----------+
| count(*) |
+----------+
|     9771 |
+----------+
1 row in set (0.01 sec)

mysql>  select count(*) from table1;
+----------+
| count(*) |
+----------+
|     9771 |
+----------+
1 row in set (0.01 sec)

Innodb status shows all 872 partitions are being accessed:
---TRANSACTION 3A8D, ACTIVE 1 sec, process no 1161, OS thread id 1195747664, thread declared inside InnoDB 95
mysql tables in use 872, locked 872
128 lock struct(s), heap size 14320, 470 row lock(s), undo log entries 469
MySQL thread id 2, query id 41 localhost root update
insert into test.table1 (linked_list_request, traffic, throttled_traffic, ....
[23 Oct 2009 21:46] Mattias Jonsson
This bug is not about limiting the used (i.e. not pruned, not locked) partitions used. It is only about delaying the internal bulk_insert call until one actually inserts into a partition.

bulk_insert is a way to prepare the engine for a given number of inserts, so that it can optimize its buffers to operate efficiently on the given number of inserted rows (where 0 stands for unknown). This is mainly used in MyISAM, which changes the way to update the index if inserting more than 10 rows (IIRC it disables the index, and then repairs it and enables it after the inserts are done). Before this fix, it did that with every partition even if they never inserted any rows.

For better performance and limiting the used (both locked and pruned/opened/searched) partitions, please see bug#37252.
[18 Dec 2009 10:28] Bugs System
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)
[18 Dec 2009 10:44] Bugs System
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)
[18 Dec 2009 10:59] Bugs System
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)
[18 Dec 2009 11:14] Bugs System
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)
[19 Dec 2009 8:46] Jon Stephens
No new changelog entries required. Closing.