MySQL Bugs: #61521: Unkown Sending data state and memory leak

Bug #61521	Unkown Sending data state and memory leak
Submitted:	15 Jun 2011 10:11	Modified:	21 Jul 2011 21:00
Reporter:	yuan chaohua	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	cluster 7.2-dev	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Sending data cluster 7.2

Description:
Hi, The 7.2 version works great for our application which has many complex joins. But sometimes a join sql(below) which use pushdown stays in the sending data state more than 40s during our load test. I try to find the reason and wrote a python script which use more than 500 threads with long connection to execute this sql for ever. and found the memory of the sql node increased with time can up to 100%. If I stop the threads ( the Persistent connection) the memory usage drop down to normal(below %5). With the same query if i set ndb_join_pushdown = 0 the memory will stay at %5 during my test. So the memory sure is eaten by ndb join pushdown. 
1. Do you know why the query cost more than 40s sometimes while it cost Less than 10 ms  99% times. The cpu of the point is less than 15% and io is less than 1% and net is less than 10M/s.
2. join push down eat memory?

We have 4 data node and 2 sql node.

Below is the table info and sql. 

Query:
SELECT a.qid, b.id, b.value1, b.Value2, b.Value3, b.Value4, b.Value5, b.goal_diff, b.matchmode, a.accept_date FROM char_quest as a  INNER JOIN quest_objective as b
        ON a.qid = b.qid  WHERE a.finish = 0 and b.type = 77 and a.cid = 123456

The explain:
1	SIMPLE	b	ref	idx_qid,idx_type	idx_type	5	const	3	Parent of 2 pushed join@1; Using where with pushed condition
1	SIMPLE	a	eq_ref	PRIMARY,idx_fcid	PRIMARY	8	const,sso_pc_dev_g.b.qid	1	Child of 'b' in pushed join@1; Using where with pushed condition

CREATE TABLE `char_quest` (
  `cid` int(11) NOT NULL,
  `qid` int(11) NOT NULL,
  `finish` int(3) NOT NULL,
  `accept_date` datetime DEFAULT NULL,
  `submit_date` datetime DEFAULT NULL,
  PRIMARY KEY (`cid`,`qid`),
  KEY `idx_fcid` (`cid`,`finish`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8 |
CREATE TABLE `quest_objective` (
  `id` int(11) NOT NULL,
  `qid` int(11) NOT NULL,
  `type` int(8) DEFAULT NULL,
  `subtarget` int(3) DEFAULT NULL,
  `city_req` int(11) DEFAULT NULL,
  `goal_diff` int(11) DEFAULT NULL,
  `matchmode` int(11) DEFAULT NULL,
  `value1` int(11) DEFAULT NULL,
  `value2` int(11) DEFAULT NULL,
  `value3` int(11) DEFAULT NULL,
  `value4` int(11) DEFAULT NULL,
  `value5` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_qid` (`qid`),
  KEY `idx_type` (`type`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8

Thanks

How to repeat:
char_quest 20 million rows.
quest_objective 450 rows.

Yuan:

Thanks for reporting this error, and testing our 'pushed joins' feature.

We have identified and reproduced a potential problem which may cause the excessive memory consumption you are observing. This happens if you execute a lot of pushed query operation and:

1. Your session is running with 'autocommit=off', AND
2. Transaction is not commited. (or transaction is really long.)

Please report back whether this looks like the setup you are using.
It would also be nice to know whether commiting you transactions
solves this problem.

Regarding some queries running for 40s: I would suspect this to be caused
by heavy memory swapping when mysqld has consumed 100% of the memory.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".