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
질문됨 4년 전2939회 조회
1개 답변
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/

답변함 4년 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠