Very high btr_search_latch after upgrading from 5.6.40 to 5.6.44

0

Hello,

We have updated our RDS MySQL instance from 5.6.40 to 5.6.44 alongside the CA update.

We have Performance insights enabled and we can see, for a 4 vCPU instance (r4.xlarge), spikes up to 107 AAS for synch/rwlock/innodb/btr_search_latch.

We don't know if this is related to the upgrade or not has we didn't have Performance Insights enabled previously.

What can be the cause, how can we decrease this?

Thank you

Edited by: mimss on Jan 20, 2020 6:42 AM

Edited by: mimss on Jan 20, 2020 7:13 AM

mimss
asked 4 years ago2915 views
1 Answer
0

That statistic is related to the innodb adaptive hash index https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html

It's a performance optimization which dynamically builds hash indices to supplement the regular btree indices whenever MySQL notices a certain pattern of queries. Unfortunately, in 5.6 it takes a global lock when doing this which causes spikes like you're seeing.

You can turn it off by setting innodb_adaptive_hash_index to 0 in your db param groups. It's dynamically settable so you can experiment quite easily to see the effect with it on/off. If you upgrade to 5.7, you can tweak an additional parameter call innodb_adaptive_hash_index_parts which lets you split it up and avoid global locking.

You can also read more about it here https://www.percona.com/blog/2016/04/12/is-adaptive-hash-index-in-innodb-right-for-my-workload/

answered 4 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions