Details

    • Task
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.0.7
    • None
    • None

    Description

      Kentoku Shiba (author of Spider storage engine) suggests metadata_lock_info plugin:

      This plugin makes it possible knowing "who has metadata locks". In development stage, sometimes DBAs meet metadata locks when using alter table statement. This metadata locks are sometimes caused by GUI tools. In service stage, sometimes DBAs meet metadata locks when using alter table statement. This metadata locks are sometimes caused by long batch processing. In many cases, the DBAs need to judge immediately. So I made it for all DBAs.

      Plugin is available at:
      lp:~kentokushiba/maria/10.0.3-metadata_lock_info

      Attachments

        Issue Links

          Activity

            Some strange names in columns....

            SELECT GET_LOCK('abc',1000);
            select * from METADATA_LOCK_INFO

            you will get DB = 'abc', that's not a true information, the 'abc' is the key of the MDL lock, not the database (DB), maybe we should have a "KEY" and a "SUB_KEY" here? example... DB and TABLE, when a table lock, or GET_LOCK key name and '' for sub key

            maybe we could change somethings...
            instead of "ID", put "THREAD_ID", since it's not a lock id and it's not the query id, (if there's a global unique mdl id could be nice put it here)
            instead of "DB" put "KEY"?
            instead of "TABLE" put "SUB KEY"?

            from mdl.h
            @remark The key for a table is <mdl_namespace><database name><table name>
            maybe we should call LOCK_TYPE as "MDL_NAMESPACE" ? but it don't tell what name is used for GET_LOCK for example...

            from mdl.cc, why not use "m_namespace_to_wait_state_name" names, or maybe add this information to mdl.h ? the "lock_type" (namescape) don't have a name in mdl.h yet, maybe should be nice one there?

            PSI_stage_info MDL_key::m_namespace_to_wait_state_name[NAMESPACE_END]=
            {

            {0, "Waiting for global read lock", 0}

            ,

            {0, "Waiting for schema metadata lock", 0}

            ,

            {0, "Waiting for table metadata lock", 0}

            ,

            {0, "Waiting for stored function metadata lock", 0}

            ,

            {0, "Waiting for stored procedure metadata lock", 0}

            ,

            {0, "Waiting for trigger metadata lock", 0}

            ,

            {0, "Waiting for event metadata lock", 0}

            ,

            {0, "Waiting for commit lock", 0}

            ,

            {0, "User lock", 0}

            /* Be compatible with old status. */
            };

            well nice plugin, i'm using it now at production and it's very nice to know what is blocking my queries
            thanks!

            rspadim roberto spadim added a comment - Some strange names in columns.... SELECT GET_LOCK('abc',1000); select * from METADATA_LOCK_INFO you will get DB = 'abc', that's not a true information, the 'abc' is the key of the MDL lock, not the database (DB), maybe we should have a "KEY" and a "SUB_KEY" here? example... DB and TABLE, when a table lock, or GET_LOCK key name and '' for sub key maybe we could change somethings... instead of "ID", put "THREAD_ID", since it's not a lock id and it's not the query id, (if there's a global unique mdl id could be nice put it here) instead of "DB" put "KEY"? instead of "TABLE" put "SUB KEY"? from mdl.h @remark The key for a table is <mdl_namespace> <database name> <table name> maybe we should call LOCK_TYPE as "MDL_NAMESPACE" ? but it don't tell what name is used for GET_LOCK for example... from mdl.cc, why not use "m_namespace_to_wait_state_name" names, or maybe add this information to mdl.h ? the "lock_type" (namescape) don't have a name in mdl.h yet, maybe should be nice one there? PSI_stage_info MDL_key::m_namespace_to_wait_state_name [NAMESPACE_END] = { {0, "Waiting for global read lock", 0} , {0, "Waiting for schema metadata lock", 0} , {0, "Waiting for table metadata lock", 0} , {0, "Waiting for stored function metadata lock", 0} , {0, "Waiting for stored procedure metadata lock", 0} , {0, "Waiting for trigger metadata lock", 0} , {0, "Waiting for event metadata lock", 0} , {0, "Waiting for commit lock", 0} , {0, "User lock", 0} /* Be compatible with old status. */ }; well nice plugin, i'm using it now at production and it's very nice to know what is blocking my queries thanks!

            for query id:

            table->field[0]>store((longlong) tmp_thd>thread_id, TRUE);

            table->field[ QUERY_ID COLUMN NUMBER ]>store((longlong) tmp_thd>query_id, TRUE);

            rspadim roberto spadim added a comment - for query id: table->field [0] >store((longlong) tmp_thd >thread_id, TRUE); table->field[ QUERY_ID COLUMN NUMBER ] >store((longlong) tmp_thd >query_id, TRUE);

            other column to add is the "duration" variable
            /** Duration of metadata lock. */

            enum enum_mdl_duration

            { /** Locks with statement duration are automatically released at the end of statement or transaction. */ MDL_STATEMENT= 0, /** Locks with transaction duration are automatically released at the end of transaction. */ MDL_TRANSACTION, /** Locks with explicit duration survive the end of statement and transaction. They have to be released explicitly by calling MDL_context::release_lock(). */ MDL_EXPLICIT, /* This should be the last ! */ MDL_DURATION_END }
            rspadim roberto spadim added a comment - other column to add is the "duration" variable /** Duration of metadata lock. */ enum enum_mdl_duration { /** Locks with statement duration are automatically released at the end of statement or transaction. */ MDL_STATEMENT= 0, /** Locks with transaction duration are automatically released at the end of transaction. */ MDL_TRANSACTION, /** Locks with explicit duration survive the end of statement and transaction. They have to be released explicitly by calling MDL_context::release_lock(). */ MDL_EXPLICIT, /* This should be the last ! */ MDL_DURATION_END }

            Added file with changes that i talked in comments

            rspadim roberto spadim added a comment - Added file with changes that i talked in comments

            Pushed to 10.0.7,
            revision-id: kentokushiba@gmail.com-20131210153104-e8h5m61072s2boqd
            revision-id: svoj@mariadb.org-20131213084245-f8w1v2lirtmqbm0e

            svoj Sergey Vojtovich added a comment - Pushed to 10.0.7, revision-id: kentokushiba@gmail.com-20131210153104-e8h5m61072s2boqd revision-id: svoj@mariadb.org-20131213084245-f8w1v2lirtmqbm0e

            People

              svoj Sergey Vojtovich
              svoj Sergey Vojtovich
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.