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

2 comments:

DataDiary.com said...

it's help me to do my stuff

akmal niazi khan said...

Programming is very interesting and creative thing if you do it with love. Your blog code helps a lot to beginners to learn programming from basic to advance level. I really love this blog because I learn a lot from here and this process is still continuing.
Love from Pro Programmer