From: Rick James Date: May 11 2012 5:06pm Subject: RE: drop partitions List-Archive: http://lists.mysql.com/mysql/227398 Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB1485F3712B@SP2-EX07VS01.ds.corp.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable If you have 14 partitions in each of 390 tables, and if you have most of th= e 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 >=20 > Hi all >=20 > last night we droped some partitions and we found that the first > drop costs about 30mins and then it's about 3 seconds. >=20 > when droping the partition all processes are shown waiting for opening > table like: >=20 >=20 > +---------+-------------+-------------------+---------------+---------- > ---+---------+--------------------------------------------------------- > -------+--------------------------------------------------------------- > ---------------------------------------+ > | 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_ | >=20 >=20 > whole instance hangs and all databases are waiting until we completely > drop partitions >=20 >=20 > we create this table as: >=20 >=20 > 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=3DMYISAM AUTO_INCREMENT=3D247057481 DEFAULT CHARSET=3Dutf8 > CHECKSUM=3D1 > DELAY_KEY_WRITE=3D1 ROW_FORMAT=3DDYNAMIC > /*!50100 PARTITION BY RANGE (to_days(create_time)) (PARTITION p1101 > VALUES LESS THAN (734534) ENGINE =3D MyISAM, PARTITION p1102 VALUES LESS > THAN (734562) ENGINE =3D MyISAM, PARTITION p1103 VALUES LESS THAN > (734593) ENGINE =3D MyISAM, PARTITION p1104 VALUES LESS THAN (734623) > ENGINE =3D MyISAM, PARTITION p1105 VALUES LESS THAN (734654) ENGINE =3D > MyISAM, PARTITION p1106 VALUES LESS THAN (734684) ENGINE =3D MyISAM, > PARTITION p1107 VALUES LESS THAN (734715) ENGINE =3D MyISAM, PARTITION > p1204 VALUES LESS THAN (734989) ENGINE =3D MyISAM, PARTITION p1205 > VALUES LESS THAN (735020) ENGINE =3D MyISAM, PARTITION p1206 VALUES LESS > THAN (735050) ENGINE =3D MyISAM, PARTITION p1207 VALUES LESS THAN > (735081) ENGINE =3D MyISAM) */ >=20 >=20 >=20 >=20 > so why first drop costs so long time and why all database are > waiting for opening table ? >=20 >=20 >=20 >=20 > cheers >=20 >=20 > -- > Phone: +86 13918046970 > Email & Gtalk: ylouis83@stripped > Personal Blog: http://www.vmcd.org