MySQL Forums
Forum List  »  Replication

how to get the execution plan of slave sql thread
Posted by: Zhiwu Zhu
Date: February 05, 2015 01:47AM

The slave sql thread is running very slowly. I analyzed the current binlog record, as follows:

BEGIN
/*!*/;
# at 482762468
#150128 16:51:21 server id 216038 end_log_pos 482762443 CRC32 0x1bacdbfe Table_map: `dataaudit`.`t_cn_cfg_vendor_contract` ma
pped to number 2249
# at 482762603
#150128 16:51:21 server id 216038 end_log_pos 482770631 CRC32 0xeee38a69 Delete_rows: table id 2249
..
### DELETE FROM `dataaudit`.`t_cn_cfg_vendor_contract`
### WHERE
### @1=109676 /* DECIMAL(22,0) meta=5632 nullable=0 is_null=0 */
### @2=147116 /* DECIMAL(22,0) meta=5632 nullable=0 is_null=0 */
### @3=NULL /* DECIMAL(22,0) meta=300 nullable=1 is_null=1 */
### @4=NULL /* DECIMAL(22,0) meta=300 nullable=1 is_null=1 */
### @5=NULL /* DECIMAL(22,0) meta=300 nullable=1 is_null=1 */
### @6=NULL /* DECIMAL(22,0) meta=300 nullable=1 is_null=1 */
### @7=NULL /* DECIMAL(22,0) meta=2 nullable=1 is_null=1 */
### @8=NULL /* DECIMAL(22,0) meta=300 nullable=1 is_null=1 */
### @9=NULL /* DECIMAL(22,0) meta=300 nullable=1 is_null=1 */
### @10=NULL /* DECIMAL(22,0) meta=300 nullable=1 is_null=1 */
### @11=NULL /* DECIMAL(22,0) meta=300 nullable=1 is_null=1 */
### @12=NULL /* DECIMAL(22,0) meta=600 nullable=1 is_null=1 */
### @13=NULL /* DECIMAL(22,0) meta=300 nullable=1 is_null=1 */
### @14=NULL /* DECIMAL(22,0) meta=6 nullable=1 is_null=1 */
### @15=NULL /* DECIMAL(22,0) meta=720 nullable=1 is_null=1 */
### @16=NULL /* DECIMAL(22,0) meta=720 nullable=1 is_null=1 */
### @17=NULL /* DECIMAL(22,0) meta=720 nullable=1 is_null=1 */
### @18=NULL /* DECIMAL(22,0) meta=720 nullable=1 is_null=1 */
### @19=NULL /* DECIMAL(22,0) meta=720 nullable=1 is_null=1 */
### @20=NULL /* DECIMAL(22,0) meta=6 nullable=1 is_null=1 */
### @21='2015-01-25 06:30:00.000000' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
### @22='0' /* STRING(3) meta=65027 nullable=1 is_null=0 */
..

The variable binlog_format is MIXED. And the definition of TABLE `t_cn_cfg_vendor_contract` is listed below:

CREATE TABLE `t_cn_cfg_vendor_contract` (
`VENDOR_ID` decimal(22,0) NOT NULL,
`CONTACT_ID` decimal(22,0) NOT NULL,
`COMPANY_CD` varchar(100) DEFAULT NULL,
`COMPANY_NAME` varchar(100) DEFAULT NULL,
`CONTACT_NAME` varchar(100) DEFAULT NULL,
`CONTACT_ROLE` varchar(100) DEFAULT NULL,
`ADRESS` text,
`EMAIL` varchar(100) DEFAULT NULL,
`MOBILE_PHONE` varchar(100) DEFAULT NULL,
`PHONE` varchar(100) DEFAULT NULL,
`FAX` varchar(100) DEFAULT NULL,
`NOTES` varchar(200) DEFAULT NULL,
`NATIONAL_IDENTIFIER` varchar(100) DEFAULT NULL,
`CONTACT_EXPIRATION_DATE` datetime(6) DEFAULT NULL,
`ATTRIBUTE1` varchar(240) DEFAULT NULL,
`ATTRIBUTE2` varchar(240) DEFAULT NULL,
`ATTRIBUTE3` varchar(240) DEFAULT NULL,
`ATTRIBUTE4` varchar(240) DEFAULT NULL,
`ATTRIBUTE5` varchar(240) DEFAULT NULL,
`LAST_UPDATE_DATE` datetime(6) DEFAULT NULL,
`RECORD_UPDATETIME` datetime(6) DEFAULT NULL,
`is_deleted` char(1) DEFAULT '0',
KEY `CN_INDEX_20130110_002` (`COMPANY_CD`),
KEY `CN_INDEX_20130110_004` (`VENDOR_ID`),
KEY `cn_index_20141209` (`VENDOR_ID`,`CONTACT_ID`,`COMPANY_CD`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

There is no primary key in this table, however key cn_index_20141209 is effective. But I wonder whether slave sql thread use this key. It is more likely a full table scan.

So, how can I get the current sql and execution plan of slave sql thread?

Thanks!
Zhiwu

Options: ReplyQuote


Subject
Views
Written By
Posted
how to get the execution plan of slave sql thread
1689
February 05, 2015 01:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.