-- 현재 performance_schema 저장?
call sys.ps_setup_save(10);
-- do before query profiling
update performance_schema.setup_instruments
set enabled = 'YES', timed = 'YES'
where name like '%statement/%' or name like '%stage/%';
--where name like 'stage/innodb/alter%'; /* when you want to profiling alter */
update performance_schema.setup_consumers
set enabled = 'YES'
where name like '%events_statements_%' or name like '%evens_stages_%';
--where name like '%stages%'; /* when you want to profiling alter */
select /* execute query what you want to profiling */ 1;
select
event_id into @eid
--,sql_text
--,sys.format_time(timer_wait) as "duration"
from performance_schema.events_statements_history_long
where sql_text like '%execute query what you want to profiling%'
--order by timer_wait desc limit 1
;
select event_name as "stage",
sys.format_time(timer_wait) as "duration",
from performance_schema.events_stages_history_long
where nesting_event_id = @eid
order by timer_start;
-- alter 작업 진행률 확인
select ps_estc.nesting_event_id,
ps_esmc.sql_text,
ps_estc.event_name,
ps_estc.work_completed,
ps_estc.work_estimated,
round((work_completed/work_estimated)*100,2) as "PROGRESS(%)"
from performance_schema.events_stages_current ps_estc
innder join performance_schema.events_statements_current ps_esmc
on ps_estc.nesting_event_id = ps_esmc.event_id
where ps_estc.event_name like 'stage/innodb/alter%' \G;