MySQL Bugs: #48846: Too much time spent in ha_partition::records_in

Bug #48846	Too much time spent in ha_partition::records_in_range if not able to prune
Submitted:	17 Nov 2009 18:51	Modified:	19 Mar 2010 10:51
Reporter:	Mattias Jonsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any
Tags:	pruning

Description:
When analysing bug#37252 and bug#47261 I found that too much time was spent in ha_partition::records_in_range since it calls records_in_range for all partitions (that are left after pruning).

The handler call records_in_range should return an estimation of records that a specific range for the given index would return. So it should be sufficient to do the estimation for the partitioning engine by only use a smaller subset of partitions for this instead of all (not pruned) partitions.

records_in_range are especially costly for MyISAM tables/partitions. My tests show that it is possible to get an 10X increase for small select queries on 1024 partitions. (For InnoDB the increase is about 2X for 1024 partitions, which is very good too).

How to repeat:
See bug#37252. Or use the included script (based on that bug). And of course compare to the attached patch.

Suggested fix:
See attached patch.

perl test script (uses mysql client).

Attachment: bug37252.no_dbi.pl (text/plain), 10.34 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/90744

3199 Mattias Jonsson	2009-11-17
      Bug#48846: Too much time spent in ha_partition::records_in_range if not able to prune
      
      Problem was that ha_partition::records_in_range called
      records_in_range for all non pruned partitions, even if
      an estimate should be given.
      
      Solution is to only use 1/3 of the partitions (up to 10) for
      records_in_range and estimate the total from this subset.
      (And continue until a non zero return value from the called
      partitions records_in_range is given, since 0 means no rows
      will match.)
     @ sql/ha_partition.cc
        Bug#48846: Too much time spent in ha_partition::records_in_range if not able to prune
        
        estimate_rows_upper_bound and records_in_range are very similar
        (the only difference is the function and its parameters to use)
        so I created a common function for this.
        
        Since these calls from the optimizer are only estimates, it is
        not neccesary to call them for every partition, it can use
        a much smaller subset of the used partitions instead,
        which improves performance for selects.
     @ sql/ha_partition.h
        Bug#48846: Too much time spent in ha_partition::records_in_range if not able to prune
        
        Added two private functions to help some
        optimizer calls.

spreadsheet showing the performance figures

Attachment: bug48846_report.ods (application/vnd.oasis.opendocument.spreadsheet, text), 134.27 KiB.

To start with do 'ulimit -n 10000' (or at least > 2050) in the session where you start the server.
The following flags was added to partition_innodb-master.opt:
--innodb_flush_method=O_DIRECT --innodb_log_file_size=256M --innodb_flush_log_at_trx_commit=0 --innodb_buffer_pool_size=2G

and then I started the server by 'cd mysql-test ; ./mtr --mem --start partition_innodb'

(--innodb_file_per_table did take too much memory on my 4GB machine, but I could not see any big difference in performance of that flag, and the rest of the flags can probably be tuned further too...)

To the reviewers:
Note that the patch only uses the partition order to get the estimates, so the estimate will probably not use the last partition(s) which may be more relevant for range partitioning, and it will not search for the partition with the most rows and use. But since it is only an estimate I focused on speed and simplicity.

Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:satya.bn@sun.com-20091223070903-6rn5kgl3chedqnxd) (merge vers: 5.1.42) (pib:16)

Documented fix in the 5.1.43 changelog as follows:

        When used on partitioned tables, the records_in_range handler 
        call checked all partitions, rather than the unpruned partitions 
        only.

Set status = NDI, waiting for merges to later trees.

Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20100119163614-172adculixyu26j5) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100205113942-oqovjy0eoqbarn7i) (version source revid:alik@sun.com-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.2-m2 (revid:alik@sun.com-20100203172258-1n5dsotny40yufxw) (version source revid:alexey.kopytov@sun.com-20091225105650-qletdbs0wz9sx5nc) (merge vers: 5.5.1-m2) (pib:16)

Also documented in the 5.5.2, 5.6.0, and 6.0.14 changelogs.

Closed.

5.6.0 changelog entry unneeded.

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

No additional changelog entries required. Returning to Closed state.

Docs, please change the release note text from:

When used on partitioned tables, the records_in_range handler call checked all partitions, rather than the unpruned partitions only.

To:

Improved partition range checking made some partition operations as much as two to ten times as fast in 1024 partition tests by reducing the number of unpruned partitions checked for statistics.

The purpose of the change is to give some idea of significance and to correct what looked like an error - seems to be reducing number of unpruned partitions checked, not simply changing from checking all to only checking unpruned.

Please also add the performance tag for this change, I've added that request to https://inside.mysql.com/wiki/Performance_improvements_docs also.

Updated changelog entry per James' comment and IRC discussion, now reads as follows:

        When used on partitioned tables, the records_in_range 
        handler call checked more partitions than necessary. The 
        fix for this issue reduces the number of unpruned partitions 
        checked for statistics in partition range checking, which has 
        resulted in some partition operations being performed up to 
        2-10 times faster than before this change was made, when 
        testing with tables having 1024 partitions.

Also added Performance tag as requested.

Docs commit is here: http://lists.mysql.com/commits/103805

Closed.