Thursday, August 30, 2012

MySQL Cluster: Too many active scans


Continuing with another blog about troubleshooting MySQL Cluster we will look at a common error message: "Too many active scans".

Now, you have already set MaxNoOfConcurrentScans=500 in your config.ini, and you can't increase it more (the max is 500), but you still get this error. Frustrating!

But looking into the code, it is not MaxNoOfConcurrentScans that sets the limitation, it is actually MaxNoOfLocalScans and the "Too many active scans" comes from the Local Query Handler of the NDB node, and that is where MaxNoOfLocalScans is used!

So what you can try to do is to actually set/increase MaxNoOfLocalScans, and if it is not set already in your config.ini, the default value is calculated as:

In 7.2.0 and later:
MaxNoOfLocalScans= tcInstances * lqhInstances * noOfDBNodes * 
                   MaxNoOfConcurrentScans + 2
So if we have tcInstances=1, lqhInstances=4, noOfDBNodes=4, and MaxNoOfConcurrentScans=500 we get
MaxNoOfLocalScans=1*4*4*500 + 2 = 8002

Earlier versions:
MaxNoOfLocalScans = (noOfDBNodes * noOfScanRecords) + 2 ,

So if we have noOfDBNodes=4, and MaxNoOfConcurrentScans=500 we get
MaxNoOfLocalScans=4*500 +2 = 2002

If you use our configuration packages, then you can simply just increase/set the MaxNoOfLocalScans and do:
./rolling-restart.sh

Good luck!

4 comments:

Unknown said...

Hi Johan

just a question:

shouldn't it be better, for a proper dimensioning, use the max number of fragments instead the noOfDBNodes ?

BR
Eduardo

Unknown said...

Hi Johan
what about
MaxNoOfLocalScans = (TableFragmentCount * noOfScanRecords) + 2 for releases before 7.2.x

BR
Eduardo

Anonymous said...

Hi Johan,
can you please explain what are the parameters

tcInstances=1 and lqhInstances=4

Thanks in advance
Sumi

Johan Andersson said...

tcInstances = No of transaction coordinators
lqh instances = No query handlers.

Defined with:
http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbmtd-threadconfig