MySQL Bugs: #47261: 5.1 fix for Partitioning performance drop...with hundreds of partitions (37252)

Bug #47261	5.1 fix for Partitioning performance drop...with hundreds of partitions (37252)
Submitted:	10 Sep 2009 21:04	Modified:	8 Feb 2010 17:35
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
This bug is to track the 'partial solution suggested for bug#37252

See bug#37252 for details

How to repeat:
See bug#37252

Suggested fix:
See bug#37252

I will make a patch that changes the thr_multi_lock to take into account that a partitioned table already have the locks for partitions sorted by partition_id, so it is safe to sort them as a block only comparing the first partition. I will also change the sort_lock function to use my_qsort instead of the current O(n2) function. This will lead to much better performance for sorting the locks for tables with many partitions.

Fix will be done as part of bug#37252

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/86694

3160 Mattias Jonsson	2009-10-13
      Bug#47261: 5.1 fix for Partitioning performance drop...
      with hundreds of partitions
      
      One problem that is solvable in 5.1 is that the sorting of locks
      is not partitioning aware, every partition have its on locks,
      and they are already sorted in partition order, but sort_locks
      sorted them as independent locks.
      
      Solution was to group locks togather as already sorted locks, and
      only sort those group of locks.
      
      And as a part of this change I also changed the sorting algorithm
      from an O(n2) to O(nlog(n)), by using my_qsort instead. The order
      remains the same.
     @ include/thr_lock.h
        Bug#47261: 5.1 fix for Partitioning performance drop...
        with hundreds of partitions
        
        Added flag for grouping locks for use with partitioning,
        since all locks in a partitioned table is stored in
        the same order.
     @ mysys/thr_lock.c
        Bug#47261: 5.1 fix for Partitioning performance drop...
        with hundreds of partitions
        
        Changed the sorting of locks into two parts, first pick out
        the locks that need to be sorted (only using the first lock in
        groups already sorted, like locks for partitioned tables).
        And then sorting only these locks, then copying the locks in the
        correct order.
        
        To ease the ordering, I changed the inplace ordering in thr_multi_lock
        to only read the first count members, and then write the ordered locks
        into the second count members (since that was already handled that way
        in sql/lock.cc).
     @ sql/ha_partition.cc
        Bug#47261: 5.1 fix for Partitioning performance drop...
        with hundreds of partitions
        
        Added grouping of locks to be able to increase the
        performance of sorting locks for heavily partitioned
        tables, by using the fact that the locks within a
        partitioned table is already stored in partition
        order.
        
        Note the DBUG_ASSERT(prev == (to - 1)), since if an engine stores
        several locks we cannot ensure the correct order.
     @ sql/lock.cc
        Bug#47261: 5.1 fix for Partitioning performance drop...
        with hundreds of partitions
        
        Since the lock array was already doubled, I moved the
        copying of the sorted array to thr_multi_lock instead,
        to ease the implementation.
        
        (Fixed a typo in a comment too...)

I have a patch for this, so I changed the status to patch pending.

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/89709

3161 Mattias Jonsson	2009-11-08
      bug#47261 bad performance in 5.1 for heavily partitioned tables
      
      Further optimizations on optimizer statistics in
      the partitioning handler
     @ mysys/thr_lock.c
        bug#47261 bad performancd in 5.1 for heavily partitioned tables
        
        fix in comment
     @ sql/ha_partition.cc
        work in progress
        
        optimization of optimizer statistic calls

Modified test script (no dbi, uses stored procedure to minimize traffic) and results with two different patches

Attachment: mattiasj_bench.tgz (application/x-gzip, text), 274.69 KiB.

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/90205

3161 Mattias Jonsson	2009-11-12
      bug#47261 bad performance in 5.1 for heavily partitioned tables
      
      The optimizer calls on a partitioned table was called
      for each and every partition. This patch makes the call
      only to a subset of the partitions instead, which improve
      the performance for tables with many partitions.
     @ sql/ha_partition.cc
        Bug#47261: Bad performance in 5.1 for heavily partitioned tables
        
        Only use up to 1/3 of the partitions (or max 10) for estimating
        the number of rows in range, scan time and rows upper bound.
        
        NOTE: If no rows or time is found, it will continue untill it finds any rows, since zero rows really means it does not exists
        any matching rows.
     @ sql/ha_partition.h
        Bug#47261: Bad performance in 5.1 for heavily partitioned tables
        
        Added helper function declaration to avoid code duplication.

Setting back to verified since I opened a new bug (bug#48846) for the records_in_range performance patch.

Fix variant for 5.1 only will not be implemented as previously planed.
General fix to the problem is tracked in bug#37252