MySQL Bugs: #22163: Load unevenly distributed on TCs

Bug #22163	Load unevenly distributed on TCs
Submitted:	9 Sep 2006 9:25	Modified:	16 Oct 2008 17:17
Reporter:	Johan Andersson	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	5.1.X	OS:	Any (*)
Assigned to:	Frazer Clement	CPU Architecture:	Any
Tags:	TC

Description:
2 NDB nodes - 1 mysqld  (nothing is colocated)

I have a _SProc_ defined in the mysqld which i call from a java test program.

with
ndb_index_stat_enable=1
the load is distributed correctly (each TC has 50-50)

with
ndb_index_stat_enable=0
One TC gets all the load

2006-09-09 11:10:11 [MgmSrvr] INFO     -- Node 2: Trans. Count = 0, Commit Count = 0, Read Count = 0, Simple Read Count = 0,
Write Count = 0, AttrInfo Count = 0, Concurrent Operations = 0, Abort Count = 0
 Scans: 0 Range scans: 0
2006-09-09 11:10:11 [MgmSrvr] INFO     -- Node 3: Trans. Count = 1470, Commit Count = 0, Read Count = 376320, Simple Read Count = 376320,
Write Count = 0, AttrInfo Count = 1128960, Concurrent Operations = 3, Abort Count = 0
 Scans: 0 Range scans: 1467

Taking the query out of the SPROC and insted exexuting it gives the opposite:

with
ndb_index_stat_enable=0

2006-09-09 11:21:18 [MgmSrvr] INFO     -- Node 3: Trans. Count = 252, Commit Count = 0, Read Count = 64145, Simple Read Count = 64145,
Write Count = 0, AttrInfo Count = 192435, Concurrent Operations = 3, Abort Count = 0
 Scans: 0 Range scans: 252
2006-09-09 11:21:18 [MgmSrvr] INFO     -- Node 2: Trans. Count = 252, Commit Count = 0, Read Count = 64415, Simple Read Count = 64415,
Write Count = 0, AttrInfo Count = 193245, Concurrent Operations = 1, Abort Count = 0
 Scans: 0 Range scans: 251

Where as  with
ndb_index_stat_enable=0

2006-09-09 11:24:13 [MgmSrvr] INFO     -- Node 3: Operations=93236
2006-09-09 11:24:13 [MgmSrvr] INFO     -- Node 2: Operations=0
2006-09-09 11:24:13 [MgmSrvr] INFO     -- Mean loop Counter in doJob last 8192 times = 4
2006-09-09 11:24:13 [MgmSrvr] INFO     -- Node 3: Mean receive size to Node = 4 last 4096 sends = 65 bytes
2006-09-09 11:24:13 [MgmSrvr] INFO     -- Node 3: Mean send size to Node = 4 last 4096 sends = 134 bytes
2006-09-09 11:24:13 [MgmSrvr] INFO     -- Node 3: Trans. Count = 365, Commit Count = 0, Read Count = 93309, Simple Read Count = 93309,
Write Count = 0, AttrInfo Count = 279927, Concurrent Operations = 3, Abort Count = 0
 Scans: 0 Range scans: 730
2006-09-09 11:24:13 [MgmSrvr] INFO     -- Node 2: Trans. Count = 0, Commit Count = 0, Read Count = 0, Simple Read Count = 0,
Write Count = 0, AttrInfo Count = 0, Concurrent Operations = 0, Abort Count = 0
 Scans: 364 Range scans: 0

Fishy indeed...

-johan

How to repeat:
do you need test program  or can you do your self?

Suggested fix:
-

On 5.0, the transaction in the SPROC is always executed on one TC only:

2006-09-09 11:34:31 [MgmSrvr] INFO     -- Node 2: Operations=0
2006-09-09 11:34:31 [MgmSrvr] INFO     -- Node 3: Operations=108288
2006-09-09 11:34:31 [MgmSrvr] INFO     -- Node 2: Trans. Count = 0, Commit Count = 0, Read Count = 0, Simple Read Count = 0,
Write Count = 0, AttrInfo Count = 0, Concurrent Operations = 0, Abort Count = 0
 Scans: 0 Range scans: 0
2006-09-09 11:34:32 [MgmSrvr] INFO     -- Node 3: Trans. Count = 425, Commit Count = 0, Read Count = 108748, Simple Read Count = 108748,
Write Count = 0, AttrInfo Count = 326244, Concurrent Operations = 0, Abort Count = 0
 Scans: 0 Range scans: 423

Whereas the "normal" query is distributed correctly onto the TCs.

No takers... okay, a test case:

with ndb_index_stat_enable=0 and SPROC 
--> skew load

with ndb_index_stat_enable=1 and SPROC 
--> evenly distributed load

with ndb_index_stat_enable=0 and Prepared Statement 
--> evenly distributed load

with ndb_index_stat_enable=1 and Prepared Statement 
--> skew load

strange.. 

SPROC used:

drop procedure test3;
delimiter $$
create procedure test3( in name varchar(255))
begin
  set @stmt = concat("select t1.a,t1.b,t1.c from t1,t2 where t1.a=t2.a and t2.c='", name,"'");
# select @stmt;
  prepare stmt from @stmt;
  execute stmt;
  drop prepare stmt;
end;
$$
delimiter ;

PREPARED STATEMENT USED:
select t1.a,t1.b,t1.c from t1,t2 where t1.a=t2.a and t2.c=?

Tables:
 CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  `d` char(255) DEFAULT 'tt',
  PRIMARY KEY (`a`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 

CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) NOT NULL DEFAULT '0',
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `ix` (`c`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

Unsuccessfully attempted to reproduce on mysql-5.1-telco-6.2 with 2 nodes and MySQLD on localhost.

Interactive Select query and stored procedure with index stats enabled and disabled all showed good balance using the TC statistics.

Perhaps bug has been fixed already, or distributed configuration is required to reproduce?