Multiple AHI partitions and multiple buffer pools can lead to circular mutex

[Raghavendra D Prabhu]

As the description in lp:331659 notes and comments in lp:791030 mention, if there are multiple innodb_adaptive_hash_index_partitions and multiple innodb_buffer_pool_instances, there can be a long semaphore wait (or a crash if it persists longer).

Comments #3, #5, #11, #12, #13 in lp:791030 and comments #6 onwards in lp:331659 have the details.

Copying few of the details here:

"
I am still able to reproduce it with config in http://sprunge.us/aESA and backtrace (though not just in time) in http://sprunge.us/INjC with:

sysbench --test=./oltp.lua --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-user=root --mysql-password=test --oltp-table-size=30000 --num-threads=32 --max-requests=1000000 --oltp-tables-count=18 run

[Raghavendra D Prabhu (raghavendra-prabhu)]

http://bugs.mysql.com/bug.php?id=66402 looks very much similar to this.

[babine (babine)]

I am having the same problem, here are some variables if that helps :

mysql> show global variables like 'ver%';
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| version | 5.5.32-log |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------+
4 rows in set (0.00 sec)

mysql> show global variables like 'innodb_buffer%';
+---------------------------------------+-------------+
| Variable_name | Value |
+---------------------------------------+-------------+
| innodb_buffer_pool_instances | 4 |
| innodb_buffer_pool_populate | OFF |
| innodb_buffer_pool_restore_at_startup | 60 |
| innodb_buffer_pool_shm_checksum | ON |
| innodb_buffer_pool_shm_key | 0 |
| innodb_buffer_pool_size | 64424509440 |
+---------------------------------------+-------------+
6 rows in set (0.00 sec)

mysql> show global variables like '%adaptive_hash_index%';
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_partitions | 4 |
+---------------------------------------+-------+
2 rows in set (0.00 sec)

I have some more logs available if needed.

[Hui Liu (hickey)]

ps-discuss link: https://groups.google.com/forum/#!topic/percona-discussion/JN524-wlV-8

what we hit recently: https://bugs.launchpad.net/percona-server/+bug/1199213

[Laurynas Biveinis (laurynas-biveinis)]

See bug 1218347. There is a good chance (although not a certainty) that the fix for that bug fixes this issue too. The fix is available in lp:percona-server/5.5 now and will be available in the next 5.5.33.31-2 release.

[Laurynas Biveinis (laurynas-biveinis)]

To everybody who have previously encountered this bug - does it still occur with a recent (i.e. 5.5.34-32.0 or later) Percona Server 5.5 release?

[babine (babine)]

We disabled it on our servers since we encountered the problem. We haven't tried enabling it again, sorry. I'll keep you posted if we do.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2878