How does pt-online-schema-change drop old table?

Polyglot Projects Percona Toolkit
1

Hi:
mysql5.7
percona_tool 3.4

/bin/pt-online-schema-change --host=${host} --port=${port} --user=${user} --password=${passwd} --recursion-method=processlist --check-interval=1 --chunk-time=1 --max-lag=1 --no-version-check --charset=“utf8” --max-load=threads_running:100 --chunk-size=2000 D=${database},t=${table}
–alter=“ENGINE=InnoDB” --print --execute
echo date +"%Y-%m-%d %H:%M:%S"
echo ‘>>>>>>>>>>>>>>>>End DDL>>>>>>>>>>>>>>>>’

some of the log as:
Copying wxashop.wxa_user_coupon: 96% 03:19 remain
Copying wxashop.wxa_user_coupon: 96% 02:50 remain
Copying wxashop.wxa_user_coupon: 97% 02:20 remain
Copying wxashop.wxa_user_coupon: 97% 01:50 remain
Copying wxashop.wxa_user_coupon: 98% 01:20 remain
Copying wxashop.wxa_user_coupon: 99% 00:51 remain
Copying wxashop.wxa_user_coupon: 99% 00:23 remain
2023-09-04T18:40:09 Copied rows OK.
2023-09-04T18:40:09 Analyzing new table…
2023-09-04T18:40:09 Swapping tables…
RENAME TABLE wxashop.wxa_user_coupon TO wxashop._wxa_user_coupon_old, wxashop._wxa_user_coupon_new TO wxashop.wxa_user_coupon
2023-09-04T18:40:09 Swapped original and new tables OK.
2023-09-04T18:40:09 Dropping old table…
DROP TABLE IF EXISTS wxashop._wxa_user_coupon_old
2023-09-04T18:40:10 Dropped old table wxashop._wxa_user_coupon_old OK.
2023-09-04T18:40:10 Dropping triggers…
DROP TRIGGER IF EXISTS wxashop.pt_osc_wxashop_wxa_user_coupon_del
DROP TRIGGER IF EXISTS wxashop.pt_osc_wxashop_wxa_user_coupon_upd
DROP TRIGGER IF EXISTS wxashop.pt_osc_wxashop_wxa_user_coupon_ins
2023-09-04T18:40:10 Dropped triggers OK.
Successfully altered wxashop.wxa_user_coupon.
2023-09-04 18:40:10

End DDL>>>>>>>>>>>>>>>>

2023-09-04T18:40:09 Dropping old table… There is a 4-second delay on the slave database relative to the master database.

Under the same environment:
When I execute the command mysql -h 172.1.1.7 -P3306 -uuser -ppassword -e ‘DROP TABLE IF EXISTS wxashop._wxa_user_coupon_old;’, there is a delay of 12 seconds.

How does pt-online-schema-change achieve it?

2

pt-osc isn’t doing anything magic. It executes the same query as you are. You are using an inaccurate measurement of lag (Seconds_behind_master). Replica lag can be caused by any number of transactions running on the replica.
Is your replica the same spec hardware? Same my.cnf config? Are you running parallel replica threads?
If you want more accurate timing, run pt-heartbeat with parallel replica workers.
You can also run pt-osc using PTDEBUG=1 pt-online-schema-change ... and see all the exact SQL being executed.

1 Like
3

Hi:
I try to get the source code about it , you are right .

Think you!!