Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
customer is asking a way to identify the stored procedure name in `show processlist`.
show processlist only shows the currently running statement inside of stored process instead of `call <sp name>...`. If that is doable, it would be nice for a customer can identify which sp is running.
Attachments
Activity
We should not add new columns to SHOW PROCESSLIST as that can mess up things for users. 'Info' is already used for information about the current statement, so we should not use this one.
Adding a new column, Function or Call_stack, to show processlist would be a better option.
The question is what to add there.
If we want to have a 'full' solution, we could have the full call stack:
- Empty if not a stored procedure or function.
- In other cases the full stack like: First_sp->second_sp->third_sp ; Up to 255 characters.
The above could be done by adding a 'call_stack[256] string in thd and append the
sp name with -> to the string on each call. The overhead of copying the sp name and zerofilling it for each call should be neglectable.
As long as the not used part of the string is zero-filled, this should be 'reasonable' thread safe and we would not need a mutex to update the string. With reasonable, I mean that there is small change one only get part of the last stored procedure name if the thread is filling the stack while 'show processlist' is reading it.
Note that just having the name of the current SP in THD can also cause 'half sp names' if another thread is reading the string while we are writing to it. As long as the name buffer or stack is zero filled we should still be ok here.
In theory we could have an 'unlimited' call stack, but that would force us to have a mutex for every SP call (because of potential mallocs), which is too much overhead
This mtr test file shows how to show a stack trace and how to do a "show processlist" like query with procedure names
source include/have_perfschema.inc;
|
set max_sp_recursion_depth=@@global.performance_schema_max_statement_stack; |
|
|
delimiter |;
|
create procedure p(x int) |
begin
|
if x < 7 then |
call p(x+1);
|
else |
select get_lock('in', 5); |
end if; |
end| |
delimiter ;|
|
|
|
connect con1,localhost,root; |
select get_lock('in', 5); |
connection default; |
send call p(1);
|
connection con1; |
sleep 1;
|
select nesting_event_id,object_name,sql_text from performance_schema.events_statements_current where thread_id=7; |
select thread_id,processlist_state,processlist_info,object_type,object_name from performance_schema.threads left join performance_schema.events_statements_current using (thread_id) where type='foreground' and sql_text=processlist_info; |
connection default; |
reap;
|
drop procedure p; |
So this task can be done with a new sys schema view. Or two, if one wants to see a stack trace too.
Of course the name of the procedure can be found, probably even all stack of calls (do they need it?, if no which procedure they want bottom-most of top-most?)