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
