Optimizer Trace Overview
MariaDB starting with 10.4.3
Optimizer Trace was introduced in MariaDB 10.4.3.
Contents
Usage
This feature produces a trace as a JSON document for any SELECT/UPDATE/DELETE containing information about decisions taken by the optimizer during the optimization phase (choice of table access method, various costs, transformations, etc). This feature helps to explain why some decisions were taken by the optimizer and why some were rejected.
Associated System Variables
- optimizer_trace=’enabled=on/off’
- Default value is off
- optimizer_trace_max_mem_size= value
- Default value: 1048576
INFORMATION_SCHEMA.OPTIMIZER_TRACE
Each connection stores a trace from the last executed statement. One can view the trace by reading the Information Schema OPTIMIZER_TRACE table.
Structure of the optimizer trace table:
SHOW CREATE TABLE INFORMATION_SCHEMA.OPTIMIZER_TRACE \G *************************** 1. row *************************** Table: OPTIMIZER_TRACE Create Table: CREATE TEMPORARY TABLE `OPTIMIZER_TRACE` ( `QUERY` longtext NOT NULL DEFAULT '', `TRACE` longtext NOT NULL DEFAULT '', `MISSING_BYTES_BEYOND_MAX_MEM_SIZE` int(20) NOT NULL DEFAULT 0, `INSUFFICIENT_PRIVILEGES` tinyint(1) NOT NULL DEFAULT 0 ) ENGINE=Aria DEFAULT CHARSET=utf8 PAGE_CHECKSUM=0
Optimizer Trace Contents
See Optimizer Trace Guide for an overview of what one can find in the trace.
Traceable Queries
These include SELECT, UPDATE, DELETE as well as their multi-table variants and all of the preceding prefixed by EXPLAIN and ANALYZE.
Enabling Optimizer Trace
To enable optimizer trace run:
SET optimizer_trace='enabled=on';
Memory Usage
Each trace is stored as a string. It is extended (with realloc()) as the optimization progresses and appends data to it. The optimizer_trace_max_mem_size variable sets a limit on the total amount of memory used by the current trace. If this limit is reached, the current trace isn't extended (so it will be incomplete), and the MISSING_BYTES_BEYOND_MAX_MEM_SIZE column will show the number of bytes missing from this trace.
Privilege Checking
In complex scenarios where the query uses SQL SECURITY DEFINER views or stored routines, it may be that a user is denied from seeing the trace of its query because it lacks some extra privileges on those objects. In that case, the trace will be shown as empty and the INSUFFICIENT_PRIVILEGES column will show "1".
Limitations
Currently, only one trace is stored. It is not possible to trace the sub-statements of a stored routine; only the statement at the top level is traced.