Saturday, April 24, 2010

distributed push down joins - first "to many" join

just managed to run the first "to-many" join inside the data-nodes.
the query correspondce to

SELECT t1.*, t2.*
FROM T1 t1
LEFT OUTER JOIN T1 as t2 on t2.pk >= t1.b

- the code inside the data-nodes is general...but incomplete (data-node later crashed)
- there is no ndbapi and no SQL,the program testing is a hard-coded c-program sending messages using the ndb-cluster wire-protocol
- the result-set is not managed (the c-program just hangs)
- hopefully converting this into usable code will be faster than last years post as ndbapi is now quite complete, and this is "only" an add-on.

for a bit more background look at my presentation from MySQL Conference 2010.

hard-core details:

r.bn: 245 "DBTC", r.proc: 2, gsn: 32 "SCAN_TABREQ" prio: 1
s.bn: 32768 "API", s.proc: 4, s.sigId: 0 length: 11 trace: 1 #sec: 2 fragInf: 0
apiConnectPtr: H'00000027 requestInfo: H'08400a01:
Parallellism: 1 Batch: 64 LockMode: 0 Keyinfo: 0 Holdlock: 0 RangeScan: 0 Desc
ending: 0 TupScan: 0
ReadCommitted: 1 DistributionKeyFlag: 0 NoDisk: 1 spj: 1 attrLen: 0, keyLen: 0
tableId: 7, tableSchemaVer: 1
transId(1, 2): (H'00000000, H'00000400) storedProcId: H'0000ffff
batch_byte_size: 32768, first_batch_size: 64
H'00000027 H'00000000 H'08400a01 H'00000007 H'00000001 H'0000ffff H'00000000
H'00000400 H'00000026 H'00008000 H'00000040
SECTION 0 type=generic size=1
H'00000014
SECTION 1 type=generic size=33
H'000f0002 H'00050002 H'00000010 H'00000007 H'00000001 H'00010001 H'00090003
H'00000003 H'00000008 H'00000001 H'00000001 H'00000003 H'00010001 H'00000002
H'00060000 H'00080002 H'00000009 H'10000020 H'00000001 H'00000012 H'00000002
H'fff00002 H'ffe90000 H'000a0003 H'00000009 H'ffff0100 H'10000024 H'00000001
H'00000012 H'00000003 H'fff00002 H'ffe90000 H'fffb0000
---- Received - Signal ----------------
r.bn: 32768 "API", r.proc: 4, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 166035 length: 3 trace: 1 #sec: 1 fragInf
: 0
H'10000020 H'00000000 H'00000400
SECTION 0 type=linear size=7
H'fff30004 H'00000003 H'00000001 H'00000002 H'ffe90008 H'00000014 H'00000000
---- Received - Signal ----------------
r.bn: 32768 "API", r.proc: 4, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 166092 length: 3 trace: 1 #sec: 1 fragInf
: 0
H'10000020 H'00000000 H'00000400
SECTION 0 type=linear size=7
H'fff30004 H'00000003 H'00000002 H'00000003 H'ffe90008 H'00000014 H'00000001
---- Received - Signal ----------------
r.bn: 32768 "API", r.proc: 4, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 166202 length: 3 trace: 1 #sec: 1 fragInf
: 0
H'10000024 H'00000000 H'00000400
SECTION 0 type=linear size=9
H'fff30004 H'00000003 H'00000001 H'00000002 H'ffe90008 H'00000014 H'00000000
H'fffb0004 H'00000000
---- Received - Signal ----------------
r.bn: 32768 "API", r.proc: 4, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 166203 length: 3 trace: 1 #sec: 1 fragInf
: 0
H'10000024 H'00000000 H'00000400
SECTION 0 type=linear size=9
H'fff30004 H'00000003 H'00000002 H'00000003 H'ffe90008 H'00000014 H'00000001
H'fffb0004 H'00000000
---- Received - Signal ----------------
r.bn: 32768 "API", r.proc: 4, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 166227 length: 3 trace: 1 #sec: 1 fragInf
: 0
H'10000024 H'00000000 H'00000400
SECTION 0 type=linear size=9
H'fff30004 H'00000003 H'00000001 H'00000002 H'ffe90008 H'00000014 H'00010002
H'fffb0004 H'00000001
---- Received - Signal ----------------
r.bn: 32768 "API", r.proc: 4, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 166234 length: 3 trace: 1 #sec: 1 fragInf
: 0
H'10000024 H'00000000 H'00000400
SECTION 0 type=linear size=9
H'fff30004 H'00000003 H'00000002 H'00000003 H'ffe90008 H'00000014 H'00010003
H'fffb0004 H'00000001

Thursday, April 15, 2010

uploaded cluster pushdown join presentation...and preview-release

presentation is here
src tarball, that do contain bugs if node-failure occurs, is here

thanks for attending for those of you that did
feedback on preview is welcome, e.g here

maybe we'll do a binary version...if so i'll make a new post