ANBOB™

在MySQL中有些DDL操作可能会导致表重组,如<“alter table ” modify column in Oracle、MySQL、PostGreSQL(数据库比较系列十三)>,如果表较大时，可能重组需要等待很久的时间，有没有什么方法可以跟踪一下DDL的进度呢？

1,  数据库内

设置setup_instruments和setup_consumers

UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME in ('events_stages_current','events_stages_history');

查询DDL 进度

select 
  event_name, work_completed, work_estimated, duration_sec, remainder_sec
from (
  select
    esh.event_name, esh.work_completed, esh.work_estimated, truncate(esh.timer_wait/1000000000000,2) duration_sec, t.processlist_info, t.processlist_id, 0 as remainder_sec
  from 
    performance_schema.events_stages_history esh
    join performance_schema.events_stages_current esc on esh.thread_id = esc.thread_id and esh.nesting_event_id = esc.nesting_event_id
    join performance_schema.threads t on esc.thread_id = t.thread_id
  where esh.event_name like "stage/innodb/alter%"
  union all
  select
    esc.event_name, esc.work_completed, esc.work_estimated, truncate(esc.timer_wait/1000000000000,2) duration_sec , t.processlist_info, t.processlist_id, truncate((esc.timer_wait/(esc.work_completed/esc.work_estimated) - esc.timer_wait)/1000000000000,2) remainder_sec
  from 
    performance_schema.events_stages_current esc join performance_schema.threads t on esc.thread_id = t.thread_id
  where esc.event_name like "stage/innodb/alter%"
) t1
where  
 t1.processlist_info like "alter%";

一个DDL执行过程包括下面几个主要阶段:
1.stage/innodb/alter table (read PK and internal sort)，读取主键（聚集索引），计算需要处理的data page数；
2.stage/innodb/alter table (merge sort)，处理ALTER TABLE影响的索引，每个索引跑一次（含主键索引）；
3.stage/innodb/alter table (insert)，同上；
4.stage/innodb/alter table (log apply index)，将执行DDL期间新增的DML操作应用到index上；
5.stage/innodb/alter table (flush)，flush阶段；
6.stage/innodb/alter table (log apply table)，将执行DDL期间新增的DML操作应用到table上；
7.stage/innodb/alter table (end)，收尾阶段。

2， 操作系统文件大小

重建的表的大小的方式，查看DDL执行的进度

脚本来自 https://github.com/slowtech/dba-toolkit/blob/master/mysql/monitor_ddl_progress.sh

#!/bin/bash

if [ $# -ne 2 ];then
  echo "sh $0 table_directory table_name"
  echo "Usage:sh $0 /var/lib/mysql/db1 t1"
  exit
fi

table_directory=$1
target_table=$2

function get_file_size() {
    local file=$1
    file_size=`stat -c '%s' $file 2>/dev/null`
    echo $file_size 
}

target_table_file="$table_directory"/"$target_table".ibd

if [[ ! -f "$target_table_file" ]]
then
    echo "The $target_table.ibd does not exist in $table_directory !!!" 
    exit 
fi

target_table_file_size=`get_file_size "$target_table_file"`
db_name=`basename "$table_directory"`

intermediate_table_file=`ls "$table_directory"/"#sql"*".ibd" 2>/dev/null`

if [[ -z "$intermediate_table_file" ]]
then
    echo "Can not find the intermediate table for $target_table.ibd,Maybe the DDL has not started yet"
        exit
fi
last_intermediate_table_file_size=`get_file_size "$intermediate_table_file"`

echo "Altering $db_name.$target_table ..."

while true
do
        sleep 10
    intermediate_table_file_size=`get_file_size "$intermediate_table_file"`
    if [[ -z "$intermediate_table_file_size" ]]
    then
        echo "Successfully altered $db_name.$target_table"
            exit
    fi
        percent=`echo "$intermediate_table_file_size*100/$target_table_file_size" | bc`
        if [[ "$percent" -gt 100 ]]
        then
        percent=100
        fi
        alter_speed=`echo "scale=2;($intermediate_table_file_size-$last_intermediate_table_file_size)/10" | bc`
        remain_second=`echo "($target_table_file_size-$intermediate_table_file_size)/$alter_speed" |bc `
        if [[ "$remain_second" -lt 0 ]]
        then
        remain_second=0
    fi
        remain_time=`date -u -d @$remain_second +"%T"`
        echo "Altering $db_name.$target_table:  $percent% $remain_time remain"
        last_intermediate_table_file_size=$intermediate_table_file_size
done


使用方法
# sh monitor_ddl.sh   /data/mysql/data/xxx  test_table

References

https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html

打赏

微信扫一扫，打赏作者吧～