MySQL Cluster Query Execution

MySQL Cluster executes the queries depending on the below type of scan’s it does

1. Primary key lookup
2. Unique key lookup
3. Ordered index scan (i.e., non-unique indexes that use T-trees)
4. Full table scan

Let’s say you have 4 data nodes in your cluster (NoOfReplicas=2). This means you have 2 node groups and each one has half the data. Cluster uses a hash on the primary key (unless you’ve controlled the partitioning using the 5.1 partitioning features). So for any table, half the rows are in one node group and half the rows are the in other node group.

Now for the 4 types of query execution. You can verify which type of execution is used with EXPLAIN. Here’s how each ones works:

1. Primary key lookup - the MySQL server can calculate the hash on the primary key and know which data node the relevant row resides in. Then the MySQL server contacts that data node and receives the row.
2. Unique key lookup - the MySQL server cannot know which data node the row might be stored in. So it contacts a pseudo-random node. That data node has a hidden table that maps the unique key values to the primary key. Then the hash on the primary key reveals where the row resides.
3. Ordered index scans are sent in parallel to all data nodes, where they search their local t-tree.
4. Full table scans are send in parallel to all data nodes, where they scan their primary fragment.

Summary: primary key lookups are best. If you have more than 2 nodes, throughput goes up because all nodes are actively serving different fragments of data. Ordered index lookup and full table scans are done in parallel, so more nodes leads to better performance.

Feature enhancements to MySQL Cluster

How important / useful would the following potential enhancements to MySQL Cluster be ?

Running MySQL Cluster on Windows in a production environment
Running MySQL Cluster within virtual machines in a production environment
Improved performance for complex joins
Support for Foreign Keys
More than 128 columns per row
More than 8 Kbytes of data (excluding BLOBs) per row
Ability for indexes to be stored on disk rather than always in memory
Improved performance for asynchronous (Geographic) replication (This does *not* refer to the synchronous replication between data nodes within a single Cluster).
Disk-durable commits - the ability to specify that a transaction does not commit until the in-memory change has been persisted to disk (Latency would be impacted)
Integrate MySQL Cluster backups with Enterprise backup solutions (e.g. Tivoli, NetBackup, HP DataProtector)
XA Distributed commit
Support for application and data nodes to run on machines with different endian architectures
Allow MySQL Cluster to continue operations after a full node-group has been lost (Depending on configuration, a limited number of rows in each table would be unavailable when a full node group is lost)
Allow effective use of more than 8 cores/hardware threads for a single data node
On-line drop column
Meta data (views, privileges etc.) shared between MySQL Servers
Enhanced asynchronous (geographic) replication management
MySQL Enterprise Monitor support for MySQL Cluster (including data nodes)
Extend conflict resolution for asynchronous (geographic) replication to include deletes and inserts
Extend conflict resolution for asynchronous (geographic) replication to remove requirement for application to manage Timestamps.
Support for the Service Availability Framework (SAF)
NDB API thread contention
2 x Data Node (1 node group) configuration where application selects which data node should be responsible for each row (e.g. all rows active on the same data node)
Variable sized VARCHARS on disk
Improve OPTIMIZE TABLE command
Enhanced memory use reporting
Savepoints
Support Map Reduce
Increase security by allowing table data to be transparently encrypted and the key kept secure.
SNMP support for traps
SNMP support for management
Backup a single database
Filtering of NDB API events
Abort transactions when disks become overloaded in order to avoid data nodes crashing

Most important MySQL Cluster features

High Availability
Distributed, shared-nothing architecture
Synchronous data replication
Fast Failover
On-line operations (i.e. add nodes, upgrades, schema changes, etc)
On-line back-up
Self-healing (i.e. automatic node recovery)
Geographic redundancy / replication
ACID Compliance / Transactional
Low latency / real-time operations
High write (UPDATE) performance
High read performance
Multiple interfaces / access methods to the data
Ability to scale out on commodity hardware
In-memory data storage
Disk-based data storage
Automatic data partitioning
User-defined data partitioning
Multi-platform support
Open source licensing / distribution
Low cost of acquisition
Low cost of operations

MySQL Cluster Query execution process

MySQL Cluster executes the queries depending on the below type of scan’s it does

1. Primary key lookup
2. Unique key lookup
3. Ordered index scan (i.e., non-unique indexes that use T-trees)
4. Full table scan

Let’s say you have 4 data nodes in your cluster (NoOfReplicas=2). This means you have 2 node groups and each one has half the data. Cluster uses a hash on the primary key (unless you’ve controlled the partitioning using the 5.1 partitioning features). So for any table, half the rows are in one node group and half the rows are the in other node group.

Now for the 4 types of query execution. You can verify which type of execution is used with EXPLAIN. Here’s how each ones works:

1. Primary key lookup - the MySQL server can calculate the hash on the primary key and know which data node the relevant row resides in. Then the MySQL server contacts that data node and receives the row.
2. Unique key lookup - the MySQL server cannot know which data node the row might be stored in. So it contacts a pseudo-random node. That data node has a hidden table that maps the unique key values to the primary key. Then the hash on the primary key reveals where the row resides.
3. Ordered index scans are sent in parallel to all data nodes, where they search their local t-tree.
4. Full table scans are send in parallel to all data nodes, where they scan their primary fragment.

Summary: primary key lookups are best. If you have more than 2 nodes, throughput goes up because all nodes are actively serving different fragments of data. Ordered index lookup and full table scans are done in parallel, so more nodes leads to better performance.