MySQL Percona Toolkit--pt-osc执行SQL命令
pt-osc执行日志
在对数据量为100000的表tb004做DROP COLUMN操作,pt-osc工具日志为:
Operation, tries, wait:
analyze_table, ,
copy_rows, , 0.25
create_triggers, ,
drop_triggers, ,
swap_tables, ,
update_foreign_keys, ,
Altering `db001`.`tb004`...
Creating new table...
Created new table db001._tb004_new OK.
Altering new table...
Altered `db001`.`_tb004_new` OK.
--10T18:: Creating triggers...
--10T18:: Created triggers OK.
--10T18:: Copying approximately rows...
--10T18:: Copied rows OK.
--10T18:: Analyzing new table...
--10T18:: Swapping tables...
--10T18:: Swapped original and new tables OK.
--10T18:: Dropping old table...
--10T18:: Dropped old table `db001`.`_tb004_old` OK.
--10T18:: Dropping triggers...
--10T18:: Dropped triggers OK.
Successfully altered `db001`.`tb004`.
pt-osc工具对MySQL执行的SQL命令为:
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` >= '' /*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` >= '')) 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` >= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) 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` >= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) 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` >= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) 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` >= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) 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` >= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) 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` >= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) 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` >= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) 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` >= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) 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` >= '')) 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` >= '')) AND ((`id` <= '')) 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` >= '')) AND ((`id` <= '')) 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
常规检查和设置
## 检查执行线程数量
SHOW GLOBAL STATUS LIKE 'Threads_running' ## 获取从库信息
SHOW FULL PROCESSLIST ## 检查是是PXC群集
SHOW VARIABLES LIKE 'wsrep_on' ## 检查表是否有触发器
SHOW TRIGGERS FROM `db001` LIKE 'tb004' ## 检查表是否有外键
SELECT table_schema, table_name
FROM information_schema.key_column_usage
WHERE referenced_table_schema='db001'
AND referenced_table_name='tb004' ## 设置会话超时和锁超时
SET SESSION wait_timeout=10000
SET SESSION lock_wait_timeout=60 ## 查看原表创建语句
SHOW CREATE TABLE `db001`.`tb004`
创建触发器
## 删除操作触发器
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`; ## 更新操作触发器
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 ## 插入操作触发器
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`)
第一次数据拷贝
在第一次数据拷贝前,先执行下面EXPLAIN语句获取表数据量:
EXPLAIN SELECT * FROM `db001`.`tb004` WHERE 1=1
如果表数据量较少,则选择通过一次全表扫描将所有数据复制到新表:
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*/
如果表数据量超过一定阈值,则选择通过N次范围扫描将数据复制到新表。
当chunk-size和chunk-time两者都未指定时,chunk-size默认值为1000,chunk-time默认值为0.5S,第一次按照chunk-size来进行数据复制,然后根据第一次复制的时间动态调整chumk-size的大小,以适应服务器的性能变化,如上一次复制1000行消耗0.1S,则下次动态调整chumk-size为5000。
1、获取第一次范围扫描的最小值和行长度:
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、获取第一次范围扫描的最大值和第二次范围扫描的最小值:
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '')) ORDER BY `id` LIMIT 999, 2
3、复制数据至新表:
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` >= '')) AND ((`id` <= ''))
LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/
4、根据步骤3的操作时长调整下次操作的chumk-size,由于第一次复制1000条数据耗时较短,第二次复制数据的chumk-size被更新为5943。
5、获取第二次范围扫描的最大值和第三次范围扫描的最小值:
SELECT /*!40001 SQL_NO_CACHE */ `id`
FROM `db001`.`tb004`
FORCE INDEX(`PRIMARY`)
WHERE ((`id` >= ''))
ORDER BY `id` LIMIT 5943, 2
6、循环第二次至第N次数数据复制。
最后一次数据拷贝
1、通过本次的范围扫描的最小值和chumk-size获取本次范围扫描的最大值:
SELECT /*!40001 SQL_NO_CACHE */ `id`
FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`)
WHERE ((`id` >= ''))
ORDER BY `id` LIMIT 22400, 2 /*next chunk boundary*/
2、步骤1未返回期望的2行记录,判断复制操作执行到数据尾部,获取当前表上最大值:
SELECT /*!40001 SQL_NO_CACHE */ `id`
FROM `db001`.`tb004` FORCE INDEX(`PRIMARY`)
ORDER BY `id` DESC LIMIT 1 /*last upper boundary*/
3、根据本次的范围扫描的最小值和当前表上最大值,做最后一次数据复制:
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` >= ''))
AND ((`id` <= ''))
LOCK IN SHARE MODE /*pt-online-schema-change 23795 copy nibble*/
4、所有数据复制完成,更新新表统计信息
ANALYZE TABLE `db001`.`_tb004_new` /* pt-online-schema-change */
5、更换原表和新表的表名:
RENAME TABLE `db001`.`tb004` TO `db001`.`_tb004_old`, `db001`.`_tb004_new` TO `db001`.`tb004`
6、清理原表和触发器:
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、确认新表已正确改名:
SHOW TABLES FROM `db001` LIKE '\_tb004\_new'
8、pt-osc工具执行完成。
最新文章
- mybatis一对多关联
- 解决在ScrollView中套用ListView显示不正常
- JS 获取url参数
- C#匿名函数的坑
- python标准模块(二)
- Spring+SpringMVC+MyBatis+Maven 服务端XML配置
- 【计算机图形学】openGL常用函数
- poj3167
- AJax的异步请求
- RabbitMQ入门-从HelloWorld开始
- Chrome自定义滚动条
- python 导入模块出错 ImportError: No module named &#39;request&#39;
- 软件工程_2nd weeks
- iOS项目之解析HTML数据
- Linux make menuconfig查找并快速跳转指定驱动选项
- Linux启动流程与模块管理(15)
- python中pip
- vlc的应用之二:vlc的ActiveX及cab
- Linux SSH Backdoor分析排查
- 网络协议之ftp---ftp 协议详解