•  

Announcement

Announcement Module
Collapse
No announcement yet.

Metadata Locking issue with percona 5.5

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

  • Metadata Locking issue with percona 5.5

    #1
    The short story is that I have a magento installation.

    It ran on a mysql 5.1.41 (ubuntu). It was crashing every now and then.
    (when trying to insert or delete from certain tables that ware running sone DDL statements like truncate and alter table).

    I upgraded to mysql 5.5.15 (from source), and the crashes became rarer ... but still happening. So I moved to Percona 5.5.20-rel24.

    No I no longer had crashes in the last month or so, but I am running (at each reindex) into another issue.

    | Id | User | Host | db | Command | Time |
    State |
    Info

    | Rows_sent | Rows_examined | Rows_read |
    +--------+-------+---------------------+-------+---------+-- ----+---------------------------------+--------------------- ------------------------------------------------------------ ---------------------+-----------+---------------+---------- -+
    | 223351 | mydatabase | www.local:40590 | mydatabase | Query |
    372 | Waiting for table metadata lock | TRUNCATE TABLE
    catalogsearch_result
    | 0 | 0 | 1 |
    | 224815 | mydatabase | www.local:52419 | mydatabase | Query |
    92 | Waiting for table metadata lock | TRUNCATE TABLE
    catalogsearch_result
    | 0 | 0 | 1 |
    | 225117 | mydatabase | www.local:52467 | mydatabase | Query |
    37 | Waiting for table metadata lock | TRUNCATE TABLE catalogsearch_result


    The problem I have is that no other running query is related to catalogsearch_result. And no lock is shown in innodb status.

    Am am somewhat confused.

    As I read here :
    http://blog.ulf-wendel.de/2011/waiting-for-table-metadata-lo ck-and-peclmysqlnd_ms/

    I know it is possible to provoke metadata lock using an valid sql on a nonexisting table . But that's not the case here. Even if is a rather unexpected behaviour and rogue behaviour, I have no DROPS and no operations with nonexisting tables..

    The bug seems to be a lot like :
    http://bugs.mysql.com/bug.php?id=60563
    and this
    http://bugs.mysql.com/bug.php?id=61935


    Here is also a innodb status :
    http://pastebin.com/0A022ASv

    First thing would be ... How do I see what query issued a metadata lock ? Second would be of course ... how do I avoid metadata lock issues.
    Tags: None

  • #2
    There are a couple of ways to get more information on locking. One is "mysqladmin debug". Unfortunately there isn't as much instrumentation as I'd like in this area (yet). I think that the changes to locking in 5.5 solved a lot of problems that are pretty serious in 5.1, but as you're seeing, there is still a possibility of such problems.

    You are pre-filtering the data you're showing to some extent; the question is not only whether any other running query is related to the table you're trying to truncate, but also whether any open connection (open transaction) has a lock. It may also be waiting on a global lock (I don't recall off the top of my head) instead of a table lock that's specific to this table. Please check the manual and see if you see more information on that; if not, source code investigation may be needed.

    I would suggest that you may consider not using TRUNCATE so heavily. It's not what you "ought" to have to do to make this work well, but unfortunately we know that schema/metadata handling in the server is not as sophisticated as the storage-engine locking that InnoDB does, and you are more likely to run into such problems.

    Comment


    • #3
      Hi Baron and thank you for your reply.

      Here is a more detailed "explanation" of what is happening :
      http://pastebin.com/Nmxj1H5H

      I have a suspicion that this behaviour is caused by some exclusive global lock (as not even SELECTS no longer work). My suspicion strongly is tied to the way mysql handles the inter-engines metadata locks . I cannot even use phpmyadmin on OTHER databases during the "blackouts" ).

      I have currently no idea about how the "catch" the perpetrator query that effectively locks me out of the database. Until now, explain, show innodb status and show full processlist helped, but now seems that I ran into a deeper problem which I do not know how to debug (especially in a production environment).

      Also, if it helps, on older mysqls (5.1.40, 5.5.15) i had problems with random (every two weeks or so) crashes. This is the reason I upgraded up to latest Percona. It no longer crashes every other week, but locking me out once or twice every day for 8-10-15 minutes is annoying.

      If it helps improve the software / confirm a bug I am open to send any info you need. I am working to determine a scenario where this happens, so I can reproduce the bug. Right now the whole thing seems random . So I am open to any suggestions. (where to look, what tools to use to get more data, anything at all ).

      Comment


      • #4
        It looks like you need to enable full logging, wait for the problem to happen again, and then dig backwards through the log to find out what each of the currently open connections has done recently. It is tedious and requires a lot of attention to detail but it will probably find the problem. You might find some of the log analysis tools in Percona Toolkit to be helpful, such as pt-query-digest's timeline feature. We do this type of thing a lot, actually

        Comment


        • #5
          Hi again Baron and thank you for your time.
          By "enable full logging" what exactly do you mean ? I have enabled both bin-log and general-log (query log). I am hunting now a lockdown .
          I downloaded the percona toolkit .

          Any other suggestion ?

          Comment


          • #6
            general-log is probably where I would look.

            Comment

            Working...
            X