{question}
What is a Debug Profile?
How do I collect a Debug Profile?
{question}
{answer}
A Debug Profile is a verbose query profile that is available in version 7.3 and later.
When profiling a query you execute it, unlike EXPLAIN.
The Debug Profile includes all the information required to analyze a query performance issue.
It contains:
- DDL for all database objects relevant to the query.
- Global variable settings.
- Session variable settings.
- Table statistics metadata.
- Sampling cache for filter selectivities.
- Sampling cache for row counts.
- Autostats results cache.
To collect a Debug Profile, you need to do the following steps:
-- Run the following command if you are using SingleStore Studio,
-- it will remove the default row limit of 300. SET sql_select_limit = 18446744073709551615;
SET profile_for_debug = on;
Then you collect the JSON profile using: (we run PROFILE command twice to avoid the additional compilation time in the second run)
PROFILE query_text;
PROFILE query_text;
SHOW PROFILE JSON;
Please note that profile_for_debug is a session variable that only applies to the SHOW PROFILE JSON operation. Due to the length of some query profiles, you may want to direct the profile to an outfile with the INTO OUTFILE '/path' option.
If you use the SingleStore Studio, you can just click on the Save as CSVbutton.
Example:
PROFILE query_text;
PROFILE query_text;
SHOW PROFILE JSON INTO OUTFILE '/tmp/debug_profile.json';
In case of Out of Memory error or when the query is prefixed with the PROFILE keyword, you can collect the debug profile using the following statements:show profile json; after the query finished or
show profile json process <process id> if the query is still running
Additionally show profile ui; can be used to generate a URL that leads to our Visual Explain tool. Similarly in Helios the Visual Explain tool is available within the Portal user interface.
{answer}