MySQL Bugs: #35845: unneccesary call to ha_start_bulk

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

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.

Verified as described.

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

The parts.rpl_partition test can be used to verify that this speeds up row-based replication to a partitioned table.

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

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

pushed into mysql-6.0-bugteam

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)

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.

Tagged changelog entry "See also Bug #35843". Closed per comment from Joro.

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.)

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

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)

Pushed to mysql-5.1-bugteam and null merged into mysql-pe (since already there).

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)

Fix also documented in the 5.4.4 changelog.

Set status to NDI pending merge to 5.1 tree.

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)

Fix also documented in the 5.1.40 changelog.

Closed.

The 5.4 fix has been pushed into 5.4.3.

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, ....

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.

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 new changelog entries required. Closing.