List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 11 2012 5:06pm
Subject:RE: drop partitions
View as plain text  
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
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