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 <rja...@yahoo-inc.com> > 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:yloui...@gmail.com] > > Sent: Friday, May 11, 2012 1:35 AM > > To: mysql@lists.mysql.com > > 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: yloui...@gmail.com > > Personal Blog: http://www.vmcd.org > -- Phone: +86 13918046970 Email & Gtalk: yloui...@gmail.com Personal Blog: http://www.vmcd.org
