如何观测MySQL DDL的进度
-- 查看相关的PS监控指标是否开启
SELECT *
FROM PERFORMANCE_SCHEMA.SETUP_INSTRUMENTS
WHERE NAME LIKE 'stage/innodb/alter%';
SELECT *
FROM PERFORMANCE_SCHEMA.SETUP_CONSUMERS
WHERE NAME LIKE '%stages%';
-- 可动态开启相关指标
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的执行进度,预估时间
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\G
转载请说明出处
|QQ:327488733@qq.com
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步