Bug #44153 Add information about Ndb scan partition pruning to MySQL server

• Submitted: 8 Apr 2009 14:43
• Modified: 16 Apr 2009 2:25
• Reporter: Frazer Clement
• Status: Closed
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S4 (Feature request)
• Version: 6.3+
• OS: Any
• Assigned to: Frazer Clement
• CPU Architecture: Any

[8 Apr 2009 14:43] Frazer Clement

Description:
Correct operation of Ndb scan partition pruning is important for maximising performance on Ndb Cluster.  

Schema design and partitioning can be used to maximise the ability of the server to prune scans to a single table partition (and therefore involve only a single node), but currently there is no easy way to determine whether the server has successfully pruned a statement.

Additionally, the SQL partitioning layer does not always know whether a statement which can conceptually be pruned is actually pruned at the NDBAPI level.

Some meta information should be added which can be used to determine whether a statement involving scan(s) has been successfully pruned at the NDBAPI level or not.  This information can then be used to guide and validate schema and query designs in the field.

How to repeat:
Execute a SQL statement involving a scan.

Try to determine whether the scan was pruned or not.

Suggested fix:
Add ndb_scan_count and ndb_pruned_scan_count status variables which can be used to monitor pruned and unpruned scans.

[8 Apr 2009 14:44] Frazer Clement

Proposed patch against 6.3

Attachment: pruning_info.patch (text/x-patch), 15.70 KiB.

[8 Apr 2009 17:41] 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/71670

2933 Frazer Clement	2009-04-08
      Bug#44153 : Add information about Ndb scan partition pruning to MySQL server
      added:
        mysql-test/include/ndb_init_scan_counts.inc
        mysql-test/include/ndb_scan_counts.inc
      modified:
        mysql-test/suite/ndb/r/ndb_basic.result
        mysql-test/suite/ndb/r/ndb_partition_hash.result
        mysql-test/suite/ndb/r/ndb_partition_key.result
        mysql-test/suite/ndb/r/ndb_partition_list.result
        mysql-test/suite/ndb/r/ndb_partition_range.result
        mysql-test/suite/ndb/t/ndb_partition_hash.test
        mysql-test/suite/ndb/t/ndb_partition_key.test
        mysql-test/suite/ndb/t/ndb_partition_list.test
        mysql-test/suite/ndb/t/ndb_partition_range.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster.h

[8 Apr 2009 18:14] Bugs System

Pushed into 5.1.32-ndb-7.0.5 (revid:[e-mail]-20090408181037-8hsxlwqqydgl8f72) (version source revid:[e-mail]-20090408181037-8hsxlwqqydgl8f72) (merge vers: 5.1.32-ndb-7.0.5) (pib:6)

[8 Apr 2009 18:15] Bugs System

Pushed into 5.1.32-ndb-6.3.25 (revid:[e-mail]-20090408174118-t104os2n29dy9q0x) (version source revid:[e-mail]-20090408174118-t104os2n29dy9q0x) (merge vers: 5.1.32-ndb-6.3.25) (pib:6)

[16 Apr 2009 2:25] Jon Stephens

Documented in the NDB-6.3.25 and 7.0.5 changelogs as follows:

        Two new server status variables Ndb_scan_count and
        Ndb_pruned_scan_count have been introduced. Ndb_scan_count gives
        the number of scans executed since the cluster was last started.
        Ndb_pruned_scan_count gives the number of scans for which
        NDBCLUSTER was able to use partition pruning. Together, these
        variables can be used to help determine in the MySQL server
        whether table scans are pruned by NDBCLUSTER.

Also added descriptions of the new variables to http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-status-variables.html