GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Checking Permissions thread state

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Checking Permissions thread state

    Since upgrading from mysql 5.0.41 to Percona 5.1.57-rel12.8-log we are experiencing queries which are stuck in thread state "Checking Permissions".

    the issue is most obvious when running stored procedures and is not constant. After a mysql restart the issue will disappear, and will return after a few days.

    Have not found any good hints on the internet on how to debug this.

    any hints?

    thanks

    marcel

  • #2
    Did you run the mysql_upgrade command properly so that the privilege tables was updated?

    Comment


    • #3
      It is also possible that there is a bug in the version of MySQL you're running. (Unlikely that it's a bug in Percona's modifications; we don't change anything to do with privileges, although it's possible that the bug is elsewhere and just manifests here.)

      Next time it hangs, get stack traces with poormansprofiler.org and paste them here. Also, it depends on whether it's idle when hung, or burning CPU time. If it's burning CPU (not really hung, but just doing a lot of useless work), then run oprofile for 30 seconds and paste the opreport.

      Comment


      • #4
        mysql_upgrade was not relevant, as we upgraded using an imported dump, so not direct binary data.

        Will try to run poormansprofiler.org next time

        thanks

        marcel

        Comment


        • #5
          Have you also imported the database named mysql?

          Comment


          • #6
            We caught the problem red handed today, here the poormansprofiler output

            hope it helps. mysql_upgrade didn't give any relieve. reloading myql, adding more memory/moving tmp to ramdisk did not help.

            Marcel

            root@mysql1:~# ./poormansprofile.sh
            108
            49
            pthread_cond_wait@@GLIBC_2.3.2 ,cache_thread,optimized,handle_one_connection,star t_thread,c lone,??
            36
            __lll_lock_wait,_L_lock_1172,pthread_mutex_lock,fi ll_schema_ user_privileges,get_schema_tables_result,JOIN::exe c,subselec t_single_select_engine::exec,Item_subselect::exec, Item_in_su bselect::val_bool,Item_in_optimizer::val_int,Item: :val_bool, Item_func_not::val_int,Item::val_bool,Item_cond_an d::val_int ,evaluate_join_record,sub_select,do_select,JOIN::e xec,subsel ect_single_select_engine::exec,Item_subselect::exe c,Item_in_ subselect::val_bool,Item_in_optimizer::val_int,Ite m::val_boo l,Item_cond_and::val_int,evaluate_join_record,sub_ select,do_ select,JOIN::exec,mysql_select,handle_select,execu te_sqlcom_ select,mysql_execute_command,mysql_open_cursor,sp_ cursor:p en,sp_instr_copen::exec_core,sp_lex_keeper::reset_ lex_and_ex ec_core,sp_instr_copen::execute,sp_head::execute,s p_head::ex ecute_procedure,mysql_execute_command,sp_instr_stm t::exec_co re,sp_lex_keeper::reset_lex_and_exec_core,sp_instr _stmt::exe cute,sp_head::execute,sp_head::execute_procedure,E vent_job_d ata::execute,Event_worker_thread::run,event_worker _thread,st art_thread,clone,??
            10
            pthread_cond_wait@@GLIBC_2.3.2 ,os_event_wait_low,os_aio_simulated_handle,fil_aio _wait,io_h andler_thread,start_thread,clone,??
            1 sigwait,signal_hand,start_thread,clone,??
            1
            pthread_cond_timedwait@@GLIBC_2.3.2,os_event_wait_time,srv_purge_thread,start_thread, clone,??
            1
            pthread_cond_timedwait@@GLIBC_2.3.2 ,os_event_wait_time,srv_monitor_thread,start_threa d,clone,??
            1
            pthread_cond_timedwait@@GLIBC_2.3.2,os_event_wait_time,srv_master_thread,start_thread ,clone,??
            1
            pthread_cond_timedwait@@GLIBC_2.3.2 ,os_event_wait_time,srv_LRU_dump_restore_thread,st art_thread ,clone,??
            1
            pthread_cond_timedwait@@GLIBC_2.3.2 ,os_event_wait_time,srv_lock_timeout_thread,start_ thread,clo ne,??
            1
            pthread_cond_timedwait@@GLIBC_2.3.2 ,os_event_wait_time,srv_error_monitor_thread,start _thread,cl one,??
            1
            pthread_cond_timedwait@@GLIBC_2.3.2 ,Event_queue::cond_wait,Event_queue::get_top_for_e xecution_i f_time,Event_scheduler::run,event_scheduler_thread ,start_thr ead,clone,??
            1 poll,handle_connections_sockets,main
            1
            memcpy,heap_scan,ha_heap::rnd_next,rr_sequential,s ub_select, do_select,JOIN::exec,subselect_single_select_engin e::exec,It em_subselect::exec,Item_in_subselect::val_bool,Ite m_in_optim izer::val_int,Item::val_bool,Item_func_not::val_in t,Item::va l_bool,Item_cond_and::val_int,evaluate_join_record ,sub_selec t,do_select,JOIN::exec,subselect_single_select_eng ine::exec, Item_subselect::exec,Item_in_subselect::val_bool,I tem_in_opt imizer::val_int,Item::val_bool,Item_cond_and::val_ int,evalua te_join_record,sub_select,do_select,JOIN::exec,mys ql_select, handle_select,execute_sqlcom_select,mysql_execute_ command,my sql_open_cursor,sp_cursor:pen,sp_instr_copen::exec_core,sp _lex_keeper::reset_lex_and_exec_core,sp_instr_cope n::execute ,sp_head::execute,sp_head::execute_procedure,mysql _execute_c ommand,sp_instr_stmt::exec_core,sp_lex_keeper::res et_lex_and _exec_core,sp_instr_stmt::execute,sp_head::execute ,sp_head:: execute_procedure,Event_job_data::execute,Event_wo rker_threa d::run,event_worker_thread,start_thread,clone,??
            1
            block=0x7f046bbf3a58,,block=0x7f046bbf3a58,,block= 0x7f046bbf 3a58,,btr_search_guess_on_hash,btr_cur_search_to_n th_level,b tr_pcur_open_with_no_init_func,optimized,optimized ,ha_innoba se::general_fetch,join_read_next_same,sub_select,e valuate_jo in_record,sub_select,evaluate_join_record,sub_sele ct,do_sele ct,JOIN::exec,mysql_select,handle_select,execute_s qlcom_sele ct,mysql_execute_command,mysql_parse,dispatch_comm and,do_com mand,handle_one_connection,start_thread,clone,??
            1
            at,Item_in_optimizer::val_int,Item::val_bool,Item_ func_not:: val_int,Item::val_bool,Item_cond_and::val_int,eval uate_join_ record,sub_select,do_select,JOIN::exec,subselect_s ingle_sele ct_engine::exec,Item_subselect::exec,Item_in_subse lect::val_ bool,Item_in_optimizer::val_int,Item::val_bool,Ite m_cond_and ::val_int,evaluate_join_record,sub_select,do_selec t,JOIN::ex ec,mysql_select,handle_select,execute_sqlcom_selec t,mysql_ex ecute_command,mysql_open_cursor,sp_cursor:pen,sp_instr_cop en::exec_core,sp_lex_keeper::reset_lex_and_exec_co re,sp_inst r_copen::execute,sp_head::execute,sp_head::execute _procedure ,mysql_execute_command,sp_instr_stmt::exec_core,sp _lex_keepe r::reset_lex_and_exec_core,sp_instr_stmt::execute, sp_head::e xecute,sp_head::execute_procedure,Event_job_data:: execute,Ev ent_worker_thread::run,event_worker_thread,start_t hread,clon e,??

            Comment

            Working...
            X