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