Optimizer Trace in MariaDB Server 10.4

One of the new features in MariaDB Server 10.4 is the Optimizer Trace. It provides diagnostics about the optimizer: you can switch the tracing on, run a statement, and then examine the trace to see how the optimizer processed the statement and arrived at the query plan.

How does that fit with other query optimizer tools, like EXPLAIN and ANALYZE for statements? It fits very well: EXPLAIN [FORMAT=JSON] shows the query plan that was chosen by the optimizer. ANALYZE [FORMAT=JSON] allows it to see what happened when the query plan was executed. And now, Optimizer trace allows to see what happened during the query optimization phase:

What can one learn from the optimizer trace contents?

The first example is potential query plans. In my experience, one of the frequent and hardest to answer questions about the optimizer is: Why did the optimizer NOT pick a certain query plan? It looks like it would be a good choice. Was that query plan even considered? If not, why? If yes, what were the expected cost? Did the optimizer discard it because index statistics made it look poor? Will collecting more statistics help?  Before the optimizer trace, one needed to look at the source code and/or make a lot of guesses. Now, one can get definite answers from the trace.

Another use case that I would find particularly useful is a complaint that one query plan is chosen on one system, and another query plan on a different system. The systems are typically nearly identical, except for a few “innocent” changes like added/removed columns, changed character sets, or minor difference between the server versions. Without optimizer trace, figuring out the cause of the difference can be very labor-intensive and require access to both systems. With optimizer trace, one can just compare the traces and instantly see where the difference started.

The third use case is asking for help. Optimizer trace contents may look a bit cryptic for those who don’t debug database performance issues for a living, but it provides a lot of useful information for those who do. Now, if one has a mis-behaving query, it is much easier to save the trace and request help. The trace contents are human-readable, so one can make sure they are not sending over any sensitive data. I think, troubleshooting optimizer issues will become much easier with the trace.

One last item to share. If you want to use the optimizer trace feature before you’re able to upgrade to MariaDB Server 10.4, it’s now possible. Optimizer Trace will be backported to MariaDB Enterprise Server 10.3.