new features in MySQL Cluster 7.2.1

• AQL (aka push down join)
  Further improvements and refinements compared to 7.2.0 from April
• Index statistics
  A long over due feature, that aims to reduce(minimize) need of manual query tuning that previously has been essential for efficient SQL usage with ndb.
• memcache access support
• Active-Active replication enhancements
• Various internal limits has been increased
  - Max row-size now 14k (previously 8k)
  - Max no of columns in table now 512 (previously 128)
• Rebase to mysql-5.5 (7.2.1 is based on mysql-5.5.15)
• Improved support for geographically separated cluster
  (note: single cluster...i.e not using asynchronous replication)

Brief introduction to AQL (aka join pushdown)

Basic concept is to evaluate joins down in data-nodes instead(in addition to) of in mysqld.
Ndb will examine query plan created by mysqld, and construct a serialized definition of this join, ship it down to data-nodes.
This join will in the data-nodes be evaluated in parallel (if appropriate), and the result set will be sent back to mysqld using a streaming interface.
Performance gain (latency reduction) is normally in the range of 20x for a 3-way join.

Brief introduction to Index statistics

The index statistics works a lot like Innodb persistent statistics.
When you execute analyze table T, data nodes will scan the indexes of T and produce a histogram of each index.
This histogram is stored in tables in ndb (mysql.ndb_index_stat_head and mysql.ndb_index_stat_sample). The histogram can then be used by any mysqld connected to this cluster. The histogram will not be generated until a new analyze table T is requested.

Brief introduction to Active-Active enhancements

MySQL Cluster has supported active-active asynchronous replication with conflict detection and conflict resolution since 6.3.
In prior version, the schema had to be modified, adding a timestamp column to each table and application has to be modified to maintain this timestamp column.
In this new version, no schema modification is required and no application modification is needed.
In previous version, conflict detection/resolution was performed on row-by-row basis.
In this new version, transaction boundaries are respected.
E.g in a row R is determined to be in conflict, not only this row-change will be resolved,
but entire transaction T that modified the row will be resolved and all transactions depending on the T transitively.
Longer descriptions can be found here and here

---

Sorry for omitting hex dumps and/or unformatted numbers

---

Mandatory late update: the join described here has now gained an extra 2x (but this improvement did not make 7.2.1)

Star schema benchmark on MySQL Cluster 7.2

I decided to try the star schema benchmark on our latest 7.2 release (link). Star schema benchmark is an analytics oriented benchmark, and MySQL Cluster has not been developed to address this kind of workload. Nevertheless I couldn't resist trying...

Setup

• 2 data-nodes each running on a 4-way Xeon E7420 @ 2.13GHx (total 16 cores) 256Gb RAM
• The mysqld was co-located with one of the data-nodes
• I used memory tables
  

Results

Queries: link

Query	sf10	sf100
Q1.1	5	62
Q1.2	0.4	4.6
Q1.3	0.1	1.1
Q2.1	9	95
Q2.2	48	495
Q2.3	51	517
Q3.1	47	481
Q3.2	47	476
Q3.3	2	20
Q3.4	2	19
Q4.1	57	572
Q4.2	57	574
Q4.3	12	120

(time in sec, less time is better)

Facts

• I have done no analysis to why some queries are faster than others
• This type of queries is currently not in focus of our efforts to improve join performance (nor has it been so far)
• for lineorder table I specified max_rows=500000000
  
• ndb_join_pushdown=on (default in 7.2)
  

Thoughts and guesses

• sf100 is almost exactly 10x slower than sf10. So I think a reasonable guess is that sf1000 is 10x slower than sf100
  
• Star schema benchmark is an easy benchmark (compared to e.g TPC-H)
• I think results looks quite good
• My guess is that scaling out (e.g to 4 nodes) would scale well (although I didn't try) (e.g scale out by running 2 ndbmtd on each machine)
• Running high write load in parallel doesnt increase query times signficantly (as tested in presentation page 45-46) which is quite cool (although I didn't try this time)
  

Load procedure for sf100

• I manually split the 61Gb file (lineorder) into 10 6Gb files (using split(1))
• Then I loaded these 10 in parallel with 10 clients using load data infile
• I disabled indexes while loading using ndb_restore --disable-indexes (from an backup made on empty db containing just schema definition)
• I then rebuilt the indexes using ndb_restore --rebuild-indexes using BuildIndexThreads=15 in config.ini

(for sf10 i did nothing special)

Load time for sf100

• 4h including rebuilding of indexes

References

• Blog where percona tries SSB

---

Last minute update

• OleJohn reports that Q2.2 and Q2.3 should be 10x faster if forcing a different join order (e.g by using straight_join or force index). But this I haven't tried

distributed pushed down joins - webinar and new preview available

as part of the webinar tonight we made a new preview release src and binary (linux x86-64 glibc23).

this includes pushing of ref that was not supported in last preview release as well as latest and greatest new optimizations.

note that this is still not production ready code.
but...we welcome any feedback on it!

---

Nov 5: And feedback can be sent to spj-feedback@sun.com

distributed pushed down joins - progress and attitude

we're now at a stage so we can test/benchmark scan/scan queries, i.e push most queries. there are still know bugs and things that we can't push.

to see how we performs, we tested a very old query/dataset, that a customer tried in the 4.1 days. the result at that time was a disaster, and they at that time decided to use something else.

it's a 11-way join with mainly REF accesses (i.e not many lookups)

i tested it on same hardware/configuration as for my UC presentation.
without join pushdown the average query time is 3.6s.
with join pushdown it's ~500ms, i.e ~7x improvement.

the nights of kni are deeply disappointed, and that's an attitude i like :-)

FYI: we also optimized TPCW::getBestSeller some more and are now at 42x on that hardware/configuration.

---

stay tuned

---

micro update: Found a bug when using ndbmtd...fixing that gave 200ms (i.e 18x)...also did some more optimizations and now record is 120ms (i.e 29x)

distributed pushed down joins - node-failure handling done

node-failure handling was as expected lots of small changes to various pieces of the code.
some non compatible protocol changes performed (nice not to have any existing GA release to care about :)

also when doing this, I re-implemented entire "abort of join"-handling.
it should now also be capable of handling aborting more complex joins
(but node-failure is still an special case)

this means that "testSpj -n NF_Join" now passes!!

---

this is an important step towards a releasable version.

note: this has only been implemented in the branch which also contains extension for scan.vs.scan joins. I.e not in the branch released as preview.

and, OleJohn integrated support for filters on child-joins with mysqld.

---

And as always please provide feedback on preview release that we made!

distributed pushed down joins - more testing

have now written and pushed ndbapi testing of our push-down join implementation (~2k LOC)

the following classes/programs was added

• HugoQueryBuilder (class)
  Construct a pseudo-random query
  inputs: a list of tables and a options specifying which features should be used in query
• HugoQueries (class)
  Run an arbitrary query.
  Note: Does only verify individual rows, not relations/result-set. For verifying result-sets we use random query generator
• testSpj (program)
  Ndbapi test-program using above classes to runs various tests for our cluster specific automatic testing framework
• hugoJoin (program)
  Stand-alone test-program that takes list of tables/options as command-line arguments
  and using above classes constructs/runs random queries

Quite happy with this, as it's very much needed to get good coverage of the implementation,
especially error cases (e.g node-failure) which is hard to test from/with SQL.

---

And please provide feedback on preview release that we made!

distributed pushed down joins - more features

update on latest accomplishments:

1. added support for filters (compare engine_condition_pushdown) on non-root operations, this in 2 flavors:
   
   • - constant/immediate filters that is provided after NdbQuery has been defined, these may not contains filter conditions referencing other NdbQueryOperation's,
   • - parameterized/linked filters, these type of programs must be provided when building the NdbQuery-object.
   
   The constant filters should be "easy" to integrate with ndbapi/mysqld, but the parameterized/linked is harder as we need to add new features to NdbScanFilter.
   
   Once this is integrated into mysqld, it will provide better performance for already pushable queries, see page 43 in my UC presentation (ref: filter).
   
   
2. added support for NdbQueryOpertation's referencing non-direct parent NdbQueryOperation's. This should also be quite easy to integrate into ndbapi/mysqld. This will mean that new types of queries will become pushable.
   
   
3. support for "to-many" joins is made so complete that it will get until we get an ndbapi for more elaborate testing. This will mean that lots of new queries will become pushable.
   

Note: all the additions has only been made inside ndb(mt)d so far, nothing new has (yet) been added to ndbapi/mysqld.

---

And please provide feedback on preview release that we made!