MariaDB> set optimizer_trace='enabled=on';
MariaDB> select * from t1 where a<10;
MariaDB> select * from information_schema.optimizer_trace limit 1\G
*************************** 1. row ***************************
QUERY: select * from t1 where a<10
TRACE: {
"steps": [
{
"join_preparation": {
"select_id": 1,
"steps": [
{
"expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c from t1 where t1.a < 10"
}
]
}
},
{
"join_optimization": {
"select_id": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "t1.a < 10",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "t1.a < 10"
},
{
"transformation": "constant_propagation",
"resulting_condition": "t1.a < 10"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "t1.a < 10"
}
]
}
},
{
"table_dependencies": [
{
"table": "t1",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": []
},
{
"rows_estimation": [
{
"table": "t1",
"range_analysis": {
"table_scan": {
"rows": 1000,
"cost": 206.1
},
"potential_range_indexes": [
{
"index": "a",
"usable": true,
"key_parts": ["a"]
},
{
"index": "b",
"usable": false,
"cause": "not applicable"
}
],
"setup_range_conditions": [],
"group_index_range": {
"chosen": false,
"cause": "no group by or distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "a",
"ranges": ["(NULL) < (a) < (10)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 10,
"cost": 13.751,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "a",
"rows": 10,
"ranges": ["(NULL) < (a) < (10)"]
},
"rows_for_plan": 10,
"cost_for_plan": 13.751,
"chosen": true
}
}
},
{
"selectivity_for_indexes": [
{
"index_name": "a",
"selectivity_from_index": 0.01
}
],
"selectivity_for_columns": [],
"cond_selectivity": 0.01
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "range",
"resulting_rows": 10,
"cost": 13.751,
"chosen": true
}
]
}
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "t1.a < 10",
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "t1",
"attached": "t1.a < 10"
}
]
}
}
]
}
},
{
"join_execution": {
"select_id": 1,
"steps": []
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0This page is licensed: CC BY-SA / Gnu FDL
Optimizer traces can be large for some queries.
In order to collect a large trace, you need to perform the following steps (using 128 MB as an example):
set global max_allowed_packet=128*1024*1024;Reconnect specifying --max-allowed-packet=128000000 for the client as well.
set optimizer_trace=1;
set optimizer_trace_max_mem_size=127*1024*1024;Now, one can run the query and save the large trace.
optimizer_trace system variable
optimizer_trace_max_mem_size system variable
max_allowed_packet system variable
This page is licensed: CC BY-SA / Gnu FDL
This article describes guidelines for what/how to write to Optimizer Trace when doing server development.
The trace is a "structured log" of what was done by the optimizer. Prefer to do tracing as soon as a rewrite/decision is made (instead of having a separate trace_something() function).
Generally, a function should expect to find the trace in a state where we're writing an array. The rationale is that array elements are ordered, while object members are not (even if they come in a certain order in the JSON text). We're writing a log, so it's natural for different entries to form an array.
Typically you'll want to start an unnamed object, then use member names to show what kind of entry you're about to write:
[
..., # Something before us
{
"my_new_rewrite": {
"from": "foo",
"to": "bar",
...
}
}
...(TODO other considerations)
Json_writer_object and Json_writer_array classes use RAII idiom and ensure that JSON objects and arrays are "closed" in the reverse order they were started.
However, they do not ensure these constraints:
JSON objects must have named members.
JSON arrays must have unnamed members.
Tracing code has runtime checks for these. Attempt to write invalid JSON will cause assertion failure.
It is possible to run mysql-test-run with this argument
--mysqld=--optimizer_trace=enabled=onThis will run all tests with tracing on. As mentioned earlier, debug build will perform checks that we are not producing invalid trace.
The BuildBot instance atalso runs tests with this argument, see mtr_opttrace pass in kvm-fulltest and kvm-fulltest2.
See optimizer-debugging-with-gdb/#printing-the-optimizer-trace for commands to print the trace for the current statement.
This page is licensed: CC BY-SA / Gnu FDL
Optimizer trace uses the JSON format. It is basically a structured log file showing what actions were taken by the query optimizer.
Let's take a simple query:
MariaDB> explain select * from t1 where a<10;
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 10 | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+One can see the full trace here. Taking only the component names, one gets:
MariaDB> select * from information_schema.optimizer_trace limit 1\G
*************************** 1. row ***************************
QUERY: select * from t1 where a<10
TRACE:
{
"steps": [
{
"join_preparation": { ... }
},
{
"join_optimization": {
"select_id": 1,
"steps": [
{ "condition_processing": { ... } },
{ "table_dependencies": [ ... ] },
{ "ref_optimizer_key_uses": [ ... ] },
{ "rows_estimation": [
{
"range_analysis": {
"analyzing_range_alternatives" : { ... },
"chosen_range_access_summary": { ... },
},
"selectivity_for_indexes" : { ... },
"selectivity_for_columns" : { ... }
}
]
},
{ "considered_execution_plans": [ ... ] },
{ "attaching_conditions_to_tables": { ... } }
]
}
},
{
"join_execution": { ... }
}
]
}For each SELECT, there are two "Steps":
join_preparation
join_optimization
Join preparation shows early query rewrites. join_optmization is where most of the query optimizations are done. They are:
condition_processing - basic rewrites in WHERE/ON conditions.
ref_optimizer_key_uses - Construction of possible ways to do ref and eq_ref accesses.
rows_estimation - Consideration of range and index_merge accesses.
considered_execution_plans - Join optimization itself, that is, choice of the join order.
attaching_conditions_to_tables - Once the join order is fixed, parts of the WHERE clause are "attached" to tables to filter out rows as early as possible.
The above steps are for just one SELECT. If the query has subqueries, each SELECT will have these steps, and there will be extra steps/rewrites to handle the subquery construct itself.
If you are interested in some particular part of the trace, MariaDB has two functions that come in handy:
JSON_EXTRACT extracts a part of JSON document
JSON_DETAILED presents it in a user-readable way.
For example, the contents of the analyzing_range_alternatives node can be extracted like so:
MariaDB> select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
-> from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')): [
{
"range_scan_alternatives":
[
{
"index": "a_b_c",
"ranges":
[
"(1) <= (a,b) < (4,50)"
],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 4,
"cost": 6.2509,
"chosen": true
}
],
"analyzing_roworder_intersect":
{
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
}
]A lot of applications construct database query text on the fly, which sometimes means that the query has constructs that are repetitive or redundant. In most cases, the optimizer will be able to remove them. One can check the trace to be sure:
explain select * from t1 where not (col1 >= 3);Optimizer trace will show:
"steps": [
{
"join_preparation": {
"select_id": 1,
"steps": [
{
"expanded_query": "select t1.a AS a,t1.b AS b,t1.col1 AS col1 from t1 where t1.col1 < 3"
}Here, one can see that NOT was removed.
Similarly, one can also see that IN(...) with one element is the same as equality:
explain select * from t1 where col1 in (1);will show
"join_preparation": {
"select_id": 1,
"steps": [
{
"expanded_query": "select t1.a AS a,t1.b AS b,t1.col1 AS col1 from t1 where t1.col1 = 1"On the other hand, converting an UTF-8 column to UTF-8 is not removed:
explain select * from t1 where convert(utf8_col using utf8) = 'hello';will show
"join_preparation": {
"select_id": 1,
"steps": [
{
"expanded_query": "select t1.a AS a,t1.b AS b,t1.col1 AS col1,t1.utf8_col AS utf8_col from t1 where convert(t1.utf8_col using utf8) = 'hello'"
}so redundant CONVERT calls should be used with caution.
MariaDB has two algorithms to handle VIEWs: merging and materialization. If you run a query that uses a VIEW, the trace will have either
"view": {
"table": "view1",
"select_id": 2,
"algorithm": "merged"
}or
{
"view": {
"table": "view2",
"select_id": 2,
"algorithm": "materialized"
}
},depending on which algorithm was used.
The MariaDB optimizer has a complex part called the Range Optimizer. This is a module that examines WHERE (and ON) clauses and constructs index ranges that need to be scanned to answer the query. The rules for constructing the ranges are quite complex.
An example: Consider a table
CREATE TABLE some_events (
start_date DATE,
end_date DATE,
...
key (start_date, end_date)
);and a query:
explain select * from some_events where start_date >= '2019-09-10' and end_date <= '2019-09-14';
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | some_events | ALL | start_date | NULL | NULL | NULL | 1000 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+One might think that the optimizer would be able to use the restrictions on both start_date and end_date to construct a narrow range to be scanned. But this is not so, one of the restrictions creates a left-endpoint range and the other one creates a right-endpoint range, hence they cannot be combined.
select
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) as trace
from information_schema.optimizer_trace\G
*************************** 1. row ***************************
trace: [
{
"range_scan_alternatives":
[
{
"index": "start_date",
"ranges":
[
"(2019-09-10,NULL) < (start_date,end_date)"
],
...the potential range only uses one of the bounds.
Index-based Nested-loops joins are called "ref access" in the MariaDB optimizer.
The optimizer analyzes the WHERE/ON conditions and collects all equality conditions that can be used by ref access using some index.
The list of conditions can be found in the ref_optimizer_key_uses node.
(TODO example)
The join optimizer's node is named considered_execution_plans.
The optimizer constructs the join orders in a left-to-right fashion. That is, if the query is a join of three tables:
SELECT * FROM t1, t2, t3 WHERE ...then the optimizer will
Pick the first table (say, it is t1),
consider adding another table (say, t2), and construct a prefix "t1, t2"
consider adding the third table (t3), and constructing a prefix "t1, t2, t3", which is a complete join plan Other join orders will be considered as well.
The basic operation here is: "given a join prefix of tables A,B,C ..., try adding table X to it". In JSON, it looks like this:
{
"plan_prefix": ["t1", "t2"],
"table": "t3",
"best_access_path": {
"considered_access_paths": [
{
...
}
]
}
}(search for plan_prefix followed by table).
If you are interested in how the join order of #t1,t2,t3
"plan_prefix":[], "table":"t1"
"plan_prefix":["t1"], "table":"t2"
"plan_prefix":["t1", "t2"], "table":"t3"
This page is licensed: CC BY-SA / Gnu FDL
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.
optimizer_trace=’enabled=on/off’
Default value is off
optimizer_trace_max_mem_size= value
Default value: 1048576
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=0See Optimizer Trace Guide for an overview of what one can find in the trace.
These include SELECT, UPDATE, DELETE as well as their multi-table variants and all of the preceding prefixed by EXPLAIN and ANALYZE.
To enable optimizer trace run:
SET optimizer_trace='enabled=on';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.
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".
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.
This page is licensed: CC BY-SA / Gnu FDL
Optimizer Trace Walkthrough talk at MariaDB Fest 2020:
A tool for processing Optimizer Trace: opttrace . Doesn't work with MariaDB at the moment but everyone is welcome to make it work.
This page is licensed: CC BY-SA / Gnu FDL