PMP on demand revealed one of reasons why we’ve been seeing ‘Opening tables’ during proper operations, not just during startup (see my previous post on this topic).
We had a thousand or so threads waiting on LOCK_open, and the only thread holding the mutex was this darling:
Thread 902 (Thread 1637624128 (LWP 22113)): #3 mutex_spin_wait (mutex=0x2aaaac3232b8, file_name=0x8b3bf7 "trx0trx.c", line=220) at sync0sync.c:565 #4 trx_allocate_for_mysql #5 ha_innobase::allocate_trx #6 check_trx_exists #7 ha_innobase::info #8 ha_innobase::open #9 handler::ha_open #10 openfrm #11 open_unireg_entry #12 open_table #13 open_tables #14 open_and_lock_tables #15 mysql_insert
So, kernel mutex, which is quite contended, will escalate to LOCK_open on table open, which will block all queries from happening on the server. Fix? Nearly same code as previous “Opening tables” fix – don’t call ha_innobase::info() as part of open(), or move the transaction establishment code outside of info().
2 thoughts on “Opening tables v2!”
I opened a bug — http://bugs.mysql.com/bug.php?id=51557
PMP is the best — http://mituzas.lt/2009/02/15/poor-mans-contention-profiling/
There is an old patch (you can search for it on the bk commits list) which safely releases the LOCK_open mutex before calling the storage engines ::open method. The patch was primarily for fixing DOS scenerio when using the Federated storage engine but it would be equally applicable in this case.
Comments are closed.