Hi all
last night we droped some partitions and we found that the first drop
costs about 30mins and then it's about 3 seconds.
when droping the partition all processes are shown waiting for opening
table like:
+---------+-------------+-------------------+---------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command |
Time | State |
Info
|
+---------+-------------+-------------------+---------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 4656803 | bkdloguser | 10.3.0.116:48642 | backend_log | Query |
1420 | Opening tables |
insert into INTERFACE_LOG
(DOC_ID,DOC_CODE,CALL_CLASS,CALL_METHOD,PARAMETERS,CALL_RESULT,CREATE_TIME |
| 4656804 | bkdloguser | 10.3.0.116:48643 | backend_log | Query |
1420 | Opening tables |
insert into INTERFACE_LOG
(DOC_ID,DOC_CODE,CALL_CLASS,CALL_METHOD,PARAMETERS,CALL_RESULT,CREATE_TIME |
| 4656805 | bkdloguser | 10.3.0.116:48644 | backend_log | Query |
1420 | Opening tables |
insert into BACK_SQL_OPERATION_LOG
(SUB_SYSTEM,OPERATION_TYPE,OP_CONDITION,OPERATION_TIME,OPERATION_ |
| 4656806 | bkdloguser | 10.3.0.116:48645 | backend_log | Query |
1420 | Opening tables |
insert into INTERFACE_LOG
(DOC_ID,DOC_CODE,CALL_CLASS,CALL_METHOD,PARAMETERS,CALL_RESULT,CREATE_TIME |
| 4656807 | bkdloguser | 10.3.0.116:48646 | backend_log | Query |
1420 | Opening tables |
insert into INTERFACE_LOG
(DOC_ID,DOC_CODE,CALL_CLASS,CALL_METHOD,PARAMETERS,CALL_RESULT,CREATE_TIME |
| 4656808 | bkdloguser | 10.3.0.116:48647 | backend_log | Query |
1420 | Opening tables |
insert into BACK_SQL_OPERATION_LOG
(SUB_SYSTEM,OPERATION_TYPE,OP_CONDITION,OPERATION_TIME,OPERATION_ |
| 4656809 | bkdloguser | 10.3.0.116:48648 | backend_log | Query |
1420 | Opening tables |
insert into BACK_SQL_OPERATION_LOG
(SUB_SYSTEM,OPERATION_TYPE,OP_CONDITION,OPERATION_TIME,OPERATION_ |
| 4656810 | bkdloguser | 10.3.0.116:48649 | backend_log | Query |
1420 | Opening tables |
insert into BACK_SQL_OPERATION_LOG
(SUB_SYSTEM,OPERATION_TYPE,OP_CONDITION,OPERATION_TIME,OPERATION_ |
| 4656811 | bkdloguser | 10.3.0.116:48650 | backend_log | Query |
1420 | Opening tables |
insert into BACK_SQL_OPERATION_LOG
(SUB_SYSTEM,OPERATION_TYPE,OP_CONDITION,OPERATION_TIME,OPERATION_ |
whole instance hangs and all databases are waiting until we completely
drop partitions
we create this table as:
CREATE TABLE `centralmobile_logs` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`trader_name` VARCHAR(25) DEFAULT NULL,
`trader_password` VARCHAR(128) DEFAULT NULL,
`client_system` VARCHAR(20) DEFAULT NULL,
`client_version` VARCHAR(20) DEFAULT NULL,
`protocol` VARCHAR(20) DEFAULT NULL,
`interface_version` VARCHAR(20) DEFAULT NULL,
`call_interface_name` VARCHAR(200) DEFAULT NULL,
`user_token` VARCHAR(128) DEFAULT NULL,
`client_IP` VARCHAR(60) DEFAULT NULL,
`server_IP` VARCHAR(128) DEFAULT NULL,
`create_time` DATETIME NOT NULL,
`request_URL` LONGTEXT,
`cookie` TEXT,
`request_head` TEXT,
`requestBody` LONGTEXT,
`responseHead` TEXT,
`responseBody` LONGTEXT,
`hasException` VARCHAR(10) NOT NULL,
`exceptionString` LONGTEXT,
`useTimes` BIGINT(20) DEFAULT NULL,
`ifOrderAmount` VARCHAR(20) DEFAULT NULL,
`ifOrderCode` VARCHAR(30) DEFAULT NULL,
`clientTelnetPhone` VARCHAR(60) DEFAULT NULL,
`clientAppVersion` VARCHAR(20) DEFAULT NULL,
`provinceId` VARCHAR(10) DEFAULT NULL,
`searchkeywords` VARCHAR(60) DEFAULT NULL,
`netnotekeywords` VARCHAR(800) DEFAULT NULL,
`unionKey` VARCHAR(20) DEFAULT NULL,
`longitude` DOUBLE DEFAULT NULL,
`latitude` DOUBLE DEFAULT NULL,
PRIMARY KEY (`id`,`create_time`),
KEY `trader_name` (`trader_name`),
KEY `createtime` (`create_time`),
KEY `usertimes` (`useTimes`),
KEY `callinterfacename` (`call_interface_name`),
KEY `clientip` (`client_IP`),
KEY `token` (`user_token`)
) ENGINE=MYISAM AUTO_INCREMENT=247057481 DEFAULT CHARSET=utf8 CHECKSUM=1
DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY RANGE (to_days(create_time))
(PARTITION p1101 VALUES LESS THAN (734534) ENGINE = MyISAM,
PARTITION p1102 VALUES LESS THAN (734562) ENGINE = MyISAM,
PARTITION p1103 VALUES LESS THAN (734593) ENGINE = MyISAM,
PARTITION p1104 VALUES LESS THAN (734623) ENGINE = MyISAM,
PARTITION p1105 VALUES LESS THAN (734654) ENGINE = MyISAM,
PARTITION p1106 VALUES LESS THAN (734684) ENGINE = MyISAM,
PARTITION p1107 VALUES LESS THAN (734715) ENGINE = MyISAM,
PARTITION p1204 VALUES LESS THAN (734989) ENGINE = MyISAM,
PARTITION p1205 VALUES LESS THAN (735020) ENGINE = MyISAM,
PARTITION p1206 VALUES LESS THAN (735050) ENGINE = MyISAM,
PARTITION p1207 VALUES LESS THAN (735081) ENGINE = MyISAM) */
so why first drop costs so long time and why all database are waiting
for opening table ?
cheers
--
Phone: +86 13918046970
Email & Gtalk: yloui...@gmail.com
Personal Blog: http://www.vmcd.org
