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年前2941ビュー
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年前

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ