MySQL Bugs: #105490: Empty loop brings loss to performance when pruning partitions for INSERT command

Bug #105490	Empty loop brings loss to performance when pruning partitions for INSERT command
Submitted:	8 Nov 2021 11:28	Modified:	8 Nov 2021 11:56
Reporter:	Hope Lee (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	8.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	contributions

Description:
There could exist empty loops when pruning partitions for INSERT command in Sql_cmd_insert_base::prepare_inner(), which brings loss to performance.

How to repeat:
Look at the pieces of codes in around line 1475 in sql/sql_insert.cc:

      while (its != insert_many_values.end()) {
        const mem_root_deque<Item *> *values = *its++;
        counter++;

        /*
          We check pruning for each row until we will
          use all partitions, Even if the number of rows is much higher than the
          number of partitions.
          TODO: Cache the calculated part_id and reuse in
          ha_partition::write_row() if possible.
        */
        if (can_prune_partitions == partition_info::PRUNE_YES) {
          if (insert_table->part_info->set_used_partition(
                  thd, insert_field_list, *values, info,
                  prune_needs_default_values, &used_partitions)) {
            can_prune_partitions = partition_info::PRUNE_NO;
            // set_used_partition may fail.
            if (thd->is_error()) return true;
          }
          if (!(counter % num_partitions)) {
            /*
              Check if we using all partitions in table after adding partition
              for current row to the set of used partitions. Do it only from
              time to time to avoid overhead from bitmap_is_set_all() call.
            */
            if (bitmap_is_set_all(&used_partitions))
              can_prune_partitions = partition_info::PRUNE_NO;
          }
        }
      }

After `can_prune_partitions` has already set `partition_info::PRUNE_NO`, the loop still goes through all the insert values and these are empty loops.

Bugfix Empty loop when pruning partitions for INSERT command

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-Bugfix-Empty-loop-when-pruning-partitions-for-INSERT.patch (application/octet-stream, text), 2.59 KiB.

Hello Lee,

Thank you for the report and contributions.

regards,
Umesh

Thank you for your contribution that was accepted and reviewed by the development team. 

This fix will be part of a future release.

Regards,

This contribution has been processed by our development team and has been accepted.

It will be included into a future release.

Thank you again.