Looking for root cause for the following problem:
A query which comes from a generated report used to take seconds and now takes 21 min (tested in house on MariaDB ES 10.6.5, in MySQL 5.6 it took 9.611s Customer is having the issue in 10.2.27 .)
The customer found that by setting the optimizer_search_depth=10 the amount of time is similar to original run times. They tested 11 through 15 as well. They noticed `optimizer_search_depth=10` performed identically to `optimizer_search_depth=0` and `optimizer_search_depth=7`. However, `optimizer_search_depth=11` started to show degraded performance. They saw that at `optimizer_search_depth=15`, there was no, discernible difference between that and the default `optimizer_search_depth=62`.
Below are the steps to reproduce the issue.
The files are included in CS0379791-March102022.tar.gz in the case.
1. Provision MariaDB enterprise 10.6.5-2 (customer had issue with 10.2 and 10.4 DB) using server.cnf.txt (modified version of customers xpo_testcase_my.cnf).
2. Connect to MariaDB instance.
3. create database discard;
4. use discard;
5. source xpo_full_testcase.sql;
6. source xpo_testcase_explain.sql.
Optimizer trace with default optimzer_search_depth takes 12 hours and we were unable to capture the data.
However, setting optimizer_search_depth to 3 allowed the capture data from an optimizer trace see optimizer_output_depth3 .
Relevant files are attached to case CS0379791 as CS0379791-March102022.tar.gz
1). optimizer_search_depth_scaling.png - graph of time is takes to run the query when setting optimizer_search_depth = [1-25]
2). server.cnf.txt - config file used by support when reproducing the issue
3). xpo_testcase_my.cnf - config file usde by customer to reproduce the issue
4). xpo_testcase_explain.sql - explain of the query in question
5). xpo_full_testcase.sql - data dump to reproduce the issue with
6). optimizer_output_depth3 - optimzer trace with optimizer_search_depth = 3
7). CS0379791_output/ - flamegraph output including perf output and more
8). explain_json.txt - Additionally the customer has attached output from explain json from customer