All queries are stuck in "opening table" state
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
Critical
|
Unassigned | ||
5.1 |
Invalid
|
Undecided
|
Unassigned | ||
5.5 |
Fix Released
|
Critical
|
Unassigned |
Bug Description
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_definitio
innodb_open_files = 4000
max_connections
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.
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(). s_lock( &buf_pool- >page_hash_ latch) in buf_page_get_gen(). &(dict_ sys->mutex) ) in dict_table_get(). mutex_enter( buf_pool) in buf_page_ io_complete( ). &dict_sys- >mutex) in dict_table_ decrement_ handle_ count() . &buf_pool- >zip_mutex) in buf_LRU_ free_block( ). block_mutex) in buf_flush_yield(). &buf_pool- >LRU_list_ mutex) in buf_page_ set_accessed_ make_young( ).
Threads 192, 166, 156, 155 are waiting at rw_lock_
Thread 173 is waiting at mutex_enter(
Thread 167 is waiting at buf_pool_
Threads 165, 161, 160 are waiting at mutex_enter(
Thread 159 is waiting at mutex_enter(
Thread 158 is waiting at mutex_enter(
Thread 157 is waiting at mutex_enter(
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?