We have an issue similar to this. Only, the solution there won't work for us as:

  1. We don't use multi-table JOINS, but UPDATE … WHERE `userid` IN (<list of 10k ids>);
  2. We currently "only" have the issue on one replication slave, so we can't change the statement there.

Forcing the update to use an index using … USE INDEX (PRIMARY) SET … would help, according to EXPLAIN.

Is there any way using variables to get similar behaviour? Or is our best approach to change the application to use explicit index hints & rebuild the slave?

  • Have you tried replacing the IN list with a table holding the 10k IDs? – Andriy M Jul 25 '18 at 14:56
  • > We currently "only" have the issue on one replication slave, so we can't change the statement there. Would if we could… – DaPeda Jul 25 '18 at 14:57
  • Sorry if this is a stupid question but – does that circumstance prevent you from even trying a rewritten version of the statement to see if the index would be used? – Andriy M Jul 25 '18 at 15:04
  • Only the fact that it's a read-only slave & I can't create new tables for a sub-select. Also, it won't solve the immediate problem of our replication being 2 days behind by now. – DaPeda Jul 25 '18 at 15:12

So, for others, we found a solution.


SET GLOBAL range_optimizer_max_mem_size=0;

and stop/start the slave.

According to the documentation on limiting range optimization memory usage, the default maximum is 8 MiB. Also, there is this:

For a simple query such as the following, where there is one candidate key for the range access method, each predicate combined with OR uses approximately 230 bytes:

WHERE a=1 OR a=2 OR a=3 OR .. . a=N;


For a query with IN() predicates:

WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);

Each literal value in an IN() list counts as a predicate combined with OR.

So that would mean our UPDATE with 10k simple IDs would need 10.000 * 230 Bytes = approx. 2,2 MiB, well within the default memory size. Even with the pre-5.7.11 requirement of 700 Bytes / predicate we'd be save.

Only, apparently the documentation is wrong, because as soon as we set the value of range_optimizer_max_mem_size to 0 (means No Limit), the replication delay went down from 3 hours to 0 in a span of less than 15 minutes.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.