Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.12
-
Fix Version/s: 10.0.13
-
Component/s: None
-
Labels:None
Description
We've ported "filesort with small limit" optimization from mysql-5.6 into MariaDB 10.0.
The problem is, it is impossible to see whether that optimization is used or not.
In MySQL, one can check this as follows
- set optimizer_trace=1
- SELECT // need to run SELECT, not EXPLAIN
- select * from optimizer_trace
And then look for something like this:
"join_execution": {
...
"filesort_priority_queue_optimization": {
"limit": 10,
"rows_estimate": 198717,
"row_size": 215,
"memory_available": 262144,
"chosen": true
},
In MariaDB, there is no optimizer_trace, so one can't see it at all.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Status variables do not have a counter for PQ-based sorting:
MariaDB [j61]> show status like '%sort%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 10000 | | Sort_scan | 1 | +-------------------+-------+ 4 rows in set (0.00 sec)
Slow query log entry (with log_slow_verbosity=query_plan,explain):
# Time: 140708 18:50:08 # User@Host: root[root] @ localhost [] # Thread_id: 3 Schema: j61 QC_hit: No # Query_time: 4.110269 Lock_time: 0.004791 Rows_sent: 10 Rows_examined: 100010 # Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No # Filesort: Yes Filesort_on_disk: No Merge_passes: 0 # # explain: id select_type table type possible_keys key key_len ref rows Extra # explain: 1 SIMPLE t5 ALL NULL NULL NULL NULL 98496 Using filesort # use j61; SET timestamp=1404831008; select * from t5 order by a limit 10;
" Filesort: Yes " comes from "log_slow_verbosity=explain", which originally comes from "extended statistics in the slow query log" from Percona Server. It may be natural to extend it to show whether PQ-based sorting was used.
Decisions after discussion: will both add a status counter and a slow query log entry.
Slow query log entries will look like this:
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: Yes|No
pt-query-digest is able to parse this.
Status variable name will be:
Sort_priority_queue_sorts
It would be logical if the output was reflected in EXPLAIN. The problem is that both MySQL and MariaDB make the decision about whether to use PQ at a very late phase:
EXPLAIN execution does not go through filesort()/check_if_pq_applicable(). In order to check whether we're using PQ in EXPLAIN, one would need to do some code re-structuring.