I'm running some deletions against tables in MySQL Cluster, and keep hitting the "Too many active scans" error. I'm aware of the article by Johan Andersson, however in this case the parameters are already set very high – I'm assuming the error occurs due to the way I'm accessing the system. I'd like to fix my code, not tweak my database config any more!
The query I'm running is along the lines of:
delete from mytable where mytable.id in (<big long list of 1000s of IDs>)
Would I be better off batching this up into smaller chunks? Would that help avoid the error?
What does "Too many active scans" actually mean, and how can I avoid it in this case (without setting parameters to even higher values)?
Best Answer
"Too many active scans" error means that at a given moment there are too many queries running which are scanning the tables while trying to get answers.
Scanning means for example range scan queries (WHERE val > 20). And if I remember right scan also means fetch queries if index is not has.
Even such query counts as scan if id field has btree index.
In your case we almost all DELETE queries are running in loop with 'LIMIT 1000' or similar value.