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 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 ServerTrigger Query 417 Locked SELECT Logon_ID FROM ServerTrigger.TB_LeaveMsg limit 1
6 ecp server Sleep 3 NULL
7 root localhost server Sleep 6306 NULL
105 ecp NULL Sleep 152 NULL
106 ecp NULL Sleep 152 NULL
199 root localhost test Sleep 35 NULL
523 ecp server Sleep 1317 NULL
525 ecp server Query 417 Locked SELECT * FROM ServerTrigger.TB_ADVICE limit 10
526 ecp server Sleep 1268 NULL
527 ecp server Sleep 1268 NULL
528 ecp server Sleep 1268 NULL
529 ecp server Sleep 1268 NULL
530 ecp server Sleep 1268 NULL
531 ecp server Sleep 1267 NULL
532 ecp server Sleep 1267 NULL
533 ecp 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
Table: TB_MStatus
Statement: BEGIN
if OLd.M_Status <> New.M_Status then
Replace INTO
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
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;

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 '??????',
UNIQUE KEY `poster1` (`poster`,`asid`),
KEY `asid` (`asid`),
KEY `uid` (`uid`)

Options: ReplyQuote

Written By
still the trigger problem
April 07, 2010 03:11AM
April 07, 2010 06:43PM
April 09, 2010 06:39PM
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.