Rappelez-vous une analyse du crash du sémaphore MySQL

BA devrait être familier avec InnoDB : l'attente du sémaphore a duré plus de 600 secondes. Nous avons intentionnellement planté le serveur car il semble être bloqué srv_error_monitor_thread. a constaté qu'il y a un verrou qui a été bloqué pendant plus de 600 secondes, si le verrou n'est pas libéré après 10 détections consécutives, une panique sera déclenchée pour empêcher le service de continuer à se bloquer.

Que s'est-il passé

Numéro de version : MySQL 5.5.40

Le journal continue de générer des threads en attente Verrouillage du dictionnaire de données, l'emplacement est dict0dict.c ligne 305, le temps d'attente dépasse 900 s.

Le fil qui maintient le verrou est 139998697924352, ce qui en hexadécimal est 7f53fca8a700.

- Thread 139998393616128 a attendu sur dict0dict.c ligne 305 pour 934.00 secondes le sémaphore: X-Lock sur RW-Latch au 0x105a1b8 créé dans le fichier dict0Dict.c Ligne 748a Écrivain (id thread 139986979243 52 52 ) l'a réservé en mode exclusifnombre de lecteurs 0, indicateur de serveurs 1, mot_verrouillage : 0Dernière lecture verrouillée dans le fichier dict0dict.c ligne 302Dernière écriture verrouillée dans le fichier /pb2/build/sb_0-13157587-1410170252.03/rpm/BUILD/mysql- 5.5 .40/mysql-5.5.40/storage/innobase/dict/dict0dict.c ligne 305

Verrouillez les informations de transaction du thread 139998697924352 et interrogez l'opération de la table du dictionnaire de données .

---TRANSACTION 0, la mise à jour des statistiques de la table n'a pas commencé : identifiant de thread MySQL 14075, descripteur de thread du système d'exploitation 0x7f53fca8a700, identifiant de requête 110414021 21.14.5.139 jzjkusrSELECT ROUND(SUM(DATA_LENGTH+INDEX_LENGTH+DATA_FREE )/1 024 /1024/1024) AS MY_DB_TOTAL_SIZE FROM information_schema.TABLES

Vérifiez si le thread détenant le verrou 139998697924352 attend d'autres verrous.

Fil trouvé 139998697924352, l'auto-verrouillage est dans la ligne 1134 de btr0sea.c, cette structure de verrouillage est liée à AHI.

--Le thread 139998697924352 a attendu à la ligne 1134 de btr0sea.c pendant 934,00 secondes le sémaphore:X-lock (wait_ex) sur le verrou RW à 0x1eb06448 créé dans le fichier btr0sea.c ligne 178a écrivain (thread id 139998697924352) l'a réservé en mode attente exclusif nombre de lecteurs 1, indicateur des serveurs 1, mot_verrouillage : ffffffffffffffffDernière lecture verrouillée dans le fichier btr0sea.c ligne 1057Dernière écriture verrouillée dans le fichier /pb2/build/sb_0-13157587-1410170252.03/rpm/BUILD /mysql-5.5.40/mysql-5.5.40/storage/innobase/btr/btr0sea.c line 1134

Regardez ensuite dans quelles fonctions se trouvent les deux structures de verrouillage :

dict0dict.c ligne 305 dans la fonction dict_table_stats_lock

btr0sea.c ligne 1134 dans la fonction btr_search_drop_page_hash_index

Dans quelles circonstances ces fonctions seront-elles appelées ?

Activez innodb_table_monitor et appelez dict_table_stats_lock pour appliquer le verrouillage X lors de la sortie des journaux. Ce cas n'est pas activé.

Lorsque innodb_stats_on_metadata est activé, l'interrogation de la table du dictionnaire de données déclenchera l'opération de mise à jour des informations statistiques et le verrou X sur dict_table_stats_lock sera appelé. Cela correspond aux informations de transaction du thread détenant le verrou.

L'index de hachage adaptatif (AHI) est une structure de table de hachage utilisée par InnoDB pour accélérer la recherche de pages d'index. Lorsque le nombre de visites de page répond à certaines conditions, l'adresse de cette page sera stockée dans une table de hachage pour réduire le coût des requêtes B-tree.

MySQL version 5.5 AHI utilise le verrou global btr_search_latch pour maintenir la cohérence des modifications de la table de hachage.

L'état du pool de tampons InnoDB montre que le tampon libre reste fondamentalement à 0 inactif. Lorsque le pool de mémoire tampon InnoDB expulse une page de données, il appelle la fonction btr_search_drop_page_hash_index pour effacer la page de données de l'AHI.

--------------------------------PISCINE TAMPON ET MÉMOIRE ----- --- ---------------------Mémoire totale allouée 17582522368 ; dans pool supplémentaire alloué 0

Mémoire du dictionnaire AlLocated 4289681

TAILLE du pool de tampons 1048576

Tampons GRATUITS 0

Pages de base de données 1040831

Anciennes pages de base de données 384193

Pages de base de données modifiées 0

Résumé

Le verrou global btr_search_latch d'AHI est souvent un point chaud de concurrence et affecte les performances. Il a été amélioré après la version 5.7 et est divisé en plusieurs instances comme le tampon InnoDB. Dans ce cas, lorsque le paramètre Innodb_stats_on_metadata est activé, les informations statistiques sont mises à jour lors de l'interrogation des informations de métadonnées et le dictionnaire de données est verrouillé, ce qui bloque un grand nombre d'opérations commerciales. De plus, en raison d'un espace de pool de mémoire tampon insuffisant, la table est expulsée. anciennes pages et déclenche la compétition de verrouillage btr_search_latch d'AHI, ce qui conduit finalement à l'expiration du délai de sémaphore et à son crash.

>> Exaspération des œufs de Pâques. Avec l'aide des trois armes magiques sed, awk et grep, bien que l'efficacité ait été améliorée, elle n'est toujours pas assez efficace. Pour être paresseux, j'ai écrit un petit programme pour aider le DBA à régler rapidement ces relations.

Son utilisation est la suivante :

hongbin@MBP ~> mysqldba doctor -f /Users/hongbin/workbench/mysqld_safe.log

Version cible, recherchez la version correspondante en vérifiant le code :

Version de MySQL Server : '5.7.16-log'

Le nombre de crashs du sémaphore qui apparaissent dans le journal et le nombre de démarrages de MySQL. Si le nombre de démarrages est supérieur au nombre de crashs, cela peut être dû à un démarrage normal ou à d'autres crashs :

*****. ******* Nombre de démarrages du service MySQL ******* ***

Crash du sémaphore MySQL -> 3 fois ["2018-08-13 23 : 12:18" "2018-08-14 12:13:43" "2018 -08-16 13:42:36"]

Démarrage du service MySQL -> 3 fois [ "2018-08-13 23:12:59" "2018-08-14 12:15:20" "2018-08-16 13:46:37"]

Quels loquets RW les fils de discussion attendent-ils principalement ? Le contenu comprend : la position du verrouillage, le nombre d'occurrences, l'identifiant du fil de discussion (heures d'occurrence), concentrez-vous sur ceux qui apparaissent le plus souvent :

******* ***** Quel thread a attendu le verrouillage **********row0purge.cc:861 - > 1)trx0undo.ic:171 -> 1 140617682765 568:(1)ha_innodb.cc:14791 -> 8:(57) 140477029533440:(56) 140617407219456:(55 ) 140477035656960:( 52) 140477035124480:(29) 140477108467456:(29) 140477025539840:(26) 140477031130880:(25) 0:(22) 140617634944768:(21) 140617634146048:(21) 140477019948800:(21) 140477026604800:(20 ) 140477022078720:( 18) 140477018883840:(16) 140477038585600:(15) 140477028734720:(10) 140477022877440:(9) :(1) 140477031663360:(1)srv0srv.cc:1968 -> 208 140477276993280:(185) 140617714235136:(23)ha_innodb.cc:5510 -&g t; (52) 140617395771136:(50) 140617636275968:(45) 140617632548608:(40) 140617634146048 :(33) 1406176396756 48 :(32) 140617397102336 :(28) 140617639409408 :(23) 140617635743488 :(21) (18) 140617638610688 :(16) 140617399232256 :(12) 140617638344448 :(10) 140617638078208 :(10 () 3) 140617402693376 :(2) 140477037254400 :(1)dict0dict.cc:1239 -> 136 140477122623232:(50) 140617392842496:(35) 140202726487808:( 26) 140477123688192:(12) 140477038851840:(5) 20 :( 4) 140617634412288 :(4)row0trunc.cc:1835 -> 1 140477109798656 :(1)

Quels fils d'écriture détiennent X verrous pour les verrous ci-dessus, concentrez-vous sur ceux qui apparaissent le plus souvent :

************ Quels fils de discussion d'écrivain bloquent à ************

140616681907968 -> 1 trx0undo.ic:171:(1)140477173069568 -> cc:1968:(185) row0purge.cc:861:(57) row0trunc.cc:1835:(1)140617682765568 -> 29 srv0s rv.cc: 1968:(23) ha_innodb.cc:5510:(5) row0purge .cc:861:(1)

Écrivez les informations de transaction correspondant au fil de discussion. Il peut également y avoir des enregistrements de journal qui ne génèrent pas d'informations de transaction :

**. ******** Ces threads d'écriture trx état **********ID de thread MySQL 83874, descripteur de thread du système d'exploitation 140477173069568, identifiant de requête 13139674 10.0.1.146 aml suppression des tables de référence

Statistiques sur le verrouillage S détenu par les fils de discussion de l'écrivain :

****Ces fils de discussion de l'écrivain lisent pour la dernière fois verrouillés ****

140477173069568 -> 243 row0purge.cc:861:(243)140617682765568 -> 24 row0purge.cc:861:(24)140616681907968 -> Statistiques sur les fils d'écriture contenant des verrous X :

****Ces fils d'écriture écrivent pour la dernière fois verrouillés ****

140477173069568 -> 243 dict0stats .cc:2366:(243)140617682765568 -> 24 dict0stats.cc:2366:(24)140616681907968 -> 1 b uf0flu.cc:1198:(1)

Pendant l'événement Lors de l'analyse du journal, il est possible que les informations de transaction du thread ne soient pas sorties dans le journal et qu'il soit impossible de connaître les opérations spécifiques effectuées par la transaction. En réponse à cette situation, le mini-programme a ajouté la possibilité de collecter des informations sur la transaction pendant la transaction.

L'utilisation est la suivante :

hongbin@MBP ~> mysqldba -uxxx -pxxx doctor -w

Il surveillera le mysql cible pour Journal des erreurs, une fois que le mot-clé "un écrivain (identifiant de thread 140616681907968) l'a réservé en mode" apparaît, interrogez les informations de transaction dans ps et enregistrez-les.

Ce qui précède n'est qu'une utilisation du mini programme. En tant que mini programme servant DBA, d'autres fonctions vous attendent. N'hésitez pas à partager vos réflexions avec moi.

Pour plus d'articles techniques liés à SQL, veuillez visiter la colonne

Tutoriel SQL

pour apprendre !

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!