If you have 14 partitions in each of 390 tables, and if you have most of the tables
'active', then you are possibly thrashing in the table_open_cache.
Compute (SHOW STATUS):
Opened_tables / Uptime -- don't want more than a few per sec.
Opened_files / Uptime -- ditto
Opened_table_definitions / Uptime -- ditto
> -----Original Message-----
> From: louis liu [mailto:ylouis83@stripped]
> Sent: Friday, May 11, 2012 1:35 AM
> To: mysql@stripped
> Subject: drop partitions
>
> 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_T
> IME |
> | 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_T
> IME |
> | 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_T
> IME |
> | 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_T
> IME |
> | 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
