a.object_schema as locked_schema, a.object_name as locked_table, 'metadata lock'as locked_type, c.processlist_id as waiting_processlist_id, c.processlist_time as waiting_age, c.processlist_info as waiting_query, c.processlist_state as waiting_state, d.processlist_id as blocking_processlist_id, d.processlist_time as blocking_age, d.processlist_info as blocking_query, concat('kill ', d.processlist_id) as sql_kill_blocking_connection from performance_schema.metadata_locks a join performance_schema.metadata_locks b on a.object_schema = b.object_schema and a.object_name = b.object_name and a.lock_status ='pending' and b.lock_status ='granted' and a.owner_thread_id <> b.owner_thread_id and a.lock_type ='exclusive' join performance_schema.threads c on a.owner_thread_id = c.thread_id join performance_schema.threads d on b.owner_thread_id = d.thread_id;
select concat('kill ', i.trx_mysql_thread_id, ';') from information_schema.innodb_trx i, ( selectmax(time) as max_time from information_schema.processlist where state ='waiting for table metadata lock' and (info like'alter%' or info like'create%' or info like'drop%' or info like'truncate%' or info like'rename%' )) p where timestampdiff(second, i.trx_started, now()) > p.max_time;
MySQL5.6
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
SELECT concat('kill ', i.trx_mysql_thread_id, ';') FROM information_schema.innodb_trx i, ( SELECTMAX(time) AS max_time FROM information_schema.processlist WHERE state ='Waiting for table metadata lock' AND (info LIKE'alter%' OR info LIKE'create%' OR info LIKE'drop%' OR info LIKE'truncate%' OR info LIKE'rename%' )) p WHERE timestampdiff(second, i.trx_started, now()) > p.max_time;
select object_type, object_schema, object_name, lock_type, lock_status, thread_id, processlist_id, processlist_info from performance_schema.metadata_locks innerjoin performance_schema.threads on thread_id = owner_thread_id where processlist_id <> connection_id(); select*from information_schema.innodb_trx; showfull processlist
六、MySQL5.7跟踪DDL进度
启用跟踪配置可以动态开启
1 2 3 4 5 6 7
update performance_schema.setup_instruments set enabled ='yes' where name like'stage/innodb/alter%';
update performance_schema.setup_consumers set enabled ='yes' where name like'%stages%';
查看DDL执行进度
1 2 3 4 5 6 7 8 9 10 11 12 13
select stmt.sql_text, stage.event_name, concat(work_completed, '/', work_estimated) as progress, concat(round(100* work_completed / work_estimated, 2), ' %') as processing_pct, sys.format_time(stage.timer_wait) as time_costs, concat(round((stage.timer_end - stmt.timer_start) /1e12* (work_estimated - work_completed) / work_completed, 2), ' s') as remaining_seconds from performance_schema.events_stages_current stage, performance_schema.events_statements_current stmt where stage.thread_id = stmt.thread_id and stage.nesting_event_id = stmt.event_id
