All queries are stuck in "opening table" state

[Nail Kashapov]

Sometimes the server randomly stops processing queries.
In that time there is lots of queries in the processlist. I cannot kill normally the queries and the mysqld process, so I have to do kill -9.
In attachment I'm including output of gdb -ex "set pagination 0" -ex "thread apply all bt" --batch -p $(pidof mysqld)
It runs on 64-bit Ubuntu 11.10 (Oneiric), Percona Server from Percona repositories.

There is about 2500 tables in the databases. And in my.cnf there is options set:
table_open_cache = 4000
table_definition_cache=4000
innodb_open_files = 4000
max_connections=1000
open_files_limit = 65535

/proc/<pid>/limits:
Max open files 65535 65535 files

The workload is not heavy at all, cpu and storage are doing okay.

[Laurynas Biveinis (laurynas-biveinis)]

Thanks for your report

I have only looked at some subset of the XtraDB threads, so I don't know the exact deadlock that caused this, but:

Thread 193 is waiting at buf_pool_mutex_enter(buf_pool) in buf_flush_start().
Threads 192, 166, 156, 155 are waiting at rw_lock_s_lock(&buf_pool->page_hash_latch) in buf_page_get_gen().
Thread 173 is waiting at mutex_enter(&(dict_sys->mutex)) in dict_table_get().
Thread 167 is waiting at buf_pool_mutex_enter(buf_pool) in buf_page_io_complete().
Threads 165, 161, 160 are waiting at mutex_enter(&dict_sys->mutex) in dict_table_decrement_handle_count().
Thread 159 is waiting at mutex_enter(&buf_pool->zip_mutex) in buf_LRU_free_block().
Thread 158 is waiting at mutex_enter(block_mutex) in buf_flush_yield().
Thread 157 is waiting at mutex_enter(&buf_pool->LRU_list_mutex) in buf_page_set_accessed_make_young().

The ones waiting for page_hash_latch() make me think that this is bug 1007268 or bug 1026926. Since buf_flush_yield() is also involved, this could be bug 1038225.

Nail, can you enable innodb_lazy_drop_table if it's not enabled and see if the issue does not repeat? Otherwise, we will release fixes for 1026926 (and probably for the other two) in 5.5.27, once it's released, can you try upgrading to see if that helps?

[Nail Kashapov (nail-kashapov)]

Laurynas,

Thanks, it seems that innodb_lazy_drop_table has solved the issue. I've enabled it 10 hours ago, no problems so far.
The application constantly creates temporary memory tables and that seems was the issue, as mysql has to drop the tables.
I'll let it run for more time and let you know then.

[Nail Kashapov (nail-kashapov)]

Okay, no more freezes after innodb_lazy_drop_table was set.

[Laurynas Biveinis (laurynas-biveinis)]

Threads 192, 166, 156, 155 are waiting at rw_lock_s_lock(&buf_pool->page_hash_latch) in buf_page_get_gen() match the symptoms of bug 1026926. We have fixed that bug and it will be released in 5.5.27-28.1 release. Please let us know if you experience any issues then. Thanks!