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,sta rt_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,ha ndle_slave _io,pfs_spawn_thread,start_thread,clone
18 cleanup_items(Item*),sp_head::execute(THD*,,sp_hea d::execute _function(THD*,,Item_func_sp::execute_impl(THD*),I tem_func_s p::execute(),Item_func_sp::val_int(),Item::val_boo l(),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_co re(THD*,,s p_head::execute(THD*,,sp_head::execute_trigger(THD *,,Table_t riggers_list:rocess_triggers(THD*,,mysql_update(THD*,,mysq l_execute_command(THD*),mysql_parse(THD*,,Query_lo g_event::d o_apply_event(Relay_log_info,apply_event_and_updat e_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_firs t(st_key_m ulti_range**,,QUICK_RANGE_SELECT::get_next(),??,my sql_update (THD*,,mysql_execute_command(THD*),mysql_parse(THD *,,Query_l og_event::do_apply_event(Relay_log_info,apply_even t_and_upda te_pos(Log_event*,,??,handle_slave_sql,pfs_spawn_t hread,star t_thread,clone
9 cleanup_items(Item*),sp_head::execute(THD*,,sp_hea d::execute _function(THD*,,Item_func_sp::execute_impl(THD*),I tem_func_s p::execute(),Item_func_sp::val_int(),Item::val_boo l(),sp_ins tr_jump_if_not::exec_core(THD*,,sp_lex_keeper::res et_lex_and _exec_core(THD*,,sp_head::execute(THD*,,sp_head::e xecute_tri gger(THD*,,Table_triggers_list:rocess_triggers(THD*,,write _record(THD*,,mysql_insert(THD*,,mysql_execute_com mand(THD*) ,mysql_parse(THD*,,Query_log_event::do_apply_event (Relay_log _info,apply_event_and_update_pos(Log_event*,,??,ha ndle_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*,,Ite m_func_sp: :execute_impl(THD*),Item_func_sp::execute(),Item_f unc_sp::va l_int(),Item::val_bool(),Item_cond_and::val_int(), Item::val_ bool(),sp_instr_jump_if_not::exec_core(THD*,,sp_le x_keeper:: reset_lex_and_exec_core(THD*,,sp_head::execute(THD *,,sp_head ::execute_trigger(THD*,,Table_triggers_list:rocess_trigger s(THD*,,mysql_update(THD*,,mysql_execute_command(T HD*),mysql _parse(THD*,,Query_log_event::do_apply_event(Relay _log_info, apply_event_and_update_pos(Log_event*,,??,handle_s lave_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_firs t(st_key_m ulti_range**,,QUICK_RANGE_SELECT::get_next(),??,my sql_delete (THD*,,mysql_execute_command(THD*),mysql_parse(THD *,,Query_l og_event::do_apply_event(Relay_log_info,apply_even t_and_upda te_pos(Log_event*,,??,handle_slave_sql,pfs_spawn_t hread,star t_thread,clone
3 Item_basic_constant::cleanup(),cleanup_items(Item* ),sp_head: :execute(THD*,,sp_head::execute_function(THD*,,Ite m_func_sp: :execute_impl(THD*),Item_func_sp::execute(),Item_f unc_sp::va l_int(),Item::val_bool(),sp_instr_jump_if_not::exe c_core(THD *,,sp_lex_keeper::reset_lex_and_exec_core(THD*,,sp _head::exe cute(THD*,,sp_head::execute_trigger(THD*,,Table_tr iggers_lis t:rocess_triggers(THD*,,write_record(THD*,,mysql_ins ert(TH D*,,mysql_execute_command(THD*),mysql_parse(THD*,, Query_log_ event::do_apply_event(Relay_log_info,apply_event_a nd_update_ pos(Log_event*,,??,handle_slave_sql,pfs_spawn_thre ad,start_t hread,clone
3 cleanup_items(Item*),sp_head::execute(THD*,,sp_hea d::execute _function(THD*,,Item_func_sp::execute_impl(THD*),I tem_func_s p::execute(),Item_func_sp::val_str(String*),Item:: save_in_fi eld(Field*,,sp_eval_expr(THD*,,sp_rcontext::set_va riable(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*,,s p_head::ex ecute(THD*,,sp_head::execute_trigger(THD*,,Table_t riggers_li st:rocess_triggers(THD*,,write_record(THD*,,mysql_ins ert(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_thr ead,start_ thread,clone
2 read,??,my_net_read,do_command(THD*),do_handle_one _connectio n(THD*),handle_one_connection,pfs_spawn_thread,sta rt_thread, clone
2 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,han dler::ha_w rite_row(unsigned,write_record(THD*,,mysql_insert( THD*,,mysq l_execute_command(THD*),mysql_parse(THD*,,Query_lo g_event::d o_apply_event(Relay_log_info,apply_event_and_updat e_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_in fo(Master_ info*,,handle_slave_io,pfs_spawn_thread,start_thre ad,clone
1 ??,sp_instr_stmt::execute(THD*,,sp_head::execute(T HD*,,sp_he ad::execute_function(THD*,,Item_func_sp::execute_i mpl(THD*), Item_func_sp::execute(),Item_func_sp::val_str(Stri ng*),Item: :save_in_field(Field*,,sp_eval_expr(THD*,,sp_rcont ext::set_v ariable(THD*,,sp_head::execute_procedure(THD*,,mys ql_execute _command(THD*),sp_instr_stmt::exec_core(THD*,,sp_l ex_keeper: :reset_lex_and_exec_core(THD*,,sp_instr_stmt::exec ute(THD*,, sp_head::execute(THD*,,sp_head::execute_trigger(TH D*,,Table_ triggers_list:rocess_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,a pply_event _and_update_pos(Log_event*,,??,handle_slave_sql,pf s_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_th read,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_r ange,handl er::read_multi_range_first(st_key_multi_range**,,Q UICK_RANGE _SELECT::get_next(),??,mysql_update(THD*,,mysql_ex ecute_comm and(THD*),mysql_parse(THD*,,Query_log_event::do_ap ply_event( Relay_log_info,apply_event_and_update_pos(Log_even t*,,??,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_fu nc_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::e xec_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:rocess_triggers(THD*,,wri te_record(THD*,,mysql_insert(THD*,,mysql_execute_c ommand(THD *),mysql_parse(THD*,,Query_log_event::do_apply_eve nt(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_ro w(unsigned ,write_record(THD*,,mysql_insert(THD*,,mysql_execu te_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::execut e_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::e xec_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:rocess_triggers(THD*,,mysql_update(THD*,,mysql_exe cute _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,c lone




/tmp# cat pmp.out.with-triggers.percona51
6001
3327 pthread_cond_wait@@GLIBC_2.3.2 ,one_thread_per_connection_end,handle_one_connecti on,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_connecti on,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,ha ndle_slave _io,start_thread,clone,??
15 pthread_cond_wait@@GLIBC_2.3.2,??,??,??,??,??,??,??,??,??,??,??,start_thread,clo ne,??
13 pread64,??,??,??,??,??,??,??,??,??,handler::read_r ange_first ,handler::read_multi_range_first,QUICK_RANGE_SELEC T::get_nex t,??,mysql_update,mysql_execute_command,mysql_pars e,Query_lo g_event::do_apply_event,apply_event_and_update_pos ,handle_sl ave_sql,start_thread,clone,??
12 pread64,??,??,??,??,??,??,??,??,??,handler::read_r ange_first ,handler::read_multi_range_first,QUICK_RANGE_SELEC T::get_nex t,??,mysql_delete,mysql_execute_command,mysql_pars e,Query_lo g_event::do_apply_event,apply_event_and_update_pos ,handle_sl ave_sql,start_thread,clone,??
12 pread64,??,??,??,??,??,??,??,??,??,??,??,??,handle r::ha_dele te_row,mysql_delete,mysql_execute_command,mysql_pa rse,Query_ log_event::do_apply_event,apply_event_and_update_p os,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,??,??,??,??,??,??,??,??,??,??,??,??,handle r::ha_upda te_row,mysql_update,mysql_execute_command,mysql_pa rse,Query_ log_event::do_apply_event,apply_event_and_update_p os,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,wri te_record, mysql_insert,mysql_execute_command,mysql_parse,Que ry_log_eve nt::do_apply_event,apply_event_and_update_pos,hand le_slave_s ql,start_thread,clone,??
2 pthread_cond_wait@@GLIBC_2.3.2 ,??,??,??,??,??,??,??,??,??,??,??,??,??,handler::h a_update_r ow,mysql_update,mysql_execute_command,mysql_parse, Query_log_ event::do_apply_event,apply_event_and_update_pos,h andle_slav e_sql,start_thread,clone,??
2 pread64,??,??,??,??,??,??,??,??,??,??,??,??,??,??, start_thre ad,clone,??
2 pread64,??,??,??,??,??,??,??,??,??,??,??,??,handle r::ha_writ e_row,write_record,mysql_insert,mysql_execute_comm and,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_in fo,handle_ slave_io,start_thread,clone,??
1 ??,??,start_thread,clone,??
1 ??,??,??,??,start_thread,clone,??
1 _start
1 pthread_mutex_trylock,my_pthread_fastmutex_lock,ac l_getroot_ no_password,Security_context::change_security_cont ext,sp_hea d::execute_trigger,Table_triggers_list:rocess_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,handl er::read_m ulti_range_first,QUICK_RANGE_SELECT::get_next,??,m ysql_updat e,mysql_execute_command,mysql_parse,Query_log_even t::do_appl y_event,apply_event_and_update_pos,handle_slave_sq l,start_th read,clone,??
1 pthread_cond_wait@@GLIBC_2.3.2 ,??,??,??,??,??,??,??,??,handler::ha_update_row,my sql_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_ev ent::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_dele te_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_slav e_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,writ e_record,m ysql_insert,mysql_execute_command,mysql_parse,Quer y_log_even t::do_apply_event,apply_event_and_update_pos,handl e_slave_sq l,start_thread,clone,??