List:General Discussion« Previous MessageNext Message »
From:louis liu Date:May 11 2012 8:34am
Subject:drop partitions
View as plain text  
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:  ylouis83@stripped
Personal Blog: http://www.vmcd.org

Thread
drop partitionslouis liu11 May
  • RE: drop partitionsRick James11 May
    • Re: drop partitionslouis liu11 May
  • RE: drop partitionsRick James11 May
    • Re: drop partitionsJohan De Meersman14 May
      • Re: drop partitionslouis liu14 May
        • RE: drop partitionsRick James14 May