SHOW EXPLAIN allows one to view EXPLAIN of a currently executing query.
However, it doesn't handle this use case:
A really huge query runs for a long time and never finishes. Since it never finishes, one cannot view ANALYZE output. Adding "LIMIT N" to the top query is of limited help.
One can view the EXPLAIN, but the query has a lot of subqueries that have many-table joins. One can see multiple sub-optimal access methods, and it is not clear which of them are the ones that are causing the performance problem.
Suggested solution: extend SHOW EXPLAIN to report SHOW ANALYZE FORMAT=JSON data of the query that's currently running.
Attachments
Issue Links
causes
MDEV-28124Server crashes in Explain_aggr_filesort::print_json_members upon SHOW ANALYZE/EXPLAIN FORMAT=JSON
Closed
MDEV-28125Server crashes in Explain_table_access::tag_to_json upon SHOW EXPLAIN FORMAT=JSON
Closed
MDEV-28201Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON
2. How to name this field and where to place it? Is it OK to place it in the top of the output like this:
SHOW ANALYZE: {
"r_query_running_time_ms": 5432
"query_block": {
As agreed on Slack: Let it be r_time_in_progress_ms at the top level. For non-ANALYZE execution, there's a question of where to get the query start time. Please check where INFORMATION_SCHEMA.PROCESSLIST gets it from.
Sergei Petrunia
added a comment -
2. How to name this field and where to place it? Is it OK to place it in the top of the output like this:
SHOW ANALYZE: {
"r_query_running_time_ms": 5432
"query_block": {
As agreed on Slack: Let it be r_time_in_progress_ms at the top level. For non-ANALYZE execution, there's a question of where to get the query start time. Please check where INFORMATION_SCHEMA.PROCESSLIST gets it from.
MDEV-27021 Add explicit indication of SHOW EXPLAIN/ANALYZE.
1. Add explicit indication that the output is produced by
SHOW EXPLAIN/ANALYZE FORMAT=JSON command.
2. Remove useless "r_total_time_ms" field from SHOW ANALYZE FORMAT=JSON
output when there is no timed statistics gathered.
3. Add "r_query_time_in_progress_ms" to the output of SHOW ANALYZE FORMAT=JSON.
}}
Oleg Smirnov
added a comment - Pushed a new commit addressing the issues discussed.
{{commit a335245bbf9c49327d8b34ac30e3ad49822a5aa3 (HEAD > bb-10.8 MDEV-10000 , origin/bb-10.8- MDEV-10000 )
Author: Oleg Smirnov <olernov@gmail.com>
Date: Wed Feb 16 13:03:46 2022 +0700
MDEV-27021 Add explicit indication of SHOW EXPLAIN/ANALYZE.
1. Add explicit indication that the output is produced by
SHOW EXPLAIN/ANALYZE FORMAT=JSON command.
2. Remove useless "r_total_time_ms" field from SHOW ANALYZE FORMAT=JSON
output when there is no timed statistics gathered.
3. Add "r_query_time_in_progress_ms" to the output of SHOW ANALYZE FORMAT=JSON.
}}
psergei , please review.
By the way, I haven't fixed test cases yet, so some tests will fail. Once we're happy with the results I'll fix the test cases accordingly.
Oleg Smirnov
added a comment - By the way, I haven't fixed test cases yet, so some tests will fail. Once we're happy with the results I'll fix the test cases accordingly.
Nuno
added a comment - - edited Hey
Nice to see this being implemented!
Here's a related post:
"extend explain output to include "attached_condition""
https://jira.mariadb.org/browse/MDEV-27146
Thanks!
People
Alice Sherepa
Sergei Petrunia
Votes:
0Vote for this issue
Watchers:
9Start watching this issue
Dates
Created:
Updated:
Resolved:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":949,"ttfb":216.90000009536743,"pageVisibility":"visible","entityId":104927,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"b5bd4e9b-ba3d-41d3-a83b-3b4989653f7b","navigationType":0,"readyForUser":1034.0999999046326,"redirectCount":0,"resourceLoadedEnd":625.6999998092651,"resourceLoadedStart":224.09999990463257,"resourceTiming":[{"duration":11.300000190734863,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":224.09999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":224.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":235.40000009536743,"responseStart":0,"secureConnectionStart":0},{"duration":13.199999809265137,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":224.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":224.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":237.69999980926514,"responseStart":0,"secureConnectionStart":0},{"duration":274.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":224.69999980926514,"connectEnd":224.69999980926514,"connectStart":224.69999980926514,"domainLookupEnd":224.69999980926514,"domainLookupStart":224.69999980926514,"fetchStart":224.69999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":239.19999980926514,"responseEnd":499.19999980926514,"responseStart":254.09999990463257,"secureConnectionStart":224.69999980926514},{"duration":400.7999997138977,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":224.90000009536743,"connectEnd":224.90000009536743,"connectStart":224.90000009536743,"domainLookupEnd":224.90000009536743,"domainLookupStart":224.90000009536743,"fetchStart":224.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":241.2999997138977,"responseEnd":625.6999998092651,"responseStart":259,"secureConnectionStart":224.90000009536743},{"duration":30.40000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":225.09999990463257,"connectEnd":225.09999990463257,"connectStart":225.09999990463257,"domainLookupEnd":225.09999990463257,"domainLookupStart":225.09999990463257,"fetchStart":225.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":241.7999997138977,"responseEnd":255.5,"responseStart":254.7999997138977,"secureConnectionStart":225.09999990463257},{"duration":36.40000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":225.2999997138977,"connectEnd":225.2999997138977,"connectStart":225.2999997138977,"domainLookupEnd":225.2999997138977,"domainLookupStart":225.2999997138977,"fetchStart":225.2999997138977,"redirectEnd":0,"redirectStart":0,"requestStart":244.19999980926514,"responseEnd":261.69999980926514,"responseStart":260.69999980926514,"secureConnectionStart":225.2999997138977},{"duration":40.799999713897705,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":225.5,"connectEnd":225.5,"connectStart":225.5,"domainLookupEnd":225.5,"domainLookupStart":225.5,"fetchStart":225.5,"redirectEnd":0,"redirectStart":0,"requestStart":245.7999997138977,"responseEnd":266.2999997138977,"responseStart":265.2999997138977,"secureConnectionStart":225.5},{"duration":20.59999990463257,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":225.59999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":225.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":246.19999980926514,"responseStart":0,"secureConnectionStart":0},{"duration":42.5,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":225.7999997138977,"connectEnd":225.7999997138977,"connectStart":225.7999997138977,"domainLookupEnd":225.7999997138977,"domainLookupStart":225.7999997138977,"fetchStart":225.7999997138977,"redirectEnd":0,"redirectStart":0,"requestStart":248.5,"responseEnd":268.2999997138977,"responseStart":267.2999997138977,"secureConnectionStart":225.7999997138977},{"duration":21.90000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":226,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":226,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":247.90000009536743,"responseStart":0,"secureConnectionStart":0},{"duration":45.69999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":226.09999990463257,"connectEnd":226.09999990463257,"connectStart":226.09999990463257,"domainLookupEnd":226.09999990463257,"domainLookupStart":226.09999990463257,"fetchStart":226.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":251,"responseEnd":271.7999997138977,"responseStart":268.5,"secureConnectionStart":226.09999990463257},{"duration":369,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":236.69999980926514,"connectEnd":236.69999980926514,"connectStart":236.69999980926514,"domainLookupEnd":236.69999980926514,"domainLookupStart":236.69999980926514,"fetchStart":236.69999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":300.19999980926514,"responseEnd":605.6999998092651,"responseStart":599.0999999046326,"secureConnectionStart":236.69999980926514},{"duration":371.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":236.7999997138977,"connectEnd":236.7999997138977,"connectStart":236.7999997138977,"domainLookupEnd":236.7999997138977,"domainLookupStart":236.7999997138977,"fetchStart":236.7999997138977,"redirectEnd":0,"redirectStart":0,"requestStart":313.19999980926514,"responseEnd":608.2999997138977,"responseStart":601.5999999046326,"secureConnectionStart":236.7999997138977},{"duration":238.5,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":634.0999999046326,"connectEnd":634.0999999046326,"connectStart":634.0999999046326,"domainLookupEnd":634.0999999046326,"domainLookupStart":634.0999999046326,"fetchStart":634.0999999046326,"redirectEnd":0,"redirectStart":0,"requestStart":835,"responseEnd":872.5999999046326,"responseStart":869.4000000953674,"secureConnectionStart":634.0999999046326}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":35,"responseStart":217,"responseEnd":236,"domLoading":220,"domInteractive":1148,"domContentLoadedEventStart":1148,"domContentLoadedEventEnd":1213,"domComplete":3160,"loadEventStart":3160,"loadEventEnd":3160,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1116.4000000953674},{"name":"bigPipe.sidebar-id.end","time":1117.2999997138977},{"name":"bigPipe.activity-panel-pipe-id.start","time":1117.5},{"name":"bigPipe.activity-panel-pipe-id.end","time":1119.1999998092651},{"name":"activityTabFullyLoaded","time":1232.5}],"measures":[],"correlationId":"8630953411a19e","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":112,"dbReadsTimeInMs":24,"dbConnsTimeInMs":33,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Task
Major
MDEV-28124 Server crashes in Explain_aggr_filesort::print_json_members upon SHOW ANALYZE/EXPLAIN FORMAT=JSON
As agreed on Slack: Let it be r_time_in_progress_ms at the top level. For non-ANALYZE execution, there's a question of where to get the query start time. Please check where INFORMATION_SCHEMA.PROCESSLIST gets it from.