List:General Discussion« Previous MessageNext Message »
From:louis liu Date:May 11 2012 4:47pm
Subject:Re: drop partitions
View as plain text  
open_files_limit =30000
table_open_cache =4096
table_definition_cache =256
How partitions in this table?
14 partitions

 How many tables in your system ?
about 390 tables



cheers


2012/5/11 Rick James <rjames@stripped>

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



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