{question}
How can I collect a profile for a query that is not completed or I think is hung?
{question}
{answer}
When a query is running longer than expected for any reason, you can still capture a profile of it, but since a query profile executes the query which generating the profile, it means that the profile of the long-running query will also run long or never complete and you cannot get the profile output.
However, there is a workaround for this. You can collect a partial query PROFILE. A partial profile is all the query execution data or work that has been performed while executing the query up to the point of you collecting the partial profile.
To collect a partial profile, use the following steps:
Step 1) Execute PROFILE query_text on the database.
Step 2) Wait 2 minutes or so, and open a second session to host that is home to the aggregator node that the profile query was ran on, and then run the command below to collect the profile in the second connection. Be sure to populate the correct connection parameters (HOST/PORT/USER/PASSWORD):
memsql -h HOST -P PORT -u USER -pPASSWORD -e "show profile JSON process $(memsql --skip-column-names -e "select ID from information_schema.processlist where STATE = 'executing' and INFO like 'PROFILE%'");" > partial_profile.json
Step 3) Once you have the partial profile, kill the original profile query in the first connection with the KILL QUERY command, so it doesn't impact memory usage on the cluster. You can then use the partial profile to see what the query has done so far in its query execution and look for issues or bottlenecks.
{answer}