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
preguntada hace 4 años2941 visualizaciones
1 Respuesta
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/

respondido hace 4 años

No has iniciado sesión. Iniciar sesión para publicar una respuesta.

Una buena respuesta responde claramente a la pregunta, proporciona comentarios constructivos y fomenta el crecimiento profesional en la persona que hace la pregunta.

Pautas para responder preguntas