Re: How to get all known bugs on specified mysql version?
don't use GTID is my suggestion :) 2014-07-07 7:42 GMT+08:00 娄帅 louis.hust...@gmail.com: Hi, all, I want to use MySQL 5.6.18 in production, so i want to get all known bugs on this version. Now i check the bugfix section in MySQL 5.6.19 and above version. Am i doing the right thing? Or Do you have a better method? Any input will be appreciated! -- Phone: +86 1868061 Email Gtalk: yloui...@gmail.com Personal Blog: http://www.vmcd.org
Re: Multi-master to one slave
you can use mysql transfer or MariaDB for this requirement 2014-02-25 12:03 GMT+08:00 张志刚 zzgang2...@gmail.com: Does someone have best schema from multi-master to one slave excluding the circular replication. -- Phone: +86 1868061 Email Gtalk: yloui...@gmail.com Personal Blog: http://www.vmcd.org
Re: LIKE sql optimization
MySQL can't use index when '%' condition gives even oracle and you can try full-text search 2014-02-12 9:55 GMT+08:00 kitlenv kitl...@gmail.com: *read how a index works technically* On Wed, Feb 12, 2014 at 12:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. zhigang _ From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like '%abcd'; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang -- Phone: +86 1868061 Email Gtalk: yloui...@gmail.com Personal Blog: http://www.vmcd.org
Re: drop partitions
not actually , first partition to have been dropped is no longer in use ,we dropped partition from old - new 2012/5/14 Johan De Meersman vegiv...@tuxera.be - Original Message - From: Rick James rja...@yahoo-inc.com 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. A distinct possibility. Compute (SHOW STATUS): Opened_tables / Uptime -- don't want more than a few per sec. Opened_files / Uptime -- ditto Opened_table_definitions / Uptime -- ditto Assuming you have anywhere near decent uptime, those are going to be way too flattened to be of use. Trend monitoring is a necessity - look at Munin or Cacti. A random thought, though - could the first partition to have been dropped not have been in active use, so the drop operation had to wait for all other statements to end, and the other (older?) partitions no longer in use, so could be dropped immediately? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Phone: +86 13918046970 Email Gtalk: yloui...@gmail.com Personal Blog: http://www.vmcd.org
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_TIME | | 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_TIME | | 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_TIME | | 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_TIME | | 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)
Re: drop partitions
open_files_limit =3 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
