List:General Discussion« Previous MessageNext Message »
From:Dragos CHIRIAC Date:March 2 2012 10:04am
Subject:Metadata Locking issue
View as plain text  
Hi,

I have a question about Metadata Locking.

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). It is the core 
Magento behaviour, and is not actually about magento and their way to 
handle indexing.

I upgraded to 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 
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.

Am am somewhat confused.

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

It is possible to provoke metadata lock using an valid sql on a 
nonexisting table ... This would provoke a  deadlock. Yet ... those 
statements are "cleared" after some random time (ranging from 30 seconds 
to 10 minutes). And I am pretty sure there are no SQL statements related 
to nonexisting tables (there are plenty of truncates, some index alters, 
in the way Magento handles indexing, but no DROPs or ALTER anything 
other than Indexes).

Is a rather unexpected behaviour.

The "bug" (I am not sure is a bug, or an annoying feature) seems to be 
related to
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

I am here to ask if setting (especially for the connections that do DDL 
statements)  autocommit to false and a small lock_wait_timeout will help 
to avoid this metadata locks. Or is there any workaround to limit the 
number of deadlocks and metadata locks (other than, of course, not using 
magento) ? I already disabled A LOT of "features".

If anyone sees something that I missed ... or can point me in the right 
direction to see WHY do i get metadata locks that take so much time to 
clear, plese help.

Thanks in advance.

D.
Thread
Metadata Locking issueDragos CHIRIAC2 Mar