Version: 5.5.5-10.0.10-MariaDB-log MariaDB Server
When I use mariadb as multi-source slave DB I met a strange problem . Creating index on a vary large partition table , Mariadb do — copy to tmp table on the slave side. It takes a long time and still not finish over 9 hours.
Primary : MySQL 5.6.16 —
add index on a partition table:
CREATE TABLE `track_raw_wap211_log` ( `table_id` varchar(100) DEFAULT NULL, `page_id` varchar(100) DEFAULT NULL, `banner_id` varchar(100) DEFAULT NULL, `button_id` varchar(100) DEFAULT NULL, `test_id` varchar(100) DEFAULT NULL, `classfication` varchar(100) DEFAULT NULL, `request_refer` varchar(100) DEFAULT NULL, `request_url` text, `title` varchar(100) DEFAULT NULL, `user_id` varchar(100) DEFAULT NULL, `language` varchar(100) DEFAULT NULL, `event` varchar(100) DEFAULT NULL, `event_desc` varchar(100) DEFAULT NULL, `event_type` varchar(100) DEFAULT NULL, `log_version` varchar(100) DEFAULT NULL, `project` varchar(100) DEFAULT NULL, `log_time` varchar(100) DEFAULT NULL, `unicookie` varchar(100) DEFAULT NULL, `session_id` varchar(100) DEFAULT NULL, `session_duration` varchar(100) DEFAULT NULL, `resolution` varchar(100) DEFAULT NULL, `channel` varchar(100) DEFAULT NULL, `refer` text, `user_agent` text, `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `httplogtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `httpforwardip` varchar(100) DEFAULT NULL, `country` varchar(100) DEFAULT NULL, `province` varchar(100) DEFAULT NULL, `city` varchar(100) DEFAULT NULL, `area` varchar(100) DEFAULT NULL, `mapweidu` varchar(100) DEFAULT NULL, `mapjingdu` varchar(100) DEFAULT NULL, `sync_mysqlid` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`sync_mysqlid`,`create_time`), KEY `ix_create_time` (`create_time`), KEY `ix_httplogtime` (`httplogtime`), KEY `ix_httplogtime_userid` (`httplogtime`,`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=23217118 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(`create_time`)) (PARTITION p20150318 VALUES LESS THAN (1426694400) ENGINE = InnoDB, PARTITION p20150319 VALUES LESS THAN (1426780800) ENGINE = InnoDB, PARTITION p20150320 VALUES LESS THAN (1426867200) ENGINE = InnoDB, PARTITION p20150321 VALUES LESS THAN (1426953600) ENGINE = InnoDB, PARTITION p20150322 VALUES LESS THAN (1427040000) ENGINE = InnoDB, PARTITION p20150323 VALUES LESS THAN (1427126400) ENGINE = InnoDB, PARTITION p20150324 VALUES LESS THAN (1427212800) ENGINE = InnoDB, PARTITION p20150325 VALUES LESS THAN (1427299200) ENGINE = InnoDB, PARTITION p20150326 VALUES LESS THAN (1427385600) ENGINE = InnoDB, PARTITION p20150327 VALUES LESS THAN (1427472000) ENGINE = InnoDB, PARTITION p20150328 VALUES LESS THAN (1427558400) ENGINE = InnoDB, PARTITION p20150329 VALUES LESS THAN (1427644800) ENGINE = InnoDB, PARTITION p20150330 VALUES LESS THAN (1427731200) ENGINE = InnoDB, PARTITION p20150331 VALUES LESS THAN (1427817600) ENGINE = InnoDB, PARTITION p20150401 VALUES LESS THAN (1427904000) ENGINE = InnoDB, PARTITION p20150402 VALUES LESS THAN (1427990400) ENGINE = InnoDB, PARTITION p20150403 VALUES LESS THAN (1428076800) ENGINE = InnoDB, PARTITION p20150404 VALUES LESS THAN (1428163200) ENGINE = InnoDB, PARTITION p20150405 VALUES LESS THAN (1428249600) ENGINE = InnoDB, PARTITION p20150406 VALUES LESS THAN (1428336000) ENGINE = InnoDB, PARTITION p20150407 VALUES LESS THAN (1428422400) ENGINE = InnoDB, PARTITION p20150408 VALUES LESS THAN (1428508800) ENGINE = InnoDB, PARTITION p20150409 VALUES LESS THAN (1428595200) ENGINE = InnoDB, PARTITION p20150410 VALUES LESS THAN (1428681600) ENGINE = InnoDB, PARTITION p20150411 VALUES LESS THAN (1428768000) ENGINE = InnoDB, PARTITION p20150412 VALUES LESS THAN (1428854400) ENGINE = InnoDB, PARTITION p20150413 VALUES LESS THAN (1428940800) ENGINE = InnoDB, PARTITION p20150414 VALUES LESS THAN (1429027200) ENGINE = InnoDB, PARTITION p20150415 VALUES LESS THAN (1429113600) ENGINE = InnoDB, PARTITION p20150416 VALUES LESS THAN (1429200000) ENGINE = InnoDB, PARTITION p20150417 VALUES LESS THAN (1429286400) ENGINE = InnoDB, PARTITION p20150418 VALUES LESS THAN (1429372800) ENGINE = InnoDB, PARTITION p20150419 VALUES LESS THAN (1429459200) ENGINE = InnoDB, PARTITION p20150420 VALUES LESS THAN (1429545600) ENGINE = InnoDB, PARTITION p20150421 VALUES LESS THAN (1429632000) ENGINE = InnoDB, PARTITION p20150422 VALUES LESS THAN (1429718400) ENGINE = InnoDB, PARTITION p20150423 VALUES LESS THAN (1429804800) ENGINE = InnoDB, PARTITION p20150424 VALUES LESS THAN (1429891200) ENGINE = InnoDB, PARTITION p20150425 VALUES LESS THAN (1429977600) ENGINE = InnoDB, PARTITION p20150426 VALUES LESS THAN (1430064000) ENGINE = InnoDB, PARTITION p20150427 VALUES LESS THAN (1430150400) ENGINE = InnoDB, PARTITION p20150428 VALUES LESS THAN (1430236800) ENGINE = InnoDB, PARTITION p20150429 VALUES LESS THAN (1430323200) ENGINE = InnoDB, PARTITION p20150430 VALUES LESS THAN (1430409600) ENGINE = InnoDB, PARTITION p20150501 VALUES LESS THAN (1430496000) ENGINE = InnoDB, PARTITION p20150502 VALUES LESS THAN (1430582400) ENGINE = InnoDB, PARTITION p20150503 VALUES LESS THAN (1430668800) ENGINE = InnoDB, PARTITION p20150504 VALUES LESS THAN (1430755200) ENGINE = InnoDB, PARTITION p20150505 VALUES LESS THAN (1430841600) ENGINE = InnoDB, PARTITION p20150506 VALUES LESS THAN (1430928000) ENGINE = InnoDB, PARTITION p20150507 VALUES LESS THAN (1431014400) ENGINE = InnoDB, PARTITION p20150508 VALUES LESS THAN (1431100800) ENGINE = InnoDB, PARTITION p20150509 VALUES LESS THAN (1431187200) ENGINE = InnoDB, PARTITION p20150510 VALUES LESS THAN (1431273600) ENGINE = InnoDB, PARTITION p20150511 VALUES LESS THAN (1431360000) ENGINE = InnoDB) */
create index idx_tmp on track_raw_wap211_log (log_time);
Slave: Mariadb – 5.5.5-10.0.15-MariaDB-log MariaDB Server
21407 | root | localhost | jkgj_log | Query | 34 | copy to tmp table | create index idx_tmp on track_raw_wap211_log (log_time) | 0.000 |
drop index command still has this problem.
——————————————————————
When I repeat these steps on MariaDB – 10.0.16 everything is OK.
This bug has been reported to MariaDB:
