MySQL Forums
Forum List  »  NDB clusters

Degraded insert performance when selecting from the join of two ndb tables
Posted by: W D
Date: October 30, 2015 01:31PM

I am seeing degraded insert performance when inserting data that is selected from the join of two (or more) ndb tables.

Minimum query to reproduce: INSERT INTO tableC SELECT col1,col2,col3 FROM tableA join tableB ON ... WHERE ...

with tableA and tableB using engine ndb and tableC using any engine.

Results of some tests I've run

1) Execution time reduces by a factor of ~10 by removing the INSERT INTO tableC portion of the query (i.e. the select is comparatively fast)

2) "SELECT .. INTO OUTFILE 'xyz' " -> "LOAD DATA INFILE 'xyz'" also reduces the execution time by a factor of ~10 (i.e. write speed is not the bottleneck)

3) Performance is not improved when tableC uses engine = BLACKHOLE (i.e. write speed really isn't the bottleneck)

4) Using tableA1 and tableB1 with identical data but engine = InnoDB also reduces the execution time by a factor of ~10 (i.e. there seems to be something amiss when the source data is in an ndb table)

5) The insert ... select... is fast if only one ndb source table is used (i.e. there seems to be something amiss with joining two ndb tables and inserting the results into a table)

6) The explain of the full query and just the select portion is identical

Based on these results, my working theory is:

After every row (or some significantly small #) is generated in the data node (a push down join is used in this case) from the select statement, it is sent back to the sql node to be inserted into tableC. This might be o.k., but the data node then waits for a commit confirmation before generating the next row.

Of course, this might be wildly incorrect and some other behavior is causing the degraded performance.

My question (of course) is how to stop this degraded insert performance from happening.

Options: ReplyQuote


Subject
Views
Written By
Posted
Degraded insert performance when selecting from the join of two ndb tables
1569
W D
October 30, 2015 01:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.