PT OSC execution log
In the DROP COLUMN operation for table tb004 with data volume of 100000, the PT OSC tool log is:
Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `db001`.`tb004`... Creating new table... Created new table db001._tb004_new OK. Altering new table... Altered `db001`.`_tb004_new` OK. 2019-07-10T18:15:03 Creating triggers... 2019-07-10T18:15:04 Created triggers OK. 2019-07-10T18:15:04 Copying approximately 89411 rows... 2019-07-10T18:15:06 Copied rows OK. 2019-07-10T18:15:06 Analyzing new table... 2019-07-10T18:15:07 Swapping tables... 2019-07-10T18:15:07 Swapped original and new tables OK. 2019-07-10T18:15:07 Dropping old table... 2019-07-10T18:15:07 Dropped old table `db001`.`_tb004_old` OK. 2019-07-10T18:15:07 Dropping triggers... 2019-07-10T18:15:07 Dropped triggers OK. Successfully altered `db001`.`tb004`.
The SQL commands executed by the PT OSC tool for MySQL are:
2019-07-10T18:15:04.271383+08:00 164 Query set autocommit=1 2019-07-10T18:15:04.272720+08:00 164 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout' 2019-07-10T18:15:04.276481+08:00 164 Query SET SESSION innodb_lock_wait_timeout=1 2019-07-10T18:15:04.277772+08:00 164 Query SHOW VARIABLES LIKE 'lock\_wait_timeout' 2019-07-10T18:15:04.280380+08:00 164 Query SET SESSION lock_wait_timeout=60 2019-07-10T18:15:04.281840+08:00 164 Query SHOW VARIABLES LIKE 'wait\_timeout' 2019-07-10T18:15:04.284424+08:00 164 Query SET SESSION wait_timeout=10000 2019-07-10T18:15:04.285704+08:00 164 Query SELECT @@SQL_MODE 2019-07-10T18:15:04.286965+08:00 164 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/ 2019-07-10T18:15:04.288318+08:00 164 Query SELECT @@server_id /*!50038 , @@hostname*/ 2019-07-10T18:15:04.292463+08:00 165 Connect admin@172.28.37.35 on db001 using TCP/IP 2019-07-10T18:15:04.293623+08:00 165 Query set autocommit=1 2019-07-10T18:15:04.294971+08:00 165 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout' 2019-07-10T18:15:04.298053+08:00 165 Query SET SESSION innodb_lock_wait_timeout=1 2019-07-10T18:15:04.299318+08:00 165 Query SHOW VARIABLES LIKE 'lock\_wait_timeout' 2019-07-10T18:15:04.302018+08:00 165 Query SET SESSION lock_wait_timeout=60 2019-07-10T18:15:04.303291+08:00 165 Query SHOW VARIABLES LIKE 'wait\_timeout' 2019-07-10T18:15:04.305993+08:00 165 Query SET SESSION wait_timeout=10000 2019-07-10T18:15:04.307228+08:00 165 Query SELECT @@SQL_MODE 2019-07-10T18:15:04.308402+08:00 165 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/ 2019-07-10T18:15:04.309580+08:00 165 Query SELECT @@server_id /*!50038 , @@hostname*/ 2019-07-10T18:15:04.310871+08:00 164 Query SHOW VARIABLES LIKE 'wsrep_on' 2019-07-10T18:15:04.313575+08:00 164 Query SHOW VARIABLES LIKE 'version%' 2019-07-10T18:15:04.316348+08:00 164 Query SHOW ENGINES 2019-07-10T18:15:04.317921+08:00 164 Query SHOW VARIABLES LIKE 'innodb_version' 2019-07-10T18:15:04.321005+08:00 164 Query SHOW VARIABLES LIKE 'innodb_stats_persistent' 2019-07-10T18:15:04.323618+08:00 164 Query SELECT @@SERVER_ID 2019-07-10T18:15:04.324841+08:00 164 Query SHOW GRANTS FOR CURRENT_USER() 2019-07-10T18:15:04.326061+08:00 164 Query SHOW FULL PROCESSLIST 2019-07-10T18:15:04.386880+08:00 164 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2019-07-10T18:15:04.389123+08:00 164 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2019-07-10T18:15:04.391521+08:00 164 Query SELECT CONCAT(@@hostname, @@port) 2019-07-10T18:15:04.394332+08:00 164 Query SHOW TABLES FROM `db001` LIKE 'tb004' 2019-07-10T18:15:04.395667+08:00 164 Query SHOW TRIGGERS FROM `db001` LIKE 'tb004' 2019-07-10T18:15:04.397242+08:00 164 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ 2019-07-10T18:15:04.398378+08:00 164 Query USE `db001` 2019-07-10T18:15:04.399527+08:00 164 Query SHOW CREATE TABLE `db001`.`tb004` 2019-07-10T18:15:04.400863+08:00 164 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ 2019-07-10T18:15:04.402364+08:00 164 Query EXPLAIN SELECT * FROM `db001`.`tb004` WHERE 1=1 2019-07-10T18:15:04.403883+08:00 164 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='db001' AND referenced_table_name='tb004' 2019-07-10T18:15:04.411418+08:00 164 Query SHOW VARIABLES LIKE 'wsrep_on' 2019-07-10T18:15:04.414122+08:00 164 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ 2019-07-10T18:15:04.415364+08:00 164 Query USE `db001` 2019-07-10T18:15:04.416546+08:00 164 Query SHOW CREATE TABLE `db001`.`tb004` 2019-07-10T18:15:04.417865+08:00 164 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ 2019-07-10T18:15:04.419206+08:00 164 Query CREATE TABLE `db001`.`_tb004_new` ( `id` varchar(20) NOT NULL, `C1` int(11) DEFAULT NULL, `DT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `c6` int(11) DEFAULT NULL, `c5` int(11) DEFAULT NULL, `c4` int(11) DEFAULT NULL, `c7` int(11) DEFAULT NULL, `c8` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 2019-07-10T18:15:04.624271+08:00 164 Query ALTER TABLE `db001`.`_tb004_new` drop column c8 2019-07-10T18:15:04.934773+08:00 164 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ 2019-07-10T18:15:04.936015+08:00 164 Query USE `db001` 2019-07-10T18:15:04.937303+08:00 164 Query SHOW CREATE TABLE `db001`.`_tb004_new` 2019-07-10T18:15:04.938540+08:00 164 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ 2019-07-10T18:15:04.940366+08:00 164 Query CREATE TRIGGER `pt_osc_db001_tb004_del` AFTER DELETE ON `db001`.`tb004` FOR EACH ROW DELETE IGNORE FROM `db001`.`_tb004_new` WHERE `db001`.`_tb004_new`.`id` <=> OLD.`id` 2019-07-10T18:15:04.995376+08:00 164 Query CREATE TRIGGER `pt_osc_db001_tb004_upd` AFTER UPDATE ON `db001`.`tb004` FOR EACH ROW BEGIN DELETE IGNORE FROM `db001`.`_tb004_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `db001`.`_tb004_new`.`id` <=> OLD.`id`;REPLACE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) VALUES (NEW.`id`, NEW.`c1`, NEW.`dt`, NEW.`c6`, NEW.`c5`, NEW.`c4`, NEW.`c7`);END 2019-07-10T18:15:05.055541+08:00 164 Query CREATE TRIGGER `pt_osc_db001_tb004_ins` AFTER INSERT ON `db001`.`tb004` FOR EACH ROW REPLACE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) VALUES (NEW.`id`, NEW.`c1`, NEW.`dt`, NEW.`c6`, NEW.`c5`, NEW.`c4`, NEW.`c7`) 2019-07-10T18:15:05.116027+08:00 164 Query EXPLAIN SELECT * FROM `db001`.`tb004` WHERE 1=1 2019-07-10T18:15:05.118327+08:00 164 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lower boundary*/ 2019-07-10T18:15:05.119873+08:00 164 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX (`PRIMARY`) WHERE `id` IS NOT NULL ORDER BY `id` LIMIT 1 /*key_len*/ 2019-07-10T18:15:05.121305+08:00 164 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `db001`.`tb004` FORCE INDEX (`PRIMARY`) WHERE `id` >= '1' /*key_len*/ 2019-07-10T18:15:05.122882+08:00 164 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/ 2019-07-10T18:15:05.124306+08:00 164 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/ 2019-07-10T18:15:05.126038+08:00 164 Query EXPLAIN SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '100897')) LOCK IN SHARE MODE /*explain pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:05.127575+08:00 164 Query INSERT LOW_PRIORITY IGNORE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '100897')) LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:05.211692+08:00 164 Query SHOW WARNINGS 2019-07-10T18:15:05.213153+08:00 164 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2019-07-10T18:15:05.215441+08:00 164 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '100898')) ORDER BY `id` LIMIT 5943, 2 /*next chunk boundary*/ 2019-07-10T18:15:05.216731+08:00 164 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '100898')) ORDER BY `id` LIMIT 5943, 2 /*next chunk boundary*/ 2019-07-10T18:15:05.220034+08:00 164 Query EXPLAIN SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '100898')) AND ((`id` <= '106246')) LOCK IN SHARE MODE /*explain pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:05.221516+08:00 164 Query INSERT LOW_PRIORITY IGNORE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '100898')) AND ((`id` <= '106246')) LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:05.602099+08:00 164 Query SHOW WARNINGS 2019-07-10T18:15:05.603631+08:00 164 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2019-07-10T18:15:05.605879+08:00 164 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '106247')) ORDER BY `id` LIMIT 7542, 2 /*next chunk boundary*/ 2019-07-10T18:15:05.607175+08:00 164 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '106247')) ORDER BY `id` LIMIT 7542, 2 /*next chunk boundary*/ 2019-07-10T18:15:05.610992+08:00 164 Query EXPLAIN SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '106247')) AND ((`id` <= '12331')) LOCK IN SHARE MODE /*explain pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:05.612558+08:00 164 Query INSERT LOW_PRIORITY IGNORE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '106247')) AND ((`id` <= '12331')) LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:06.292364+08:00 164 Query SHOW WARNINGS 2019-07-10T18:15:06.293864+08:00 164 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2019-07-10T18:15:06.296249+08:00 164 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '12332')) ORDER BY `id` LIMIT 6202, 2 /*next chunk boundary*/ 2019-07-10T18:15:06.297653+08:00 164 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '12332')) ORDER BY `id` LIMIT 6202, 2 /*next chunk boundary*/ 2019-07-10T18:15:06.301010+08:00 164 Query EXPLAIN SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '12332')) AND ((`id` <= '15422')) LOCK IN SHARE MODE /*explain pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:06.311117+08:00 164 Query INSERT LOW_PRIORITY IGNORE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '12332')) AND ((`id` <= '15422')) LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:06.448604+08:00 164 Query SHOW WARNINGS 2019-07-10T18:15:06.450120+08:00 164 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2019-07-10T18:15:06.452431+08:00 164 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '15423')) ORDER BY `id` LIMIT 8709, 2 /*next chunk boundary*/ 2019-07-10T18:15:06.453744+08:00 164 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '15423')) ORDER BY `id` LIMIT 8709, 2 /*next chunk boundary*/ 2019-07-10T18:15:06.457900+08:00 164 Query EXPLAIN SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '15423')) AND ((`id` <= '23261')) LOCK IN SHARE MODE /*explain pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:06.459481+08:00 164 Query INSERT LOW_PRIORITY IGNORE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '15423')) AND ((`id` <= '23261')) LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:06.622244+08:00 164 Query SHOW WARNINGS 2019-07-10T18:15:06.623716+08:00 164 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2019-07-10T18:15:06.626148+08:00 164 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '23262')) ORDER BY `id` LIMIT 12224, 2 /*next chunk boundary*/ 2019-07-10T18:15:06.627482+08:00 164 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '23262')) ORDER BY `id` LIMIT 12224, 2 /*next chunk boundary*/ 2019-07-10T18:15:06.632599+08:00 164 Query EXPLAIN SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '23262')) AND ((`id` <= '38324')) LOCK IN SHARE MODE /*explain pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:06.634037+08:00 164 Query INSERT LOW_PRIORITY IGNORE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '23262')) AND ((`id` <= '38324')) LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:06.850117+08:00 164 Query SHOW WARNINGS 2019-07-10T18:15:06.851574+08:00 164 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2019-07-10T18:15:06.854054+08:00 164 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '38325')) ORDER BY `id` LIMIT 16344, 2 /*next chunk boundary*/ 2019-07-10T18:15:06.855361+08:00 164 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '38325')) ORDER BY `id` LIMIT 16344, 2 /*next chunk boundary*/ 2019-07-10T18:15:06.861804+08:00 164 Query EXPLAIN SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '38325')) AND ((`id` <= '53034')) LOCK IN SHARE MODE /*explain pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:06.863334+08:00 164 Query INSERT LOW_PRIORITY IGNORE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '38325')) AND ((`id` <= '53034')) LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:07.143641+08:00 164 Query SHOW WARNINGS 2019-07-10T18:15:07.145231+08:00 164 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2019-07-10T18:15:07.147665+08:00 164 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '53035')) ORDER BY `id` LIMIT 20280, 2 /*next chunk boundary*/ 2019-07-10T18:15:07.148966+08:00 164 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '53035')) ORDER BY `id` LIMIT 20280, 2 /*next chunk boundary*/ 2019-07-10T18:15:07.156635+08:00 164 Query EXPLAIN SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '53035')) AND ((`id` <= '80422')) LOCK IN SHARE MODE /*explain pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:07.158179+08:00 164 Query INSERT LOW_PRIORITY IGNORE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '53035')) AND ((`id` <= '80422')) LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:07.546098+08:00 164 Query SHOW WARNINGS 2019-07-10T18:15:07.547632+08:00 164 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2019-07-10T18:15:07.550054+08:00 164 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '80423')) ORDER BY `id` LIMIT 22400, 2 /*next chunk boundary*/ 2019-07-10T18:15:07.551354+08:00 164 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '80423')) ORDER BY `id` LIMIT 22400, 2 /*next chunk boundary*/ 2019-07-10T18:15:07.559527+08:00 164 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) ORDER BY `id` DESC LIMIT 1 /*last upper boundary*/ 2019-07-10T18:15:07.560937+08:00 164 Query EXPLAIN SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '80423')) AND ((`id` <= '99999')) LOCK IN SHARE MODE /*explain pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:07.562464+08:00 164 Query INSERT LOW_PRIORITY IGNORE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '80423')) AND ((`id` <= '99999')) LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/ 2019-07-10T18:15:07.893310+08:00 164 Query SHOW WARNINGS 2019-07-10T18:15:07.894806+08:00 164 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2019-07-10T18:15:07.897361+08:00 164 Query ANALYZE TABLE `db001`.`_tb004_new` /* pt-online-schema-change */ 2019-07-10T18:15:07.965519+08:00 164 Query RENAME TABLE `db001`.`tb004` TO `db001`.`_tb004_old`, `db001`.`_tb004_new` TO `db001`.`tb004` 2019-07-10T18:15:08.223514+08:00 164 Query DROP TABLE IF EXISTS `db001`.`_tb004_old` 2019-07-10T18:15:08.362410+08:00 164 Query DROP TRIGGER IF EXISTS `db001`.`pt_osc_db001_tb004_del` 2019-07-10T18:15:08.392249+08:00 164 Query DROP TRIGGER IF EXISTS `db001`.`pt_osc_db001_tb004_upd` 2019-07-10T18:15:08.422315+08:00 164 Query DROP TRIGGER IF EXISTS `db001`.`pt_osc_db001_tb004_ins` 2019-07-10T18:15:08.445194+08:00 164 Query SHOW TABLES FROM `db001` LIKE '\_tb004\_new' 2019-07-10T18:15:08.447215+08:00 165 Quit 2019-07-10T18:15:08.447328+08:00 164 Quit
General checks and settings
## Check the number of execution threads SHOW GLOBAL STATUS LIKE 'Threads_running' ## Get from library information SHOW FULL PROCESSLIST ## Check if it is a PXC cluster SHOW VARIABLES LIKE 'wsrep_on' ## Check if the table has triggers SHOW TRIGGERS FROM `db001` LIKE 'tb004' ## Check whether the table has foreign key SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='db001' AND referenced_table_name='tb004' ## Set session timeout and lock timeout SET SESSION wait_timeout=10000 SET SESSION lock_wait_timeout=60 ## View the original table creation statement SHOW CREATE TABLE `db001`.`tb004`
Create trigger
## Delete action trigger CREATE TRIGGER `pt_osc_db001_tb004_del` AFTER DELETE ON `db001`.`tb004` FOR EACH ROW DELETE IGNORE FROM `db001`.`_tb004_new` WHERE `db001`.`_tb004_new`.`id` <=> OLD.`id`; ## Update action trigger CREATE TRIGGER `pt_osc_db001_tb004_upd` AFTER UPDATE ON `db001`.`tb004` FOR EACH ROW BEGIN DELETE IGNORE FROM `db001`.`_tb004_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `db001`.`_tb004_new`.`id` <=> OLD.`id`; REPLACE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) VALUES (NEW.`id`, NEW.`c1`, NEW.`dt`, NEW.`c6`, NEW.`c5`, NEW.`c4`, NEW.`c7`); END ## Insert action trigger CREATE TRIGGER `pt_osc_db001_tb004_ins` AFTER INSERT ON `db001`.`tb004` FOR EACH ROW REPLACE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) VALUES (NEW.`id`, NEW.`c1`, NEW.`dt`, NEW.`c6`, NEW.`c5`, NEW.`c4`, NEW.`c7`)
First data copy
Before the first data copy, execute the following EXPLAIN statement to obtain the table data volume:
EXPLAIN SELECT * FROM `db001`.`tb004` WHERE 1=1
If the amount of table data is small, select to copy all data to the new table through a full table scan:
2019-07-10T17:34:46.226165+08:00 154 Query EXPLAIN SELECT * FROM `db001`.`tb002` WHERE 1=1 2019-07-10T17:34:46.339284+08:00 154 Query EXPLAIN SELECT `c1`, `c2`, `c3` FROM `db001`.`tb002` LOCK IN SHARE MODE /*explain pt-online-schema-change 19569 copy table*/ 2019-07-10T17:34:46.340637+08:00 154 Query INSERT LOW_PRIORITY IGNORE INTO `db001`.`_tb002_new` (`c1`, `c2`, `c3`) SELECT `c1`, `c2`, `c3` FROM `db001`.`tb002` LOCK IN SHARE MODE /*pt-online-schema-change 19569 copy table*/
If the amount of table data exceeds a certain threshold, select to copy the data to the new table through N range scans.
When neither chunk size nor chunk time is specified, the default value of chunk size is 1000, and the default value of chunk time is 0.5S. For the first time, the data is copied according to chunk size, and then the chunk size is dynamically adjusted according to the time of the first copy to adapt to the performance change of the server. If 1000 rows of the last copy consume 0.1S, the next time the chunk size is dynamically adjusted to 5000.
1. Obtain the minimum value and row length of the first range scan:
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lower boundary*/ SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX (`PRIMARY`) WHERE `id` IS NOT NULL ORDER BY `id` LIMIT 1 /*key_len*/
2. Obtain the maximum value of the first range scan and the minimum value of the second range scan:
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 999, 2
3. Copy data to the new table:
INSERT LOW_PRIORITY IGNORE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '100897')) LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/
4. Adjust the chumk size of the next operation according to the operation duration in step 3. Because the first time to copy 1000 pieces of data is shorter, the chumk size of the second time to copy data is updated to 5943.
5. Obtain the maximum value of the second range scan and the minimum value of the third range scan:
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '100898')) ORDER BY `id` LIMIT 5943, 2
6. Data replication from the second cycle to the nth cycle.
Last data copy
1. Obtain the maximum value of this range scan through the minimum value and chumk size of this range scan:
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '80423')) ORDER BY `id` LIMIT 22400, 2 /*next chunk boundary*/
2. Step 1 does not return the expected 2 rows of records. Judge whether the copy operation is performed to the end of the data to obtain the maximum value on the current table:
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) ORDER BY `id` DESC LIMIT 1 /*last upper boundary*/
3. Make the last data copy according to the minimum value of this range scan and the maximum value on the current table:
INSERT LOW_PRIORITY IGNORE INTO `db001`.`_tb004_new` (`id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7`) SELECT `id`, `c1`, `dt`, `c6`, `c5`, `c4`, `c7` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '80423')) AND ((`id` <= '99999')) LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/
4. All data are copied and the new table statistics are updated.
ANALYZE TABLE `db001`.`_tb004_new` /* pt-online-schema-change */
5. Replace the names of the original and new tables:
RENAME TABLE `db001`.`tb004` TO `db001`.`_tb004_old`, `db001`.`_tb004_new` TO `db001`.`tb004`
6. Clean the original table and trigger:
DROP TABLE IF EXISTS `db001`.`_tb004_old` DROP TRIGGER IF EXISTS `db001`.`pt_osc_db001_tb004_del` DROP TRIGGER IF EXISTS `db001`.`pt_osc_db001_tb004_upd` DROP TRIGGER IF EXISTS `db001`.`pt_osc_db001_tb004_ins`
7. Confirm that the new form has been renamed correctly:
SHOW TABLES FROM `db001` LIKE '\_tb004\_new'
8. The implementation of Pt OSC tool is completed.