List:Bugs« Previous MessageNext Message »
From:Dragos CHIRIAC Date:March 6 2012 3:27pm
Subject:Mysql Metadata Locking bug ?
View as plain text  
I have some issues with MySQL 5.5.20 ().

I know "If the server acquires metadata locks for a statement that is 
syntactically valid but fails during execution, it does not release the 
locks early". But this is not my problem. My problem is that I have long 
time (2-10 minutes) of metadata lock on certain tables that do not seem 
to have any active (insert/update) transaction or DDL statemets on them 
(at least not in the innodb engine or processlist).

This only happens on heavy load.

First of all I would like to know how do I find the transactions that 
create the metadata lock.
They do not show up in the "show engine innodb status\G".

The  :
select * from performance_schema.rwlock_instances where 
write_locked_by_thread_id is not null or read_locked_by_count is not null;
select * from performance_schema.mutex_instances where 
locked_by_thread_id is not null;
Are (almost) always empty even with 500 queries waiting for the metadata 
lock.

(I guess this an expected behaviour, as the metalocks are not actually 
part of the innodb engine).

Also "A metadata lock on a table prevents changes to the table's 
structure." But I have hundreds of :

61034 | xxx | 192.168.1.240:55481 | xxx | Query   |  832 | Waiting for 
table metadata lock | SELECT DISTINCT `e`.`attribute_set_id` FROM 
`catalog_product_entity` AS `e`
  INNER JOIN `catalog_pro |         0 |             0 |         1 |
| 61035 | xxx | 192.168.1.240:55483 | xxx | Query   |  832 | Waiting for 
table metadata lock | SELECT DISTINCT `e`.`attribute_set_id` FROM 
`catalog_product_entity` AS `e`
  INNER JOIN `catalog_pro |         0 |             0 |         1 |

piling up during the infamous metalock.

So basically the medatada lock prevents me from "SELECT-ing". Is this 
the intendend behaviour ? (should this be a full/exclusive lock, or a 
read-lock ?)

I attach the output of show processlist and innodb status. I am puzzled, 
as I have many SELECTs/tables that are "Waiting for table metadata lock" 
that should not have any kind of metadata lock.

Maybe you can shed some light into this issue, and sorry for the size of 
the file attached , but as I said , it only happens under load. Maybe 
show me WHAT statements create the metadata lock ... so I can avoid 
locking ... at least the reporting (SELECT) part.

Seems related to :
http://bugs.mysql.com/bug.php?id=60563
http://bugs.mysql.com/bug.php?id=61935
Except that this basically freezes my database for minutes for most of 
the usual and basic operations.

The my.cnf is my-huge.cnf, innodb enabled, and innodb_buffer_pool_size = 
7G out of 12 (if it helps).

[not sure if rellevant]
Some say that this kind of bahaviour can be caused also by metadata 
corruption (broken tables, that still work or smth like that). There is 
anedoctal evidence that dropping/reimporting the database, or running 
certain (3rd party) repair tools fix this kind of behaviour [1]. If that 
is the case ... how do I find and "repair" the said tables ... ( 
probably this will be the next "dead chicken[2]" that i'll wave, as i'm 
out of ideas ) .

D.

PS: (As you might have guessed, the app that uses the DB is a magento 
installation)
[1] http://www.magentocommerce.com/boards/viewthread/269086/#t375209
[2] http://www.science.uva.nl/~mes/jargon/w/waveadeadchicken.html :)


Attachment: [application/x-gzip] mysql.txt.tar.gz
Thread
Mysql Metadata Locking bug ?Dragos CHIRIAC6 Mar
  • Re: Mysql Metadata Locking bug ?Sinisa Milivojevic6 Mar