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
demandé il y a 4 ans2941 vues
1 réponse
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/

répondu il y a 4 ans

Vous n'êtes pas connecté. Se connecter pour publier une réponse.

Une bonne réponse répond clairement à la question, contient des commentaires constructifs et encourage le développement professionnel de la personne qui pose la question.

Instructions pour répondre aux questions