List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 11 2012 2:51pm
Subject:RE: drop partitions
View as plain text  
What are the VARIABLES values of
  open_files_limit
  table_open_cache
  table_definition_cache
How partitions in this table?  How many tables in your system?

> -----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
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