MySQL Forums
Forum List  »  Optimizer & Parser

still the trigger problem
Posted by: h ch
Date: April 07, 2010 03:11AM

first i have two host ,the trigger set in slave (not master)

run show processlist ,i notice that the trigger run very long time
# mysql -e "show processlist"|cat
Id User Host db Command Time State Info
1 system user NULL Connect 7093 Waiting for master to send event NULL
2 ecp 202.107.194.178 server Connect 4637 Sending data Replace INTO \n ServerTrigger.TB_ADVICE_Q(ADV_Id,ADVS_Id,CM_Id,CM_Status,Subscribe_Q_Id) \n
3 ecp 172.16.16.12:4446 ServerTrigger Query 417 Locked SELECT Logon_ID FROM ServerTrigger.TB_LeaveMsg limit 1
6 ecp 202.107.194.184:62188 server Sleep 3 NULL
7 root localhost server Sleep 6306 NULL
105 ecp 202.107.194.184:62376 NULL Sleep 152 NULL
106 ecp 202.107.194.184:62377 NULL Sleep 152 NULL
199 root localhost test Sleep 35 NULL
523 ecp 172.16.16.13:1196 server Sleep 1317 NULL
525 ecp 172.16.16.13:1230 server Query 417 Locked SELECT * FROM ServerTrigger.TB_ADVICE limit 10
526 ecp 172.16.16.13:1233 server Sleep 1268 NULL
527 ecp 172.16.16.13:1236 server Sleep 1268 NULL
528 ecp 172.16.16.13:1239 server Sleep 1268 NULL
529 ecp 172.16.16.13:1242 server Sleep 1268 NULL
530 ecp 172.16.16.13:1246 server Sleep 1268 NULL
531 ecp 172.16.16.13:1249 server Sleep 1267 NULL
532 ecp 172.16.16.13:1252 server Sleep 1267 NULL
533 ecp 172.16.16.13:1255 server Sleep 1267 NULL

and the same time ,i observed the slave SQL thread behind the master so long,so
i judge the trigger blocked the sql thread ,this is the slow replication reason

and here is the trigger code:

Trigger: TRS_AFT_UPD
Event: UPDATE
Table: TB_MStatus
Statement: BEGIN
if OLd.M_Status <> New.M_Status then
Replace INTO
ServerTrigger.TB_ADVICE(ADV_Id,ADVS_Id,CM_Id,CM_Status)
select M_Id,S_Id,New.M_Id as AdvM_Id,New.M_Status as CM_Status from server.TB_MStatus where
M_Id in
(
select userId from server.frirelation where
friendId = New.M_Id and
userid in (select M_Id from server.TB_MStatus where M_Id <> New.M_Id)
);
end if;

if (OLd.M_Status <> New.M_Status) or (Old.SubscribeQ_Id <> New.SubscribeQ_Id) then
Replace INTO
ServerTrigger.TB_ADVICE_Q(ADV_Id,ADVS_Id,CM_Id,CM_Status,Subscribe_Q_Id)
select M_Id,S_Id,New.M_Id as AdvM_Id,New.M_Status as CM_Status,SubscribeQ_Id from server.TB_MStatus where
M_Id in
(select M_Id from server.TB_MStatus as a,
(select asid from `netsun_Q`.`my_Q` where poster =
(select login from `hub_netsun`.`minfo` where id = New.M_Id limit 1))as dummy
where a.SubscribeQ_Id=dummy.asid
);
End If;
END

also the my_Q and the minfo table is very big
mysql> select count(*) from netsun_Q.my_Q;
+----------+
| count(*) |
+----------+
| 13088315 |
+----------+
mysql> select count(*) from hub_netsun.minfo;
+----------+
| count(*) |
+----------+
| 3351717 |
+----------+

*************************** 1. row ***************************
Table: my_Q
Create Table: CREATE TABLE `my_Q` (
`id` int(10) NOT NULL auto_increment,
`asid` int(11) NOT NULL default '0' COMMENT '????id',
`uid` int(11) default NULL,
`poster` varchar(50) collate utf8_unicode_ci default NULL COMMENT '?????',
`post_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '????',
`status` tinyint(4) default '0' COMMENT '??????',
`rank` int(11) default '0',
`vip_date` varchar(10) collate utf8_unicode_ci default NULL,
`ecp` tinyint(2) NOT NULL default '0' COMMENT '??????',
PRIMARY KEY (`id`),
UNIQUE KEY `poster1` (`poster`,`asid`),
KEY `asid` (`asid`),
KEY `uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=15384514 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Options: ReplyQuote


Subject
Views
Written By
Posted
still the trigger problem
3223
April 07, 2010 03:11AM
1478
April 07, 2010 06:43PM
1628
April 09, 2010 06:39PM
1507
April 10, 2010 01:10AM


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.