# Performance regression with 5.5 around triggers / functions and replication
**URL:** https://forums.percona.com/t/performance-regression-with-5-5-around-triggers-functions-and-replication/1654
**Category:** Other MySQL® Questions
**Created:** [August 21, 2011, 11:17pm UTC](https://forums.percona.com/t/performance-regression-with-5-5-around-triggers-functions-and-replication/1654 "2011-08-21T23:17:34Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@mysqldbahelp](https://forums.percona.com/u/mysqldbahelp)
#### Post date: [August 21, 2011, 11:17pm UTC](https://forums.percona.com/t/performance-regression-with-5-5-around-triggers-functions-and-replication/1654/1 "2011-08-21T23:17:34Z")
Hello
I have noticed a performance regression running Percona 5.5 over Percona 5.1 with the use of triggers and function and replication.
We have a replicated configuration and we have recently upgraded from Percona 5.1 5.1.45-89.lenny.52-log under Lenny to 5.5.12-55 under RHEL6.1
The master server is running 5.5 under rhel6 and we have two slaves
1. running 5.1 under lenny with an extra schema and triggers and functions that are fired upon the slave SQL thread [to generate messages in the new schema]
2. running 5.5 under rhel6 with an extra schema and triggers and functions that are fired upon the slave SQL thread [to generate messages in the new schema]
The schema between the two replicas is the same.
We noticed a 50% performance hit when looking at the two. I have tweaked the 5.5 box as much as i can though I cannot get any decent write performance from it. The box is spending most of its time in user space.
Comparing the pmp output of the two boxes:
Percona 5.5:
/tmp# cat pmp.out.with-triggers.percona55
1501
600 ??,??,??,??,start\_thread,clone
298 pthread\_cond\_wait@@GLIBC\_2.3.2 ,one\_thread\_per\_connection\_end(THD\*,do\_handle\_one\_connectio n(THD\*),handle\_one\_connection,pfs\_spawn\_thread,start\_thread, clone
180 pthread\_cond\_timedwait@@GLIBC\_2.3.2,??,??,start\_thread,clone
177 select,??,??,start\_thread,clone
60 sigwait,signal\_hand,pfs\_spawn\_thread,start\_thread,clone
60 poll,handle\_connections\_sockets(),mysqld\_main(int,\_\_libc\_st art\_main,\_start
56 read,vio\_read\_buff,??,my\_net\_read,cli\_safe\_read,handle\_slave \_io,pfs\_spawn\_thread,start\_thread,clone
18 cleanup\_items(Item\*),sp\_head::execute(THD\*,sp\_head::execute _function(THD\*,Item\_func\_sp::execute\_impl(THD\*),Item\_func\_s p::execute(),Item\_func\_sp::val\_int(),Item::val\_bool(),Item\_c ond\_and::val\_int(),Item::val\_bool(),sp\_instr\_jump\_if\_not::ex ec\_core(THD\*,sp\_lex\_keeper::reset\_lex\_and\_exec\_core(THD\*,s p\_head::execute(THD\*,sp\_head::execute\_trigger(THD\*,Table\_t riggers\_list::process\_triggers(THD\*,mysql\_update(THD\*,mysq l\_execute\_command(THD\*),mysql\_parse(THD\*,Query\_log\_event::d o\_apply\_event(Relay\_log\_info,apply\_event\_and\_update\_pos(Log_ event\*,??,handle\_slave\_sql,pfs\_spawn\_thread,start\_thread,cl one
9 pread64,??,??,??,??,??,??,??,??,??,??,??,handler::read\_range \_first(st\_key\_range,handler::read\_multi\_range\_first(st\_key\_m ulti\_range\*\*,QUICK\_RANGE\_SELECT::get\_next(),??,mysql\_update (THD\*,mysql\_execute\_command(THD\*),mysql\_parse(THD\*,Query\_l og\_event::do\_apply\_event(Relay\_log\_info,apply\_event\_and\_upda te\_pos(Log\_event\*,??,handle\_slave\_sql,pfs\_spawn\_thread,star t\_thread,clone
9 cleanup\_items(Item\*),sp\_head::execute(THD\*,sp\_head::execute \_function(THD\*,Item\_func\_sp::execute\_impl(THD\*),Item\_func\_s p::execute(),Item\_func\_sp::val\_int(),Item::val\_bool(),sp\_ins tr\_jump\_if\_not::exec\_core(THD\*,sp\_lex\_keeper::reset\_lex\_and \_exec\_core(THD\*,sp\_head::execute(THD\*,sp\_head::execute\_tri gger(THD\*,Table\_triggers\_list::process\_triggers(THD\*,write _record(THD\*,mysql\_insert(THD\*,mysql\_execute\_command(THD\*) ,mysql\_parse(THD\*,Query\_log\_event::do\_apply\_event(Relay\_log info,apply\_event\_and\_update\_pos(Log\_event\*,??,handle\_slave sql,pfs\_spawn\_thread,start\_thread,clone
8 Item\_basic\_constant::cleanup(),cleanup\_items(Item\*),sp\_head: :execute(THD\*,sp\_head::execute\_function(THD\*,Item\_func\_sp: :execute\_impl(THD\*),Item\_func\_sp::execute(),Item\_func\_sp::va l\_int(),Item::val\_bool(),Item\_cond\_and::val\_int(),Item::val bool(),sp\_instr\_jump\_if\_not::exec\_core(THD\*,sp\_lex\_keeper:: reset\_lex\_and\_exec\_core(THD\*,sp\_head::execute(THD\*,sp\_head ::execute\_trigger(THD\*,Table\_triggers\_list::process\_trigger s(THD\*,mysql\_update(THD\*,mysql\_execute\_command(THD\*),mysql parse(THD\*,Query\_log\_event::do\_apply\_event(Relay\_log\_info, apply\_event\_and\_update\_pos(Log\_event\*,??,handle\_slave\_sql,p fs\_spawn\_thread,start\_thread,clone
3 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,start\_t hread,clone
3 pread64,??,??,??,??,??,??,??,??,??,??,??,handler::read\_range first(st\_key\_range,handler::read\_multi\_range\_first(st\_key\_m ulti\_range\*\*,QUICK\_RANGE\_SELECT::get\_next(),??,mysql\_delete (THD\*,mysql\_execute\_command(THD\*),mysql\_parse(THD\*,Query\_l og\_event::do\_apply\_event(Relay\_log\_info,apply\_event\_and\_upda te\_pos(Log\_event\*,??,handle\_slave\_sql,pfs\_spawn\_thread,star t\_thread,clone
3 Item\_basic\_constant::cleanup(),cleanup\_items(Item\*),sp\_head: :execute(THD\*,sp\_head::execute\_function(THD\*,Item\_func\_sp: :execute\_impl(THD\*),Item\_func\_sp::execute(),Item\_func\_sp::va l\_int(),Item::val\_bool(),sp\_instr\_jump\_if\_not::exec\_core(THD ,sp\_lex\_keeper::reset\_lex\_and\_exec\_core(THD,sp\_head::exe cute(THD\*,sp\_head::execute\_trigger(THD\*,Table\_triggers\_lis t::process\_triggers(THD\*,write\_record(THD\*,mysql\_insert(TH D\*,mysql\_execute\_command(THD\*),mysql\_parse(THD\*,Query\_log event::do\_apply\_event(Relay\_log\_info,apply\_event\_and\_update pos(Log\_event\*,??,handle\_slave\_sql,pfs\_spawn\_thread,start\_t hread,clone
3 cleanup\_items(Item\*),sp\_head::execute(THD\*,sp\_head::execute function(THD\*,Item\_func\_sp::execute\_impl(THD\*),Item\_func\_s p::execute(),Item\_func\_sp::val\_str(String\*),Item::save\_in\_fi eld(Field\*,sp\_eval\_expr(THD\*,sp\_rcontext::set\_variable(THD ,sp\_head::execute\_procedure(THD,mysql\_execute\_command(TH D\*),sp\_instr\_stmt::exec\_core(THD\*,sp\_lex\_keeper::reset\_lex and\_exec\_core(THD\*,sp\_instr\_stmt::execute(THD\*,sp\_head::ex ecute(THD\*,sp\_head::execute\_trigger(THD\*,Table\_triggers\_li st::process\_triggers(THD\*,write\_record(THD\*,mysql\_insert(T HD\*,mysql\_execute\_command(THD\*),mysql\_parse(THD\*,Query\_log event::do\_apply\_event(Relay\_log\_info,apply\_event\_and\_update pos(Log\_event\*,??,handle\_slave\_sql,pfs\_spawn\_thread,start thread,clone
2 read,??,my\_net\_read,do\_command(THD\*),do\_handle\_one\_connectio n(THD\*),handle\_one\_connection,pfs\_spawn\_thread,start\_thread, clone
2 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,handler::ha\_w rite\_row(unsigned,write\_record(THD\*,mysql\_insert(THD\*,mysq l\_execute\_command(THD\*),mysql\_parse(THD\*,Query\_log\_event::d o\_apply\_event(Relay\_log\_info,apply\_event\_and\_update\_pos(Log event\*,??,handle\_slave\_sql,pfs\_spawn\_thread,start\_thread,cl one
1 write,my\_write,my\_b\_flush\_io\_cache,MYSQL\_BIN\_LOG::flush\_and sync(bool\*),MYSQL\_BIN\_LOG::appendv(char,??,handle\_slave\_io,p fs\_spawn\_thread,start\_thread,clone
1 write,my\_write,my\_b\_flush\_io\_cache,flush\_master\_info(Master_ info\*,handle\_slave\_io,pfs\_spawn\_thread,start\_thread,clone
1 ??,sp\_instr\_stmt::execute(THD\*,sp\_head::execute(THD\*,sp\_he ad::execute\_function(THD\*,Item\_func\_sp::execute\_impl(THD\*), Item\_func\_sp::execute(),Item\_func\_sp::val\_str(String\*),Item: :save\_in\_field(Field\*,sp\_eval\_expr(THD\*,sp\_rcontext::set\_v ariable(THD\*,sp\_head::execute\_procedure(THD\*,mysql\_execute _command(THD\*),sp\_instr\_stmt::exec\_core(THD\*,sp\_lex\_keeper: :reset\_lex\_and\_exec\_core(THD\*,sp\_instr\_stmt::execute(THD\*, sp\_head::execute(THD\*,sp\_head::execute\_trigger(THD\*,Table_ triggers\_list::process\_triggers(THD\*,write\_record(THD\*,mys ql\_insert(THD\*,mysql\_execute\_command(THD\*),mysql\_parse(THD\* ,Query\_log\_event::do\_apply\_event(Relay\_log\_info,apply\_event \_and\_update\_pos(Log\_event\*,??,handle\_slave\_sql,pfs\_spawn\_th read,start\_thread,clone
1 \_\_printf\_fp,vfprintf,\_\_vsprintf\_chk,\_\_sprintf\_chk,flush\_mast er\_info(Master\_info\*,handle\_slave\_io,pfs\_spawn\_thread,start \_thread,clone
1 poll,my\_connect,mysql\_real\_connect,mysql\_reconnect,??,??,han dle\_slave\_io,pfs\_spawn\_thread,start\_thread,clone
1 memcpy,??,??,??,handler::read\_range\_first(st\_key\_range,handl er::read\_multi\_range\_first(st\_key\_multi\_range\*\*,QUICK\_RANGE \_SELECT::get\_next(),??,mysql\_update(THD\*,mysql\_execute\_comm and(THD\*),mysql\_parse(THD\*,Query\_log\_event::do\_apply\_event( Relay\_log\_info,apply\_event\_and\_update\_pos(Log\_event\*,??,han dle\_slave\_sql,pfs\_spawn\_thread,start\_thread,clone
1 Item::is\_splocal(),??,sp\_instr\_stmt::execute(THD\*,sp\_head:: execute(THD\*,sp\_head::execute\_function(THD\*,Item\_func\_sp:: execute\_impl(THD\*),Item\_func\_sp::execute(),Item\_func\_sp::val \_str(String\*),Item::save\_in\_field(Field\*,sp\_eval\_expr(THD\*, ,sp\_rcontext::set\_variable(THD\*,sp\_head::execute\_procedure( THD\*,mysql\_execute\_command(THD\*),sp\_instr\_stmt::exec\_core(T HD\*,sp\_lex\_keeper::reset\_lex\_and\_exec\_core(THD\*,sp\_instr\_s tmt::execute(THD\*,sp\_head::execute(THD\*,sp\_head::execute\_t rigger(THD\*,Table\_triggers\_list::process\_triggers(THD\*,wri te\_record(THD\*,mysql\_insert(THD\*,mysql\_execute\_command(THD _),mysql\_parse(THD_,Query\_log\_event::do\_apply\_event(Relay\_l og\_info,apply\_event\_and\_update\_pos(Log\_event\*,??,handle\_sla ve\_sql,pfs\_spawn\_thread,start\_thread,clone
1 ??,??,??,??,??,??,??,??,??,??,handler::ha\_write\_row(unsigned ,write\_record(THD\*,mysql\_insert(THD\*,mysql\_execute\_command (THD\*),mysql\_parse(THD\*,Query\_log\_event::do\_apply\_event(Rel ay\_log\_info,apply\_event\_and\_update\_pos(Log\_event\*,??,handle \_slave\_sql,pfs\_spawn\_thread,start\_thread,clone
1 alloc\_root,sp\_rcontext::init(THD\*),sp\_head::execute\_function (THD\*,Item\_func\_sp::execute\_impl(THD\*),Item\_func\_sp::execut e(),Item\_func\_sp::val\_int(),Item::val\_bool(),Item\_cond\_and:: val\_int(),Item::val\_bool(),sp\_instr\_jump\_if\_not::exec\_core(T HD\*,sp\_lex\_keeper::reset\_lex\_and\_exec\_core(THD\*,sp\_head::e xecute(THD\*,sp\_head::execute\_trigger(THD\*,Table\_triggers\_l ist::process\_triggers(THD\*,mysql\_update(THD\*,mysql\_execute \_command(THD\*),mysql\_parse(THD\*,Query\_log\_event::do\_apply\_e vent(Relay\_log\_info,apply\_event\_and\_update\_pos(Log\_event\*,? ?,handle\_slave\_sql,pfs\_spawn\_thread,start\_thread,clone
/tmp# cat pmp.out.with-triggers.percona51
6001
3327 pthread\_cond\_wait@@GLIBC\_2.3.2 ,one\_thread\_per\_connection\_end,handle\_one\_connection,start\_t hread,clone,??
2019 pthread\_cond\_wait@@GLIBC\_2.3.2,??,??,??,??,start\_thread,clone,??
193 select,??,??,start\_thread,clone,??
93 read,??,my\_net\_read,do\_command,handle\_one\_connection,start\_t hread,clone,??
60 select,handle\_connections\_sockets,main,select
60 do\_sigwait,sigwait,signal\_hand,start\_thread,clone,??
59 read,vio\_read\_buff,??,my\_net\_read,cli\_safe\_read,handle\_slave _io,start\_thread,clone,??
15 pthread\_cond\_wait@@GLIBC\_2.3.2,??,??,??,??,??,??,??,??,??,??,??,start\_thread,clone,??
13 pread64,??,??,??,??,??,??,??,??,??,handler::read\_range\_first ,handler::read\_multi\_range\_first,QUICK\_RANGE\_SELECT::get\_nex t,??,mysql\_update,mysql\_execute\_command,mysql\_parse,Query\_lo g\_event::do\_apply\_event,apply\_event\_and\_update\_pos,handle\_sl ave\_sql,start\_thread,clone,??
12 pread64,??,??,??,??,??,??,??,??,??,handler::read\_range\_first ,handler::read\_multi\_range\_first,QUICK\_RANGE\_SELECT::get\_nex t,??,mysql\_delete,mysql\_execute\_command,mysql\_parse,Query\_lo g\_event::do\_apply\_event,apply\_event\_and\_update\_pos,handle\_sl ave\_sql,start\_thread,clone,??
12 pread64,??,??,??,??,??,??,??,??,??,??,??,??,handler::ha\_dele te\_row,mysql\_delete,mysql\_execute\_command,mysql\_parse,Query_ log\_event::do\_apply\_event,apply\_event\_and\_update\_pos,handle\_ slave\_sql,start\_thread,clone,??
11 pwrite64,??,??,??,??,??,start\_thread,clone,??
7 pthread\_cond\_wait@@GLIBC\_2.3.2,??,??,??,??,??,start\_thread,clone,??
7 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,start\_t hread,clone,??
7 fsync,??,??,??,??,??,start\_thread,clone,??
5 pread64,??,??,??,??,??,start\_thread,clone,??
5 pread64,??,??,??,??,??,??,??,??,??,??,??,handler::ha\_write\_r ow,write\_record,mysql\_insert,mysql\_execute\_command,mysql\_par se,Query\_log\_event::do\_apply\_event,apply\_event\_and\_update\_po s,handle\_slave\_sql,start\_thread,clone,??
4 pwrite64,??,??,??,??,??,??,start\_thread,clone,??
3 pthread\_cond\_wait@@GLIBC\_2.3.2,??,??,??,??,??,??,??,??,??,??,start\_thread,clone,??
3 pread64,??,??,??,??,??,??,??,??,??,??,??,??,handler::ha\_upda te\_row,mysql\_update,mysql\_execute\_command,mysql\_parse,Query\_ log\_event::do\_apply\_event,apply\_event\_and\_update\_pos,handle\_ slave\_sql,start\_thread,clone,??
2 pthread\_cond\_wait@@GLIBC\_2.3.2,??,??,??,??,??,??,??,??,start\_thread,clone,??
2 pthread\_cond\_wait@@GLIBC\_2.3.2 ,??,??,??,??,??,??,??,??,handler::ha\_write\_row,write\_record, mysql\_insert,mysql\_execute\_command,mysql\_parse,Query\_log\_eve nt::do\_apply\_event,apply\_event\_and\_update\_pos,handle\_slave\_s ql,start\_thread,clone,??
2 pthread\_cond\_wait@@GLIBC\_2.3.2 ,??,??,??,??,??,??,??,??,??,??,??,??,??,handler::ha\_update\_r ow,mysql\_update,mysql\_execute\_command,mysql\_parse,Query\_log\_ event::do\_apply\_event,apply\_event\_and\_update\_pos,handle\_slav e\_sql,start\_thread,clone,??
2 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,??,start\_thre ad,clone,??
2 pread64,??,??,??,??,??,??,??,??,??,??,??,??,handler::ha\_writ e\_row,write\_record,mysql\_insert,mysql\_execute\_command,mysql\_ parse,Query\_log\_event::do\_apply\_event,apply\_event\_and\_update _pos,handle\_slave\_sql,start\_thread,clone,??
2 fsync,??,??,??,??,start\_thread,clone,??
1 write,my\_write,my\_b\_flush\_io\_cache,flush\_master\_info,handle_ slave\_io,start\_thread,clone,??
1 ??,??,start\_thread,clone,??
1 ??,??,??,??,start\_thread,clone,??
1 _start
1 pthread\_mutex\_trylock,my\_pthread\_fastmutex\_lock,acl\_getroot_ no\_password,Security\_context::change\_security\_context,sp\_hea d::execute\_trigger,Table\_triggers\_list::process\_triggers,mys ql\_update,mysql\_execute\_command,mysql\_parse,Query\_log\_event: :do\_apply\_event,apply\_event\_and\_update\_pos,handle\_slave\_sql, start\_thread,clone,??
1 pthread\_cond\_wait@@GLIBC\_2.3.2,??,??,??,??,??,??,??,??,??,start\_thread,clone,??
1 pthread\_cond\_wait@@GLIBC\_2.3.2 ,??,??,??,??,??,??,handler::read\_range\_first,handler::read\_m ulti\_range\_first,QUICK\_RANGE\_SELECT::get\_next,??,mysql\_updat e,mysql\_execute\_command,mysql\_parse,Query\_log\_event::do\_appl y\_event,apply\_event\_and\_update\_pos,handle\_slave\_sql,start\_th read,clone,??
1 pthread\_cond\_wait@@GLIBC\_2.3.2 ,??,??,??,??,??,??,??,??,handler::ha\_update\_row,mysql\_update ,mysql\_execute\_command,mysql\_parse,Query\_log\_event::do\_apply _event,apply\_event\_and\_update\_pos,handle\_slave\_sql,start\_thr ead,clone,??
1 pthread\_cond\_wait@@GLIBC\_2.3.2 ,??,??,??,??,??,??,??,??,??,handler::ha\_delete\_row,mysql\_del ete,mysql\_execute\_command,mysql\_parse,Query\_log\_event::do\_ap ply\_event,apply\_event\_and\_update\_pos,handle\_slave\_sql,start_ thread,clone,??
1 pthread\_cond\_wait@@GLIBC\_2.3.2 ,??,??,??,??,??,??,??,??,??,??,??,handler::ha\_delete\_row,mys ql\_delete,mysql\_execute\_command,mysql\_parse,Query\_log\_event: :do\_apply\_event,apply\_event\_and\_update\_pos,handle\_slave\_sql, start\_thread,clone,??
1 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,??,handler::r ead\_multi\_range\_next,QUICK\_RANGE\_SELECT::get\_next,??,mysql\_d elete,mysql\_execute\_command,mysql\_parse,Query\_log\_event::do\_ apply\_event,apply\_event\_and\_update\_pos,handle\_slave\_sql,star t\_thread,clone,??
1 open,my\_open,load\_db\_opt,load\_db\_opt\_by\_name,Query\_log\_event ::do\_apply\_event,apply\_event\_and\_update\_pos,handle\_slave\_sql ,start\_thread,clone,??
1 ??,??,??,??,??,??,??,??,handler::ha\_write\_row,write\_record,m ysql\_insert,mysql\_execute\_command,mysql\_parse,Query\_log\_even t::do\_apply\_event,apply\_event\_and\_update\_pos,handle\_slave\_sq l,start\_thread,clone,??
---
### Author:  [@mysqldbahelp](https://forums.percona.com/u/mysqldbahelp)
#### Post date: [August 21, 2011, 11:21pm UTC](https://forums.percona.com/t/performance-regression-with-5-5-around-triggers-functions-and-replication/1654/2 "2011-08-21T23:21:39Z")
What I have noticed is that when I do not have any triggers on the RHEL6 percona 5.5 replica the performance is amazing - really high write IO and output is:
/tmp# cat pmp.out.without-triggers.percona55
1501
601 ??,??,??,??,start\_thread,clone
297 pthread\_cond\_wait@@GLIBC\_2.3.2 ,one\_thread\_per\_connection\_end(THD\*,do\_handle\_one\_connectio n(THD\*),handle\_one\_connection,pfs\_spawn\_thread,start\_thread, clone
180 pthread\_cond\_timedwait@@GLIBC\_2.3.2,??,??,start\_thread,clone
156 select,??,??,start\_thread,clone
60 sigwait,signal\_hand,pfs\_spawn\_thread,start\_thread,clone
60 poll,handle\_connections\_sockets(),mysqld\_main(int,\_\_libc\_st art\_main,\_start
59 read,vio\_read\_buff,??,my\_net\_read,cli\_safe\_read,handle\_slave \_io,pfs\_spawn\_thread,start\_thread,clone
20 pread64,??,??,??,??,??,??,??,??,??,??,??,handler::read\_range \_first(st\_key\_range,handler::read\_multi\_range\_first(st\_key\_m ulti\_range\*\*,QUICK\_RANGE\_SELECT::get\_next(),??,mysql\_update (THD\*,mysql\_execute\_command(THD\*),mysql\_parse(THD\*,Query\_l og\_event::do\_apply\_event(Relay\_log\_info,apply\_event\_and\_upda te\_pos(Log\_event\*,??,handle\_slave\_sql,pfs\_spawn\_thread,star t\_thread,clone
14 pread64,??,??,??,??,??,??,??,??,??,??,??,handler::read\_range _first(st\_key\_range,handler::read\_multi\_range\_first(st\_key\_m ulti\_range\*\*,QUICK\_RANGE\_SELECT::get\_next(),??,mysql\_delete (THD\*,mysql\_execute\_command(THD\*),mysql\_parse(THD\*,Query\_l og\_event::do\_apply\_event(Relay\_log\_info,apply\_event\_and\_upda te\_pos(Log\_event\*,??,handle\_slave\_sql,pfs\_spawn\_thread,star t\_thread,clone
12 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,start\_t hread,clone
8 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,handler::ha\_w rite\_row(unsigned,write\_record(THD\*,mysql\_insert(THD\*,mysq l\_execute\_command(THD\*),mysql\_parse(THD\*,Query\_log\_event::d o\_apply\_event(Relay\_log\_info,apply\_event\_and\_update\_pos(Log_ event\*,??,handle\_slave\_sql,pfs\_spawn\_thread,start\_thread,cl one
6 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,star t\_thread,clone
3 read,??,my\_net\_read,do\_command(THD\*),do\_handle\_one\_connectio n(THD\*),handle\_one\_connection,pfs\_spawn\_thread,start\_thread, clone
3 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,??,handler::h a\_write\_row(unsigned,write\_record(THD\*,mysql\_insert(THD\*,m ysql\_execute\_command(THD\*),mysql\_parse(THD\*,Query\_log\_event ::do\_apply\_event(Relay\_log\_info,apply\_event\_and\_update\_pos(L og\_event\*,??,handle\_slave\_sql,pfs\_spawn\_thread,start\_thread ,clone
2 pthread\_cond\_wait@@GLIBC\_2.3.2,??,??,??,??,??,??,??,??,??,??,start\_thread,clone
2 pthread\_cond\_wait@@GLIBC\_2.3.2 ,??,??,??,??,??,??,handler::read\_range\_first(st\_key\_range,ha ndler::read\_multi\_range\_first(st\_key\_multi\_range\*\*,QUICK\_RA NGE\_SELECT::get\_next(),??,mysql\_update(THD\*,mysql\_execute\_c ommand(THD\*),mysql\_parse(THD\*,Query\_log\_event::do\_apply\_eve nt(Relay\_log\_info,apply\_event\_and\_update\_pos(Log\_event\*,??, handle\_slave\_sql,pfs\_spawn\_thread,start\_thread,clone
2 pthread\_cond\_wait@@GLIBC\_2.3.2 ,??,??,??,??,??,??,??,??,??,handler::ha\_update\_row(unsigned, mysql\_update(THD\*,mysql\_execute\_command(THD\*),mysql\_parse(T HD\*,Query\_log\_event::do\_apply\_event(Relay\_log\_info,apply\_ev ent\_and\_update\_pos(Log\_event\*,??,handle\_slave\_sql,pfs\_spawn \_thread,start\_thread,clone
2 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,s tart\_thread,clone
2 MYSQLparse(void\*),parse\_sql(THD\*,mysql\_parse(THD\*,Query\_lo g\_event::do\_apply\_event(Relay\_log\_info,apply\_event\_and\_updat e\_pos(Log\_event\*,??,handle\_slave\_sql,pfs\_spawn\_thread,start \_thread,clone
1 ??,??,??,??,??,??,??,??,??,??,??,??,start\_thread,clone
1 pwrite64,??,??,??,??,??,??,??,??,??,??,??,handler::ha\_update \_row(unsigned,mysql\_update(THD\*,mysql\_execute\_command(THD\*) ,mysql\_parse(THD\*,Query\_log\_event::do\_apply\_event(Relay\_log \_info,apply\_event\_and\_update\_pos(Log\_event\*,??,handle\_slave \_sql,pfs\_spawn\_thread,start\_thread,clone
1 pread64,??,??,??,??,??,??,??,??,??,??,??,??,handler::read\_ra nge\_first(st\_key\_range,handler::read\_multi\_range\_first(st\_ke y\_multi\_range\*\*,QUICK\_RANGE\_SELECT::get\_next(),??,mysql\_upd ate(THD\*,mysql\_execute\_command(THD\*),mysql\_parse(THD\*,Quer y\_log\_event::do\_apply\_event(Relay\_log\_info,apply\_event\_and\_u pdate\_pos(Log\_event\*,??,handle\_slave\_sql,pfs\_spawn\_thread,s tart\_thread,clone
1 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,handler ::ha\_update\_row(unsigned,mysql\_update(THD\*,mysql\_execute\_co mmand(THD\*),mysql\_parse(THD\*,Query\_log\_event::do\_apply\_even t(Relay\_log\_info,apply\_event\_and\_update\_pos(Log\_event\*,??,h andle\_slave\_sql,pfs\_spawn\_thread,start\_thread,clone
1 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,hand ler::ha\_update\_row(unsigned,mysql\_update(THD\*,mysql\_execute _command(THD\*),mysql\_parse(THD\*,Query\_log\_event::do\_apply\_e vent(Relay\_log\_info,apply\_event\_and\_update\_pos(Log\_event\*,? ?,handle\_slave\_sql,pfs\_spawn\_thread,start\_thread,clone
1 my\_timer\_cycles,get\_timer\_value(enum\_timer\_name),??,??,??,?? ,??,??,??,handler::ha\_write\_row(unsigned,write\_record(THD\*, mysql\_insert(THD\*,mysql\_execute\_command(THD\*),mysql\_parse(T HD\*,Query\_log\_event::do\_apply\_event(Relay\_log\_info,apply\_ev ent\_and\_update\_pos(Log\_event\*,??,handle\_slave\_sql,pfs\_spawn thread,start\_thread,clone
1 insert\_events\_waits\_history\_long(PFS\_events\_waits\*),??,load db\_opt(THD\*,load\_db\_opt\_by\_name(THD\*,Query\_log\_event::do\_a pply\_event(Relay\_log\_info,apply\_event\_and\_update\_pos(Log\_eve nt\*,??,handle\_slave\_sql,pfs\_spawn\_thread,start\_thread,clone
1 ??,??,handle\_slave\_io,pfs\_spawn\_thread,start\_thread,clone
1 ??,??,??,??,handler::read\_range\_first(st\_key\_range,handler:: read\_multi\_range\_first(st\_key\_multi\_range\*\*,QUICK\_RANGE\_SEL ECT::get\_next(),??,mysql\_update(THD\*,mysql\_execute\_command( THD\*),mysql\_parse(THD\*,Query\_log\_event::do\_apply\_event(Rela y\_log\_info,apply\_event\_and\_update\_pos(Log\_event\*,??,handle_ slave\_sql,pfs\_spawn\_thread,start\_thread,clone
1 ??,??,??,??,??,??,??,??,handler::read\_range\_first(st\_key\_ran ge,handler::read\_multi\_range\_first(st\_key\_multi\_range\*\*,QUI CK\_RANGE\_SELECT::get\_next(),??,mysql\_update(THD\*,mysql\_exec ute\_command(THD\*),mysql\_parse(THD\*,Query\_log\_event::do\_appl y\_event(Relay\_log\_info,apply\_event\_and\_update\_pos(Log\_event\* ,??,handle\_slave\_sql,pfs\_spawn\_thread,start\_thread,clone
1 get\_table\_share(THD\*,open\_table(THD\*,open\_tables(THD\*,mys ql\_update(THD\*,mysql\_execute\_command(THD\*),mysql\_parse(THD\* ,Query\_log\_event::do\_apply\_event(Relay\_log\_info,apply\_event \_and\_update\_pos(Log\_event\*,??,handle\_slave\_sql,pfs\_spawn\_th read,start\_thread,clone
* * *
What is also interesting is that when we start MySQL replication on the percona5.5 slave the QPS is high but it slowly degrades overtime. [a lame workaround we’re using is to restart the sql thread every minute!]
Looking at the pmp output the Percona5.5 rhel6 box is spending lots of its time within cleanup\_items(Item\*),
So what I think is happening is that there is a session memory issue or some such and given the SQL thread uses that single ‘connection’, we’re having a performance regression.
I have graphs of bufferpool and queries though they really only reflect a slowly degrading server over time.
Have you guys come across this? Any suggestions would be appreciated!
Kind regards,
Trent Hornibrook
@mysqldbahelp
---
### Author:  [@xaprb](https://forums.percona.com/u/xaprb)
#### Post date: [September 1, 2011, 10:04am UTC](https://forums.percona.com/t/performance-regression-with-5-5-around-triggers-functions-and-replication/1654/3 "2011-09-01T10:04:39Z")
Trent,
Sorry for the slow reply. PMP output is useful here but could be misleading. I would suggest that since blocking doesn’t appear to be the problem, but rather actual consumption of CPU, that you look at oprofile instead. Let’s see what that reveals.