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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
