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
