Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.10
-
CentOS release 6.5 (Final)
Description
table define:
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) */ |
--------------------------------------------------------------------------------------------------
This table has more than 20 million records.
when add index on this table maria do a copy to tmp table – that cost a long time but still can't finish.
create index idx_tmp on track_raw_wap211_log (log_time); |
|
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 also meets this problem
alter table track_raw_wap211_log drop index ix_httplogtime; |
|
22488 | root | localhost | jkgj_log | Query | 33 | copy to tmp table | alter table track_raw_wap211_log drop index ix_httplogtime | 0.409 | |
----------------------------------------------------------------------
Repeat these on 5.5.5-10.0.15-MariaDB-log MariaDB Server everything is OK