MySQL Internals Manual  /  Tracing the Optimizer  /  Tuning Trace Purging

8.6 Tuning Trace Purging

This is done with

 SET optimizer_trace_offset=<OFFSET>, optimizer_trace_limit=<LIMIT>

where OFFSET is a signed integer, and LIMIT is a positive integer. The default for optimizer_trace_offset is -1; the default for optimizer_trace_limit is 1. The SET statement has the following effects:

  • All remembered traces are cleared

  • A later SELECT on the OPTIMIZER_TRACE table returns the first LIMIT traces of the OFFSET oldest remembered traces (if OFFSET ≥ 0), or the first LIMIT

traces of the (-OFFSET) newest remembered traces (if OFFSET < 0).

For example, a combination of OFFSET=-1 and LIMIT=1 will make the last trace be shown (as is default), OFFSET=-2 and LIMIT=1 will make the next-to-last be shown, OFFSET=-5 and LIMIT=5 will make the last five traces be shown. Such negative OFFSET can be useful when one knows that the interesting substatements are the few last ones of a stored routine, like this:

SET optimizer_trace_offset=-5, optimizer_trace_limit=5;
CALL stored_routine(); # more than 5 substatements in this routine
SELECT * FROM information_schema.OPTIMIZER_TRACE; # see only last 5 traces

On the opposite, a positive OFFSET can be useful when one knows that the interesting substatements are the few first ones of a stored routine.

The more accurately those two variables are adjusted, the less memory is used. For example, OFFSET=0 and LIMIT=5 will use memory to remember 5 traces, so if only the three first are needed, OFFSET=0 and LIMIT=3 is better (tracing stops after LIMIT traces, so the 4th and 5th trace are not created and take up no memory). A stored routine may have a loop which executes many substatements and thus generates many traces, which would use a lot of memory; proper OFFSET and LIMIT can restrict tracing to one iteration of the loop for example. This also gains speed, as tracing a substatement impacts performance.

If OFFSET≥0, only LIMIT traces are kept in memory. If OFFSET<0, that is not true: instead, (-OFFSET) traces are kept in memory; indeed even if LIMIT is smaller than (-OFFSET), so excludes the last statement, the last statement must still be traced because it will be inside LIMIT after executing one more statement (remember than OFFSET<0 is counted from the end: the "window" slides as more statements execute).

Such memory and speed gains are the reason why optimizer_trace_offset and optimizer_trace_limit, which are restrictions at the trace producer level, are offered. They are better than using


which is a restriction on the trace consumer level and saves almost nothing.